Expired role/privilege removal issue in IDM

[Former Member]

Hello All,

I am using SAP IDM 7.2, SP8 .

I have scenario where business user assign privilege/role to the user with validfrom and validto date.

example, validfrom = 04.12.2014 or NULL(empty) and validto = 25.12.2014 .

Question 1. When validto date becomes past then role/privilege is expired. Does IDM automatically remove this expired role/privilege from IDM ?

If yes, then what configuration is required to do this as in my IDM system, it does not happen (not even with reconciliation).

Question 2. If answer to question 1 is No then sap standard script "sap_abap_getNameOfAssignedPendingPrivileges" should not fetch expired privileges when provisioning but query used in the script fetches expired privileges resulting in  provisioning job getting failed.

Anyone having this issue ?

Regards,

Pradeep

[Former Member]

Hi All,

As I understand, Expired Roles/Privileges should get removed from the user automatically due to Housekeeping scheduled procedure 'Check for expired attributes' as shown below.

But it does not happen and user continue to have expired roles/privileges in IDM.

Could you please suggest how to resolve this issue ?

Thank you.

Regards,

Pradeep

[Steffi_Warnecke]

Hello Pradeep,

I would suggest to also open an OSS ticket with SAP for this.

Regards,

Steffi.

[Former Member]

Hi,

You may check the state of the assignment with this query:

select mcThisMSKEY, mcOtherMSKEY, mcLinkState, mcLinkType, mcDisabled, mcValidFrom, mcValidTo, mcDirValidFrom, mcDirValidTo, mcAssignedDirect, mcAssignedInheritCount, mcExecState

from idmv_link_ext woth (nolock) where mcThisMSKEY = <UserMSKEY> and mcOtherMSKEY = <PrivMSKEY>

Regards,

Ole K.

[Former Member]

Hi Ole K. ,

Thank you.

Yes, I had checked the status of Expired Roles/Privileges assigned to user and they are having mcexecstate = 1 .

I wonder why housekeeping job does not remove these expired entries or am I missing something ?

Thanks & Regards,

Pradeep

[Former Member]

Hi Steffi,

I have opened OSS and solution is awaited.

Just thought if I can get solution here in the meantime.

Thank you.

Regards,

Pradeep

[Former Member]

Hi Pradeep,

Could you show an example of all the columns from the query? That will show a more exact state of the assignment.

Regards,

Ole K.

[Former Member]

Hi Ole K. ,

Here is screenshot.

Thank you.

Thanks & Regards,

Pradeep

[terovirta]

You cannot get better IdM-help than Ole K (or Per-C)

It sounds like a bug to me, AFAIK the script in assign/remove ABAP role/profile have changed between Sp8-9 patches so that hints to me that there have been problems that have been addressed.

regards, Tero

[Former Member]

Hi Tero,

Thank you for response.

Could you please let me know the exact script name ?

I would check it.

As I understand, expired priv/role assigned to user should get removed automatically due to housekeeping job(procedure) 'Check for expired atributes'.

Regards,

Pradeep

[Former Member]

Hi Pradeep,

I am not sure that the housekeeping will resolve this.

Make sure that your dispatcher policy have "Evaluate relations" checked.

Also check the mcCheckLink column of the link records.

Regards,

Ole K.

[terovirta]

Hi Pradeep,

I am on Sp9 patch 7 designtime (MMC+DB) and Sp9p4 runtime. We tested similar expiration use case to yours after upgrading to these versions (which was suggested upgrade via OSS to another issue) and it worked out of the box (as assumed and as it should work for you).

In Sp9 the script that is called by the ABAP plugins #4 and #5 is the highlighted one, why the other ones (that are not called from the higlighted one) are still included probably hint about the corrections that have taken place. I am usign the Provisioning Framework 2 and have been using it since first Sp9 upgrade.

regards, Tero

[Former Member]

Hi Ole K.

Thank you for your response.

Yes, dispatcher policy have "Evaluate relations" checked.

mcCheckLink is NULL for all such link entries.

Thank you.

Regards,

Pradeep

[Former Member]

Hi Tero,

Thank you for your response.

The script "sap_abap_getNameOfAssignedPendingPrivileges" which you highlighted basically fetch user privileges which are already in OK status and status as 'Pending Add' because of below query being used in the script.

SELECT DISTINCT privilegename.mcMSKEYVALUE

FROM idmv_value_basic_all repositorynames 

INNER JOIN idmv_value_basic_all privilegetype   ON privilegetype.mskey = repositorynames.mskey 

INNER JOIN idmv_entry_simple privilegename   ON privilegename.mcMSKEY = repositorynames.mskey

INNER JOIN mxi_link assignment  ON assignment.mcOtherMskey = repositorynames.mskey

WHERE assignment.mcThisMskey = <usermskey>

AND assignment.mcLinkType = 2 AND assignment.mcLinkState IN (0, 1) AND assignment.mcExecState IN (1, 512, 513)

AND (assignment.mcAddAudit > assignment.mcValidateAddAudit or assignment.mcValidateAddAudit IS NULL)

AND repositorynames.attrname = 'MX_REPOSITORYNAME' AND repositorynames.SearchValue = '<reponame>'

AND privilegetype.attrname = 'MX_PRIVILEGE_TYPE' AND privilegetype.SearchValue = 'ROLE'

This script does not exclude expired privileges.

Moreover, I think both expired privileges/roles should get removed from user in IDM itself and should not appear in idmv_link_ext view(DB).

Could you please share the code of the script in SP9 so that I can compare it with script in SP8 ?

Regards,

Pradeep

[terovirta]

Pradeep Singh Rawat wrote:

assignment.mcExecState IN (1, 512, 513) 

What is bothering me are the execStates, unless my notes fail me;1 is OK, 512 is pending add and 513 is pending remove.

You would think there's difference if it's pending for deprovision (513) vs provision (512)? If you assign a bus role and take away old role at the same time in the UI, both roles have privs to same repository and you submit the form, what kind of combination of ABAP-roles get sent?

This is Sp9 and the pending add/remove are neither differentiated..

var sql = "SELECT DISTINCT privilegename.mcMSKEYVALUE, assignment.mcValidFrom, assignment.mcValidTo \

FROM idmv_value_basic_all repositorynames " + nolock + " \

INNER JOIN idmv_value_basic_all privilegetype " + nolock + " ON privilegetype.mskey = repositorynames.mskey \

INNER JOIN idmv_entry_simple privilegename " + nolock + " ON privilegename.mcMSKEY = repositorynames.mskey \

INNER JOIN mxi_link assignment " + nolock + " ON assignment.mcOtherMskey = repositorynames.mskey \

WHERE assignment.mcThisMskey = " + mskey + " \

  AND assignment.mcLinkType = 2 \

  AND (\

     (assignment.mcLinkState = 0 AND assignment.mcExecState = 1 AND assignment.mcDisabled = 0) \

    OR  (\

      assignment.mcLinkState = 1 AND assignment.mcExecState  IN (512,513) \

      AND ( \

         (assignment.mcAddAudit > assignment.mcValidateAddAudit) \

        OR \

         (assignment.mcAddAudit IS NOT NULL AND assignment.mcValidateAddAudit IS NULL) \

       ) \

     ) \

   ) \

  AND repositorynames.attrname = 'MX_REPOSITORYNAME' AND repositorynames.SearchValue = '" + repositoryName + "' \

  AND privilegetype.attrname = 'MX_PRIVILEGE_TYPE'  AND privilegetype.SearchValue = '" + privilegeType + "'";

[Former Member]

Hi Tero,

513 is also Pending Add ( Details here - LinkExecState and LinkExecStateHier)

Provisioning always overwrites all abap backend roles with all privileges in IDM coming out of query given below in the script used in Hook Task 4 and 5.

Removal and Addition of  roles with common privileges at same time would cause problem for common privileges (due to removal - mcexecstate = 1536(pending remove) and due to add - mcexecstate = 512/513 (pending add)). I am not sure how common privilege provisioning due to these status would react.

I don't think this script is playing any role on expired privilege/role issue . Script is responsible only to fetch privileges to be provisioned to backend systems using Hook task 4 and 5.

I do not know what is causing the issue.

Below query, I am using in SP8 script. earlier one was same as of below just adapted to run directly in DB2 to fetch privileges of the user to be provisioned for analysis purpose.

I only see that change is on use below

++++++++++++++++++++++++++++++++++++++++++++++

(assignment.mcLinkState = 0 AND assignment.mcExecState = 1 AND assignment.mcDisabled = 0)

assignment.mcLinkState = 1 AND assignment.mcExecState  IN (512,513)

assignment.mcAddAudit IS NOT NULL

++++++++++++++++++++++++++++++++++++++++++++++

SP 8 query:

var sql = "SELECT DISTINCT privilegename.mcMSKEYVALUE,assignment.mcvalidfrom,assignment.mcvalidto \

      FROM idmv_value_basic_all repositorynames " + nolock + " \

      INNER JOIN idmv_value_basic_all privilegetype " + nolock + " ON privilegetype.mskey = repositorynames.mskey \

      INNER JOIN idmv_entry_simple privilegename " + nolock + " ON privilegename.mcMSKEY = repositorynames.mskey \

      INNER JOIN mxi_link assignment " + nolock + " ON assignment.mcOtherMskey = repositorynames.mskey \

      WHERE assignment.mcThisMskey = " + mskey + " \

            AND assignment.mcLinkType = 2 AND assignment.mcLinkState IN (0, 1) AND assignment.mcExecState IN (1, 512, 513) \

            AND (assignment.mcAddAudit > assignment.mcValidateAddAudit or assignment.mcValidateAddAudit IS NULL) \

            AND repositorynames.attrname = 'MX_REPOSITORYNAME' AND repositorynames.SearchValue = '" + repositoryName + "' \

            AND privilegetype.attrname = 'MX_PRIVILEGE_TYPE'  AND privilegetype.SearchValue = '" + privilegeType + "'";

Thank you.

Regards,

Pradeep

[terovirta]

Ok, I'm glad (for once) that my memory failed..

regards, Tero

[former_member2987]

Pradeep,

Evaluation of VALID FROM/TO is supposed to happen in a housekeeping task as I recall.

Not quite sure why the script would be not working in this case.

You're probably going to have to go hunting in the database to look at the SPs that evaluate the FROM/TO.

Matt

[Former Member]

Hi Matt,

Thank you. 🙂

1. If Evaluation of valid from/to was part of housekeeping then reconciliation should have removed expired roles/privileges but that does not happen in my case.

2. Query in the script does not have mcvalidto in where condition to exclude expired privileges.

ex. (mcvalidto >= to_char(SYSDATE,'YYYY-MM-DD') OR mcvalidto IS NULL) if provided in the query can exclude expired privileges.

3. I have checked all SPs of IDM 7.2  but did not find any relevant info about this.

Appreciate any help to rectify this issue.

Regards,

Pradeep