SQL Server Performance Forum – Threads Archive

Need advice for a row-based permissions model.

Hi Forum, From what I’ve read so far, you guys are great! Lots of good ideas and suggestions shooting around here. I’m currently updating an application that uses row-based permission checking, if that’s the right term… It just means that some users get to see certain rows in the table, while other rows belong to other users/groups. Essentially what the previous developer has done is filtered all results from searches (in sp’s) by user groups. The list of groups changes frequently. The current process ties the group id to each record in the main table. What happens is that the application passes the group or groups to the stored procedure and the sp returns the dataset with the user’s regular search criteria, but also only returns the records that belong to that user’s group. What makes this more complicated is that there is different group TYPES.. like this: PK Data Data IdIn_GroupTypeA IsIn_GroupTypeB IsIn_GroupTypeC
————————————————————————–
1 — — 4 14 52
2 — — 9 14 21 So all the searching sp’s filter the searches like this: if in grouptypeA, filter by groupA
if in grouptypeB, filter by groupB
etc… The list of group TYPES is more or less static, which is why it was done that way. However, since I’m upgrading the system, I’m looking for a better way to handle this. For one thing, there’s no actual security here, as someone can still search for a single row without any permissions being checked… but the effective result is fine with the client.. they aren’t that concerned about it. The application uses one shared account for database access (SQL Server authentication), so there is no list of sql server roles or users set up at the database level. All users and groups are stored in tables. Ideally, I’d like to just be able to pass ONLY the user’s ID into every sp and have the sp handle everything permission based. I’m not sure how to implement this though. I thought of creating a seperate table that contains just the main table’s PK and another column that contains the id of a group that can access that row. Problem is here, that we can end up with a lot of records explaining the permissions for just one row, and there’s going to be a lot of joining going on on these potentially very large tables. For all intents and purposes, we can forget about the group type.. It’s confusing, so I’m going to change the way it works so that each groupID is unique no matter what group type they’re from… so basically we’ll have user’s and groups. A user can belong to multiple groups, which will give her access to more rows. The data rows in the main table need to be tied to the groupid’s in some way… that’s where I get stuck on the best way to do it. Somebody must have experience with this type of thing. Hopefully someone can point me to a sample or document, or just give me an idea of where to go with this. Thanks,
John

Hi John, I presume you are using SQL2000? If so to solve your original problem you could use a table valued function…
create function fn_get_data(
@user_id int
)
returns @table( <cols to be returned> )
as
begin if user in group a
insert @table select <cols to be returned> from <table> where user in groupa
else if user in group b
insert @table select <cols to be returned> from <table> where user in groupb
else if user in group c
insert @table select <cols to be returned> from <table> where user in groupc return
end
now you can do
select * from dbo.fn_get_data( @user_id ) as for a generic best practice…? perhaps depends on the number of groups you’re talking about… If less than 32 (or 64) then I’d do something like add a column to your data table group_list_mask int (or bigint)
add a column to your user table group_list_mask int (or bigint) have a unique int between 0 and 31 (or 63) assigned to each group. If a user belongs to multiple groups sum( power( 2, groupid ) ) gives you the mask (alternatively use the same datatype as group_list_mask and set it to 1, 2, 4, 8, 16, etc. and then sum() it ) then use
select * from data
where exists( select *
from user
where user_id = @user_id
and ( data.group_list_mask & user.group_list_mask ) > 0 )
placing an index on
user( user_id, group_list_mask ) NOTE if you need to use bigint, then ensure that your client software supports bigint too (e.g. .NET but not VB6) NOTE 2 if you need more than 64 groups then you could use binary or varbinary, but I’ve never tried that before…
Cheers
Twan

Just thinking about this on the way home. If I needed more that 64 groups then I’d be tempted to use repeating columns on the data, user and group tables… I think binary is going to be too hard to use efficiently…? ie group_list_mask1, group_list_mask2 on user and data group_mask1, group_mask2 on group with only one of them ever > 0 The statement then becomes select * from data
where exists( select *
from user
where user_id = @user_id
and ( ( data.group_list_mask1 & user.group_list_mask1 ) > 0
or ( data.group_list_mask2 & user.group_list_mask2 ) > 0 ) )
Cheers
Twan

Wow, that sounds like a good plan! It sounds like the "magic number" idea I learned about a few years ago. The idea did occur to me while I was thinking about design, but I wasn’t sure if it applied here. Your code simplifies the idea greatly for me. Thank you. We won’t have more than 64 groups for quite a while, and I can use the multi-column approach like you described if it ever does. This does solve my problem and let me just pass the userID around to filter records. This will make code maintenance much easier. I’m interested to see how it performs compared to the current model. Thanks again! John

Hi John, I’ve used this kind of technique before and as long as you have an index on user_id + the mask field(s) then there is not really a noticable difference from ignoring row-level permissions… Cheers
Twan

Yes, I implemented this for two of the tables on Friday, and I was pleased to see that it didn’t seem to affect performance at all. My manager is happy as well [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. I have an easy question for SQL Server guru though. Right now I’m generating the mask key for the groups by having an autonumber integer column (1-64) that I use as a seed, and the mask key column which is a formula column using the seed column. I’m assuming that there is a way to make a formula so that it knows it’s value is 2^rownum, without having to use this seed column, but I have yet to figure it out.. Thanks again. John

alter table x add group_mask as power( 2, id ) alternatively you could just compute the total mask when a user’s group membership changes…? setting the user’s group_list_mask to the sum( power( 2, id ) ) of the groups that he/she is in… Cheers Twan NOTE that the seed should be 0-63, otherwise subtract 1 from the id in each calculation… 2 to the power of 0 being 1 which is the first mask <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

yup, got that. my table key isn’t an identity column, so i had to add one (the seed). was just wondering if there was another way to do that. the mistake that I was making was setting up a formula column that was set to (power( 2,seedColumn )) to get the mask rather than just assigning the actual value via SQL… i imagine that having a column using a sql formula was probably slower.

Hi ya, yep formula will be slightly slower than an actual value, although in this case the column would only be used when a user changes group membership, which is probably not a frequent event Either way works though, so good luck with it <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

]]>