Retrieving all authorization IDs or roles with granted privileges
Catalog tables that contain authorization information include GRANTEE and GRANTEETYPE columns. Depending on the settings of these columns, you can modify the WHERE clause of the SELECT statement to retrieve all IDs or roles with the same privileges.
About this task
No single catalog table contains information about
all privileges. If GRANTEETYPE is blank, the value of GRANTEE is the
primary or secondary authorization ID that has been granted a privilege.
If GRANTEETYPE is "L", the value of GRANTEE is a role.
To retrieve all IDs or roles with privileges, you can issue the SQL code as shown in the following example:
SELECT GRANTEE, 'PACKAGE ' FROM SYSIBM.SYSPACKAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'TABLE ' FROM SYSIBM.SYSTABAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'COLUMN ' FROM SYSIBM.SYSCOLAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'ROUTINE ' FROM SYSIBM.SYSROUTINEAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'PLAN ' FROM SYSIBM.SYSPLANAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'SYSTEM ' FROM SYSIBM.SYSUSERAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'DATABASE' FROM SYSIBM.SYSDBAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'SCHEMA ' FROM SYSIBM.SYSSCHEMAAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'USER ' FROM SYSIBM.SYSRESAUTH
WHERE GRANTEETYPE IN (' ','L')
UNION
SELECT GRANTEE, 'SEQUENCE ' FROM SYSIBM.SYSSEQUENCEAUTH
WHERE GRANTEETYPE IN (' ','L'); Periodically, you should compare the list of IDs or roles that is retrieved by this SQL code with the following lists:
- Lists of users from subsystems that connect to Db2 (such as IMS, CICS®, and TSO)
- Lists of RACF® users and groups
- Lists of users from other DBMSs that access your Db2 subsystem
- Lists of remote connections.
If Db2 lists IDs or
roles that do not exist elsewhere, you should revoke their privileges.