June 2, 2008 at 7:21 am
Okay, this is a strange problem, so I'll try and describe it the best I can.
I am working on a system that has users and user-defined groups. Each user can add a record to the database and then define which groups or users they want to have access to it. And the user can change/modify that access at any time. Also, each user can define as many groups as they want and associate new users to themselves at any time.
For example, say user 1 has four other users associated to them (users 2, 3, 4 & 5). User 1 defined users 2 & 3 into 'group 1' and also defined users 4 & 5 defined into 'group 2'. (again the user can change groups or users associated to them at anytime)
User 1 inserts an item record (say the Item_ID is 347) and defines that they want 'group 1' and user 5 to have access to it.
There is the scenario. Hopefully that made sense!
So, there are a few ways I've thought about implementing it.
1 - Have another table called Permissions. That table would look like this:
Item_ID
User_OR_Group_ID
Is_Group
So from my example above, two records would be inserted into this table. (347, 1, true) and (347, 5, false) This says that group 1 and user 5 have access to this record.
Now, the real problem comes when it is time to access those records. When user 2, 3 or 5 log in they should see the record. But when user 3 or 4 log in, they will not see the record.
I thought about writing a function (like UserHasAccess) that would determine if that user has access to the record. So when user 2 logs in, it would run a select statement like this:
Select * From Items
where UserHasAccess(2, Item_ID)
That function would run a select statement on the Permissions table like this:
Select count(*) From Permissions
where item_ID = @itemID and
user_or_group_ID = 2 and (that would be a variable)
is_group = false
Then run a second query in the function to check group membership
Select count(*) From Permissions
where item_id = @itemID and
is_group = true and
user_or_group_ID in (Select group_ID from groups where user_ID = 2)
2 - The other option I've thought of is to include a coma separated list of the allowed users/groups as a field on the item itself. But then parsing through that one field in SQL will probably be more CPU intense then calling my function from within a stored procedure.
My biggest concern is performance. If I go with the first solution, and user 1 has 10,000 items, that could take a VERY long time for user 2 to get the records back.
Any thoughts?
June 2, 2008 at 7:38 am
This is a pretty common issue. Unfortunately, SQL Server does not have data-level permissions like this. There are lots of ways to implement it and you should read up on the options. Both of your options can be performance problems or maintenance issues.
First, if you use a UDF in a WHERE clause of your query, you will get a table scan. This is because the function needs to be resolved for every row, so having an index will not help you.
In your second scenario, if you have to parse the security field, or even lead your search with a wildcard, you will have the same table scan issues. Even worse, if a user is added and you need to apply permissions for this user, you have a nightmare of an update to perform. In addition, you have some fixed number of users that you cna have before your permissions field is used up unless you have a MAX field or something that will be a bit harder to access.
So, you should have actual tables that cross reference your security access to each record. Something with RecordID and UserID or GroupID. If you resolve this down to a UserID, you can create views in the database that include the security join. So you would have something like:
CREATE VIEW dbo.Customers
AS
SELECT C.*
FROM UnderlyingTables.Customers C
INNER JOIN UnderlyingTables.CustomersSecurity S ON C.CustomerID = S.CustomerID
INNER JOIN UnderlyingTables.Users U ON S.UserID = U.UserID
WHERE U.UserName = SUSER_SNAME()
You can then make the views updatable or use INSTEAD OF triggers to manage updates and inserts.
This big advantage of handing this with views is that you make the security model transparent to the tools you are using. Suddenly, if the user runs a reporting services report with their credentials, a report against Customers will only display their data. You can then secure the actual tables so they end up completely unseen to the users.
This is one of many options. And it has holes (like what if a user has access to a child record but not a parent record in a FK relationship). There are lots of available options, this is not a unique problem. Research a bit and you will find other ways of doing this.
June 2, 2008 at 7:43 am
Thanks for all the good info!
Sounds like I have a LOT more research to do!
June 2, 2008 at 12:41 pm
So, I did more research and also a little bit of testing. Here is a working query that does what it is suppose to. Does it look too off-the-wall?
For completeness, I'm using 4 tables here. The Groups and Group_Members table are just for the user defined groups and are pretty simple. The Blog_Entries table is pretty simple too, and here is the Permissions table:
Permissions_ID
Blog_ID, (the post ID)
User_ID, (the user who is permitted to view the post)
Group_ID (the group who is permitted to view the post)
-- Create a new temp table for the groups that this member belongs to
declare @userGroups TABLE (
group_ID int NOT NULL)
-- Load the calling user's group memberships into the temp table
INSERT into @userGroups
SELECT Groups.group_id
FROM Groups grps INNER JOIN Group_Members members ON
grps.group_id = members.group_id
where grps.user_id = @ownerID and
members.user_id = @callingUserID
SELECT * -- TODO: only retrieve needed fields
FROM Blog_Entries blog LEFT JOIN Permission perm ON
(perm.Blog_ID = blog.Blog_ID AND
( perm.User_ID = @userID OR
perm.Group_ID IN (SELECT group_ID FROM @userGroups)
)
)
WHERE blog.user_ID = @ownerID AND
(
perm.Permission_ID > 0 OR -- the calling user has permissions to the post
blog.Allow_everyone = 1 OR -- post marked so everyone has access to it
blog.user_ID = @callerUserID -- the owner can always view all their posts
)
Like I said, this works fine, but does it look too off-the-wall?
June 2, 2008 at 1:26 pm
That's looking somewhat reasonable. The sub-queries and OR's in it may give you some performance issues, but this is looking along the lines I would start. It will take some testing and tuning to get it right.
June 2, 2008 at 1:28 pm
Great. Thanks for looking! 🙂
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply