ACL Reporting

[AEM_Dan]

Is there a way, outside of custom code, to generate a report of all ACLs?  I am really just interested in everything under /content.  I am currently creating a package of /content, unzipping the file, then searching all the _rep_policy.xml files.  It's very tedious and error-prone.

[Jineet_Vora · Accepted solution]

Hi @AEM_Dan, I don't think there is any OOTB way to fetch ACLs. You can explore the ACS Commons plugin called User Exporter here - https://adobe-consulting-services.github.io/acs-aem-commons/features/exporters/users/index.html which might be useful in pulling in the report; basically a list of users under certain groups.

For ACLs probably you might have to write a custom code.

[lukasz-m]

Hi @AEM_Dan, you can try to use ACS Commons Report Builder - [1]. It use SQL query so it is flexible in terms of collecting data that will be included in the report. It allows to browse report results directly in AEM or/and download it and review offline. I think you should be able to achieve your goal by creating proper query.

[1] - https://adobe-consulting-services.github.io/acs-aem-commons/features/report-builder/index.html

[AEM_Dan]

Thanks guys.  Do you know the syntax to retrieve ACLs using SQL (or x-path, QureyBuilder, etc)?  Or could point me to documentation?  I've searched before and can't find anything.

[lukasz-m]

@AEM_Danyou can try one of below queries as a starting point, those are SQL2 query

• it will return all allow and deny nodes that are stored under rep:policy node
  

  SELECT * FROM [rep:GrantACE] AS s WHERE ISDESCENDANTNODE([/content]) UNION SELECT * FROM [rep:DenyACE] AS s WHERE ISDESCENDANTNODE([/content])

  This query will require custom index to be created you can use OAK index generator for that https://oakutils.appspot.com/generate/index
• this will return rep:policy nodes
  

  SELECT * FROM [rep:ACL] AS s WHERE ISDESCENDANTNODE([/content])

[AEM_Dan]

I know it's been some time, but I did want to follow up on this.  Those SQL queries did indeed work, so thank you @lukasz-m.  I also added rep:Restrictions. They don't line up with the allow/deny results so you have to manually sort them with the correct allow/deny.  DM me if you want the entire package, but here are the .content.xml files that would go under /jcr_root/var/acs-commons/reports.

ACL Report:

<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:sling="http://sling.apache.org/jcr/sling/1.0" xmlns:cq="http://www.day.com/jcr/cq/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0"
    jcr:primaryType="cq:Page">
    <jcr:content
        cq:designPath="/etc/designs/acs-commons"
        jcr:created="{Date}2022-05-02T10:10:32.521-04:00"
        jcr:createdBy="admin"
        jcr:primaryType="nt:unstructured"
        jcr:title="ACL Report"
        sling:resourceType="acs-commons/components/utilities/report-builder/report-page">
        <config
            jcr:primaryType="nt:unstructured"
            sling:resourceType="wcm/foundation/components/parsys">
            <queryconfig
                jcr:created="{Date}2022-05-02T10:14:16.943-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T10:46:00.780-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/configs/queryconfig"
                pageSize="25"
                query="SELECT * FROM [rep:GrantACE] AS S WHERE ISDESCENDANTNODE([{{path}}]) UNION SELECT * FROM [rep:DenyACE] AS S WHERE ISDESCENDANTNODE([{{path}}]) UNION SELECT * FROM [rep:Restrictions] AS S WHERE ISDESCENDANTNODE([{{path}}])"
                queryLanguage="JCR-SQL2"
                reportExecutor="com.adobe.acs.commons.reports.models.QueryReportExecutor"/>
        </config>
        <parameters
            jcr:primaryType="nt:unstructured"
            sling:resourceType="wcm/foundation/components/parsys">
            <basic
                jcr:created="{Date}2022-05-02T10:17:06.718-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-02T17:22:23.349-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/parameters/basic"
                fieldLabel="Path"
                name="path"
                required="true"
                resourceType="granite/ui/components/coral/foundation/form/textfield"/>
        </parameters>
        <columns
            jcr:primaryType="nt:unstructured"
            sling:resourceType="wcm/foundation/components/parsys">
            <path
                jcr:created="{Date}2022-05-02T10:18:02.837-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-02T10:18:13.182-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/path"
                exporter="com.adobe.acs.commons.reports.models.PathReportCellCSVExporter"
                heading="Path"
                link="true"/>
            <text
                jcr:created="{Date}2022-05-02T10:22:27.427-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-02T10:22:41.590-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/text"
                exporter="com.adobe.acs.commons.reports.models.StringReportCellCSVExporter"
                heading="Type"
                property="jcr:primaryType"/>
            <text_303698746
                jcr:created="{Date}2022-05-02T10:22:56.991-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T10:52:54.907-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/text"
                exporter="com.adobe.acs.commons.reports.models.StringReportCellCSVExporter"
                heading="User/Group"
                property="rep:principalName"/>
            <text_1323327829
                jcr:created="{Date}2022-05-02T10:23:24.205-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-02T10:23:55.627-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/text"
                exporter="com.adobe.acs.commons.reports.models.StringReportCellCSVExporter"
                heading="Permissions"
                property="rep:privileges"/>
            <text_1604372322
                jcr:created="{Date}2022-05-02T10:25:37.420-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-02T10:26:14.153-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/text"
                exporter="com.adobe.acs.commons.reports.models.StringReportCellCSVExporter"
                heading="Restrictions"
                property="rep:glob"/>
        </columns>
    </jcr:content>
</jcr:root>

ACL Report by Principal Name:

<?xml version="1.0" encoding="UTF-8"?>
<jcr:root xmlns:sling="http://sling.apache.org/jcr/sling/1.0" xmlns:cq="http://www.day.com/jcr/cq/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0"
    jcr:primaryType="cq:Page">
    <jcr:content
        cq:designPath="/etc/designs/acs-commons"
        jcr:created="{Date}2022-05-05T10:43:48.262-04:00"
        jcr:createdBy="admin"
        jcr:primaryType="nt:unstructured"
        jcr:title="ACL Report by Principal Name"
        sling:resourceType="acs-commons/components/utilities/report-builder/report-page">
        <config
            jcr:primaryType="nt:unstructured"
            sling:resourceType="wcm/foundation/components/parsys">
            <queryconfig
                jcr:created="{Date}2022-05-05T10:46:18.180-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T11:11:49.107-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/configs/queryconfig"
                pageSize="25"
                query="SELECT * FROM [rep:GrantACE] AS S WHERE ISDESCENDANTNODE([{{path}}]) AND [rep:principalName]='{{repPrincipal}}' UNION SELECT * FROM [rep:DenyACE] AS S WHERE ISDESCENDANTNODE([{{path}}]) AND [rep:principalName]='{{repPrincipal}}' UNION SELECT * FROM [rep:Restrictions] AS S WHERE ISDESCENDANTNODE([{{path}}])"
                queryLanguage="JCR-SQL2"
                reportExecutor="com.adobe.acs.commons.reports.models.QueryReportExecutor"/>
        </config>
        <parameters
            jcr:primaryType="nt:unstructured"
            sling:resourceType="wcm/foundation/components/parsys">
            <basic
                jcr:created="{Date}2022-05-05T10:47:21.879-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T10:47:34.616-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/parameters/basic"
                fieldLabel="Path"
                name="path"
                required="true"
                resourceType="granite/ui/components/coral/foundation/form/textfield"/>
            <basic_2025136412
                jcr:created="{Date}2022-05-05T10:47:39.049-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T10:48:12.838-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/parameters/basic"
                fieldLabel="User/Group"
                name="repPrincipal"
                required="true"
                resourceType="granite/ui/components/coral/foundation/form/textfield"/>
        </parameters>
        <columns
            jcr:primaryType="nt:unstructured"
            sling:resourceType="wcm/foundation/components/parsys">
            <path
                jcr:created="{Date}2022-05-05T10:48:47.304-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T10:48:54.139-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/path"
                exporter="com.adobe.acs.commons.reports.models.PathReportCellCSVExporter"
                heading="Path"
                link="true"/>
            <text
                jcr:created="{Date}2022-05-05T10:49:25.783-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T10:49:36.893-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/text"
                exporter="com.adobe.acs.commons.reports.models.StringReportCellCSVExporter"
                heading="Type"
                property="jcr:primaryType"/>
            <text_814444162
                jcr:created="{Date}2022-05-05T10:49:43.357-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T10:50:18.839-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/text"
                exporter="com.adobe.acs.commons.reports.models.StringReportCellCSVExporter"
                heading="User/Group"
                property="rep:principalName"/>
            <text_1000560093
                jcr:created="{Date}2022-05-05T10:50:23.933-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T11:06:00.104-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/text"
                exporter="com.adobe.acs.commons.reports.models.StringReportCellCSVExporter"
                heading="Permissions"
                property="rep:privileges"/>
            <text_1557720899
                jcr:created="{Date}2022-05-05T11:06:07.216-04:00"
                jcr:createdBy="admin"
                jcr:lastModified="{Date}2022-05-05T11:06:23.186-04:00"
                jcr:lastModifiedBy="admin"
                jcr:primaryType="nt:unstructured"
                sling:resourceType="acs-commons/components/utilities/report-builder/columns/text"
                exporter="com.adobe.acs.commons.reports.models.StringReportCellCSVExporter"
                heading="Restrictions"
                property="rep:glob"/>
        </columns>
    </jcr:content>
</jcr:root>