Cannot change WebCenter Spaces role memberships #2

To complement my earlier posting, Cannot change WebCenter Spaces role memberships, Support finally came around to confirm the issue.

It is raised as bug Bug 20662946 : IF UNIQUEMEMBER OF A USER IN A ROLE IS DELETE ERRORS OCCUR IN WCP

Abstract:
WebCenter role membership object in OID is using the uniquemember attribute to keep track of its members. The formatting of uniquemember is not compliant to specification as it should have been the RDN of the user.
Due to invalid RDN formatting, the uniquemember is identified as dangling (orphan) attribute by the OID diagnostics tool - which in case this OID diagnostics tool is run with the fix option enabled - can lead a to broken WebCenter where memberships no longer can be modified.

Cannot change WebCenter Spaces role memberships

For a still unknown reason, existing memberships for some users cannot be changed anymore. The information to the user is not explaining:



"Cannot modify or revoke role Y for user X"

The logs are just one bit more explaining:

Caused by: oracle.security.jps.service.policystore.PolicyObjectNotFoundException: 
javax.naming.directory.NoSuchAttributeException: 
[LDAP: error code 16 - One or more values for attribute uniquemember does not exist]; 
remaining name 'cn=se8272062_f650_41a3_bd20_0817cdd18e2a\#-\#Moderator,cn=Roles,
cn=webcenter,cn=dev_domain,cn=JPSContext,cn=root_webcenter_dev'

That didn't help, or did it? One example reveals indeed it is a missing uniquemember, querying the given entry in the OID:

dn: cn=s05aa87b8_5a13_4471_9e07_55f74cbbc402\#-\#Moderator, cn=Roles, cn=webc
 enter, cn=dev_domain, cn=JPSContext, cn=root_webcenter_dev
orcljaznprincipal: orcljaznjavaclass=weblogic.security.principal.WLSUserImpl+
 cn=avmolden
orcljaznprincipal: orcljaznjavaclass=weblogic.security.principal.WLSUserImpl+
 cn=AVTVDIJK
objectclass: top
objectclass: orclrole
orcljaznjavaclass: oracle.security.jps.service.policystore.ApplicationRole
uniquemember: cn=avmolden+orcljaznjavaclass=weblogic.security.principal.WLSUs
 erImpl
cn: s05aa87b8_5a13_4471_9e07_55f74cbbc402#-#Moderator
orclapplicationcommonname: webcenter

Indeed, apparently there should be a orcljaznprincipal-uniquemember pair for a valid user membership. Here, the affected user AVTVDIJK didn't have the uniquemember part of this pair. To fix, we need to have the "missing" uniquemember added, based on the existing orcljaznprincipal membership. As this ldap information is ultimately in the database (schema ODS), we can simply query for the missing/affected entries.

Affected members

We need a new function to normalize the Relative Distinguished Name.

create or replace function ods.reverse_tokens
  (p_in in varchar2) return varchar2
is
   t_in varchar2(1024) := p_in;
   t_out varchar2(1024) := '';
begin
   --2 commas, cut after second but last comma
   while instr(t_in,',',-2)>0 loop
      t_out:=t_out||','||substr(t_in,  instr(t_in,',',-2)+1,
         instr(t_in,',',-1)-instr(t_in,',',-2)-1);
      t_in := substr(t_in,1,instr(t_in,',',-2));
   end loop;
   --1 comma, cut from beginning
   t_out:=t_out||','||substr(t_in,1,instr(t_in,',',-1)-1);
   return t_out;
end;
/

The grand query for the affected users:

-- wc_space_header is a synonym to the spaces owners table wc_space_header
select (select wc.space_id from wc_space_header wc 
   where wc.space_guid=substr(dn.rdn,4,instr(dn.rdn,'\',1)-4)) "SPACE_ID"
,      lower(substr(p.attrvalue,instr(p.attrvalue,'+',1)+1)) "USER"
,      dn.rdn||ods.reverse_tokens(dn.parentdn) "OID_RDN"
from   ods.ct_orcljaznprincipal p 
,      ods.ct_dn dn
where  dn.entryid=p.entryid
and    instr(p.attrvalue,'orcljaznjavaclass=weblogic.security.principal.WLSUserImp',1)=1
and    instr(dn.rdn,'orclguid=',1)=0
and    (p.entryid,lower(substr(p.attrvalue,instr(p.attrvalue,'+',1)+1))) not in (
  select u.entryid
  ,      lower(substr(u.attrvalue,1,instr(u.attrvalue,'+',1)-1))
  from   ods.ct_uniquemember u
  where  p.entryid=u.entryid
);

Fix

Now we can run the fixscript. An sql query producing an ldif file.

select 'dn: '||dn.rdn||ods.reverse_tokens(dn.parentdn)||chr(13)||chr(10)||
   'changetype: modify'||chr(13)||chr(10)||'add: uniquemember'||chr(13)||chr(10)||
   'uniquemember: '||lower(substr(p.attrvalue,instr(p.attrvalue,'+',1)+1))||
   '+orcljaznjavaclass=weblogic.security.principal.WLSUserImpl'
from   ods.ct_orcljaznprincipal p 
,      ods.ct_dn dn
where  dn.entryid=p.entryid
and    instr(p.attrvalue,'orcljaznjavaclass=weblogic.security.principal.WLSUserImp',1)=1
and    instr(dn.rdn,'orclguid=',1)=0
and    (p.entryid,lower(substr(p.attrvalue,instr(p.attrvalue,'+',1)+1))) not in (
  select u.entryid
  ,      lower(substr(u.attrvalue,1,instr(u.attrvalue,'+',1)-1))
  from   ods.ct_uniquemember u
  where  p.entryid=u.entryid
);

It produces an ldif file like this:

dn: cn=s05aa87b8_5a13_4471_9e07_55f74cbbc402\#-\#Moderator,cn=roles,cn=webcenter,cn=dev_domain,cn=jpscontext,cn=root_webcenter_dev
changetype: modify
add: uniquemember
uniquemember: cn=avtvdijk+orcljaznjavaclass=weblogic.security.principal.WLSUserImpl

The fix file can be loaded with $ORACLE_HOME/bin/ldapadd