Quick primer on checking database object privileges in DB2 LUW

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

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.

Advertisements

2 thoughts on “Quick primer on checking database object privileges in DB2 LUW

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s