Query users and groups security

Did you have ever spent precious time searching in the Group Security application what group grants a specific permission to a certain user?
Do you want to list all the sigoption granted to a specific user?
Do you want to list all the groups to which a specific user belongs together with all the granted sigoptions?
Do you want to know who has access to a specific application or sigoption?
Here is a SQL query that may help you. It joins several tables and allows to answer all the previous questions... and many more.

SELECT
  groupuser.userid,
  maxgroup.groupname,
  sigoption.app,
  sigoption.description sigoptiondesc
FROM groupuser
JOIN maxgroup ON maxgroup.groupname=groupuser.groupname
JOIN applicationauth ON applicationauth.groupname=maxgroup.groupname
JOIN sigoption ON sigoption.optionname=applicationauth.optionname AND sigoption.app=applicationauth.app
WHERE sigoption.app='YOURAPP' AND sigoption.description='YOURSIGOPTION' AND groupuser.userid='YOURUSER'
ORDER BY groupuser.userid;

By changing the where and the order by clauses you can analyze your user's and group security settings.

Labels: , , ,