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.
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?