Improve handling of inconsistent parent/child permissions leading to unnavigable content

[cdrfun]

Describe the feature you'd like

This feature request aims to address situations where BookStack's permission system allows child items (pages or chapters) to have read permissions while their parent items (books, chapters, or shelves) do not have corresponding read permissions for the same user/role. This configuration leads to content being discoverable via search results but inaccessible through normal hierarchical navigation (e.g., browse through a book or chapter).
The proposed features to mitigate and manage this are:

1. Permission warnings in UI:
   When an author is editing the permissions of a page or chapter, the system should display a warning if the proposed permission settings would (likely) result in an item becoming "orphaned." An item is considered orphaned if it has read permissions, but its direct parent entity (book for a chapter; book or chapter for a page) does not grant the same level of access to the same user/role.

2. Administrative report for permission inconsistencies:
   Provide an administrative tool within the settings area that lists all pages and chapters currently in an "orphaned" state. This list would identify content that is readable but whose parent hierarchy is not accessible to the same audience, allowing administrators to proactively identify and correct these permission misconfigurations.

Describe the benefits this would bring to existing BookStack users

Implementing these features would provide these benefits:

1. Simplified prevention of incorrect permissions:
   Authors and administrators will be actively guided by UI warnings when setting permissions. This makes it significantly easier to proactively avoid configurations that lead to "orphaned" content (pages/chapters visible in search but not via navigation), thus reducing common permission-related errors from the outset.

2. More consistent and intuitive content access for all users:
   Users will experience a more reliable platform where content discoverable through search is also consistently accessible via the book and chapter navigation. This eliminates confusion and frustration caused by finding content that cannot be reached through a logical, hierarchical path.

3. Streamlined administrative oversight and correction:
   The administrative report provides a straightforward way to identify any existing instances of orphaned content. This enables administrators to efficiently audit and correct permission misconfigurations, ensuring the integrity and reliability of content accessibility across the platform with less manual effort.

Can the goal of this request already be achieved via other means?

I've described two changes: permission warnings and administration report. Imho both features would bring the biggest benefit. As a minimal solution, I'd implement the administration report.

Have you searched for an existing open/closed issue?

• I have searched for existing issues and none cover my fundamental request

How long have you been using BookStack?

Over 5 years

Additional context

As part of identifying these problematic permission scenarios, I developed following SQL query. It aims to list pages and chapters where users/roles have read access to the item itself, but lack the necessary read access to its parent book or chapter. This query attempts to consider BookStack's direct and reference-based permission checks. This query was commented by AI. It's not perfect, but a good start. I think for production the query should

-- Chapters and pages that users can read without having read access to the corresponding book/chapter.
-- This query identifies users who have read access to specific chapters or pages but do not have read access
-- to the parent elements. This means these users cannot navigate correctly through the hierarchy.
-- However, if a user has access to a referenced document, the remaining permission checks might be successful
-- (this query tries to account for such reference-based access paths).

WITH
-- 1. Collect basic permission data
-- ----------------------------------------

-- Base: Determines read permissions for a specific entity type
view_permissions_by_type AS (
    SELECT
        jp.entity_id,
        jp.entity_type,
        ur.user_id,
        u.name AS user_name,
        r.display_name AS role_name,
        r.id AS role_id -- Added role_id for potentially more precise joins if needed
    FROM joint_permissions jp
    INNER JOIN role_user ur ON jp.role_id = ur.role_id
    INNER JOIN roles r ON r.id = ur.role_id
    INNER JOIN users u ON ur.user_id = u.id
    WHERE jp.status >= 1 -- Read status or higher (assuming 1 = view)
),

-- Chapter view permissions (filtered view of base data)
chapter_view_permissions AS (
    SELECT
        entity_id AS chapter_id,
        user_id,
        user_name,
        role_name,
        role_id
    FROM view_permissions_by_type
    WHERE entity_type = 'chapter'
),

-- Book view permissions (filtered view of base data)
book_view_permissions AS (
    SELECT
        entity_id AS book_id,
        user_id,
        user_name,
        role_name,
        role_id
    FROM view_permissions_by_type
    WHERE entity_type = 'book'
),

-- Page view permissions (filtered view of base data)
page_view_permissions AS (
    SELECT
        entity_id AS page_id,
        user_id,
        user_name,
        role_name,
        role_id
    FROM view_permissions_by_type
    WHERE entity_type = 'page'
),

-- 2. Determine reference permissions
-- -----------------------------------
-- This CTE collects all users who have access to an entity because it's linked/referenced
-- from another entity they can access (e.g., an item on a shelf might make the shelf visible).
-- The `references` table links `from_id` (e.g. a shelf) to `to_id` (e.g. a book on that shelf).
-- If a user can view `to_id`, this query considers them to have a form of derived access to `from_id`.
reference_permissions AS (
    SELECT DISTINCT
        r.from_id,    -- The ID of the referencing entity (e.g., shelf, book)
        r.from_type,  -- The type of the referencing entity
        vp.user_id,   -- The user who has access
        vp.role_id    -- The role granting access
    FROM `references` r
    INNER JOIN view_permissions_by_type vp ON r.to_id = vp.entity_id AND r.to_type = vp.entity_type
),

-- 3. Identify problematic elements
-- ---------------------------------------

-- Case 1: Chapters with missing book access
problematic_chapters AS (
    SELECT DISTINCT
        b.id AS book_id,
        b.name AS book_name,
        c.id AS chapter_id,
        c.name AS chapter_name,
        NULL AS page_id,
        NULL AS page_name,
        cvp.user_id,
        cvp.user_name,
        cvp.role_name,
        cvp.role_id,
        'chapter' AS entity_type_affected,
        'Has read access to chapter, but not to parent book' AS issue_description
    FROM chapters c
    INNER JOIN books b ON c.book_id = b.id
    INNER JOIN chapter_view_permissions cvp ON c.id = cvp.chapter_id
    -- Check for direct permissions on the book
    LEFT JOIN book_view_permissions bvp ON b.id = bvp.book_id AND cvp.user_id = bvp.user_id AND cvp.role_id = bvp.role_id
    -- Check for reference-based permissions on the book
    LEFT JOIN reference_permissions rp_bk ON rp_bk.from_id = b.id
                                         AND rp_bk.from_type = 'book'
                                         AND rp_bk.user_id = cvp.user_id
                                         AND rp_bk.role_id = cvp.role_id
    WHERE
        -- Only consider non-deleted items
        b.deleted_at IS NULL AND c.deleted_at IS NULL
        -- User has chapter access but no direct or reference-based book access for the same role
        AND bvp.user_id IS NULL AND rp_bk.user_id IS NULL
),

-- Case 2a: Pages within a chapter, but with missing parent permissions
problematic_pages_with_chapter AS (
    SELECT DISTINCT
        b.id AS book_id,
        b.name AS book_name,
        c.id AS chapter_id,
        c.name AS chapter_name,
        p.id AS page_id,
        p.name AS page_name,
        pvp.user_id,
        pvp.user_name,
        pvp.role_name,
        pvp.role_id,
        'page' AS entity_type_affected,
        CASE
            -- No access to chapter AND no access to book
            WHEN (cvp.user_id IS NULL AND rp_ch.user_id IS NULL) AND
                 (bvp.user_id IS NULL AND rp_bk.user_id IS NULL)
                THEN 'Has read access to page, but not to parent book and not to parent chapter'
            -- No access to book (but has chapter access)
            WHEN bvp.user_id IS NULL AND rp_bk.user_id IS NULL
                THEN 'Has read access to page and parent chapter, but not to parent book'
            -- No access to chapter (but has book access - less likely to be the primary issue if book is accessible)
            WHEN cvp.user_id IS NULL AND rp_ch.user_id IS NULL
                THEN 'Has read access to page, but not to parent chapter'
            ELSE 'Unknown parent permission issue for page within chapter' -- Should ideally not be hit due to WHERE
        END AS issue_description
    FROM pages p
    INNER JOIN books b ON p.book_id = b.id
    INNER JOIN chapters c ON p.chapter_id = c.id -- Page is directly in a chapter
    INNER JOIN page_view_permissions pvp ON p.id = pvp.page_id

    -- Check for direct permissions on chapter and book
    LEFT JOIN chapter_view_permissions cvp ON c.id = cvp.chapter_id AND pvp.user_id = cvp.user_id AND pvp.role_id = cvp.role_id
    LEFT JOIN book_view_permissions bvp ON b.id = bvp.book_id AND pvp.user_id = bvp.user_id AND pvp.role_id = bvp.role_id

    -- Check for reference-based permissions on chapter and book
    LEFT JOIN reference_permissions rp_ch ON rp_ch.from_id = c.id
                                         AND rp_ch.from_type = 'chapter'
                                         AND rp_ch.user_id = pvp.user_id
                                         AND rp_ch.role_id = pvp.role_id
    LEFT JOIN reference_permissions rp_bk ON rp_bk.from_id = b.id
                                         AND rp_bk.from_type = 'book'
                                         AND rp_bk.user_id = pvp.user_id
                                         AND rp_bk.role_id = pvp.role_id
    WHERE
        -- Only non-deleted items and pages with a valid chapter_id
        b.deleted_at IS NULL AND c.deleted_at IS NULL AND p.deleted_at IS NULL
        AND p.chapter_id IS NOT NULL AND p.chapter_id > 0 -- Ensure page is meant to be in a chapter
        -- User is missing access to at least one of the parent elements (chapter or book)
        AND (
            (cvp.user_id IS NULL AND rp_ch.user_id IS NULL) -- Missing chapter access (direct or reference)
            OR
            (bvp.user_id IS NULL AND rp_bk.user_id IS NULL) -- Missing book access (direct or reference)
        )
),

-- Case 2b: Pages directly under a book (not in a chapter), with missing book permissions
problematic_pages_without_chapter AS (
    SELECT DISTINCT
        b.id AS book_id,
        b.name AS book_name,
        NULL AS chapter_id,
        NULL AS chapter_name,
        p.id AS page_id,
        p.name AS page_name,
        pvp.user_id,
        pvp.user_name,
        pvp.role_name,
        pvp.role_id,
        'page' AS entity_type_affected,
        'Has read access to page, but not to parent book' AS issue_description
    FROM pages p
    INNER JOIN books b ON p.book_id = b.id
    INNER JOIN page_view_permissions pvp ON p.id = pvp.page_id

    -- Check for direct permissions on the book
    LEFT JOIN book_view_permissions bvp ON b.id = bvp.book_id AND pvp.user_id = bvp.user_id AND pvp.role_id = bvp.role_id
    -- Check for reference-based permissions on the book
    LEFT JOIN reference_permissions rp_bk ON rp_bk.from_id = b.id
                                         AND rp_bk.from_type = 'book'
                                         AND rp_bk.user_id = pvp.user_id
                                         AND rp_bk.role_id = pvp.role_id
    WHERE
        -- Only non-deleted items and pages without a chapter_id
        b.deleted_at IS NULL AND p.deleted_at IS NULL
        AND (p.chapter_id IS NULL OR p.chapter_id = 0) -- Page is directly in a book
        -- User has no book access (neither direct nor reference-based) for the same role
        AND bvp.user_id IS NULL AND rp_bk.user_id IS NULL
)

-- 4. Combine results
-- --------------------------
-- Main query combines all problematic cases, showing one distinct issue per item/role combination.
SELECT DISTINCT
    book_name AS BookName,
    chapter_name AS ChapterName,
    page_name AS PageName,
    role_name AS UserRole, -- Identifies the role that has the problematic permission
    -- user_name AS UserName, -- Can be included if identifying specific users is preferred over roles
    entity_type_affected AS AffectedEntityType,
    issue_description AS IssueDescription
FROM (
    SELECT book_id, book_name, chapter_id, chapter_name, page_id, page_name, user_id, user_name, role_name, entity_type_affected, issue_description FROM problematic_chapters
    UNION ALL
    SELECT book_id, book_name, chapter_id, chapter_name, page_id, page_name, user_id, user_name, role_name, entity_type_affected, issue_description FROM problematic_pages_with_chapter
    UNION ALL
    SELECT book_id, book_name, chapter_id, chapter_name, page_id, page_name, user_id, user_name, role_name, entity_type_affected, issue_description FROM problematic_pages_without_chapter
) AS combined_results
ORDER BY BookName, ChapterName, PageName, UserRole;