How to implement a dynamic Authorization Scheme in Oracle APEX?

How to implement a dynamic Authorization Scheme in Oracle APEX?


In almost every Oracle APEX application, we need to set up authorization schemes to manage access to pages, buttons, processes, etc... This post will explain how it is possible to build a dynamic authorization allowing your users to update their rights without having to change your code.

Some time ago, I published a "Monday's Tip for Oracle APEX" on Twitter about building a dynamic authorization scheme

This tip was one of the most engaging and Patrick Wolf retweeted it to explain why and how to use dynamic authorization

Based on the engagement, it seems that this APEX feature was not very well documented and there is not that many blog post on the subject. I decided to write this guide to give the light this feature deserves!

Why Use a Dynamic Authorization Scheme in your APEX Applications?

The first question that comes immediately is, why should I use it?

The answer to that question is simple: if you want to update your user permission without having to modify your application then this feature is for you.

The idea is to assign authorization to APEX components such as pages, regions, buttons, etc. to roles. Then having a single authorization scheme which is PL/SQL function returning a boolean that will check if the current user's role(s) can grant access to the component.

Let's look at what we need to get that working, hopefully, it will become clearer...

How to Set Up a Dynamic Authorization Scheme in Oracle APEX

Creating the data model

To make this solution work, we have to store the APEX components in a table first and then we need another table to indicate whether this component is authorized or not for a specific role. Not that we will use the built-in access control available out of the box with APEX to simplify the setup.

-- create tables
create table apex_auth_component (
    id                  number generated by default on null as identity 
                        constraint apex_auth_componen_id_pk primary key,
    app_id              number not null,
    page_id             number not null,
    component_static_id varchar2(255 char),
    component_type      varchar2(255 char) not null

create table apex_auth_role_component (
    id                     number generated by default on null as identity 
                           constraint apex_auth_role_com_id_pk primary key,
    apex_auth_component_id number
                           constraint apex_auth_rol_apex_auth_com_fk
                           references apex_auth_component on delete cascade,
    role_static_id         varchar2(255 char) not null,
    is_authorized          varchar2(1 char) default 'N' 
                           constraint apex_auth_role_is_authoriz_ck
                           check (is_authorized in ('Y','N'))

-- table index
create index apex_auth_role_com_i1 on apex_auth_role_component (apex_auth_component_id);

To summarize:

  • the apex_auth_component table will store the APEX components

  • the apex_auth_role_component will store if a role have access to a specific APEX component

Building the authorization scheme logic

For any components for which I will assign the authorization scheme, I want that the authorization procedure

  1. checks if the component is already stored in the apex_auth_component table

  2. register the component if it doesn't exist

  3. validate that the connected user can access it (based on the apex_auth_role_component table content)

So let's see the package code

create or replace package dynamic_authorization as
    function is_component_authorized (
        p_application_id in number,
        p_page_id in number,
        p_component_type in varchar2,
        p_component_id in number,
        p_component_name in varchar2
    ) return boolean;
end dynamic_authorization;
create or replace package body dynamic_authorization as

    procedure register_component(
        p_application_id in number,
        p_page_id in number,
        p_component_type in varchar2,
        p_component_static_id in varchar2
        pragma autonomous_transaction;
        insert into apex_auth_component
        (app_id, page_id, component_type, component_static_id)
        (p_application_id, p_page_id, p_component_type, p_component_static_id);

    end register_component;

    function is_component_authorized (
        p_application_id in number,
        p_page_id in number,
        p_component_type in varchar2,
        p_component_id in number,
        p_component_name in varchar2
    return boolean
        l_is_authorized boolean := false;
        l_component_static_id apex_auth_component.component_static_id%type;
        l_component_registered pls_integer;
        l_component_authorized pls_integer;
            p_routine_name => 'dynamic_auth.is_component_authorized', 
            p_name01       => 'p_application_id',
            p_value01      => p_application_id,
            p_name02       => 'p_page_id',
            p_value02      => p_page_id,
            p_name03       => 'p_component_type',
            p_value03      => p_component_type,
            p_name04       => 'p_component_id',
            p_value04      => p_component_id ,
            p_name05       => 'p_component_name',
            p_value05      => p_component_name
        case p_component_type
            when 'APEX_APPLICATION_PAGES' then
                l_component_static_id := null;
            when 'APEX_APPLICATION_PAGE_REGIONS' then

                select static_id
                into l_component_static_id
                from apex_application_page_regions
                where application_id = p_application_id
                and page_id = p_page_id
                and region_id = p_component_id;

            when 'APEX_APPLICATION_BUTTONS' then
                select button_static_id
                into l_component_static_id
                from apex_application_page_buttons
               where application_id = p_application_id
                and page_id = p_page_id
                and button_id = p_component_id;
        /*Add more cases here depending on the components you want to use*/
        end case;

        select count(*)
        into l_component_registered
        from apex_auth_component
        where app_id = p_application_id
        and page_id = p_page_id
        and component_type = p_component_type
        and (l_component_static_id is null or component_static_id = l_component_static_id);

        if l_component_registered = 0 then
                p_application_id => p_application_id,
                p_page_id => p_page_id,
                p_component_type => p_component_type,
                p_component_static_id => l_component_static_id
        end if;

        select count(*)
        into l_component_authorized
        from apex_appl_acl_user_roles aaur
        join apex_auth_role_component aurc 
          on aurc.role_static_id = aaur.role_static_id
          and aurc.is_authorized = 'Y'
        join apex_auth_component auco 
          on = aurc.apex_auth_component_id
          and auco.app_id = p_application_id
          and auco.page_id = p_page_id
          and auco.component_type = p_component_type
          and (l_component_static_id is null or auco.component_static_id = l_component_static_id)
        where aaur.application_id = p_application_id
        and aaur.user_name = sys_context('APEX$SESSION','APP_USER');

        return (l_component_authorized > 0);
    end is_component_authorized;
end dynamic_authorization;

This package contains two procedures:

  • is_component_authorized: which contains the logic detailed previously and returns a boolean whether the user can access the component or not

  • register_component: that inserts the component in the apex_auth_component table with an autonomous transaction

Note: for this demo, I use the default APEX ACL roles (reader, contributor and administrator) to keep it simple. You could use your own role table by replacing the reference to the apex_appl_acl_user_roles view.

This package is only here for demonstration and does not manage all the component types.

Now we have our database tables and package, let's create our simple demo application to leverage that power!

APEX application setup

Creating the authorization scheme

Create an authorization scheme "Dynamic Authorization" named "Dynamic Authorization" as displayed in the screenshot below

Screenshot showing the Dynamic Authorization configuration

This authorization scheme calls the is_component_authorized function to authorize the user to access or not the component. Here is the code for easy copy/paste:

  p_application_id => :APP_ID,
  p_page_id => :APP_PAGE_ID,
  p_component_type => :APP_COMPONENT_TYPE,
  p_component_id   => :APP_COMPONENT_ID,
  p_component_name => :APP_COMPONENT_NAME

The last parameter is the Evaluation Point where you can only choose between the last two options:

  • Once per component

  • Always (No Caching)

The main difference is that the first option is evaluated only once per component and then the result is stored in the cache to be retrieved if the user needs to access the component another time during the same session. That's why it is faster and recommended in production. But you also need to consider that the second option allows live access to change users' authorizations during the same session. You can grant access to pages, buttons, etc. without having the user log out and then log in to get the new rights.

Building the authorization configuration page

This page is really important because it's the one that will let the administrator associate role and component authorization. For this example, I simply build a basic master/detailed Interactive Grid page to let the user select an APEX component like a page and choose which role is authorized to access it.

The master Interactive Grid is based on the apex_auth_component table and will display all the components that are registered with the "Dynamic Authorization" authorization scheme. The detailed IG will display the child records from the apex_auth_role_component table and let the administrator toggle the is_authorized flag.

Screenshot showing the component authorization page with a master/detailed interactive grid allowing end user to toggle on/off the access to application components

Of course, you can enhance the UI/UX of this page, this example is like the MVP of dynamic authorization! If someday you implement it, drop me a comment to see the nice configuration screen you decided to build!

Assigning the authorization scheme to APEX components

In the Oracle APEX Builder, you have to assign the authorization scheme to the components (pages, regions, buttons, etc.) you want to dynamically control the access. To do this, select the component in the tree and find under the Security section, the Authorization Scheme attribute. See the screenshot below.

Screenshot showing where to assign the Authorization Scheme "Dynamic Authorization" to an APEX component


Here is a small video showing the dynamic authorization scheme in action, the app on the left uses the demo_reader user and the one on the right uses the demo_administrator to manage access to the components.

If you want to test it, you can access the demo app here:

Users: demo_reader, demo_contributor, demo_administrator
Password: demo123456


In this article, we have seen how to implement a basic dynamic authorization scheme in an Oracle APEX application. By choosing the evaluation point "Once per component" or "Always (No Caching)", APEX gives us additional information about the component for which the authorization is evaluated. We can use this extra information to dynamically check if the user can access it or not. By using this functionality you can give more power to your end users allowing them to manage the rights and permissions in their applications.

I hope this blog post will help you to understand better this awesome APEX feature.