DB2 how to get user privilege information when privilege is granted to its group

Question

I grant schema CREATEIN privilege for schema 'test' to user group 'test-group', then add a user 'test-user' into this 'test-group' in Windows OS.

I would like to know what DB2 function or SQL statement can be used to retrieve privilege information for user 'test-user'. I am aware of that the user-group relationship is not defined in DB2, but there must be some ways to look up such relationship data.

As an example, I can create a table successfully in schema 'test' after log in database by user 'test-user' which means the DB2 engine can get 'test-user' CREATEIN privilege inherited from 'test-group'.

I tried to check syscat.schemaauth view by SQL statment select * from syscat.schemaauth but cannot find user 'test-user' privilege definition only group 'test-group' privilege definition:

GRANTOR      GRANTORTYPE  GRANTEE GRANTEETYPE  SCHEMANAME   ALTERINAUTH   CREATEAUTH  DROPINAUTH
...  ....
SYSIBM          S          PUBLIC   G         ADMINISTRATOR N                Y          N
ADMINISTRATOR   U          TEST     G          TEST         N                Y          Y
.. ...

data_henrik data_henrik · Accepted Answer · 2016-11-22T08:57:06

You could use the table function AUTH_LIST_GROUPS_FOR_AUTHID to look up the groups for "test-user". That would list ALL groups the user belongs to, including OS groups not used for the database.

SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('TEST-USER')) AS T

There is another view, SYSIBMADM.AUTHORIZATIONIDS, which lists all authorization IDs, i.e., groups, users and roles:

SELECT * FROM SYSIBMADM.AUTHORIZATIONIDS

The last view you need is SYSIBMADM.PRIVILEGES which lists the privileges. Depending on what you need you would combine the three views/table functions

look up the user's groups for groups known to the database
list the privileges for the user AND
list the privileges for all the groups the user belongs to

UPDATE:
I got interested and quickly typed up and tested a query. This should answer it directly. The "or a.authid='PUBLIC'" is needed to include those privileges coming from PUBLIC.

SELECT distinct p.AUTHID, p.PRIVILEGE, p.OBJECTNAME, p.OBJECTSCHEMA, p.OBJECTTYPE   
FROM SYSIBMADM.PRIVILEGES P, SYSIBMADM.AUTHORIZATIONIDS A,   
     TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('userID')) as U  
WHERE p.privilege='CREATEIN' and a.authidtype='G' and a.authid=p.authid
AND (u.group=a.authid or a.authid='PUBLIC')

DB2 how to get user privilege information when privilege is granted to its group

1 Answers