I trying to create reports for our auditors that 1. lists all technical roles, the entitlement(s) to that role, and the entitlement’s source. 2. would list users, their technical roles, and the date the roles were granted.
anyone know how to get this information?
Thanks,
Lee
For report #1, see if you can use the following:
select VGD.ROLE_NAME "Role Name", VGD.ENT_TYPE as "Access Right Type",
(VGD.CHILD_APPROLE_NAME || VGD.CHILD_GROUP_NAME || VGD.RESOURCE_NAME || ' ' || VGD.ACTION_NAME) as "Access Right Name",
case
when VGD.APPLICATION_NAME is null then VD.NAME
Else VGD.APPLICATION_NAME
end as "Application/Directory"
from V_GLOBALROLE_DEFINITIONS VGD
JOIN PV_ROLE PR
on VGD.ROLE_ID=PR.ID
left Join PV_USER_GROUP PUG
JOIN V_DIRECTORIES VD
on PUG.APPLICATION_ID = VD.ID
on VGD.ENT_ID = PUG.ID
--Only technical roles
where PR.TYPE='TR'
For report #1, see if you can use the following: