Authorization App in Oracle APEX Part 3

This is the 3rd part of a technical article regarding the development of an Authorization App in Oracle APEX. The article has educational purposes so I welcome comments and insights on the subject. Here the Part 2:

UPDATE: You can freely download the Final Application in This Github Repository:

In the first two parts we created Objects and App to manage authorizations, now let's create some line of code to use all this stuff. But first a brief recap. We need to define:

• Application (With the name defined in the APP_NAME variable)
• Type Object Name (With the name defined in the APP_COMPONENT_TYPE variable:)

• Object Name (With its own name except when it is a Page, in this case The Object Name is P<page number>.
• Privilege (Here my six: CREATE,READ,EDIT,DELETE,ADMIN,DENY)
• Role (The owner of the Authorization)
• Authorization (The bind of the previous 5 objects)

Finally:

• User (With the name of the authenticated users)
• Group (To group users)
• User Group (A Link between Users and Groups)
• Group Role (A link between Groups and Roles)

If we have all the data we can query this view aut_all_vw to check aut authorization:

create or replace view aut_all_vw as 
select 
    authorizations.id                                  authorization_id,
    authorizations.name                                authorization_name,
    authorizations.description                         authorization_description,
    authorizations.created                             authorization_created,
    authorizations.created_by                          authorization_created_by,
    authorizations.updated                             authorization_updated,
    authorizations.updated_by                          authorization_updated_by,
    https://www.dhirubhai.net/redir/invalid-link-page?url=roles%2eid                                           role_id,
    roles.name                                         role_name,
    roles.description                                  role_description,
    roles.created                                      role_created,
    roles.created_by                                   role_created_by,
    roles.updated                                      role_updated,
    roles.updated_by                                   role_updated_by,
    privileges.id                                      privilege_id,
    privileges.name                                    privilege_name,
    privileges.description                             privilege_description,
    privileges.created                                 privilege_created,
    privileges.created_by                              privilege_created_by,
    privileges.updated                                 privilege_updated,
    privileges.updated_by                              privilege_updated_by,
    objects.id                                         object_id,
    objects.name                                       object_name,
    objects.description                                object_description,
    objects.created                                    object_created,
    objects.created_by                                 object_created_by,
    objects.updated                                    object_updated,
    objects.updated_by                                 object_updated_by,
    type_object.id                                     type_object_id,
    type_object.name                                   type_object_name,
    type_object.description                            type_object_description,
    type_object.created                                type_object_created,
    type_object.created_by                             type_object_created_by,
    type_object.updated                                type_object_updated,
    type_object.updated_by                             type_object_updated_by,
    applications.id                                    application_id,
    applications.name                                  application_name,
    applications.description                           application_description,
    applications.created                               application_created,
    applications.created_by                            application_created_by,
    applications.updated                               application_updated,
    applications.updated_by                            application_updated_by,
    users_groups.id                                    users_group_id,
    users_groups.created                               users_group_created,
    users_groups.created_by                            users_group_created_by,
    users_groups.updated                               users_group_updated,
    users_groups.updated_by                            users_group_updated_by,
    users.id                                           user_id,
    users.name                                         user_name,
    users.description                                  user_description,
    users.created                                      user_created,
    users.created_by                                   user_created_by,
    users.updated                                      user_updated,
    users.updated_by                                   user_updated_by,
    groups.id                                          group_id,
    groups.name                                        group_name,
    groups.description                                 group_description,
    groups.created                                     group_created,
    groups.created_by                                  group_created_by,
    groups.updated                                     group_updated,
    groups.updated_by                                  group_updated_by,
    groups_https://www.dhirubhai.net/redir/invalid-link-page?url=roles%2eid                                    groups_role_id,
    groups_roles.created                               groups_role_created,
    groups_roles.created_by                            groups_role_created_by,
    groups_roles.updated                               groups_role_updated,
    groups_roles.updated_by                            groups_role_updated_by
from 
    aut_authorizations authorizations,
    aut_roles roles,
    aut_privileges privileges,
    aut_objects objects,
    aut_type_object type_object,
    aut_applications applications,
    aut_users_groups users_groups,
    aut_users users,
    aut_groups groups,
    aut_groups_roles groups_roles
where
    users_groups.user_id = users.id and
    users_groups.group_id = groups.id and
    groups_roles.group_id = groups.id and
    groups_roles.role_id = https://www.dhirubhai.net/redir/invalid-link-page?url=roles%2eid and
    objects.application_id = applications.id and
    objects.type_object_id = type_object.id and
    authorizations.role_id = https://www.dhirubhai.net/redir/invalid-link-page?url=roles%2eid and
    authorizations.object_id = objects.id and
    authorizations.privilege_id = privileges.id
/

For example we could check if the user DEMO can READ a BUTTON named CREATE in the Application AUT - Authorizations:

select  count(distinct 1) 
  from aut_all_vw a
 where application_name in ('ALL_APPLICATIONS','AUT - Authorizations') 
   and type_object_name in ('APEX_ALL','APEX_APPLICATION_BUTTONS')
   and object_name in ('ALL',
                   decode(type_object_name,'APEX_APPLICATION_PAGES',
                                           'P'||11,'CREATE'))
   and privilege_name in ('ADMIN','READ')
           and user_name = 'DEMO'; 

This query has 4 conditions:

• application_name: (With reserved word ALL_APPLICATIONS for all the applications)
• type_object_name: (With reserved word APEX_ALL for all type object name)
• object_name: (With reserved word ALL for all objects and page number exception like I said)
• privilege_name: (With reserved word ADMIN for all privileges

We can also check if there is an explicit denial for an object:

select  count(distinct 1) 
  from aut_all_vw a
 where application_name in ('ALL_APPLICATIONS','AUT - Authorizations') 
   and type_object_name in ('APEX_ALL','APEX_APPLICATION_BUTTONS')
   and object_name in ('ALL',
                   decode(type_object_name,'APEX_APPLICATION_PAGES',
                                           'P'||11,'CREATE'))
   and privilege_name in ('DENY')

Well, now the code, let's create a package with our self-explaining functions:

create or replace package aut_pkg
is
    function is_not_denied (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    ) return number;


    function can_read (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    ) return number;


    function can_edit (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    ) return number;


    function is_admin (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    ) return number;


    function can_create (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    ) return number;


    function can_delete (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    ) return number;
end aut_pkg;
/

And the body:

create or replace package  body aut_pkg
is
    function is_not_denied (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    )
    return number
    is
      l_return number :=0 ;
    begin
        select  count(distinct 1) into l_return 
          from aut_all_vw a 
         where application_name in ('ALL_APPLICATIONS',p_application_name) 
           and type_object_name in ('APEX_ALL',p_app_component_type)
           and object_name in ('ALL',decode(type_object_name,'APEX_APPLICATION_PAGES','P'||p_app_page_id,p_app_component_name))
           and privilege_name in ('DENY')
           and user_name = p_username ;


    return 1-l_return; 


    exception 
    when others then 
      return 0; 
    end is_not_denied;


    function can_read (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    )
    return number
    is
      l_return number :=0 ;
    begin
        select  count(distinct 1) into l_return 
          from aut_all_vw a
         where application_name in ('ALL_APPLICATIONS',p_application_name) 
           and type_object_name in ('APEX_ALL',p_app_component_type)
           and object_name in ('ALL',decode(type_object_name,'APEX_APPLICATION_PAGES','P'||p_app_page_id,p_app_component_name))
           and privilege_name in ('ADMIN','EDIT','READ')
           and user_name = p_username; 


    if l_return = 0 
    then
        return l_return; 
    else
        return is_not_denied (
        p_app_page_id        => p_app_page_id,
        p_application_name   => p_application_name,
        p_app_component_type => p_app_component_type,
        p_app_component_name => p_app_component_name,
        p_username           => p_username);
    end if;
    
    exception 
    when others then 
      return 0; 
    end can_read;


    function can_edit (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    )
    return number
    is
      l_return number :=0 ;
    begin
        select  count(distinct 1) into l_return 
          from aut_all_vw a
         where application_name in ('ALL_APPLICATIONS',p_application_name) 
           and type_object_name in ('APEX_ALL',p_app_component_type)
           and object_name in ('ALL',decode(type_object_name,'APEX_APPLICATION_PAGES','P'||p_app_page_id,p_app_component_name))
           and privilege_name in ('ADMIN','EDIT')
           and user_name = p_username; 


    if l_return = 0 
    then
        return l_return; 
    else
        return is_not_denied (
        p_app_page_id        => p_app_page_id,
        p_application_name   => p_application_name,
        p_app_component_type => p_app_component_type,
        p_app_component_name => p_app_component_name,
        p_username           => p_username);
    end if;


    exception 
    when others then 
      return 0; 
    end can_edit;


    function is_admin (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    )
    return number
    is
      l_return number :=0 ;
    begin
        select  count(distinct 1) into l_return 
          from aut_all_vw a
         where application_name in ('ALL_APPLICATIONS',p_application_name) 
           and type_object_name in ('APEX_ALL',p_app_component_type)
           and object_name in ('ALL',decode(type_object_name,'APEX_APPLICATION_PAGES','P'||p_app_page_id,p_app_component_name))
           and privilege_name in ('ADMIN')
           and user_name = p_username; 


    if l_return = 0 
    then
        return l_return; 
    else
        return is_not_denied (
        p_app_page_id        => p_app_page_id,
        p_application_name   => p_application_name,
        p_app_component_type => p_app_component_type,
        p_app_component_name => p_app_component_name,
        p_username           => p_username);
    end if;


    exception 
    when others then 
      return 0; 
    end is_admin;


    function can_create (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    )
    return number
    is
      l_return number :=0 ;
    begin
        select  count(distinct 1) into l_return 
          from aut_all_vw a
         where application_name in ('ALL_APPLICATIONS',p_application_name) 
           and type_object_name in ('APEX_ALL',p_app_component_type)
           and object_name in ('ALL',decode(type_object_name,'APEX_APPLICATION_PAGES','P'||p_app_page_id,p_app_component_name))
           and privilege_name in ('ADMIN','CREATE')
           and user_name = p_username; 


    if l_return = 0 
    then
        return l_return; 
    else
        return is_not_denied (
        p_app_page_id        => p_app_page_id,
        p_application_name   => p_application_name,
        p_app_component_type => p_app_component_type,
        p_app_component_name => p_app_component_name,
        p_username           => p_username);
    end if;


    exception 
    when others then 
      return 0; 
    end can_create;


    function can_delete (
        p_app_page_id                   in number,
        p_application_name              in varchar2,
        p_app_component_type            in varchar2,
        p_app_component_name            in varchar2,
        p_username                      in varchar2
    )
    return number
    is
      l_return number :=0 ;
    begin
        select  count(distinct 1) into l_return 
          from aut_all_vw a
         where application_name in ('ALL_APPLICATIONS',p_application_name) 
           and type_object_name in ('APEX_ALL',p_app_component_type)
           and object_name in ('ALL',decode(type_object_name,'APEX_APPLICATION_PAGES','P'||p_app_page_id,p_app_component_name))
           and privilege_name in ('ADMIN','DELETE')
           and user_name = p_username; 


    if l_return = 0 
    then
        return l_return; 
    else
        return is_not_denied (
        p_app_page_id        => p_app_page_id,
        p_application_name   => p_application_name,
        p_app_component_type => p_app_component_type,
        p_app_component_name => p_app_component_name,
        p_username           => p_username);
    end if;


    exception 
    when others then 
      return 0; 
    end can_delete;
end aut_pkg;
/

Some comments:

• The code is simple to read, only an explanation: the first function is_not_denied, is always included in the other funtions because a denial always override the other privileges
• The functions are quite similar so we could refactor them (an exercise for you)
• The exceptions management is very poor :( (another improvement if you can)

Now we can create our Authorization scheme:

Let's go to Shared Components -> Authorization Schemes and create New Scheme: CAN_READ

The Scheme Type is a PL/SQL Funtion Returning Boolean and the code is (some new variables are explained later):

return aut_pkg.can_read (
        p_app_page_id                   =>:APP_PAGE_ID,
        p_application_name              =>:APP_NAME,
        p_app_component_type            =>:APP_COMPONENT_TYPE,
        p_app_component_name            =>:APP_COMPONENT_NAME,
        p_username                      =>v('APP_USER')
    ) =1;

Then you have tho choose an option in the Evaluation Point :

Only the last two option are useful because (from the Help)

The options Once per component and Always (No Caching) provide additional information about the component that is secured by the authorization scheme. In authorization plugins, you can use the component.type, component.id and component.name attributes of the t_authorization parameter. For the built-in Exists SQL Query and PL/SQL Function scheme types, equivalent bind variables :APP_COMPONENT_TYPE, :APP_COMPONENT_ID and :APP_COMPONENT_NAME are available.

Example

In the Home Page we want authorize the view of the 2 regions with tha navigation list:

Let's edit the page , go to the Authorization Navigation Region, Security Section, choose the CAN_READ Authorization Scheme previously created and save the page.

Let's return in the Home Page and we will see our region (the first of two) missing because no one is authorized to see it now:

To Authorize we need:

• Application: AUT - Authorizations

• Type Object Name: APEX_APPLICATION_PAGE_REGIONS (We already have)
• Object Name: Authorization Navigation

• Privilege: READ (We already have)
• Role: Can Read Region ROLE

• Authorization: Can Read Region AUTH. IMPORTANT!! The Authorization doesn't consider the page name so is applied to All the objects with the same Type Object and the same Name in the Application. If you want to prevent this, prefix the Name Object with the page number.

• User: DEMO

• Group: Can Read Region GROUP

• User Group: Assign DEMO to Can Read Region GROUP
• Group Role: Assign Can Read Region ROLE to Can Read Region GROUP

Now in the Home Page we can see the Authorization Navigation again.

If we create a new Authorization with a DENY privilege:

this privilege overrides the others so the Region is missing again:

I hope I was clear in this tutorial, comments are welcome!