Changing the Alfresco LDAP Group Identifier

We were originally using Apple OS X Server as our LDAP store for our Alfresco instance.

Apple’s OS X Server uses OpenLDAP but adds custom schema for many things including users and groups. As a result we ended up using the description LDAP attribute for Alfresco’s ldap.synchronisation.groupIdAttributeName.

We’ve since migrated to a generic OpenLDAP server (with a bit of our own custom schema) so we’re now able to use the more common and unchanging cn attribute for the group id.

When we change ldap.synchronisation.groupIdAttributeName in ldap-synchronisation.properties Alfresco imports the new groups properly but group permissions on spaces will retain the old group name so we need to change those to use the new cn attribute.

What we did was to create a temporary table in the Alfresco database, import the mapping of the cn attribute to the description attribute, then run a query to replace the old authorities with the new.

The following assumes Alfresco version 3.x.

Create the Temp Table

CREATE TABLE  alfresco.t_ldap_groups (

`dn` VARCHAR( 255 ) NULL ,

`cn` VARCHAR( 255 ) NULL ,

`description` VARCHAR( 255 ) NULL

);

Import the LDAP Group Data

We used phpLDAPAdmin to export our groups subtree as CSV with only the cn and description attributes, then imported that file into the t_ldap_groups table just created.

Replace the Old Authorities

I’m by no means an SQL expert but the query below does the following:

Strips GROUP_ from the current stored group long name
Searches the temporary LDAP table for that group long name and corresponding group short name
Updates the alf_authority.authority field with GROUP_group short name

UPDATE alf_authority

SET authority = CONCAT('GROUP_',

(SELECT cn FROM t_ldap_groups WHERE description =    SUBSTRING(alf_authority.authority, 7) LIMIT 1))

WHERE authority LIKE 'GROUP_%' AND

(SELECT cn FROM t_ldap_groups WHERE description =    SUBSTRING(alf_authority.authority, 7) LIMIT 1) IS NOT NULL;

In Alfresco 2.x the authority is stored directly in the alf_access_control_entry table as well so the update statement would be a bit more complicated.

Drop the Temp Table

DROP TABLE t_ldap_groups;

So far we haven’t had any adverse effects on our development server doing things this way but if anyone has a better method or potential issues with this one let us know.