Fixing Rhino.Security AddPermissionToQuery(user, op, criteria ) for hierarchical entity groups
20 views
Skip to first unread message
Beto
Jul 22, 2010, 5:12:59 PM7/22/10
to Rhino Tools Dev
Hi Everyone
See: http://groups.google.com/group/rhino-tools-dev/browse_thread/thread/f39c54016f6ade58/155e4ab2940bace9#155e4ab2940bace9
I’m trying to update the method in order for it to include nested
entity groups. Right now the query only goes one level/grouping of
entity groups down. Here is the original SQL statement that gets
added to your Select Statement:
(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 (this_0_.[User] = 1 /* @p3 */
or this_0_.UsersGroup in (SELECT
this_0_0_.Id as y0_
FROM
security_UsersGroups this_0_0_
WHERE
this_0_0_.Id in (SELECT this_0_0_0_.Id as y0_
FROM security_UsersGroups this_0_0_0_
left outer join security_UsersToUsersGroups users4_
on this_0_0_0_.Id = users4_.GroupId
left outer join [core_User] user1_
on users4_.UserId = user1_.Id
left outer join security_UsersGroupsHierarchy allchildre6_
on this_0_0_0_.Id = allchildre6_.ParentGroup
left outer join security_UsersGroups child2_
on allchildre6_.ChildGroup = child2_.Id
WHERE (child2_.Id in (SELECT this_0_0_0x0_.Id as y0_
FROM security_UsersGroups this_0_0_0x0_
inner join security_UsersToUsersGroups users3_
on this_0_0_0x0_.Id = users3_.GroupId
inner join [core_User] user1_
on users3_.UserId = user1_.Id
WHERE user1_.Id = 1 /* @p4 */)
or user1_.Id = 1 /* @p5 */))))
and ((this_.SecurityKey =
this_0_.EntitySecurityKey
or this_.SecurityKey =
entitykey3_.EntitySecurityKey)
or (this_0_.EntitySecurityKey is null
and this_0_.EntitiesGroup is null))
and op1_.Name in ('/Organization/View' /* @p7 */,'/
Organization' /* @p8 */)
ORDER BY this_0_.Level desc,
this_0_.Allow asc)
I have been able to get all SecurityKeys in the nested entity groups
using the following statement:
SELECT distinct entitykey3_.EntitySecurityKey
FROM security_EntityGroupsHierarchy allchildre0_
left outer join security_EntitiesGroups entitiesgr1_
on allchildre0_.ChildGroup = entitiesgr1_.Id
left outer join security_EntityReferencesToEntitiesGroups
entities7_
on entitiesgr1_.Id = entities7_.GroupId
left outer join security_EntityReferences entitykey3_
on entities7_.EntityReferenceId = entitykey3_.Id
WHERE allchildre0_.ParentGroup in
((SELECT distinct this_0_.EntitiesGroup
FROM security_Permissions this_0_
WHERE (this_0_.[User] = 1 /* @p3 */
or this_0_.UsersGroup in (SELECT this_0_0_.Id as y0_
FROM security_UsersGroups this_0_0_
WHERE this_0_0_.Id in (SELECT this_0_0_0_.Id
as y0_
FROM security_UsersGroups this_0_0_0_
left outer join
security_UsersToUsersGroups users4_
on this_0_0_0_.Id = users4_.GroupId
left outer join [core_User] user1_
on users4_.UserId = user1_.Id
left outer join
security_UsersGroupsHierarchy allchildre6_
on this_0_0_0_.Id =
allchildre6_.ParentGroup
left outer join security_UsersGroups
child2_
on allchildre6_.ChildGroup = child2_.Id
WHERE (child2_.Id in (SELECT
this_0_0_0x0_.Id as y0_
FROM security_UsersGroups
this_0_0_0x0_
inner join
security_UsersToUsersGroups users3_
on this_0_0_0x0_.Id =
users3_.GroupId
inner join [core_User] user1_
on users3_.UserId = user1_.Id
WHERE user1_.Id = 1 /* @p4 */)
or user1_.Id = 1 /* @p5 */))))))
But now I’m having trouble attaching the rest of the permission
criteria or finding out where it goes.
This:
and ((this_.SecurityKey = this_0_.EntitySecurityKey
or this_.SecurityKey =
entitykey3_.EntitySecurityKey)
or (this_0_.EntitySecurityKey is null
and this_0_.EntitiesGroup is null))
and op1_.Name in ('/Organization/View' /* @p7 */,'/
Organization' /* @p8 */)
ORDER BY this_0_.Level desc,
this_0_.Allow asc)
See: http://groups.google.com/group/rhino-tools-dev/browse_thread/thread/f39c54016f6ade58/155e4ab2940bace9#155e4ab2940bace9
I’m trying to update the method in order for it to include nested
entity groups. Right now the query only goes one level/grouping of
entity groups down. Here is the original SQL statement that gets
added to your Select Statement:
(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 (this_0_.[User] = 1 /* @p3 */
or this_0_.UsersGroup in (SELECT
this_0_0_.Id as y0_
FROM
security_UsersGroups this_0_0_
WHERE
this_0_0_.Id in (SELECT this_0_0_0_.Id as y0_
FROM security_UsersGroups this_0_0_0_
left outer join security_UsersToUsersGroups users4_
on this_0_0_0_.Id = users4_.GroupId
left outer join [core_User] user1_
on users4_.UserId = user1_.Id
left outer join security_UsersGroupsHierarchy allchildre6_
on this_0_0_0_.Id = allchildre6_.ParentGroup
left outer join security_UsersGroups child2_
on allchildre6_.ChildGroup = child2_.Id
WHERE (child2_.Id in (SELECT this_0_0_0x0_.Id as y0_
FROM security_UsersGroups this_0_0_0x0_
inner join security_UsersToUsersGroups users3_
on this_0_0_0x0_.Id = users3_.GroupId
inner join [core_User] user1_
on users3_.UserId = user1_.Id
WHERE user1_.Id = 1 /* @p4 */)
or user1_.Id = 1 /* @p5 */))))
and ((this_.SecurityKey =
this_0_.EntitySecurityKey
or this_.SecurityKey =
entitykey3_.EntitySecurityKey)
or (this_0_.EntitySecurityKey is null
and this_0_.EntitiesGroup is null))
and op1_.Name in ('/Organization/View' /* @p7 */,'/
Organization' /* @p8 */)
ORDER BY this_0_.Level desc,
this_0_.Allow asc)
I have been able to get all SecurityKeys in the nested entity groups
using the following statement:
SELECT distinct entitykey3_.EntitySecurityKey
FROM security_EntityGroupsHierarchy allchildre0_
left outer join security_EntitiesGroups entitiesgr1_
on allchildre0_.ChildGroup = entitiesgr1_.Id
left outer join security_EntityReferencesToEntitiesGroups
entities7_
on entitiesgr1_.Id = entities7_.GroupId
left outer join security_EntityReferences entitykey3_
on entities7_.EntityReferenceId = entitykey3_.Id
WHERE allchildre0_.ParentGroup in
((SELECT distinct this_0_.EntitiesGroup
FROM security_Permissions this_0_
WHERE (this_0_.[User] = 1 /* @p3 */
or this_0_.UsersGroup in (SELECT this_0_0_.Id as y0_
FROM security_UsersGroups this_0_0_
WHERE this_0_0_.Id in (SELECT this_0_0_0_.Id
as y0_
FROM security_UsersGroups this_0_0_0_
left outer join
security_UsersToUsersGroups users4_
on this_0_0_0_.Id = users4_.GroupId
left outer join [core_User] user1_
on users4_.UserId = user1_.Id
left outer join
security_UsersGroupsHierarchy allchildre6_
on this_0_0_0_.Id =
allchildre6_.ParentGroup
left outer join security_UsersGroups
child2_
on allchildre6_.ChildGroup = child2_.Id
WHERE (child2_.Id in (SELECT
this_0_0_0x0_.Id as y0_
FROM security_UsersGroups
this_0_0_0x0_
inner join
security_UsersToUsersGroups users3_
on this_0_0_0x0_.Id =
users3_.GroupId
inner join [core_User] user1_
on users3_.UserId = user1_.Id
WHERE user1_.Id = 1 /* @p4 */)
or user1_.Id = 1 /* @p5 */))))))
But now I’m having trouble attaching the rest of the permission
criteria or finding out where it goes.
This:
and ((this_.SecurityKey = this_0_.EntitySecurityKey
or this_.SecurityKey =
entitykey3_.EntitySecurityKey)
or (this_0_.EntitySecurityKey is null
and this_0_.EntitiesGroup is null))
and op1_.Name in ('/Organization/View' /* @p7 */,'/
Organization' /* @p8 */)
ORDER BY this_0_.Level desc,
this_0_.Allow asc)
Beto
Jul 23, 2010, 5:51:36 PM7/23/10
to Rhino Tools Dev
Hi Everyone
I was able to get the query to work moving all decedents portion of it
into the joins and it now retrieves all keys in the nested
EntityGroups. I could convert this to the Criteria API and submit it
as a patch to RhinoSecurity but I’m pausing before doing this because
the query is taking way too long to run. In my db it takes about 6min
to retrieve all results, I don’t know all the logic and reasons of the
query for me to move things around. So I am posting the complete
working query in hope that someone else can assist in improving the
performance. If need be I can convert this to the NHibernate Criteria
and post the AddPermissionToQuery() method.
I was able to get the query to work moving all decedents portion of it
into the joins and it now retrieves all keys in the nested
EntityGroups. I could convert this to the Criteria API and submit it
as a patch to RhinoSecurity but I’m pausing before doing this because
the query is taking way too long to run. In my db it takes about 6min
to retrieve all results, I don’t know all the logic and reasons of the
query for me to move things around. So I am posting the complete
working query in hope that someone else can assist in improving the
performance. If need be I can convert this to the NHibernate Criteria
and post the AddPermissionToQuery() method.
SELECT this_.Id as Id12_0_,
this_.Name as Name12_0_,
this_.ParentId as ParentId12_0_,
this_.OrgTypeId as OrgTypeId12_0_,
this_.SecurityKey as Security5_12_0_,
this_.IsInactive as IsInactive12_0_
FROM [core_Organization] this_
WHERE 1 /* @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
--Modifications
inner join security_EntityGroupsHierarchy allchildre0_
on allchildre0_.ParentGroup = this_0_.EntitiesGroup or
allchildre0_.ChildGroup = this_0_.EntitiesGroup
left outer join security_EntitiesGroups entitygrou2_
on allchildre0_.ChildGroup = entitygrou2_.Id
--End Modifications
On Jul 22, 2:12 pm, Beto <humbertofra...@gmail.com> wrote:
> Hi Everyone
>
> See:http://groups.google.com/group/rhino-tools-dev/browse_thread/thread/f...
Ayende Rahien
Jul 24, 2010, 1:49:21 AM7/24/10
to rhino-t...@googlegroups.com
Gmail mungled things up.
Can you post the query as a text attachment?
--
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-t...@googlegroups.com.
To unsubscribe from this group, send email to rhino-tools-d...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.
Humberto Franco
Jul 24, 2010, 1:57:16 AM7/24/10
to rhino-t...@googlegroups.com
I will do so first thing on monday once I get back to work since I don't have access to it from home.
Ayende Rahien
Jul 26, 2010, 3:12:53 PM7/26/10
to rhino-t...@googlegroups.com
There are 12 joins in the where clause, I am not surprised :-)
On your data, can you activate the indexing advisor?
Ayende Rahien
Jul 26, 2010, 3:13:51 PM7/26/10
to rhino-t...@googlegroups.com
It might be a good time to explore the denormalized option, btw.
Humberto Franco
Jul 26, 2010, 4:58:45 PM7/26/10
to rhino-t...@googlegroups.com
I ran the DB Tuning Advisor with the workload script that I attached and no recommendations were made on the indexes. On denormoalizing RhinoSecurity I would need recommendations on what tables would need to change... and then see how much work this would take.
Reply all
Reply to author
Forward
0 new messages