AnsweredAssumed Answered

SQL query to fetch spaces on which user has permissions

Question asked by nikes on Apr 5, 2013
Latest reply on Apr 5, 2013 by 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

Outcomes