How to debug APEX authorization schemes, #JoelKallmanDay

I

I was going to write about the brand new and hot Oracle AI feature, the prebuilt OCI Generative AI agent with RAG, which I saw last week at local Oracle event at Prague. You basically get a pretrained AI agent, throw at it your files and it will be answering your questions with knowledge gained from your files.

The plan was to spend some time during the weekend and play with it, but both my cloud accounts are at Frankfurt. Despite the claims of the Oracle support, you have to be in Chicago region. It is not available in other regions yet. I don't want to create another free OCI account and on free it is not possible to subscribe to another region. Today is day #5 since I am unsuccessfully upgrading my OCI to paid version (and despite having a ton of credits from the Oracle ACE program). As all of you certainly know, the Oracle support sucks.

You don't have to be sad, because Adrian is the expert on this field and he wrote about this amazing feature today and with much bigger details you would get from me. And he also integrated this into the APEX... So go ahead and read it, I promise it is very interesting.

So I will write about something else.

The problem

How to check which authorization schemes in APEX (lets call them roles) can user access?

Typically I create a card region on user profile page and list assigned roles there. That looks neat and user can check if he has the roles he requested. But today I will cover more generic approach useful for debugging roles, including the internal roles which should not be exposed to the user (like when you combine 2 user roles into 1, like is_team_leader_or_manager) and you are interested if your autorization schemes works as intended.

First of all, we need to establish APEX security context. I will use the CORE package to help me with this.

SET DEFINE ON
BEGIN
    core.create_session (
        in_user_id  => '&USER_ID',
        in_app_id   => &APP_ID
    );
END;
/

To list these roles, we can utilize the APEX_APPLICATION_AUTHORIZATION view and to evaluate the role we can use the APEX_AUTHORIZATION.IS_AUTHORIZED API. A small problem is that it returns boolean, so unless you are lucky to be on 23ai, you must create a wrapper around (which I did for you in the CORE package).

-- check all application authorizations
SELECT
    t.authorization_scheme_name,
    core.is_authorized(t.authorization_scheme_name) AS is_authorized
    --
FROM apex_application_authorization t
WHERE t.application_id = core.get_app_id()
ORDER BY t.authorization_scheme_name;

When I am showing this info to users, it is good to show them friendly names and some description. Typically, I have a table for this, but lets use the APEX translation messages.

For each your role create two messages (you can do it manually in APEX if you want and actually to be able to call this you have to enable Modify This Application in Runtime API Usage under the application Security):

SET DEFINE ON
SET VERIFY OFF
DECLARE
    in_auth_scheme              CONSTANT VARCHAR2(256) := '&AUTHZ_SCHEME_NAME';
    in_auth_name                CONSTANT VARCHAR2(256) := '&USER_FRIENDLY_NAME';
    in_auth_desc                CONSTANT VARCHAR2(256) := '&USER_FRIENDLY_DESCRIPTION';
BEGIN
    APEX_LANG.CREATE_MESSAGE (
        p_application_id        => core.get_app_id(),
        p_name                  => in_auth_scheme || '_NAME',
        p_language              => 'en',
        p_message_text          => in_auth_name,
        p_used_in_javascript    => FALSE
    );
    --
    APEX_LANG.CREATE_MESSAGE (
        p_application_id        => core.get_app_id(),
        p_name                  => in_auth_scheme || '_DESC',
        p_language              => 'en',
        p_message_text          => in_auth_desc,
        p_used_in_javascript    => FALSE
    );
    --
    COMMIT;
END;
/

Now we can create a view which will be the source of any report region you like or the list or cards region. If you create this inn the footer on global page, you will see it on every page.

CREATE OR REPLACE VIEW ... AS
SELECT
    t.authorization_scheme_name                                 AS auth_id,
    core.is_authorized(t.authorization_scheme_name)             AS is_authorized,
    --
    APEX_LANG.MESSAGE(t.authorization_scheme_name || '_NAME')   AS auth_name,
    APEX_LANG.MESSAGE(t.authorization_scheme_name || '_DESC')   AS auth_desc
    --
FROM apex_application_authorization t
WHERE t.application_id = core.get_app_id()
ORDER BY t.authorization_scheme_name;