The solution is to use SYSTABAUTH table. Assume the table name is ‘DB1T04’ and we want to find out what all programs performs C(CREATE) R(READ) U(UPDATE) or D(DELETE) operation. A simple query to fetch the matrix would like below:
SELECT SUBSTR(TCREATOR,1,10) AS CREATOR
, SUBSTR(TTNAME,1,10) AS NAME
, SUBSTR(GRANTEE,1,10) AS PROGRAM
, CASE WHEN INSERTAUTH = 'Y' THEN 'C'
ELSE '-'
END AS C
, CASE WHEN SELECTAUTH = 'Y' THEN 'R'
ELSE '-'
END AS R
, CASE WHEN UPDATEAUTH = 'Y' THEN 'U'
ELSE '-'
END AS U
, CASE WHEN DELETEAUTH = 'Y' THEN 'D'
ELSE '-'
END AS D
, CASE WHEN COLLID = ' ' THEN '** PLAN **'
ELSE COLLID
END AS "PLAN/COLLECTION"
, CASE WHEN CONTOKEN = ' ' THEN CONTOKEN
ELSE HEX(CONTOKEN)
END AS TOKEN
FROM SYSIBM.SYSTABAUTH
WHERE TTNAME = 'DB1T04'o/p will be generated as :
The program names will be shown along with the operation it does on the table.
(image is blurred on purpose).
Detailed descriptions of the columns of SYSTABAUTH can be found on IBM manuals.