Search code examples
securitydb2privileges

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

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
.. ...
Solution

  • 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')