List permissions over all documents

[hgonzalez_]

Do you know how to obtain the permissions that a user has over all documents in a workspace, via SQL?

[Clément_Lardeur · Accepted solution]

I think it's not possible by using an NXQL querry however you can try directly in SQL (postgresql) :

SELECT hierarchy.id AS "Document ID",
   hierarchy.name AS "Document Name",
   acls.grant AS "Grant",
   acls.permission AS "Permission", 
   acls.user AS "User/Group ID" FROM acls 
LEFT JOIN hierarchy ON (hierarchy.parentId = acls.id)
LEFT JOIN user2group ON ("user2group"."groupId" = acls.user)
WHERE hierarchy.parentId = '<documentId>' 
    AND ("user2group"."userId" = '<userId>' OR acls.user = '<userId>');

Replace <userId> and <documentId> according to your needs.

NB: This request not take into account rights inherited by groups. (try to make a more complicated request using 'group2group')

[hgonzalez_]

Thanks.

[Clément_Lardeur]

The good way is to pass by the SecurityManager.java service provided by Nuxeo and use it in your own view. Generally, you never had to pass directly by an SQL request.

[Florent_Guillau]

FWIW the reason why there's no built-in way to do this in NXQL is that NXQL was designed to return documents (CoreSession.query) or document's properties (CoreSession.queryAndFetch).

Conceivably something could be added to the existing NXQL framework to do what you want, but in what format do you propose that query results be returned?