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.