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.

4 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. Article is quite good. Pegasi Media is a b2b marketing firm that has worked with many top organizations. Availing its email list is fast, simple, convenient and efficient. Appending services adds the new record as well as fills up the fields that are missing. Pegasi Media Group also perform Data Refinement, Data building, Data Enchancement, and Data De-Duplication. Database marketing is a form of direct market in which the customers are contacted through their email addresses with the help of the database. There is a scope for email marketing to generate personalized communication with the clients in order to promote your sales.
    IBM Websphere Users

    ReplyDelete
  4. Article is quite good. Pegasi Media is a b2b marketing firm that has worked with many top organizations. Availing its email list is fast, simple, convenient and efficient. Appending services adds the new record as well as fills up the fields that are missing. Pegasi Media Group also perform Data Refinement, Data building, Data Enchancement, and Data De-Duplication. Database marketing is a form of direct market in which the customers are contacted through their email addresses with the help of the database. There is a scope for email marketing to generate personalized communication with the clients in order to promote your sales.
    IBM Websphere Users

    ReplyDelete