Skip to Content

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%>

ProfileAssign.png

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’)))”;

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Nicolas Varga

    Hi Peter,

    I’m trying your solution and I have an error message when I save the JScript sap_abap_getName….

    The error is : Function: sap_abap_getName….:Unterminated Sstring constant: 59

    Here is the line 59 :

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

    Here is all the section i’ve pasted :

    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 + “‘”;

    Could you please help me ?

    i tried to understand the error and compared the original script and this one, but i don’t find the solution.

    Thx.

    (0) 
    1. Peter Wass Post author

      Hi Nicolas

      This is why I had splitting lines with \.

      It may be that some strange characters have copied over if you just copied it from here.  Try deleteing the return carraiges at the end of each line and putting them back.  Alternatively, you can go the brute force method and set var sql for each line.

      sql = sql + “<next line>”;

      This removes all the ‘\’ and return carraige problems but is slightly less efficient…

      Peter

      (0) 
  2. Cooper Richard

    Hi Peter,

    Great analysis and well written.

    If anyone from SAP is reading this then I would be very interested to hear of any plans to fix this bug (or is there already a fix available). It seems to me to be a very serious issue.

    Richard.

    (0) 
  3. Stephen Hart

    Hi Nicolas,

    I had the same issue,  it seems that when you copy and paste the sql a space is added after the split line, so make sure that there are no spaces between split line \ and the start of the WHERE clause

    As per below

    repositorynames.mskey \WHERE assignment.mcThisMskey = ” + mskey + ” \

    (0) 
    1. Nicolas Varga

      Hi Peter & Stephen,

      You both were right, there was a blank and / or a return carriages somewhere.

      Now I don’t have that error message, but it still doesn’t work.

      I’ll create new post right nowbecause here I cannot add pictures.

      Nicolas.

      (0) 

Leave a Reply