How to Control Access to a Page in Oracle Apex?

How to Control Access to a Page in Oracle Apex?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

We can create authorization schemes to control access to a page in Oracle Apex. Below are more details:

Page Access Control in Oracle Apex Using Authorization Scheme

To control access over a page, you need a table where you can store the data for the users having access to specific pages. For this, I have written a detailed post User Access Control in Oracle Apex, you can check this for a better understanding.

Create a Table to Store User Acess Information

Here, I am giving below a simple table structure for this example:

Create Table user_access (record_id number primary key,
                          user_id number,
                          page_id number);

Insert Records into user_access Table

We will insert a record to specify that the user (with user_id 1) having the access to page number 5:

Insert into user_access
    (record_id, user_id, page_id)
    values (1001, 1, 5);

Commit;

Create an Authorization Scheme

As of now, we have created the table and inserted a record. And now we can create an authorization scheme to control access to page number 5 in Oracle Apex. Below are the steps:

  1. Click on the Shared Components > Authorization Schemes then click on the Create button.
  2. Then in the next step, choose From Scratch radio button and click on the Next button.
  3. In the next step, specify the name for the scheme, for example, page_5_access.
  4. Select scheme type as Exists SQL query.
  5. And for SQL query, specify the following query:
Select null from user_access
    where user_id = :yourAppUserId
    and page_id = 5;
  1. Then specify the error message for the error message field as "You have no access to this page!".
  2. Then click on the Create Authorization Scheme button.
  3. Now go to page number 5 and in the property palette > Security, select the Authorization scheme page_5_access.
  4. Save the changes.

And it is done. Now you can run the page to test for the user with user_id 1 and for the other users as well.