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' "