Introduction
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
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.
Showtime!
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
Password: demo123456
Conclusion
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.