private ResultPage<DocumentUserPermissions> fetchDeepFolderUserPermissions(
            final FetchDocumentUserPermissionsRequest request) {
        final UserDaoImpl userDao = userDaoProvider.get();
        final DocRef docRef = request.getDocRef();

        final int offset = JooqUtil.getOffset(request.getPageRequest());
        final int limit = JooqUtil.getLimit(request.getPageRequest(), true);

        final List<Condition> conditions = new ArrayList<>();

        conditions.add(userDao.getUserCondition(request.getExpression()));
        if (request.getUserRef() != null) {
            conditions.add(STROOM_USER.UUID.eq(request.getUserRef().getUuid()));
        }

        // If we have a single doc then try to deliver more useful permissions.
        final StroomUser su = STROOM_USER.as("su");
        final StroomUserGroup sug = STROOM_USER_GROUP.as("sug");
        final PermissionDoc pd = PERMISSION_DOC.as("pd");
        final PermissionDoc pdParent = PERMISSION_DOC.as("pd_parent");
        final PermissionDocCreate pdc = PERMISSION_DOC_CREATE.as("pdc");
        final PermissionDocCreate pdcParent = PERMISSION_DOC_CREATE.as("pdc_parent");

        final Name cte = DSL.name("cte");
        final Field<String> cteUserUuid = DSL.field(cte.append("user_uuid"), String.class);
        final Field<String> cteGroupUuid = DSL.field(cte.append("group_uuid"), String.class);
        final Field<Integer> ctePerms = DSL.field(cte.append("perms"), Integer.class);
        final Field<Integer> cteInheritedPerms = DSL.field(cte.append("inherited_perms"), Integer.class);
        final Field<String> cteCreatePerms = DSL.field(cte.append("create_perms"), String.class);
        final Field<String> cteInheritedCreatePerms = DSL.field(cte.append("inherited_create_perms"),
                String.class);

        final List<DocumentUserPermissions> list = JooqUtil.contextResult(securityDbConnProvider, context -> {

            // Create a select to group permissions and parent permissions for the doc.
            final Select<?> select = context
                    .select(
                            su.UUID.as("user_uuid"),
                            sug.GROUP_UUID,
                            DSL.groupConcatDistinct(pd.PERMISSION_ID).as("perms"),
                            DSL.groupConcatDistinct(pdParent.PERMISSION_ID).as("parent_perms"),
                            DSL.groupConcatDistinct(pdc.DOC_TYPE_ID).as("create_perms"),
                            DSL.groupConcatDistinct(pdcParent.DOC_TYPE_ID).as("parent_create_perms"))
                    .from(su)
                    .leftOuterJoin(sug)
                    .on(sug.USER_UUID.eq(su.UUID))
                    .leftOuterJoin(pd)
                    .on(pd.USER_UUID.eq(su.UUID).and(pd.DOC_UUID.eq(docRef.getUuid())))
                    .leftOuterJoin(pdParent)
                    .on(pdParent.USER_UUID.eq(sug.GROUP_UUID).and(pdParent.DOC_UUID.eq(docRef.getUuid())))
                    .leftOuterJoin(pdc)
                    .on(pdc.USER_UUID.eq(su.UUID).and(pdc.DOC_UUID.eq(docRef.getUuid())))
                    .leftOuterJoin(pdcParent)
                    .on(pdcParent.USER_UUID.eq(sug.GROUP_UUID).and(pdcParent.DOC_UUID.eq(docRef.getUuid())))
                    .groupBy(su.UUID, sug.GROUP_UUID);

            final Table<?> v = select.asTable("v");
            final Field<String> vUserUuid = v.field("user_uuid", String.class);
            final Field<String> vGroupUuid = v.field("group_uuid", String.class);
            final Field<Integer> vPerms = v.field("perms", Integer.class);
            final Field<Integer> vParentPerms = v.field("parent_perms", Integer.class);
            final Field<String> vCreatePerms = v.field("create_perms", String.class);
            final Field<String> vParentCreatePerms = v.field("parent_create_perms", String.class);
            assert vUserUuid != null;
            assert vGroupUuid != null;
            assert vPerms != null;
            assert vParentPerms != null;
            assert vCreatePerms != null;
            assert vParentCreatePerms != null;

            // Create a view to recursively aggregate parent permissions for users and groups so we can see all
            // inherited permissions.
            // Create common table expression to apply `with recursive`.
            final CommonTableExpression<?> commonTableExpression = cte
                    .as(context
                            .select(
                                    vUserUuid,
                                    vGroupUuid,
                                    vPerms,
                                    vParentPerms.as("inherited_perms"),
                                    vCreatePerms,
                                    vParentCreatePerms.as("inherited_create_perms"))
                            .from(v)
                            .unionAll(
                                    context.select(
                                                    vUserUuid,
                                                    vGroupUuid,
                                                    vPerms,
                                                    DSL.greatest(
                                                            DSL.ifnull(cteInheritedPerms, NULL_PERM),
                                                            DSL.ifnull(vParentPerms, NULL_PERM)),
                                                    vCreatePerms,
                                                    DSL.if_(cteInheritedCreatePerms.isNull(),
                                                            vParentCreatePerms,
                                                            DSL.if_(vParentCreatePerms.isNull(),
                                                                    cteInheritedCreatePerms,
                                                                    DSL.concat(
                                                                            DSL.concat(cteInheritedCreatePerms,
                                                                                    ","),
                                                                            vParentCreatePerms))))
                                            .from(DSL.table(cte))
                                            .join(v).on(vGroupUuid.eq(cteUserUuid))));

            // Apply `with recursive`
            final Table<?> recursive = context
                    .withRecursive(commonTableExpression)
                    .select(
                            cteUserUuid,
                            cteGroupUuid,
                            DSL.max(DSL.ifnull(ctePerms, NULL_PERM)).as("perms"),
                            DSL.max(DSL.ifnull(cteInheritedPerms, NULL_PERM)).as("inherited_perms"),
                            DSL.groupConcatDistinct(cteCreatePerms).as("create_perms"),
                            DSL.groupConcatDistinct(cteInheritedCreatePerms).as("inherited_create_perms"))
                    .from(commonTableExpression)
                    .groupBy(cteUserUuid, cteGroupUuid)
                    .asTable();

            final Field<String> recUserUuid = recursive.field("user_uuid", String.class);
            final Field<String> recGroupUuid = recursive.field("group_uuid", String.class);
            final Field<Integer> recPerms = recursive.field("perms", Integer.class);
            final Field<Integer> recInheritedPerms = recursive.field("inherited_perms", Integer.class);
            final Field<String> recCreatePerms = recursive.field("create_perms", String.class);
            final Field<String> recInheritedCreatePerms = recursive.field("inherited_create_perms",
                    String.class);
            assert recUserUuid != null;
            assert recGroupUuid != null;
            assert recPerms != null;
            assert recInheritedPerms != null;
            assert recCreatePerms != null;
            assert recInheritedCreatePerms != null;

            final Field<Integer> maxOfPermsAgg = DSL.max(DSL.ifnull(recPerms, NULL_PERM));
            final Field<Integer> maxOfInheritedAgg = DSL.max(DSL.ifnull(recInheritedPerms, NULL_PERM));
            final Field<String> concatOfCreatePermsAgg = DSL.groupConcatDistinct(
                    DSL.ifnull(recCreatePerms, ""));
            final Field<String> concatOfInheritedCreatePermsAgg = DSL.groupConcatDistinct(
                    DSL.ifnull(recInheritedCreatePerms, ""));

            final Field<Integer> maxOfPermsField = maxOfPermsAgg.as(recPerms.getName());
            final Field<Integer> maxOfInheritedField = maxOfInheritedAgg.as(recInheritedPerms.getName());
            final Field<String> concatOfCreatePerms = concatOfCreatePermsAgg.as(recCreatePerms.getName());
            final Field<String> concatOfInheritedCreatePerms = concatOfInheritedCreatePermsAgg.as(
                    recInheritedCreatePerms.getName());
            final Field<Integer> effectivePerm = DSL.greatest(
                    DSL.ifnull(maxOfPermsAgg, NULL_PERM),
                    DSL.ifnull(maxOfInheritedAgg, NULL_PERM));

            // Add additional conditions if we want to just show effective or explicit permissions.
            switch (request.getShowLevel()) {
                case SHOW_EFFECTIVE -> conditions.add(DSL.ifnull(recPerms, NULL_PERM).notEqual(NULL_PERM)
                        .or(DSL.ifnull(recInheritedPerms, NULL_PERM).notEqual(NULL_PERM))
                        .or(recCreatePerms.isNotNull())
                        .or(recInheritedCreatePerms.isNotNull()));
                case SHOW_EXPLICIT -> conditions.add(DSL.ifnull(recPerms, NULL_PERM).notEqual(NULL_PERM)
                        .or(recCreatePerms.isNotNull()));
            }

            // No sorting on the create perms
            final Map<String, Field<?>> additionalSortFieldMappings;
            if (request.isFieldInSort(DocumentPermissionFields.FIELD_EXPLICIT_DOC_PERMISSION)) {
                additionalSortFieldMappings = Map.of(
                        DocumentPermissionFields.FIELD_EXPLICIT_DOC_PERMISSION, maxOfPermsField);
            } else if (request.isFieldInSort(DocumentPermissionFields.FIELD_EFFECTIVE_DOC_PERMISSION)) {
                additionalSortFieldMappings = Map.of(
                        DocumentPermissionFields.FIELD_EFFECTIVE_DOC_PERMISSION, effectivePerm);
            } else {
                additionalSortFieldMappings = null;
            }

            final Collection<OrderField<?>> orderFields = userDao.createOrderFields(
                    request, additionalSortFieldMappings);

            // Join recursive select to user.
            final var sql = context
                    .select(STROOM_USER.UUID,
                            STROOM_USER.NAME,
                            STROOM_USER.DISPLAY_NAME,
                            STROOM_USER.FULL_NAME,
                            STROOM_USER.IS_GROUP,
                            STROOM_USER.ENABLED,
                            maxOfPermsField,
                            maxOfInheritedField,
                            effectivePerm,
                            concatOfCreatePerms,
                            concatOfInheritedCreatePerms)
                    .from(STROOM_USER)
                    .join(recursive).on(recUserUuid.eq(STROOM_USER.UUID))
                    .where(conditions)
                    .groupBy(
                            STROOM_USER.UUID,
                            STROOM_USER.NAME,
                            STROOM_USER.DISPLAY_NAME,
                            STROOM_USER.FULL_NAME,
                            STROOM_USER.IS_GROUP,
                            STROOM_USER.ENABLED)
                    .orderBy(orderFields)
                    .offset(offset)
                    .limit(limit);

            LOGGER.debug("fetchDeepFolderUserPermissions sql:\n{}", sql);
            return sql.fetch();

        }).map(r -> {
            final UserRef userRef = recordToUserRef(r);
            final Integer perms = r.get(ctePerms);
            final Integer inheritedPerms = r.get(cteInheritedPerms);
            final String createPerms = r.get(cteCreatePerms);
            final String inheritedCreatePerms = r.get(cteInheritedCreatePerms);
            final DocumentPermission permission = getPermFromPrimitive(perms);
            final DocumentPermission inherited = getPermFromPrimitive(inheritedPerms);
            final Set<String> documentCreatePermissions = getDocCreatePermissionSet(createPerms);
            final Set<String> inheritedDocumentCreatePermissions = getDocCreatePermissionSet(
                    inheritedCreatePerms);
            return new DocumentUserPermissions(
                    userRef,
                    permission,
                    inherited,
                    documentCreatePermissions,
                    inheritedDocumentCreatePermissions);
        });

        return ResultPage.createCriterialBasedList(list, request);
    }
Get permissions for users on folders plus any permissions that they inherit from their group membership.
Also join to the document create permission table to see what document create permissions users have explicitly
or inherit from their group membership.

@param request The Fetch request.
@return A result page of user permissions.
stroom-security/stroom-security-impl-db/src/main/java/stroom/security/impl/db/DocumentPermissionDaoImpl.java