March 9, 2012

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.

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. These kinds of list will going to be a big help for all email marketing programs. With the help of this list everything will be so easy.If you want to know about email list marketing B2B marketing company in UK , Newyork & USA
    services then check it here at.
    IBM Users Email List

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete