ACL.RightName = 'SuperUser' OR ACL.RightName = 'ShowACL' :: please help

Maybe this time I’ll get an answer …
RT-3.8.7 NO customisation apart from css
DBIx - 1.56 apache2,mod_perl2

I am seeing a lot of queries (1146 of them)

SELECT ACL.id FROM ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘ShowACL’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.PrincipalType = ‘Group’ AND
Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = 1 AND ACL.PrincipalType = Groups.Type AND
((ACL.ObjectType = ‘RT::System’ AND ACL.ObjectId = 1) OR (ACL.ObjectType
= ‘RT::Queue’ AND ACL.ObjectId = 4)) AND Groups.Domain =
‘RT::System-Role’ AND Groups.Instance = ‘1’ LIMIT 1

The only changes I see between these queries are

  • variation between ShowACL and ModifyACL
  • ACL.ObjectId = x (where x is the id foreach active queue in the system
    – we have 116 active queues)

1- The first question that comes to mind what are we calling this lookup
anyway, if the RT System user does not have permission on a queue, then
the system is broken, so this lookup should be done differently and else
where??
2- Where is this called from so I can disable it (unless some one can
answer question 1 and enlighten me as to why its there)??

Any help will truly be appreciated , these 1147 for 116 queues, my live
system have twice the amount of queues and my concerns that I will end
up with 3000 or so pointless queries with every page.

Please please help.

Roy

Raed,

The only thing I can think of to help de-bug this is to list the
privileges you’ve set and then compare that list to what you see in the
ACL table. Unless, of course, you have Rights Matrix installed. That
would also help. I don’t know if “Rights Matrix” works on 3.8.x, but it
certainly is worth a try.

Kenn
LBNLOn 2/8/2010 8:50 AM, Raed El-Hames wrote:

Maybe this time I’ll get an answer …
RT-3.8.7 NO customisation apart from css
DBIx - 1.56 apache2,mod_perl2

I am seeing a lot of queries (1146 of them)

SELECT ACL.id FROM ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘ShowACL’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.PrincipalType = ‘Group’ AND
Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = 1 AND ACL.PrincipalType = Groups.Type AND
((ACL.ObjectType = ‘RT::System’ AND ACL.ObjectId = 1) OR (ACL.ObjectType
= ‘RT::Queue’ AND ACL.ObjectId = 4)) AND Groups.Domain =
‘RT::System-Role’ AND Groups.Instance = ‘1’ LIMIT 1

The only changes I see between these queries are

  • variation between ShowACL and ModifyACL
  • ACL.ObjectId = x (where x is the id foreach active queue in the system
    – we have 116 active queues)

1- The first question that comes to mind what are we calling this lookup
anyway, if the RT System user does not have permission on a queue, then
the system is broken, so this lookup should be done differently and else
where??
2- Where is this called from so I can disable it (unless some one can
answer question 1 and enlighten me as to why its there)??

Any help will truly be appreciated , these 1147 for 116 queues, my live
system have twice the amount of queues and my concerns that I will end
up with 3000 or so pointless queries with every page.

Please please help.

Roy


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

2010 RT Training Sessions!
San Francisco, CA, USA - Feb 22 & 23
Dublin, Ireland - Mar 15 & 16
Boston, MA, USA - April 5 & 6
Washington DC, USA - Oct 25 & 26

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Thanks for your response Ken ,
I traced it down to missing entry in CachedGroupMembers;

When I upgraded my dev box from 3.6.3 to 3.8.7 I ran
shrink_cgm_table.pl, which for some reason (maybe something in my
system) deleted the following row:

mysql> select * from CachedGroupMembers where id = 2;
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
| 2 | 2 | 1 | 2 | 2 | 0 |

This is the row that tells the RT::SystemUser that its a member of
itself , and its the row that ACLs and Principals are checked against.
The fact it was missing, it made the system loop through the queue
checking acls for the RT user.
so I added it via sql, and that problem is resolved, however :

I do have another problem now, which is repeated :
176 Query SELECT * FROM CachedGroupMembers WHERE GroupId = '2034’
AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = ‘1’

No doubt its missing entries in the database ? (for the curious 2034 is
a defined user group) , why its checking if the SystemUser is a member
of that group is beyond my little brains.

Again Any help will be appreciated

Roy

Ken Crocker wrote:

Raed,

seems ANY query saved by group (OH, this is on 3.6.4. I haven’t had a
chance yet to REALLY look at the DB from a 3.8.x view) will always want
to check permissions for a single user that wants to look at that query
to see if that user is in the group. You say it is happening alot. Does
that mean this security check happens regardless of the user? Regardless
of the query? Is this GroupID a /system/ group? If so, is this query
saved as a /system/ query? There might be something in these
relationships that is escaping your notice. I’ve found, at least in the
past, that a /LOT/ of the /time-consuming/ search problems are related
to user/group membership/privileges. Hope this helps you in de-bugging
the problem.

Kenn
LBNLOn 2/8/2010 9:41 AM, Raed El-Hames wrote:

Thanks for your response Ken ,
I traced it down to missing entry in CachedGroupMembers;

When I upgraded my dev box from 3.6.3 to 3.8.7 I ran
shrink_cgm_table.pl, which for some reason (maybe something in my
system) deleted the following row:

mysql> select * from CachedGroupMembers where id = 2;
±—±--------±---------±-----±------------------±---------+
| id | GroupId | MemberId | Via | ImmediateParentId | Disabled |
±—±--------±---------±-----±------------------±---------+
| 2 | 2 | 1 | 2 | 2 | 0 |
±—±--------±---------±-----±------------------±---------+

This is the row that tells the RT::SystemUser that its a member of
itself , and its the row that ACLs and Principals are checked against.
The fact it was missing, it made the system loop through the queue
checking acls for the RT user.
so I added it via sql, and that problem is resolved, however :

I do have another problem now, which is repeated :
176 Query SELECT * FROM CachedGroupMembers WHERE GroupId =
‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = '1’
176 Query SELECT * FROM CachedGroupMembers WHERE
GroupId = ‘2034’ AND MemberId = ‘1’

No doubt its missing entries in the database ? (for the curious 2034
is a defined user group) , why its checking if the SystemUser is a
member of that group is beyond my little brains.

Again Any help will be appreciated

Roy

Ken Crocker wrote:

Raed,

The only thing I can think of to help de-bug this is to list the
privileges you’ve set and then compare that list to what you see in
the ACL table. Unless, of course, you have Rights Matrix installed.
That would also help. I don’t know if “Rights Matrix” works on 3.8.x,
but it certainly is worth a try.

Kenn
LBNL

On 2/8/2010 8:50 AM, Raed El-Hames wrote:

Maybe this time I’ll get an answer …
RT-3.8.7 NO customisation apart from css
DBIx - 1.56 apache2,mod_perl2

I am seeing a lot of queries (1146 of them)

SELECT ACL.id FROM ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘ShowACL’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.PrincipalType = ‘Group’ AND
Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = 1 AND ACL.PrincipalType = Groups.Type
AND ((ACL.ObjectType = ‘RT::System’ AND ACL.ObjectId = 1) OR
(ACL.ObjectType = ‘RT::Queue’ AND ACL.ObjectId = 4)) AND
Groups.Domain = ‘RT::System-Role’ AND Groups.Instance = ‘1’ LIMIT 1

The only changes I see between these queries are

  • variation between ShowACL and ModifyACL
  • ACL.ObjectId = x (where x is the id foreach active queue in the
    system – we have 116 active queues)

1- The first question that comes to mind what are we calling this
lookup anyway, if the RT System user does not have permission on a
queue, then the system is broken, so this lookup should be done
differently and else where??
2- Where is this called from so I can disable it (unless some one
can answer question 1 and enlighten me as to why its there)??

Any help will truly be appreciated , these 1147 for 116 queues, my
live system have twice the amount of queues and my concerns that I
will end up with 3000 or so pointless queries with every page.

Please please help.

Roy


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

2010 RT Training Sessions!
San Francisco, CA, USA - Feb 22 & 23
Dublin, Ireland - Mar 15 & 16
Boston, MA, USA - April 5 & 6
Washington DC, USA - Oct 25 & 26

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com