Query groups application security

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') appdel
FROM maxapps, maxgroup
ORDER 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 auth
FROM maxapps, maxgroup
ORDER 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') appdel
FROM maxapps, maxgroup
WHERE maxapps.app='ASSET'
ORDER BY maxapps.app, maxgroup.groupname;

Labels: , , ,