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?
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)