Query groups application security
Query groups application security
August 31, 2011
The standard Maximo ‘Security Groups’ application provides an easy way to view and modify users access to applications. However it is not always easy to have an overview of what groups are granted to access a specific application or what applications can be accessed by a specific group.
With the following SQL query you can get a report of read/write permissions granted to all the groups.
SELECT maxapps.app, maxapps.description, maxgroup.groupname, maxgroup.description, (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='READ') appread, (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='SAVE') appsave, (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='INSERT') appins, (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='DELETE') appdelFROM maxapps, maxgroupORDER BY maxapps.app, maxgroup.groupname;If you are using the Oracle’s than it is possible to list all sigoptions using CURSOR statement.
SELECT maxapps.app, maxapps.description, maxgroup.groupname, maxgroup.description, CURSOR (SELECT optionname FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app) AS authFROM maxapps, maxgroupORDER BY maxapps.app, maxgroup.groupname;It is possible to narrow down the results of the query to understand what groups have access to a specific application adding a where clause in the previous query. For example here is how to list the permissions to ASSET application:
SELECT maxapps.app, maxapps.description, maxgroup.groupname, maxgroup.description, (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='READ') appread, (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='SAVE') appsave, (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='INSERT') appins, (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='DELETE') appdelFROM maxapps, maxgroupWHERE maxapps.app='ASSET'ORDER BY maxapps.app, maxgroup.groupname;