Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hdv212
Posting Yak Master
140 Posts |
 Posted - 2010-04-01 : 19:16:29
|
| Hii have 3 tables which hold some security information in my app, here is my tables :Roles(RoleID, RoleName) to hold user roles like Admin, Contributer, Viewer, etc.Objects(ObjectID, ObjectName) to hold all objects which i want to assign security to, like Contacts, Persons, Settings, etc.Actions(ActionID, ActionName) to holds action types like Insert, Update, Delete, Select, etc.Permissons(PermissonID, RoleID, ObjectID, ActionID) to hold information about which role can do which action on which object.my question is that how to give a table to user like this :Header : Objects | Insert | Update | Delete | Select | ...Values : Object1 | True | False | True | True | ... Object2 | False | False | False | True | ... ObjectN | True | False | True | True | ...this table is something like pivot tables between Objects, Actions and Permissons tables which filtered by RoleID.how can i do this ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-02 : 02:34:27
|
its easy, see an illustration below--temporary tables and data for illustration( you dont need to worry about this part)DECLARE @Objects table(ObjectID int identity(1,1), ObjectName varchar(100)) --to hold all objects which i want to assign security to, like Contacts, Persons, Settings, etc.DECLARE @Actions table(ActionID int identity(1,1), ActionName varchar(100)) --to holds action types like Insert, Update, Delete, Select, etc.DECLARE @Permissons table(PermissonID int identity(1,1), RoleID int, ObjectID int, ActionID int)insert @Objects (ObjectName)values ('Contacts'),('Persons'),('Settings')Insert @Actions (ActionName)values ('Insert'),('Update'),('Delete'),('Select')INSERT @Permissons (RoleID , ObjectID , ActionID)VALUES(1,1,1),(1,1,3),(1,1,4),(2,2,4),(2,3,1),(2,3,3),(2,3,4)--Actual solution (this is what you need to apply for your table dataSELECT ObjectName,CASE WHEN [Insert]> 0 THEN 'True' ELSE 'False' END AS [Insert],CASE WHEN [Update]> 0 THEN 'True' ELSE 'False' END AS [Update],CASE WHEN [Delete]> 0 THEN 'True' ELSE 'False' END AS [Delete],CASE WHEN [Select]> 0 THEN 'True' ELSE 'False' END AS [Select]FROM(SELECT o.ObjectName, a.ActionName FROM @Permissons pJOIN @Objects oON o.ObjectID = p.ObjectIDJOIN @Actions aON a.ActionID=p.ActionID)mPIVOT (COUNT(ActionName) FOR ActionName IN ([Insert],[Update],[Delete],[Select]))poutput---------------------ObjectName Insert Update Delete SelectContacts True False True TruePersons False False False TrueSettings True False True True------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
hdv212
Posting Yak Master
140 Posts |
Posted - 2010-04-02 : 05:40:46
|
| Many thanks visakh16it's very helpful for me, also i modified your suggested query to filter resultSet by given roleID, but there is problem in this query :it does not display all objects in result set if given roleID does not have any permisson on specific object, wheras i want to display all objects in the result set to give user that enable or disable permisson of each object that want to.here is my query :SELECT ObjectName,CASE WHEN [Insert]> 0 THEN 'True' ELSE 'False' END AS [Insert],CASE WHEN [Update]> 0 THEN 'True' ELSE 'False' END AS [Update],CASE WHEN [Delete]> 0 THEN 'True' ELSE 'False' END AS [Delete],CASE WHEN [Select]> 0 THEN 'True' ELSE 'False' END AS [Select]FROM(SELECT o.ObjectName, a.ActionName FROM Permissons pJOIN Objects oON o.ObjectID = p.ObjectIDJOIN Actions aON a.ActionID=p.ActionIDwhere p.RoleID = 2)mPIVOT (COUNT(ActionName) FOR ActionName IN ([Insert],[Update],[Delete],[Select]))pi use cross join to display all objects in the result set but i don't get correct result. how to solve this issue ?thanks in advance |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-02 : 05:45:48
|
| sorry I'm not clear. Can you illustrate your problem with some sample data as i did above?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
hdv212
Posting Yak Master
140 Posts |
Posted - 2010-04-02 : 07:15:48
|
| Sure ...I have 2 objects in Objects table as follow :ObjectID ObjectName1 FrmContactItem2 FrmContactsReportIf i use above query for roleID=2, i get this result :ObjectName Insert Update Delete SelectFrmContactItem False True False FalseFrmContactsReport True False False TrueAnd if i use the same query for roleID=1, i got this result :ObjectName Insert Update Delete SelectFrmContactsReport False True False TrueAs u can see in two above resultSet, the object named 'FrmContactItem' does not exists in second resultSet because roleID=1 does not have any permisson on object 'FrmContactItem' (means there is no record in Permissons table where roleID=1 and objectName='FrmContactItem'), hence the object 'FrmContactItem' does not display in second resultSet, wheras i want to display all objects in the resultSet whether the given RoleID have or have not permisson on objects.i hope u understand what i want.thanks in advance |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-02 : 11:08:36
|
ok. in that case you need to start from Objects table and left join everything else from it. like:-SELECT ObjectName,CASE WHEN [Insert]> 0 THEN 'True' ELSE 'False' END AS [Insert],CASE WHEN [Update]> 0 THEN 'True' ELSE 'False' END AS [Update],CASE WHEN [Delete]> 0 THEN 'True' ELSE 'False' END AS [Delete],CASE WHEN [Select]> 0 THEN 'True' ELSE 'False' END AS [Select]FROM(SELECT o.ObjectName, a.ActionName FROM @Objects o LEFT JOIN @Permissons pON o.ObjectID = p.ObjectIDAND p.RoleID= @YourPassedRoleIDvalueLEFT JOIN @Actions aON a.ActionID=p.ActionID)mPIVOT (COUNT(ActionName) FOR ActionName IN ([Insert],[Update],[Delete],[Select]))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
hdv212
Posting Yak Master
140 Posts |
Posted - 2010-04-02 : 13:25:28
|
| Many thanks visakh16It works goood!thanks again |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-02 : 13:27:36
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Need a complex query in pivot table.