Migrating a conversation with Ayende per his request: >inline > >On Tue, Sep 21, 2010 at 5:56 PM, Jeffrey Becker <[email protected]> >wrote: >> >> I have a table 'Companies' with hundreds of thousands of rows. I was having >> performance issues asking the question `what is the set of Companies that >> the current user has '/DefaultCompany' permission on`. My security setup has >> a number of Entities Groups into which companies were divided and explicitly >> added. For cases where any given user has access to a small set of >> companies the performance isn’t intolerable. However I have the >> 'Vertmarkets Employees' UsersGroup which has access to all companies. In >> these cases query My proposed solution is to alter the query which gets >> executed from something like: >> >> >> >> Select c.* from Company >> where 1 = ( {correlated subquery to calculate allow for this a row}) >> >> >> >> To something more like : >> >> >> >> Select c.* from Company c >> Inner Join PermissionsFlattened p on c.EntitySecurityKey = >> p.EntitySecurityKey or p.EntitySecurityKey IS NULL >> where p.User = @userId and p.Operation = @operation and p.EntityTypeName = >> @entityTypeName and p.Allow = @allow >> >> The performance of the queries in management studio is radically improved. >> >That is one impressive view. :-) The goal of the view is to materialize all the ways a user can gain access to an entity. As I see it there are 6:
* Entities Given directly to a User * Entities Given directly to a UserGroup to which the user belongs * Entities in an EntityGroup given to a User * Entities in an EntityGroup given to a UserGroup to which the user belongs * Users Given an Operation OnEverything * UsersGroups to which the user belongs Given an Operation OnEverything >Only scanned it so far, but it looks good. >One thing that bothered me is the hierarchies of groups & users. I saw that >you explicitly allowed 2 levels of those. Yes. My knowledge of the Criteria api is somewhere between non-existant and pathetic so I based this view on a reverse-engineering of the AddPermissionsToQuery sql. I missed something here didn't I? >May I suggest that we will take this to the [email protected] >mailing list? > >> >> PermissionsFlattened is a denormalized view of all the permissions in the >> system. The view enumerates all the ways a user can be granted permission to >> an Entity and union-alls them. Initially this worked fine because my use of >> Rhino Security does not utilize the multi-level permission overrides. In >> order to maintain the level-based functionality some changes to my original >> system were clearly needed. The level-based permissions seem to follow the >> following rules: >> >> >> >> Permissions not explicitly allowed are denied. >> Permissions at higher levels override permissions at lower levels. >> At the same level an explicit deny overrides an explicit allow. >> >> > >Yes, that is how it works > >> >> The existing view was renamed to PermissionsFlattenedInner. The Allow >> column was removed and replaced with a column [Security] calculated as such: >> >> >> >> ([Level] * 10) + (case when Allow = 0 then 2 else 1 end) >> >> >> >> This achieves the following: >> >> The numeric values of Allow and Level are merged without loss of information >> The case expression causes the value of an explicit deny to be greater than >> an explicit Allow >> The case expression causes the modulo 2 of the value to accurately reflect >> the value of Allow. >> >> >> >> I then re-created PermissionsFlattened as such: >> >> >> >> create view security_PermissionsFlattened as >> >> Select NEWID() as [Id], [User], Operation, EntitySecurityKey, >> EntityTypeName, MAX([security]) % 2 as [Allow] >> >> from PermissionsFlattenedInner >> >> Group by [User], Operation, EntitySecurityKey, EntityTypeName >> >> >> >> >> >> I am currently working through learning the criteria api and integrating >> this into Rhino Security. If you have any specific guidance on how I should >> approach this aspect of it please let me know. >> >> >> >> ________________________________ >> >> From: Ayende Rahien [mailto:[email protected]] >> Sent: Sunday, September 19, 2010 2:11 AM >> To: Jeffrey Becker >> Subject: Re: Rhino Security performance issues >> >> >> >> Yes, I would certainly like that. >> >> On Fri, Sep 17, 2010 at 3:24 PM, Jeffrey Becker <[email protected]> >> wrote: >> >> I was having some performance issues related to the correlated sub-query >> that gets generated when using AddPermissionsToQuery. I was able to >> introduce a few objects backed by views which seem to have alleviated the >> issue. If you have some time I’d like to discuss my changes with hopes of >> eventually getting them merged back into the project. >> -- You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.
RhinoSecurity_PermissionsFlattenedVew.sql
Description: Binary data
