If you are a DBA, you will inevitably work on troubleshooting/ granting / revoking object privileges to database users. In this blog post, I am going to share how to check for privileges that have been granted to an object in a DB2 LUW database. This post is an introductory level post for new DBAs. Database level authorities are not discussed in this post.
SYSIBMADM.PRIVILEGES is a catalog view that holds database object privilege information for authorization IDs (roles, users) defined in the system catalog table. If the database was created using ‘RESTRICTIVE’ clause, select privilege is not automatically granted to PUBLIC. If that is the case, one of the following privileges/authorities are needed to query this view.
- SELECT privilege on the PRIVILEGES administrative view
- CONTROL privilege on the PRIVILEGES administrative view
- DATAACCESS authority
- DBADM authority
- SQLADM authority
SYSIBMADM.PRIVILEGES has privilege information about the following different types of database objects.
db2 "select distinct(objecttype) from sysibmadm.privileges" OBJECTTYPE ------------------------ DB2 PACKAGE FUNCTION INDEX MATERIALIZED QUERY TABLE METHOD MODULE PROCEDURE SCHEMA SEQUENCE TABLE TABLESPACE VIEW WORKLOAD 13 record(s) selected.
What privileges have been granted to this object?
Given a database object (table / view / alias etc.) name ($OBJECTNAME) and its schema ($SCHEMA) here is how you check current privileges that users hold for this table.
db2 "SELECT char(authid, 20) AS auth_id, CASE authidtype WHEN 'U' THEN 'USER' WHEN 'G' THEN 'GROUP' WHEN 'R' THEN 'ROLE' END authidtype , privilege FROM sysibmadm.privileges WHERE objectname = '$OBJECTNAME' AND objectschema = '$SCHEMA' ORDER BY authid, authidtype, privilege WITH ur"
Which privileges have been granted to a role?
If you use database roles, here is the SQL to find out what privileges does a particular role ($ROLE) have.
SELECT char(objectschema,10) AS OBJECT , char(objectname,65) SCHEMA, char(privilege, 10) privilege, objecttype FROM sysibmadm.privileges WHERE authid='$ROLE' AND authidtype='R' ORDER BY 1,2,3 WITH UR;
Which privileges have been granted to a user?
The above SQL’s where clause would have to be slightly modified to list the privileges granted to a user ($USER) which could be an LDAP ID or OS ID.
db2 "SELECT char(objectschema,10) AS OBJECT , char(objectname,65) SCHEMA, char(privilege, 10) privilege, objecttype FROM sysibmadm.privileges WHERE authid='$USER' AND authidtype='U' ORDER BY 1,2,3 WITH UR"
We all have busy DBA days. Most of us do not like to do too much of repetitive typing. Why not save SQLs / commands that are frequently used in a file, do some UNIX make-up and call the shell script whenever you want to do the task? I take this approach for many of my daily DBA tasks especially the mundane ones. I usually add the script’s location to UNIX’s $PATH to be able to execute the script from any location on the database server. In future posts, I will share how to take Lazy DBA approach to doing repetitive / mundane tasks.
How do you check privileges in your databases? Are there other easier ways? I would be eager to know. Thanks for reading.