5

Is there an SQL way to show the privileges present on a table or view in DB2 Z/OS? I would like to achieve something like this:

select * from sysibm.magic_table where table_name = 'users'

|TABLE_NAME|PRIVILEGE_TYPE|USER_OR_GROUP_NAME|
|     USERS|        INSERT|              ANDI|
|     USERS|        SELECT|            ADMINS|

Is that possible?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
andy
  • 1,035
  • 1
  • 13
  • 35
  • unlikely to be that simple. Have a look at the DB System tables https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_catalogtablesintro.html, Sysresauth looks like one to look at – Bruce Martin Jun 29 '16 at 07:14
  • For Cics programs, it is the Bind that gets the access (rather than the actual end user). – Bruce Martin Jun 29 '16 at 07:17
  • 1
    That would be [SYSIBM.SYSTABAUTH](https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_sysibmsystabauthtable.html) I think. – mustaccio Jun 29 '16 at 11:04
  • Thank you @mustaccio, I think you are right. `SELECT grantee, screator, stname, tcreator, ttname, updatecols, alterauth, deleteauth, indexauth, insertauth, selectauth, updateauth FROM SYSIBM.SYSTABAUTH WHERE TTNAME = 'MY_TABLE' AND TCREATOR = 'MY_SCHEMA'`. I will test tomorrow. – andy Jun 30 '16 at 13:40

1 Answers1

11

This query tells the user / role names in the grantee column and the various assigned rigths in the ...auth columns where Y = right present, G = right present and right to grant it to others.

SELECT
    grantee,
    screator,
    stname,
    tcreator,
    ttname,
    updatecols,
    alterauth,
    deleteauth,
    indexauth,
    insertauth,
    selectauth,
    updateauth
FROM
    SYSIBM.SYSTABAUTH
WHERE
    TTNAME = 'MY_TABLE'
    AND TCREATOR = 'MY_SCHEMA'

Sample result set:

|GRANTEE  |SCREATOR |STNAME  |TCREATOR |TTNAME  |UPDATECOLS|ALTERAUTH|DELETEAUTH|INDEXAUTH|INSERTAUTH|SELECTAUTH|UPDATEAUTH|
============================================================================================================================
|MY_SCHEMA|MY_SCHEMA|MY_TABLE|MY_SCHEMA|MY_TABLE|          |         |         G|         |         G|         G|         G|
|USER2    |MY_SCHEMA|MY_TABLE|MY_SCHEMA|MY_TABLE|          |         |         Y|         |         Y|         Y|         Y|
|USER3    |MY_SCHEMA|MY_TABLE|MY_SCHEMA|MY_TABLE|          |         |         Y|         |         Y|         Y|         Y|
|GROUP1   |MY_SCHEMA|MY_TABLE|MY_SCHEMA|MY_TABLE|          |         |         Y|         |         Y|         Y|         Y|
|GROUP2   |MY_SCHEMA|MY_TABLE|MY_SCHEMA|MY_TABLE|          |         |         Y|         |         Y|         Y|         Y|
|GROUP3   |MY_SCHEMA|MY_TABLE|MY_SCHEMA|MY_TABLE|          |         |          |         |          |         Y|          |
|GROUP4   |MY_SCHEMA|MY_TABLE|MY_SCHEMA|MY_TABLE|          |         |         Y|         |         Y|         Y|         Y|

IBM documentation of SYSIBM.SYSTABAUTH (Thanks to @mustaccio)

andy
  • 1,035
  • 1
  • 13
  • 35