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