Match authorization schemes on pages with navigation

T

Today, I will show you how to easily check if your authorization schemes set on pages match the schemes set in the navigation.

In my custom navigation (including the multicolumn navigation), I don't have to set it in two places (which is never a good idea). My navigation gets the schemes from the pages. But the default APEX navigation is not like that. That causes a lot of confusion to users when they see a page in navigation, but can't access it. Or when they have some hidden pages, and they are missing the navigation links.

Fortunately, if you combine few APEX views, you can find out:

SELECT
    p.application_id,
    e.list_name,
    p.page_id,
    p.page_name,
    --p.page_alias,
    --p.page_group,
    --p.page_mode,
    p.authorization_scheme  AS auth_scheme_page,
    e.authorization_scheme  AS auth_scheme_nav,
    e.entry_text,
    e.entry_target
    --
FROM apex_application_pages p
LEFT JOIN apex_application_lists l
    ON l.application_id     = p.application_id
    AND l.list_name         LIKE '%Navigation%'
    --AND l.list_name         IN ('Navigation Menu', 'Navigation Bar', 'Desktop Navigation Menu', 'Desktop Navigation Bar')
LEFT JOIN apex_application_list_entries e
    ON e.application_id     = l.application_id
    AND e.list_id           = l.list_id
    AND p.page_id           = TO_NUMBER(REGEXP_SUBSTR(e.entry_target, 'f?p=[^:]+[:](\d+)[:]', 1, 1, NULL, 1))
WHERE 1 = 1
    --AND p.application_id    = core.get_app_id()
    --AND p.page_name         != e.entry_text
    --
    AND TO_NUMBER(REGEXP_SUBSTR(e.entry_target, 'f?p=[^:]+[:](\d+)[:]', 1, 1, NULL, 1)) IS NOT NULL
    AND NVL(p.authorization_scheme, '!') != NVL(e.authorization_scheme, '!')
ORDER BY
    p.application_id,
    e.list_name,
    p.page_id;

Take a look at previous articles about authentication & authorization.