private ResultPage<DocumentUserPermissions> fetchDeepDocumentUserPermissions(
            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()));
        }

        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 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 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.max(DSL.ifnull(pd.PERMISSION_ID, NULL_PERM)).as("perms"),
                            DSL.max(DSL.ifnull(pdParent.PERMISSION_ID, NULL_PERM)).as("parent_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())))
                    .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);
            assert vUserUuid != null;
            assert vGroupUuid != null;
            assert vPerms != null;
            assert vParentPerms != 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"))
                            .from(v)
                            .unionAll(
                                    context.select(
                                                    vUserUuid,
                                                    vGroupUuid,
                                                    vPerms,
                                                    DSL.greatest(
                                                            DSL.ifnull(cteInheritedPerms, NULL_PERM),
                                                            DSL.ifnull(vParentPerms, NULL_PERM)))
                                            .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"))
                    .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);
            assert recUserUuid != null;
            assert recGroupUuid != null;
            assert recPerms != null;
            assert recInheritedPerms != 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<Integer> maxOfPermsField = maxOfPermsAgg.as(recPerms.getName());
            final Field<Integer> maxOfInheritedField = maxOfInheritedAgg.as(recInheritedPerms.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)));
                case SHOW_EXPLICIT -> conditions.add(DSL.ifnull(recPerms, NULL_PERM).notEqual(NULL_PERM));
            }

            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.
            // Max on the perms, as a user may be a member of multiple groups each with a perm on the doc
            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)
                    .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("fetchDeepDocumentUserPermissions 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 DocumentPermission permission = getPermFromPrimitive(perms);
            final DocumentPermission inherited = getPermFromPrimitive(inheritedPerms);
            return new DocumentUserPermissions(
                    userRef,
                    permission,
                    inherited,
                    Collections.emptySet(),
                    Collections.emptySet());
        });

        return ResultPage.createCriterialBasedList(list, request);
    }
Get permissions for users on documents plus any permissions that they 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