Skip to Content
avatar image
Former Member

SAP IdM - indirect privileges and further information | SQL querie

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

Add comment
10|10000 characters needed characters exceeded

  • What, exactly is your question? We're all happy to help, but that's hard if we don't know what needs to be done.

    Matt

    IDM Moderator

  • Former Member

    Hi Matt,

    thanks for your answer.

    Due that I'm not a SQL Expert, i have currently the issue to build a single Report with the following requirements: Validity of a user, overview of all permissions to a user (direct & indirect), Group of the Business role and e.g. from where the indirect privilege comes.

    The code above was an entry Point - Have someone such like an Report or an idea how to customize the Code above.

    Thanks a lot in advance.

    Best regards , Hussein

  • Get RSS Feed

0 Answers