SQL query to fetch spaces on which user has permissions

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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