Oracle Apex - Creating Custom User Access Control

In my previous post, I have given the examples to create a dynamic tree menu and dynamic popup navigation menu in Oracle Apex. And in this post, I am going to provide an example of custom user access control so that you can control access to specific menu options and pages for a user in Oracle Apex application.

Creating Custom User Access Control in Oracle Apex

To understand this tutorial, first you have to follow one of my tutorials, I mentioned in the above paragraph. Because we will use the same table TREE_MENU that we have created previously for our tree menu and popup navigation menu. The following is the structure of that table:

Create table tree_menu (
  parent_node integer,
  child_node integer,
  menu_desc varchar2(50),
  menu_type varchar2(20),
  page_no integer
);

Alter Table Tree_Menu add constraint pk_treemenu
primary key (menu_type, parent_node);

We have the following example data into it:

You can see in the above data, that we have primary key columns PARENT_NODE and MENU_TYPE.  Also, you can notice that PAGE_NO is linked to it. Note, the hyphen is a null value, do not insert a hyphen, simply leave blank those values.

So if you want to control access to these options, then you have to create a new table in which you can specify the MENU_TYPE, PARENT_NODE, USERNAME (Oracle Apex APP_USER) and the STATUS (A for active and I for Inactive). Finally, create the following new table to control user access:

create table user_access (
  username varchar2(100),
  menu_type varchar2(20),
  parent_node number,
  status varchar2(1)
);

Enter the following data into it (Change the USERNAME to your username):

Now we can add the subquery based on the above table to the query we created for the tree menu and the popup navigation menu. The following is the additional criteria to control user access to the menu options:

and exists (Select null from user_access u
where t.parent_node = u.parent_node 
and t.menu_type = u.menu_type
and upper(u.username) = upper(:APP_USER)
and u.status = 'A')

You have to add the following query to the custom list we created for the popup navigation menu. Go to the Shared Components > Lists, then click on the DYNAMIC_POPUP_MENU. Then overwrite the existing query with the following query:

select     level,
        menu_desc as label,
		        decode(page_no, null, null, 'f?p=&APP_ID.:'||"PAGE_NO"||':&APP_SESSION.')  as target, 
				'NO' is_current,
        decode(page_no, null, 'fa-folder-o', 'fa-file-text') as image
   from (select menu_desc, 
parent_node, 
child_node, 
page_no, 
menu_type
from TREE_MENU T where menu_type = 'HOME' 
and exists (Select null from user_access u
  where t.parent_node = u.parent_node 
and t.menu_type = u.menu_type
and upper(u.username) = upper(:APP_USER)
and u.status = 'A'))
  start with child_node is null
connect by prior parent_node = child_node 
union all
select     level,
        menu_desc as label,
		        decode(page_no, null, null, 'f?p=&APP_ID.:'||"PAGE_NO"||':&APP_SESSION.')  as target, 
				'NO' is_current,
        decode(page_no, null, 'fa-folder-o', 'fa-file-text') as image
   from (select menu_desc, 
parent_node, 
child_node, 
page_no, 
menu_type
from TREE_MENU T where menu_type = 'MAIN'
and exists (Select null from user_access u
  where t.parent_node = u.parent_node 
and t.menu_type = u.menu_type
and upper(u.username) = upper(:APP_USER)
and u.status = 'A'))
  start with child_node is null
connect by prior parent_node = child_node

Now when you will run the application, you will see the only options you have configured Active in the USER_ACCESS table. Below is the screenshot:

You can add the subquery mentioned above to the tree as well the filter out the result.

Now you have controlled the menu options for the user, means the only options will display you have configured in USER_ACCESS. But what if a user tries to directly access the application URL by entering the page number on which he/she doesn't have the access?

To control this, we have to create a custom authorization scheme using the tables TREE_MENU and the USER_ACCESS. And then we will assign this authorization scheme to every page in our application. To do that, follow these steps:

Create an Authorization Scheme in Oracle Apex

1. Go to Shared Components > Authorization Schemes and then click on the Create button. Then follow the instructions as shown in the below images:

Add the following query in the above PL/SQL function body:

declare
cursor c_access
is
select 'Y'
from tree_menu t
  where page_no = :APP_PAGE_ID
  and exists (select null from user_access u
  where t.parent_node = u.parent_node
  and t.menu_type = u.menu_type
  and u.status = 'A'
  and upper(u.username) = UPPER(:APP_USER));

l_exist varchar2(1);
begin

  open c_access;
  fetch c_access into l_exist;
  close c_access;

  if nvl(l_exist, 'N') = 'Y' then
     return true;
  else
     return false;
  end if;

exception
when others then
   return false;
end;

Then click on the Create button, and your authorization scheme will be created.

Now you have to assign this authorization scheme to every page of your application. To do this, open the page in the page designer, then in the Security section, change the authorization scheme to USER_PAGE_ACCESS, which you have just created.

Now save the changes. Logout from the runtime application and re-login to take effect. Below is the screenshot if a user doesn't have access to a particular page:

See also:

• Oracle Apex Tree Example
• Oracle Apex Dynamic Popup Navigation Menu Example
• Oracle Apex – Create Report with Form with Region Display Selector Example