jcr-sql2 query jackrabbit ACL ACE access controls nt:hierarchyNode rep:policy
The ultimate problem was actually how do you handle 'gracefully' removing Principals from the (default) jackrabbit security workspace when there's the potential they are being referenced by either: (a) groups; or (b) access controls (ACLs). This may be a topic for another (much more detailed) post, but for now the focus is on (b) detecting if there are any ACLs in the repository that reference the Principal.
Aside: why is it a concern? Removing the Principal won't effect enforcement of the access controls, but anything attempting to process the ACL definition will hit an error for the missing Principal:
javax.jcr.InvalidItemStateException: Item does not exist anymore: 6e332039-2956-323c-8e82-212de8f88ff0`
The AccesControl documentation on the jackrabbit wiki states:
How Resource-based ACLs are stored
Resource-based ACLs are stored per resource/node in a special child node
rep:policy. This one will have a list of
rep:GrantACEchild nodes (usually named
allow0,...) for grant access control entries and
rep:DenyACEchild nodes (usually named
deny0,...) for deny access control entries.
Each ACE node has a
rep:principalNameSTRING property pointing to the user or group this ACE belongs to, and a
rep:privilegesNAME multi-value property, containing all the privileges of this ACE.
Note that you can read/browse these nodes using the JCR API, but cannot modify them. This must always happen through the JCR access control API.
How do you target these
rep:policyitems in a JCR-SQL2 query ... and more importantly has anyone done this before - to save me the time?
I couldn't find an example, but by careful reading of the
builtin_nodetypes.cnd(and some trial and error) the following query will list all access control entities (ACEs) that reference a given Principal (i.e a User or Group) with-in a given access control list (ACL) set on a resource (file or folder).
select resource.*, ace.* from [nt:hierarchyNode] as resource inner join [rep:ACL] as acl ON ISCHILDNODE(acl, resource) inner join [rep:ACE] as ace ON ISCHILDNODE(ace, acl) where ace.[rep:principalName] = "kevin"
The results will look like (in table form):
Note that 'Result Node-path' - full path to the resource - won't (and can't) be a a "column" Value in the result
javax.jcr.query.Rowitems but can be obtained via a
javax.jcr.Nodeitem referenced by the
javax.jcr.query.Row. Also note that the Principal reference here is via the 'name' as stored in the protected
rep:principalNamefield - even if using the default security workspace, this won't be the full 'principal path' (path to the org.apache.jackrabbit.api.security.principal.ItemBasedPrincipal).
The stackoverflow [jcr-sql2] info page includes a pretty neat summary of what JCR-SQL2 is with links to the reference specs and implementations (note the above is specific to jackrabbit). The JCR 2.0 SQL-2 Grammar diagrams are particularly useful.