SQL query to fetch spaces on which user has permissions

[nikes]

Hello,

I am trying to list all the spaces and subspaces on which users has permissions (any permission) using SQL.

I have managed to list spaces/sub-spaces where a user is invited directly and also as a member of a group.

but what I am trying also is when user is not directly invited on a space by by a subgroup.

Example: GroupA is a parent group of GroupB
User1 added in GroupB
GroupA has been invited on Space ABC

Now, how can I get listing of Space ABC using SQL?

I used following SQL to fetch when user/group invited directly

SELECT ap.node_id nid, ap.string_value nodeName, acl.inherits inherit, aperm.name perms
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= 107831655 
AND an.type_qname_id = 107831661
AND an.acl_id = acl_m.acl_id AND alfauth.authority = 'user/group-name'
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;

‍‍‍‍‍‍‍‍‍‍‍

Thanks,
Nikesh

[jpfi]

Hi,
why are you using SQL instead of any Alfresco API as recommended?
Cheers, Jan

[nikes]

Hi,

Yes, we always use API for any sorts of communication with Alfresco.

Actually we have urgent request, so to save time and opportunity to dig in Alfresco schema.

Additional question:
Does Alfresco evaluates permission at runtime? I mean when a Parent group is invited, and user is added in a subgroup.
Or full permissions hierarchy (Subgroups at any level) is persisted in ACL related tables?


Thanks,
Nikesh