OnDemand User Group
Support Forums => z/OS Server => Topic started by: chuckalugk on December 02, 2020, 12:48:07 PM
-
Does anyone have SQL they can share for Annual Access Reviews? SQL showing users access to what reports and groups....or who has access to a group....We'd like to create a report so everyone can see it. I know the summarize way for admins, but we are trying to create reports that will load into CMOD and can be viewed by all.
-
Just to get you started, here is a sample query that will provide you list of [User x Group x Folder] in a situation where access is controlled by Folder.
SELECT
U.USERID AS USER
, G.NAME AS GROUP
, F.NAME AS FOLDER
FROM CCSYSAD.ARSUSER U
, owner.ARSUSRGRP UG
, owner.ARSFOLPERMS FP
, owner.ARSFOL F
, owner.ARSGROUP G
WHERE U.UID = UG.UID
AND UG.GID = FP.ID
AND FP.FID = F.FID
AND UG.GID = G.GID
WITH UR;
In your specific situation, you need to know how access is provided (directly to user id, via AG and/or App or via folder only etc..) and build different queries to obtain your list.
I hope this helps you start from a step ahead. You will need to look at CMOD system tables and build the E-R Diagram for your queries. You may need multiple queries and join their results in some creative ways.
Good luck
-Mehmet
-
Thank you very much. I am not too familiar with SQL, and many customers are asking about AAR, so this is helpful.