My initial view approach caused the unit tests to fail in ways I was
not sufficiently able to debug. I was able to restructure my application
such that I never executed the sub-query against more than a few hundred
rows. I strongly suspect that performance would improve vastly if I could
figure out how to turn:
Select {fields}
from Entity
where 1 = ({subquery to calculate permissions to this entity})
into
select {fields}
from Entity
where EntitySecurityKey in ({subquery to calculate the set of entities
this user has permission to})
However at this point I my Criteria kung foo is weak.
On Wed, Sep 29, 2010 at 9:33 AM, Kurt Johnson <[email protected]> wrote:
> 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 <[email protected]> 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
> <[email protected]> wrote:
> > > 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]<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.
