Database Permission Structure

Question

Many of my employers applications share a similar internal permission structure for restricting data to a specific set of users or groups. Groups can also be nested.

The problem we're currently facing with this approach is that enumerating the permissions is incredibly slow. The current method uses a stored procedure with many cursors and temporary tables. This has worked fine for smaller applications, but we now have one particular system which is growing quickly, and it's starting to slow down.

The basic table structure is as follows;

tblUser { UserID, Username, WindowsLogonName }

tblGroup { GroupID, Name, Description, SystemFlag }

tblGroupGroup { GroupGroupID, Name, }

tblGroupUser { GroupUserID, Name, }

and to tie it all together;

tblPermission { PermissionID, SecurityObjectID, SecuredID, TableName, AllowFlag }

which contains rows like..

'5255-5152-1234-5678', '{ID of a Group}', '{ID for something in tblJob}', 'tblJob', 1

'4240-7678-5435-8774', '{ID of a User}', '{ID for something in tblJob}', 'tblJob', 1

'5434-2424-5244-5678', '{ID of a Group}', '{ID for something in tblTask}', 'tblTask', 0

Surely there must be a more efficient approach to enumerating all the groups, and getting the ID's of the secured rows?

To complicate things further; if a user is explicitly denied access to a row then this overrules any group permissions. This is all in MSSQL.

Answer 1

I'm guessing it would be useful to break apart tblPermission into a couple of tables: one for groups and one for users. By having both groups and users in there, it seems to add complexity to the design (and maybe that's why you need the stored procedures).

If you want to break down the tblPermission table (into something like tblUserPermission and tblGroupPermission) but still want a representation of the tables that looks like tblPermission, you can make a view that union's the data from the two tables.

Hope this helps. Do you have examples of what the stored procedures do?

Answer 2

I think you could use a Recursive Common Table Expressions (CTE) hierarchical query. You can find many examples if you search for it. This is one of them.

Answer 3

Perhaps your design is OK, but the implementation/code is wrong.

Some thoughts:

• Are all your ID columns GUID? Not recommended Kimberley L Tripp article
• Indexes on all foreign keys, perhaps with other columns in key or INCLUDE
• Regular maintenance? eg fragmented indexes, stats ot of date etc
• Are all datatypes matching (assumes no FKs): datatype precedence and implicit conversion errors may creep in

Some more schema info and examples of poorly performing code may help