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
-~----------~----~----~----~------~----~------~--~---
