Posted by Edit: 01/23/2018 – corrected one word not in an SQL statement.
I have written several other articles on security and permissions, but I thought I would write one from a purely practical perspective. If you don’t understand the basics of how DB2 handles users, authentication, authorization, and privileges, please read Db2 Basics: Users, Authentication, and Authorization.
The Easiest Method
Back in the day, I worked as a peon-DBA in IBM Global Services. One of the cross-departmental teams I was on was tasked with scripting things related to security and one of my tasks was to write a script that would find all permissions for an ID and revoke them. This was on either Db2 7.2 or Db2 8.1. It was HARD. I had to use the method at the bottom of this article and crawl through the system catalog tables. As a fairly new DBA, I spent a lot of time with my reference book for the system catalog (SQL Reference Volume 1, Appendix D) trying to find all the views that covered permissions on the various objects.
Some of the objects I didn’t even fully understand what they were at that point. Luckily, in in Db2 9.5, IBM introduced some tools to make this easier.
Authorities
If you want a list of all IDs that have system or database authorities, then SYSIBMADM.AUTHORIZATIONIDS is the place to go. You can query it like this:
select substr(authid,1,20) as authid
, authidtype
from sysibmadm.authorizationids
AUTHID AUTHIDTYPE
-------------------- ----------
PUBLIC G
SYSDEBUG R
SYSDEBUGPRIVATE R
SYSTS_ADM R
SYSTS_MGR R
DB2INST2 U
6 record(s) selected.
This lists the type of entity that holds authorizations and the name of ID/Group/Role. G stands for group, R stands for Role, and U stands for User. The above is the output I see on the standard SAMPLE database.
Once you have this list, you can get all authorities for any or all of these IDS at both the system and database level, using the AUTH_LIST_AUTHORITIES_FOR_AUTHID table function. Yes, table functions can be a bit intimidating if you haven’t worked with them, but here’s a simple query that returns most of the information available:
select substr(AUTHORITY,1,30) as Authority
, D_USER
, D_GROUP
, D_PUBLIC
, ROLE_USER
, ROLE_GROUP
, ROLE_PUBLIC
, D_ROLE
from table(auth_list_authorities_for_authid('EDBA','U'))
AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------------ ------ ------- -------- --------- ---------- ----------- ------
SYSADM * Y * * * * *
DBADM Y N N N N N *
CREATETAB N N Y N N N *
BINDADD N N Y N N N *
CONNECT N N Y N N N *
CREATE_NOT_FENCED_ROUTINE N N N N N N *
SYSCTRL * N * * * * *
SYSMAINT * N * * * * *
IMPLICIT_SCHEMA N N Y N N N *
LOAD N N N N N N *
CREATE_EXTERNAL_ROUTINE N N N N N N *
QUIESCE_CONNECT N N N N N N *
SECADM N N N N N N *
SYSMON * N * * * * *
SQLADM N N N N N N *
WLMADM N N N N N N *
EXPLAIN N N N N N N *
DATAACCESS Y N N N N N *
ACCESSCTRL Y N N N N N *
CREATE_SECURE_OBJECT N N N N N N *
20 record(s) selected.
Note that in running that query, I passed to the table function the name of a specific user ('EDBA') and 'U' to indicate they are a user. You cannot use NULL for the input parameters here, or you get no results.
In the output, Y means a permission is held and gained via the method specified in the column name. N means a permission is not held via the method specified in the column name. * means no one can gain that permission via the method specified in the column name. The output from this query should list every authority an ID has, regardless if we are talking about system or database authorizations. It also tells you how that ID got that authority – through a direct grant, a group, a role, or PUBLIC. This makes it easy to know how to revoke each as well.
Privileges
On an individual object level, you can use the SYSIBMADM.PRIVILEGES view. This view pulls together all the individual authorization system views to present a single view of privileges. Here’s a good way to query it:
select substr(authid,1,20) as authid
, authidtype
, privilege
, grantable
, substr(objectschema,1,12) as objectschema
, substr(objectname,1,30) as objectname
, objecttype
from sysibmadm.privileges
where objectschema not like 'SYS%'
AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE OBJECTSCHEMA OBJECTNAME OBJECTTYPE
-------------------- ---------- ----------- --------- ------------ ------------------------------ ------------------------
PUBLIC G USAGE N SYSDEFAULTUSERWORKLOAD WORKLOAD
DB2INST2 U REFERENCE Y DB2INST2 STAFFG TABLE
DB2INST2 U REFERENCE Y DB2INST2 ADEFUSR MATERIALIZED QUERY TABLE
DB2INST2 U REFERENCE Y DB2INST2 EMPMDC TABLE
DB2INST2 U SELECT Y DB2INST2 VPHONE VIEW
DB2INST2 U SELECT Y DB2INST2 VEMPLP VIEW
DB2INST2 U SELECT Y DB2INST2 STAFFG TABLE
DB2INST2 U SELECT Y DB2INST2 ADEFUSR MATERIALIZED QUERY TABLE
...
Notice here that I’ve specified the elimination of system objects – you may or may not want to do that. You can also add a where clause on AUTHID to specify a single user id or group, or leave it off and see all permissions. This tends to return a large number of records, because each ID often has multiple permissions on an object, and each permission shows up as another row.
The Bad Old Way – Crawl the System Catalog and DBM CFG
The way we used to accomplish this was by starting out at the system level and looking at each of the system groups:
$ db2 get dbm cfg |grep GROUP SYSADM group name (SYSADM_GROUP) = DB2IADM2 SYSCTRL group name (SYSCTRL_GROUP) = SYSMAINT group name (SYSMAINT_GROUP) = SYSMON group name (SYSMON_GROUP) =
Then we would cat /etc/groups to check the membership of those groups. After that, we would query SYSCAT.DBAUTH to get all database level authorizations:
select substr(grantee,1,20) as grantee
, GRANTEETYPE
, BINDADDAUTH
, CONNECTAUTH
, CREATETABAUTH
, DBADMAUTH
, EXTERNALROUTINEAUTH
, IMPLSCHEMAAUTH
, LOADAUTH
, NOFENCEAUTH
, QUIESCECONNECTAUTH
, SECURITYADMAUTH
, SQLADMAUTH
, WLMADMAUTH
, EXPLAINAUTH
, DATAACCESSAUTH
, ACCESSCTRLAUTH
, CREATESECUREAUTH
from syscat.dbauth with ur
GRANTEE GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH EXTERNALROUTINEAUTH IMPLSCHEMAAUTH LOADAUTH NOFENCEAUTH QUIESCECONNECTAUTH SECURITYADMAUTH SQLADMAUTH WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH ACCESSCTRLAUTH CREATESECUREAUTH
-------------------- ----------- ----------- ----------- ------------- --------- ------------------- -------------- -------- ----------- ------------------ --------------- ---------- ---------- ----------- -------------- -------------- ----------------
DB2INST2 U N N N Y N N N N N Y N N N Y Y N
PUBLIC G Y Y Y N N Y N N N N N N N N N N
2 record(s) selected.
Finally we would query each of the other authorization system views – SYSCAT.TABAUTH, SYSCAT.INDEXAUTH, etc.
This method is still valid, and I still regularly query syscat.DBAUTH and SYSCAT.TABAUTH. I use the other AUTH views from time to time, but if I’m looking for an overall picture, I always go to SYSIBMADM.PRIVILEGES and AUTH_LIST_AUTHORITIES_FOR_AUTHID first.
In old bad way:), what about use ‘db2look -x’ to get started
That would work for object-level permissions, but would not get you system or database level authorities. As a scripter, I also mostly dislike methods that simply put data out to a flat file where I cannot change how I return or filter on what I retrieve. The db2look method places each permission on a separate line, so if an ID has 4 permissions on 1500 tables, it gets long, fast. The advantage of using db2look is that you immediately have the commands to grant permissions, which are easy to transform into statements to revoke permissions. You would actually have to use `-xd` to get all permissions, as some obvious permissions are not included with the `-x` option.
Thanks for this post. It does cover a lot of things.
One issue I have from time to time is the privileges of admin user. For example, admin user can select a table, but that privilege is not shown when querying the sysibmadm.privileges
How can I confidently tell that what privileges of admin user on a particular object?
Thanks
Some privileges are implicit. The owner of a table will always be able to select from a table, without that permission being listed. If it’s DBADM, they only have access if they also have DATAACCESS or some other permission. You can revoke DATAACCESS while leaving DBADM.
I learned something today. Thanks for sharing!
Thanks, very useful info.
I have a user who has the rights only to select the data from any table from the database. Now, we have created new tables and he is not able to access them.
How can i check these access rights and how can i check who all can access a single xyz table in my database.
This is a great and simple way of getting out exactly what a given user can do in the database, also in a specific context as SAP running on db2. Thanks a lot!! Francois
Hi
When I run the query, I get duplicate rows returned as shown below.
Why are duplicate rows returned?
SELECT substr(OBJECTSCHEMA,1,12) AS OBJECTSCHEMA,
substr(OBJECTNAME,1,30) as OBJECTNAME,
OBJECTTYPE,
substr(AUTHID,1,20) as AuthID,
AUTHIDTYPE,
PRIVILEGE,
GRANTABLE
FROM SYSIBMADM.PRIVILEGES
WHERE OBJECTSCHEMA NOT LIKE ‘SYS%’
AND OBJECTNAME = UPPER(‘scwa_item_movement’)
ORDER BY OBJECTSCHEMA, OBJECTNAME, AuthID, PRIVILEGE;
OBJECTSCHEMA OBJECTNAME OBJECTTYPE AUTHID AUTHIDTYPE PRIVILEGE GRANTABLE
———— —————————— ———————— ——————– ———- ———– ———
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U ALTER Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U ALTER Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U CONTROL N
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U CONTROL N
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U DELETE Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U DELETE Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U INDEX Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U INDEX Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U INSERT Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U INSERT Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U REFERENCE Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U REFERENCE Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U SELECT Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U SELECT Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U UPDATE Y
CSNX SCWA_ITEM_MOVEMENT TABLE DB2INST1 U UPDATE Y
Is the object name longer than that? this query truncates table names to make the output more readable. You may need to increase that 30 to something larger.