How to implement a dynamic Authorization Scheme in Oracle APEX?
7 min read
Table of contents
- Why Use a Dynamic Authorization Scheme in your APEX Applications?
- How to Set Up 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);
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
checks if the component is already stored in the apex_auth_component table
register the component if it doesn't exist
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 ) is pragma autonomous_transaction; begin insert into apex_auth_component (app_id, page_id, component_type, component_static_id) values (p_application_id, p_page_id, p_component_type, p_component_static_id); commit; 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 is 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; begin apex_debug.enter( 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*/ else null; 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 register_component( 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 auco.id = 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
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:
return dynamic_authorization.is_component_authorized( 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.
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.
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: https://apex.oracle.com/pls/apex/r/louis/authorization-scheme/home
Users: demo_reader, demo_contributor, demo_administrator
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.