Jeffrey,
Could you share the improvements that you made?
I would love to use Rhino, but our team is chosen to disable it. They
are not confident with performance.
We saw queries on secured entities taking nearly one second each per
the criteria evaluation subquery. With 48K entities, performance was
horrible. Then, we applied Bart's indexes. The indexes did improve
performance somewhat, but not to a satisfactory level.
Does anyone else have indexing, or other, improvements that they would
care to share?
On Sep 24, 1:31 pm, Jeffrey Becker <jeffrey.a.bec...@gmail.com> wrote:
> My understanding of the Criteria API and how its used in rhino
> security has failed and I've restructured my application so my
> performance is acceptable.On Thu, Sep 23, 2010 at 11:42 AM, Ayende Rahien
> <aye...@ayende.com> wrote:
> > Oh, cool!
>
> > On Thu, Sep 23, 2010 at 5:31 PM, Jeffrey Becker <jeffrey.a.bec...@gmail.com>
> > 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 <aye...@ayende.com> wrote:
> >> > Yes, it si
>
> >> > On Thu, Sep 23, 2010 at 3:42 PM, Jeffrey Becker
> >> > <jeffrey.a.bec...@gmail.com>
> >> > 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
> >> >> <jeffrey.a.bec...@gmail.com> 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 <aye...@ayende.com>
> >> >> > wrote:
> >> >> >> Sounds good, yes.
>
> >> >> >> On Wed, Sep 22, 2010 at 1:13 PM, Jeffrey Becker
> >> >> >> <jeffrey.a.bec...@gmail.com>
> >> >> >> 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 <aye...@ayende.com>
> >> >> >>> 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
> >> >> >>> > <jeffrey.a.bec...@gmail.com> wrote:
>
> >> >> >>> >> Migrating a conversation with Ayende per his request:
> >> >> >>> >> >inline
>
> >> >> >>> >> >On Tue, Sep 21, 2010 at 5:56 PM, Jeffrey Becker
> >> >> >>> >> > <jbec...@vertmarkets.com>
> >> >> >>> >> > 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
> >> >> >>> >> > rhino-tools-dev@googlegroups.com 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:aye...@ayende.com]
> >> >> >>> >> >> 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
> >> >> >>> >> >> <jbec...@vertmarkets.com> 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
> >> >> >>> >> rhino-tools-...@googlegroups.com.
> >> >> >>> >> To unsubscribe from this group, send email to
> >> >> >>> >> rhino-tools-dev+unsubscr...@googlegroups.com.
> >> >> >>> >> 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
> >> >> >>> > rhino-tools-...@googlegroups.com.
> >> >> >>> > To unsubscribe from this group, send email to
> >> >> >>> > rhino-tools-dev+unsubscr...@googlegroups.com.
> >> >> >>> > 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
> >> >> >>> rhino-tools-...@googlegroups.com.
> >> >> >>> To unsubscribe from this group, send email to
> >> >> >>> rhino-tools-dev+unsubscr...@googlegroups.com.
> >> >> >>> 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
> >> >> >> rhino-tools-...@googlegroups.com.
> >> >> >> To unsubscribe from this group, send email to
> >> >> >> rhino-tools-dev+unsubscr...@googlegroups.com.
> >> >> >> 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 rhino-tools-...@googlegroups.com.
> >> >> To unsubscribe from this group, send email to
> >> >> rhino-tools-dev+unsubscr...@googlegroups.com.
> >> >> 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 rhino-tools-...@googlegroups.com.
> >> > To unsubscribe from this group, send email to
> >> > rhino-tools-dev+unsubscr...@googlegroups.com.
> >> > 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 rhino-tools-...@googlegroups.com.
> >> To unsubscribe from this group, send email to
> >> rhino-tools-dev+unsubscr...@googlegroups.com.
> >> 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 rhino-tools-...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > rhino-tools-dev+unsubscr...@googlegroups.com.
> > 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 rhino-tools-...@googlegroups.com.
To unsubscribe from this group, send email to
rhino-tools-dev+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rhino-tools-dev?hl=en.
