Oh, cool! On Thu, Sep 23, 2010 at 5:31 PM, Jeffrey Becker <[email protected]>wrote:
> In that case, the view will exhibit correct behavior for any number of > hierarchical groups. > > On Thu, Sep 23, 2010 at 11:16 AM, Ayende Rahien <[email protected]> wrote: > > Yes, it si > > > > On Thu, Sep 23, 2010 at 3:42 PM, Jeffrey Becker < > [email protected]> > > wrote: > >> > >> It appears that UsersGroupsHierarchy already encodes this information. > >> > >> Running: > >> authorizationRepository.CreateUsersGroup("Root"); > >> authorizationRepository.CreateChildUserGroupOf("Root", "child1"); > >> authorizationRepository.CreateChildUserGroupOf("Root", "child2"); > >> authorizationRepository.CreateChildUserGroupOf("child1", > "child3"); > >> authorizationRepository.CreateChildUserGroupOf("child2", > "child4"); > >> > >> > >> results in UsersGroupsHierarchy containing: > >> > >> AncestorName, DescendantName > >> Root, child1 > >> Root, child2 > >> Root, child3 > >> Root, child4 > >> child1, child3 > >> child2, child4 > >> > >> Is this correct behavior expected? > >> On Wed, Sep 22, 2010 at 8:44 AM, Jeffrey Becker > >> <[email protected]> wrote: > >> > I will work on a patch and send a pull request when I'm at a place I > >> > feel comfortable. > >> > > >> > On Wed, Sep 22, 2010 at 8:31 AM, Ayende Rahien <[email protected]> > >> > wrote: > >> >> Sounds good, yes. > >> >> > >> >> On Wed, Sep 22, 2010 at 1:13 PM, Jeffrey Becker > >> >> <[email protected]> > >> >> wrote: > >> >>> > >> >>> How would you feel about introducing a table: > >> >>> > >> >>> Create Table UsersGroupsAncestry > >> >>> ( > >> >>> Id uniqueidentifier not null PRIMARY KEY, > >> >>> AncestorGroupId uniqueidentifier not null references > >> >>> UsersGroups(Id), > >> >>> DescendantGroupId uniqueidentifier not null > references > >> >>> UsersGroups(Id), > >> >>> Depth int not null > >> >>> ) > >> >>> > >> >>> Which would store the full ancestry of any given UsersGroup. This > >> >>> would allow hierarchies to be queried as > >> >>> > >> >>> Select > >> >>> ug.[UserId] as [User] , (p.[Level] * 10) + (case when > p.Allow > >> >>> = 0 > >> >>> then 2 else 1 end) as security, o.Name as Operation, > >> >>> p.EntitySecurityKey, p.EntityTypeName > >> >>> from security_Permissions p > >> >>> inner join security_Operations o on p.Operation = o.Id > >> >>> Inner Join security_UsersGroupsAncestry uga on > >> >>> uga.AncestorGroupId > >> >>> = > >> >>> p.UsersGroup > >> >>> Inner Join security_UsersToUsersGroups ug on > >> >>> uga.DescendantGroupId > >> >>> = > >> >>> ug.GroupId > >> >>> where [UsersGroup] IS NOT NULL and EntitySecurityKey IS NOT > >> >>> NULL > >> >>> > >> >>> This adds some complexity to the maintenance of groups hierarchies > but > >> >>> would be cross-database. > >> >>> > >> >>> On Wed, Sep 22, 2010 at 1:49 AM, Ayende Rahien <[email protected]> > >> >>> wrote: > >> >>> > Looking at this, it looks like it has one issue, and that is the > >> >>> > support > >> >>> > for > >> >>> > hierarchies. > >> >>> > It only support two levels. > >> >>> > However, the perf implications are pretty important. > >> >>> > The other approach that I have been toying about is introducing a > >> >>> > custom > >> >>> > CLR > >> >>> > type, which would allow us to store a list inside a single column, > >> >>> > which > >> >>> > would mean giving up a lot of the correlated sub queries that we > >> >>> > have > >> >>> > here. > >> >>> > > >> >>> > On Wed, Sep 22, 2010 at 12:35 AM, Jeffrey Becker > >> >>> > <[email protected]> wrote: > >> >>> >> > >> >>> >> 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]<rhino-tools-dev%[email protected]> > . > >> >>> >> For more options, visit this group at > >> >>> >> http://groups.google.com/group/rhino-tools-dev?hl=en. > >> >>> >> > >> >>> > > >> >>> > -- > >> >>> > 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]<rhino-tools-dev%[email protected]> > . > >> >>> > For more options, visit this group at > >> >>> > http://groups.google.com/group/rhino-tools-dev?hl=en. > >> >>> > > >> >>> > >> >>> -- > >> >>> 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]<rhino-tools-dev%[email protected]> > . > >> >>> For more options, visit this group at > >> >>> http://groups.google.com/group/rhino-tools-dev?hl=en. > >> >>> > >> >> > >> >> -- > >> >> 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]<rhino-tools-dev%[email protected]> > . > >> >> For more options, visit this group at > >> >> http://groups.google.com/group/rhino-tools-dev?hl=en. > >> >> > >> > > >> > >> -- > >> 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]<rhino-tools-dev%[email protected]> > . > >> For more options, visit this group at > >> http://groups.google.com/group/rhino-tools-dev?hl=en. > >> > > > > -- > > 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]<rhino-tools-dev%[email protected]> > . > > For more options, visit this group at > > http://groups.google.com/group/rhino-tools-dev?hl=en. > > > > -- > 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]<rhino-tools-dev%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/rhino-tools-dev?hl=en. > > -- 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.
