Tags

Below query that is written against DB2 catalog table will be helpful to see what program has what level of access for a specific DB2 table that you are looking at.

SELECT TCREATOR AS CREATOR
, TTNAME AS TABLE
, GRANTEE AS GRANTEE
, SUBSTR(TTNAME,6,3) AS TBL
, VALUE(USR_USER_NAME,’ ‘) AS “USER NAME”
, VALUE(USR_DEL_IND,’ ‘) AS DEL_IND
, VALUE(USR_DEPT_ID,’ ‘) AS DEPT
, GRANTEETYPE AS TYPE
, SUM(CASE WHEN INSERTAUTH = ‘Y’ THEN 1 ELSE 0 END) *1 AS C
, SUM(CASE WHEN SELECTAUTH = ‘Y’ THEN 1 ELSE 0 END) *3 AS R
, SUM(CASE WHEN UPDATEAUTH = ‘Y’ THEN 1 ELSE 0 END) *5 AS U
, SUM(CASE WHEN DELETEAUTH = ‘Y’ THEN 1 ELSE 0 END) *7 AS D
, ((SUM(CASE WHEN INSERTAUTH = ‘Y’ THEN 1 ELSE 0 END) *1) +
(SUM(CASE WHEN SELECTAUTH = ‘Y’ THEN 1 ELSE 0 END) *3) +
(SUM(CASE WHEN UPDATEAUTH = ‘Y’ THEN 1 ELSE 0 END) *5) +
(SUM(CASE WHEN DELETEAUTH = ‘Y’ THEN 1 ELSE 0 END) *7)) AS TOT
FROM SYSIBM.SYSTABAUTH
WHERE LOCATION = ”
AND GRANTEETYPE = ‘P’
AND TCREATOR = ‘SCHEMA_NAME’
AND TTNAME LIKE ‘TABLE_NAME%’
GROUP BY TCREATOR
, TTNAME
, GRANTEE
, GRANTEETYPE
, USR_USER_NAME
, USR_DEL_IND
, USR_DEPT_ID
WITH UR;
{"ft_lang_label":"__label__en","ft_lang_prob":0.6103321,"math_prob":0.97702503,"size":1095,"snap":"2019-35-2019-39","text_gpt3_token_len":397,"char_repetition_ratio":0.17598534,"word_repetition_ratio":0.19607843,"special_character_ratio":0.33242008,"punctuation_ratio":0.12206573,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9793486,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-09-19T12:50:45Z\",\"WARC-Record-ID\":\"<urn:uuid:fece7895-8f5d-4a25-927a-6d308766f287>\",\"Content-Length\":\"63329\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:b8ed25ea-8a21-4cf2-a633-a2dfed1cb711>\",\"WARC-Concurrent-To\":\"<urn:uuid:37aa687f-f0ef-4747-9099-19a53a6e542b>\",\"WARC-IP-Address\":\"192.0.78.25\",\"WARC-Target-URI\":\"https://mainframesf1.com/2018/10/08/db2-crud-query-create-read-update-delete/?shared=email&msg=fail\",\"WARC-Payload-Digest\":\"sha1:XSW7NLN2ANCD2346HDR7OJTSBGLVHT6Z\",\"WARC-Block-Digest\":\"sha1:HIN4TLQMGN7LT42PJPFB33PRKIBKNQ4J\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-39/CC-MAIN-2019-39_segments_1568514573519.72_warc_CC-MAIN-20190919122032-20190919144032-00287.warc.gz\"}"}