Alfresco & Share Blog

In Alfresco, you can create Groups (i.e group of users), and use them to manage permission on spaces. This is obviously a best practice to assign permission through Groups, not Users.

If you have a LDAP, you can also easily synchronized your Groups and Users from LDAP to Alfresco, using the standard synchro job provided by Alfresco.

But once the Group has been applied on several spaces ACL (as Coordinator, Contributor, etc), you have currently no possibility to know the corresponding space list…So what if you want to check on which spaces/documents the Group has access and also which rights have been granted to this Group ? This is the purpose of the SQL query provided hereafter…

Actually we had this need, because we had to rename the Group name in Alfresco into upper case (due to some upper/lower case conflict between the Group name in our LDAP and in Alfresco).
Basically, (our) LDAP is case “unsensitive” (I mean we cannot create 2 Groups with the same name but distinct case, like “My_Group” and “MY_GROUP”), but Alfresco is case “sensitive” (so you can create 2 Groups with the same name but distinct case, like “My_Group” and “MY_GROUP”).

Also, what you should know is that there is no way to change the Group name in Alfresco programatically….(validated with support ; see https://issues.alfresco.com/jira/browse/ALF-2619).

This query will return the list of spaces, on which the Group “My_Group” has access (explicitely, not through inheritance), and also the level of access granted (Coordinator, Contributor, etc):

SELECT ap.node_id, ap.string_value, acl.inherits, aperm.name
FROM alf_node an, alf_node_properties ap, alf_acl_member acl_m,alf_access_control_entry ace,alf_authority alfauth, alf_access_control_list acl, alf_permission aperm
WHERE an.id = ap.node_id AND ap.qname_id= 90
AND an.acl_id = acl_m.acl_id AND alfauth.authority LIKE ‘%My_Group’
AND acl_m.ace_id = ace.id AND alfauth.id = ace.authority_id
AND acl.id = acl_m.acl_id AND aperm.id = ace.permission_id
AND acl_m.pos=0;

———-
Some explanation about the parameters used:
ap.node_id (space id)
ap.string_value (space name)
acl.inherits (Inherit Parent Permission check box : value 1- true, 0- false)
aperm.name (Permission role : value Coordinator, Collaborator, Consumer, Editor etc)
———-

Then with the node id retrieved from above query you could get the exact path of the space, with this other query:

Example:
SELECT parent_node_id, child_node_id, child_node_name
FROM alf_child_assoc
start with child_node_id = 66469
CONNECT BY PRIOR parent_node_id = child_node_id
order by parent_node_id;

If you want to get also the corresponding inherited permission (i.e sub-spaces and documents which inherits from the parent space ACL), you just have to remove the filtering condition ” AND acl_m.pos=0; “.

Note1: these queries has been tested with version 3.2 E.

Note2: if the 3.2 Alfresco instance has been installed from scratch (empty), you should use the value : “ap.qname_id= 90”.
Otherwhise, if the instance has been upgraded (e.g from v2.x to 3.2) then you might have to get the proper qname_id value with this query:
SELECT MIN(ID) FROM alf_qname WHERE LOCAL_NAME = ‘name’;

This entry was posted on Friday, July 30th, 2010 at 11:45 am and is filed under Permission, Repository. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.