Preventing Privileges being removed after an Initial Load

[Former Member]

This blog is relevant to Version 7.2 SP7.

While it may valid for other versions but you'll need to investigate yourselves.

The Issue: When users are synchronised to ABAP they loose their original roles and profiles

You will probably find that, after the initial load, everything looks fine.  However, when you first synchronise a user back to the ABAP (or ABAP Business Suite) system, they lose all their roles and profiles.  Then comes the wailing and gnashing of teeth.  If you were careful, it's closely followed by the restoration of your user store and a trip back to the drawing board.  This has been a problem for a while and people have come up with a variety of ways around it.  Below  is mine.

The Cause: sap_abap_getNameOfAssignedPendingPrivileges

The issue is this script. If you go through it (as I did), it basically comes down to an SQL statement that retrieves the privileges that should be assigned to the back-end system.  It works fine in all cases EXCEPT when dealing with 'initial load' privileges.  The offending SQL is:

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 = <mskey>
AND assignment.mcLinkType = 2 AND assignment.mcLinkState IN (0, 1) AND assignment.mcExecState IN (1, 512, 513)
AND assignment.mcAddAudit IS NOT NULL 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>';

Simple.

However, during the initial load, the following siituation occurs:

Roles:

A role is imported with mcAddAudit of -1 and mcValidateAddAudit of -1.

This means that the role will never satisfy the SQL criteria: assignment.mcAddAudit > assignment.mcValidateAddAudit

Profiles

Profiles have a different issue.  They are imported with mcAddAudit = NULL and mcValidateAudit = NULL.  You can see immediately the problem here:  assignment.mcAddAudit IS NOT NULL

Not going to get past that gate any time soon.

The Solution: Modifying Initial Load and updating sap_abap_getNameOfAssignedPendingPrivileges

Roles

The first problem, roles is easy enough to fix by changing the SQL in sap_abap_getNameOfAssignedPendingPrivileges.  You simply need to change:

assignment.mcAddAudit > assignment.mcValidateAddAudit 

to

assignment.mcAddAudit >= assignment.mcValidateAddAudit 

Given that you'd be strugging to have an assignment with the same add audit value as validate audit value, this should cause no issues.  It allows the -1 = -1 to pass the SQL and therefore Roles will not be kept in the back end.  Don't do it yet - there's more changes to come.

Profiles

Profiles are a little more tricky.

I got around it by setting the Process Id.

In the Initial Load job for ABAP or ABAP Business Suite, locate the pass:  WriteABAPUsersProfilePrivilegeAssigments

Change the Destination to include a process info tag:

MXREF_MX_PRIVILEGE {A}{ProcessInfo=InitialLoad}<PRIV:PROFILE:%$rep.$NAME%:%profileAssignments%>

Now we need to modify the script to cater for this.  I broke the original single SQL assignment up to make it easier on myself.  Its included below:

var sql = "SELECT DISTINCT privilegename.mcMSKEYVALUE \ 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) ";sql = sql + "AND ((assignment.mcAddAudit IS NOT NULL AND (assignment.mcAddAudit >= assignment.mcValidateAddAudit OR assignment.mcValidateAddAudit IS NULL)) or (assignment.mcAddAudit IS NULL AND assignment.mcProcessInfo = 'InitialLoad')) ";sql = sql + "AND repositorynames.attrname = 'MX_REPOSITORYNAME' AND repositorynames.SearchValue = '" + repositoryName + "' \
AND privilegetype.attrname = 'MX_PRIVILEGE_TYPE'  AND privilegetype.SearchValue = '" + privilegeType + "'";

This will ensure that it finds any profile that has the Process Info set to 'Initial Load' if the mcAddAudit is NULL.  Once the privilege has been 'touched' by the identity store, it'll get an mcAddAudit set and the original script would have worked.

There's a whole reason why you can't just search for mcAddAudit is NULL (it could be waiting on a task or approval) which means you need to set some other identifier to make sure that your initial profiles are kept.

There's probably a number of other ways of doing it.  This works for me and has been tested successfully.

Hope it helps.

Update: Bulk uploads and Reconcile - the next adventure.

So it appears that bulk uploads of profile assignments have the same problems.  My fix disappears as soon as the first Reconcile process comes through and sets the mcProcessInfo to 'Reconcile'.  You only need to do this if you bulk upload profile assignments.  Doing them through the UI works fine.

My new sql line is:

sql = sql + "AND ((assignment.mcAddAudit IS NOT NULL AND (assignment.mcAddAudit >= assignment.mcValidateAddAudit OR assignment.mcValidateAddAudit IS NULL)) or (assignment.mcAddAudit IS NULL AND (assignment.mcProcessInfo = 'InitialLoad' OR  assignment.mcProcessInfo = 'Reconcile')))";