Pages

Wednesday, November 25, 2015

Database query to find the application access level

If we have to check the access level to applications for a particular security group/user, it would be time consuming to look in the UI.

Below is the query to check the READ, INSERT, SAVE & DELETE permission to applications for a security group.

SELECT APPLICATIONAUTH.GROUPNAME,
       MAXAPPS.DESCRIPTION,
       MAX(CASE WHEN (OPTIONNAME IN 'READ') THEN 1 ELSE 0 END) AS READ_ACCESS,
       MAX(CASE WHEN (OPTIONNAME IN 'SAVE') THEN 1 ELSE 0 END) AS SAVE_ACCESS,
       MAX(CASE WHEN (OPTIONNAME IN 'INSERT') THEN 1 ELSE 0 END) AS NEW_ACCESS,
       MAX(CASE WHEN (OPTIONNAME IN 'DELETE') THEN 1 ELSE 0 END) AS DELETE_ACCESS
FROM APPLICATIONAUTH
INNER JOIN MAXAPPS ON MAXAPPS.APP = APPLICATIONAUTH.APP
WHERE OPTIONNAME IN ('READ',
                     'SAVE',
                     'INSERT',
                     'DELETE')
  AND GROUPNAME = <group_name>
GROUP BY APPLICATIONAUTH.GROUPNAME,
         MAXAPPS.DESCRIPTION
ORDER BY GROUPNAME;


Slightly modified query to check READ, INSERT, SAVE & DELETE permission to applications for a particular user.

SELECT MAXAPPS.DESCRIPTION,
       MAX(CASE WHEN (OPTIONNAME IN 'READ') THEN 1 ELSE 0 END) AS READ_ACCESS,
       MAX(CASE WHEN (OPTIONNAME IN 'SAVE') THEN 1 ELSE 0 END) AS SAVE_ACCESS,
       MAX(CASE WHEN (OPTIONNAME IN 'INSERT') THEN 1 ELSE 0 END) AS NEW_ACCESS,
       MAX(CASE WHEN (OPTIONNAME IN 'DELETE') THEN 1 ELSE 0 END) AS DELETE_ACCESS
FROM APPLICATIONAUTH
INNER JOIN MAXAPPS ON MAXAPPS.APP = APPLICATIONAUTH.APP
INNER JOIN groupuser ON GROUPUSER.GROUPNAME = APPLICATIONAUTH.GROUPNAME
WHERE GROUPUSER.USERID = <user_id>
  AND OPTIONNAME IN ('READ',
                     'SAVE',
                     'INSERT',
                     'DELETE')
GROUP BY MAXAPPS.DESCRIPTION;


No comments:

Post a Comment