Find where are the authorization schemes used

I

I was tired of searching the app for places where is the authorization scheme referenced. So I wrote this script to get a nice overview of components and relevant pages.

To get list of the autorization schemes/roles:

SELECT
    a.authorization_scheme_name AS scheme_name,
    a.authorization_scheme_id   AS scheme_id
FROM apex_application_authorization a
WHERE a.application_id = &APP_ID
ORDER BY 1;

To get list of components and relevant pages:

SET SERVEROUTPUT ON
DECLARE
    in_app_id       CONSTANT NUMBER     := &APP_ID;
    --
    v_records       NUMBER;
    v_pages         VARCHAR2(4000);
BEGIN
    FOR c IN (
        WITH x AS (
            SELECT
                a.application_id,
                a.authorization_scheme_name AS scheme_name,
                a.authorization_scheme_id   AS scheme_id
            FROM apex_application_authorization a
            WHERE a.application_id = in_app_id
        ),
        p AS (
            SELECT
                d.apex_view_name
            FROM apex_dictionary d
            LEFT JOIN all_tab_cols c
                ON c.owner          = (SELECT MAX(u.username) FROM all_users u WHERE REGEXP_LIKE(u.username, 'APEX_\d+'))
                AND c.table_name    = d.apex_view_name
                AND c.column_name   = d.column_name
            WHERE d.column_name     = 'PAGE_ID'
        )
        SELECT
            x.scheme_name,
            d.apex_view_name,
            p.apex_view_name        AS apex_pages,  -- just to check if we run the q2 or not
            --
            -- get number of uses
            --
            'SELECT COUNT(*) FROM ' || RPAD(LOWER(d.apex_view_name), 33) ||
            ' WHERE application_id = ' || x.application_id ||
            ' AND ' || LOWER(d.column_name) || ' IN (''' || x.scheme_id || ''', ''!' || x.scheme_id || ''')' AS q1,
            --
            -- get list of pages
            --
            'SELECT LISTAGG(page_id, '','') WITHIN GROUP (ORDER BY page_id) FROM (' ||
            'SELECT DISTINCT page_id FROM ' || RPAD(LOWER(d.apex_view_name), 33) ||
            ' WHERE application_id = ' || x.application_id ||
            ' AND ' || LOWER(d.column_name) || ' IN (''' || x.scheme_id || ''', ''!' || x.scheme_id || ''') GROUP BY page_id)' AS q2
        FROM apex_dictionary d
        CROSS JOIN x
        LEFT JOIN p
            ON p.apex_view_name     = d.apex_view_name
        WHERE d.column_name         = 'AUTHORIZATION_SCHEME_ID'
            AND d.apex_view_name    != 'APEX_APPLICATION_AUTHORIZATION'
        ORDER BY 1, 2
    ) LOOP
        EXECUTE IMMEDIATE c.q1 INTO v_records;
        --
        v_pages := NULL;
        IF c.apex_pages IS NOT NULL THEN
            EXECUTE IMMEDIATE c.q2 INTO v_pages;
        END IF;
        --
        IF v_records > 0 THEN
            DBMS_OUTPUT.PUT_LINE(RPAD(c.scheme_name, 32) || RPAD(c.apex_view_name, 32) || LPAD(v_records, 4) || ' | ' || v_pages);
        END IF;
    END LOOP;
END;
/