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