Checking the privileges, authorities and authorizations

How to find on System Catalog Views the authorities, privileges and authorizations for a specific user on database

You have to connect to a database for using the System Catalog Views.

After to connect on database perform the following command.

db2 list tables for schema syscat | grep -i auth

All authorities, privileges and permissions are listed below.

system catalog	description
SYSCAT.DBAUTH	Lists the database privileges
SYSCAT.TABAUTH	Lists the table and view privileges
SYSCAT.COLAUTH	Lists the column privileges
SYSCAT.PACKAGEAUTH	Lists the package privileges
SYSCAT.INDEXAUTH	Lists the index privileges
SYSCAT.SCHEMAAUTH	Lists the schema privileges
SYSCAT.PASSTHRUAUTH	Lists the server privileges
SYSCAT.ROUTINEAUTH	Lists the routine (functions,  methods, and stored procedures) privileges

How to search authorities , privileges and permissions that user XXX has on database

db2 "describe table SYSCAT.DBAUTH"

The output is following.

Column                         Type              Type
name                           schema           name                  Length     Scale Nulls
------------------------------ --------- ------------------ --------   -----           ------
GRANTOR                        SYSIBM       VARCHAR                 128     0 No
GRANTEE                        SYSIBM       VARCHAR                 128     0 No
GRANTEETYPE                 SYSIBM      CHARACTER                 1      0 No
DBADMAUTH                    SYSIBM     CHARACTER                 1      0 No
CREATETABAUTH             SYSIBM     CHARACTER                 1      0 No
BINDADDAUTH                 SYSIBM     CHARACTER                 1      0 No
CONNECTAUTH                SYSIBM     CHARACTER                 1      0 No
NOFENCEAUTH                 SYSIBM     CHARACTER                1      0 No
IMPLSCHEMAAUTH           SYSIBM     CHARACTER                 1      0 No
LOADAUTH                       SYSIBM     CHARACTER                 1      0 No
EXTERNALROUTINEAUTH        SYSIBM    CHARACTER            1      0 No
QUIESCECONNECTAUTH        SYSIBM    CHARACTER            1      0 No
LIBRARYADMAUTH                  SYSIBM    CHARACTER            1      0 No
SECURITYADMAUTH                SYSIBM    CHARACTER            1     0 No

To know more about each column go to URL:
http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp and do a search using SYSCAT.DBAUTH.

The following SQL select is to  find which are the permissions user 'XXX'  has on Database.

db2 "select * from SYSCAT.DBAUTH  where GRANTEE = 'XXX' "