My point is that it is likely that denormalizing the data would give even bigger benefit down the road. I am certainly open for tailored solution, though.
On Wed, Feb 4, 2009 at 11:25 PM, Bart Reyserhove <[email protected]>wrote: > I don't know to be honest. I thought it would be a good idea to tailor the > generated query a bit based on how Rhino.Security is used. In our case for > example we have lot of permissions but we do not have a complex user - > usersgroups domain. A user can only belong to one group and there is no > hierarchy in the usersgroups. So we can just add permissions to a query > based on the usersgroup of the user. The big query is then reduced to: > > ELECT TOP 10 THIS_.ID AS ID32_0_, > > THIS_.NAME AS NAME32_0_, > > THIS_.CODE AS CODE32_0_, > > THIS_.ISACTIVE AS ISACTIVE32_0_, > > THIS_.DELETED AS DELETED32_0_, > > THIS_.COMPANYID AS COMPANYID32_0_, > > THIS_.PARENTDEPARTMENTID AS PARENTDE7_32_0_ > > FROM DEPARTMENT THIS_ > > WHERE (THIS_.DELETED = 0) > > AND @p0 = (SELECT TOP 1 THIS_0_.ALLOW AS Y0_ > > FROM SECURITY_PERMISSIONS THIS_0_ > > INNER JOIN SECURITY_OPERATIONS OP1_ > > ON THIS_0_.OPERATION = OP1_.ID > > LEFT OUTER JOIN SECURITY_ENTITIESGROUPS ENTITYGROU2_ > > ON THIS_0_.ENTITIESGROUP = ENTITYGROU2_.ID > > LEFT OUTER JOIN SECURITY_ENTITYREFERENCESTOENTITIESGROUPS ENTITIES7_ > > ON ENTITYGROU2_.ID = ENTITIES7_.GROUPID > > LEFT OUTER JOIN SECURITY_ENTITYREFERENCES ENTITYKEY3_ > > ON ENTITIES7_.ENTITYREFERENCEID = ENTITYKEY3_.ID > > WHERE OP1_.NAME IN (@p1,@p2) > > AND (THIS_0_.USERSGROUP = @p6) > > AND ((THIS_.ID = THIS_0_.ENTITYSECURITYKEY > > OR THIS_.ID = ENTITYKEY3_.ENTITYSECURITYKEY) > > OR (THIS_0_.ENTITYSECURITYKEY IS NULL > > AND THIS_0_.ENTITIESGROUP IS NULL)) > > ORDER BY THIS_0_.LEVEL DESC, > > THIS_0_.ALLOW ASC) > > ORDER BY THIS_.CODE ASC > > The performance pain we were experiencing came from this subquery in the > standard query: > > SELECT this_0_0_.Id as y0_ > FROM security_UsersGroups this_0_0_ > left outer join security_UsersToUsersGroups users4_ > on this_0_0_.Id = users4_.GroupId > left outer join [User] user1_ > on users4_.UserId = user1_.Id > left outer join security_UsersGroupsHierarchy > allchildre6_ > on this_0_0_.Id = allchildre6_.ParentGroup > left outer join security_UsersGroups child2_ > on allchildre6_.ChildGroup = child2_.Id > WHERE ( child2_.Id in > ( > SELECT this_0_0_0_.Id as y0_ > FROM security_UsersGroups this_0_0_0_ > inner join security_UsersToUsersGroups > users3_ > on this_0_0_0_.Id = users3_.GroupId > inner join [User] user1_ > on users3_.UserId = user1_.Id > WHERE user1_.Id > > After looking at it, it turned out that this part is not needed in our > case. > > On Wed, Feb 4, 2009 at 9:22 PM, Ayende Rahien <[email protected]> wrote: > >> No objections from me. >> But, one question. Is it possible to denormalize the table instead? That >> would allow better indexing instead of joins. >> >> >> On Wed, Feb 4, 2009 at 10:18 PM, Bart Reyserhove < >> [email protected]> wrote: >> >>> We have this method in Rhino.Security to add permissions to a query: >>> >>> >>> /// <summary> >>> /// Adds the permissions to query. >>> >>> /// </summary> >>> /// <param name="user">The user.</param> >>> /// <param name="criteria">The criteria.</param> >>> /// <param name="operation">The operation.</param> >>> public void AddPermissionsToQuery(IUser user, string operation, >>> DetachedCriteria criteria) >>> { >>> Type rootType = Commons.NHibernate.CriteriaUtil.GetRootType(criteria, >>> UnitOfWork.CurrentSession); >>> string securityKeyProperty = criteria.Alias + "." + >>> Security.GetSecurityKeyProperty(rootType); >>> ICriterion allowed = GetPermissionQueryInternal(user, operation, >>> securityKeyProperty); >>> >>> You pass it a user, an operation and criteria (detached or not) and it >>> adds permissions to a query. This query works really well if you use all >>> possible features of Rhino.Security: permissions on users, permissions on >>> usergroups, hierarchy of usersgroups... >>> However if your application only puts permissions on usersgroups and does >>> not have an hierarchy the generated query could be more efficient. We ran >>> into a performance issue mainly because we have many permissions. >>> >>> Any objections agains adding an overload that only takes the permissions >>> defined on the passed usersgroup into account? We would probably need a case >>> in which you want to pass a usersgroup but also want the usersgroup >>> hierarchy to be respected. >>> >>> Bart >>> >>> >>> >>> >> >> >> > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
