Hi Experts,
I have the request from my customer to build a summarized SQL querie (Report) with the following requirements:
- Validity of a user
- Overview of all users with indirect privileges
- Group of the Business role, from where the indirect privilege comes from
- the execution state of the Privileges
Currently i get an overview of users with indirect privileges, but so far not the Information from where the indirect privileges comes from. It will be nice, to create the querie also based on historic values.
Here is my current Status of my SQL Code (SAP IDM 7.2 | Oracle DB):
SELECT
a.mcthismskeyvalue AS USERID,
CASE WHEN c.MCATTRNAME in 'MX_VALIDTO' THEN 'VALID TO '
WHEN c.MCATTRNAME in 'MX_VALIDFROM' THEN 'VALID FROM'
END AS STATUS,
substr (c.mcvalue,1,10) as Datum,
a.mcassigneddirect AS DIRECT,
a.mcassignedinheritcount AS INDIRECT,
a.mcassigneddynamicgroup AS DYNAMIC_GROUP,
a.mcorphan AS ORPHAN,
-- b.mcothermskeyvalue AS BUSINESS_ROLE,
a.mcothermskeyvalue AS Usergroup,
b.mcthismskeyvalue AS ROLES,
CASE WHEN a.mcexecstate in (0,1) THEN 'OK'
WHEN a.mcexecstate in (2) THEN 'Rejected'
WHEN a.mcexecstate in (4) THEN 'Failed'
WHEN a.mcexecstate in (512,1536,1537)THEN 'Pending'
ELSE 'NOT OK - unexpected result, please check'
END AS EXECUTION_STATE,
CASE WHEN a.mcothermskeyvalue like 'PRIV:%:ONLY' THEN SUBSTR(a.mcothermskeyvalue,6,6)
WHEN a.mcothermskeyvalue like 'PRIV:ROLE:%' THEN SUBSTR(a.mcothermskeyvalue,11,6)
END AS SYSTEM
FROM
MXMC_RT.idmv_link_ext a
LEFT OUTER JOIN MXMC_RT.idmv_link_ext2 b ON
a.mcothermskeyvalue = b.mcothermskeyvalue
INNER JOIN MXMC_RT.idmv_link_ext2 b ON
a.mcothermskeyvalue = b.mcthismskeyvalue
INNER JOIN mxmc_rt.idmv_vallink_basic c ON
a.mcthismskey = c.mskey
WHERE
a.mcotherocname = 'MX_PRIVILEGE' and a.mcthisocname = 'MX_PERSON' and
a.mcothermskeyvalue like 'ROLE:%' and c.mcvalue like '%0%-%9%'
OR((c.MCATTRNAME = 'MX_VALIDTO')OR (c.MCATTRNAME = 'MX_VALIDFROM'));
Since I'm not a SQL expert, i hope that someone can help me here.
Thanks in advance for your time and help.
Best regards,
Hussein