The sample SQL query compares the ID in the data set access record
(DSACC_AUTH_ID) with the list of valid user and group
IDs (in AUTH_IDS). When a user ID is found that is not a valid user
ID or group ID, it is listed. The query also lists the data set profile
name, the authority that the user has, and the access count. Figure 1. A Sample SQL Query
------------------------------------------------------------------------
-- Description: Check all of the data set standard access lists and --
-- verify that each user ID is a valid user or --
-- group ID --
-- --
-- Tables Accessed: SQL --
-- "DS_ACCESS" - A list of dataset authorities --
-- "AUTH_IDS" - A list of valid user/group IDs --
-- --
------------------------------------------------------------------------
SELECT
DSACC_NAME
,DSACC_AUTH_ID
,DSACC_ACCESS
,DSACC_ACCESS_CNT
FROM
USER01.DS_ACCESS X
WHERE NOT EXISTS
( SELECT *
FROM
USER01.AUTH_IDS
WHERE
X.DSACC_AUTH_ID=AUTHID_NAME
)
AND
X.DSACC_AUTH_ID¬='*'
ORDER BY 1
;