Everything You Need to Know About Oracle APEX Authorization
Authorization is a process to control access to resources based on user privileges. If all users have the same rights and privileges, they are referred to as public users. However, if your application implements a different set of business rules for different users, then you must create authorization schemes in your application.
This exercise will teach you how to secure your application by implementing authorization schemes. Authorization schemes are implemented after completing an application because at that stage you know all segments of your application. You are also required to add application users and the groups they belong to. For your convenience, I have provided a script file for this section, which fulfills these prerequisites. Watch this video for visual instructions.
By defining authorization schemes, you control users' access to an entire application, page, or specific components such as a region, item, or button. For example, you could use an authorization scheme to selectively determine which menu options, pages, or buttons a user sees. It is an important security measure implemented to augment the application's authentication scheme. Common authorization scheme types include Exists, Not Exists SQL Queries, and PL/SQL Function Returning Boolean. If a component or control level authorization scheme succeeds, the user can view the component or control. If it fails, the user cannot view the component or the control.
The exercise assumes that the application comprises three main menu options, three corresponding pages, and some buttons, as depicted in the following figure.
Execute the following steps to create and implement a few authorization schemes to control your application components just mentioned.
Create Objects
Go to SQL Workshop | SQL Scripts. Click the Upload button, choose CreateTables.sql file from the downloaded Authorization folder, and click Upload. The file will appear on the SQL Scripts page. Click the run icon representing this file. Click the Run Now button on the Run Script page. The script must execute with zero errors. It creates a few tables and some seed data to test this module.
Menu Authorization
First of all, we are going to enforce the authorization scheme on application menu entries. This procedure is a bit different as you do not explicitly create an authorization scheme. Instead, you apply a condition on every menu item through a PL/SQL function that returns either true or false based on the access privileges stored in the group detail table.
- Go to Shared Components | Navigation Menu and select Desktop Navigation Menu.
- Click the CF Home menu entry.
- On the Create/Edit page, click the Conditions tab, set Condition Type to Function Body Returning a Boolean, enter the following code in the Expression 1 text area, and apply the change.
The statement on line 6 evaluates whether the currently logged-in user is an administrator. If so, then the code on line 14 is executed to return a true value. Note that admin users are exempt from restrictions and possess full access privileges to all application segments. On the contrary, if the user is not an administrator, the code specified from lines 7 through 12 is executed. This block is specified for non-admins. It checks whether the user is privileged to access the CF Home menu. Note that this code is specified for the CF Home menu entry. Modify all menu entries and add this code with the same Condition Type. The only thing you need to replace is the ‘CF Home%’ entry (on line 7) with the corresponding menu entry. For example, if you are entering this code for the Transactions menu entry, then replace the LIKE operator’s string value with ‘Transactions%’.
Page Authorization
While conditions control the rendering and processing of specific page controls or components, authorization schemes control user access to pages and page components. After protecting the application’s main menu from unauthorized access by applying conditions, the next line of defense is to make application pages secure and this can be done by adding Authorization Schemes. Note that even after protecting a specific menu option, a restricted user can get access to the pages linked with the prohibited menu options. For example, if you revoke the CF Home menu option from a user, she can still get the Home page when she logs into the application.
When you define an authorization scheme, you give it a unique name. Once defined, you can attach it to any component or control in your application. To attach an authorization scheme to a component or control in your application, simply select a scheme from the Authorization Scheme property in Page Designer.
Here are the steps to apply security to application pages by creating authorization schemes.
- Go to Shared Components and, in the Security section, click Authorization Schemes.
- Click the Create button to launch the scheme wizard.
- On the Creation Method screen, select From Scratch to create a new authorization scheme from scratch and click Next.
- On the Details wizard screen, enter the following values and click the Create Authorization Scheme button. The PL/SQL code defined underneath is similar to the one entered for menu items, except for the segment type, which in this case is the application Page. The value Once per page view selected for the Validate authorization scheme property evaluates once for each request that is processed. You can use the memorized result if the authorization scheme is referenced in more than one component on the page.
|
Property
|
Value
|
|
Name
|
Page
Access
|
|
Scheme
Type
|
PL/SQL Function Returning Boolean
|
|
PL/SQL
Function Body
|
declare
Vadmin varchar2(1);
Vallow varchar2(1);
begin
select admin into Vadmin from app_users
where upper(userid)=upper(:APP_USER);
if Vadmin = 'N' then
select allow_access into Vallow from
group_detail
where segmentID=(select segmentID from
segments
where
pageID=:APP_PAGE_ID and
segmentType='Page') and
groupID=(select groupID from
app_users
where
upper(userid)=upper(:APP_USER));
if Vallow='Y' then
return true;
else
return false;
end if;
else
return true;
end if;
exception
when NO_DATA_FOUND then return false;
end;
|
|
Error Message
|
You
are not authorized to view this page!<br /> Click <a
href="f?p=&APP_ID.:999:&SESSION.">here</a> to continue
|
|
Validation
|
Once
per page view
|
Note: Create a Blank Page and set its number to 999. After displaying the error message shown in the above table, the user will be taken to this page.
After creating the authorization scheme, call the Home page of your application in Page Designer and click the root node – Page 1: Home. In the Property Editor pane, scroll down to the Security section, set the Authorization Scheme property to Page Access, and click the Save button. The Home page is now associated with the Page Access authorization scheme. User groups who are not granted access privilege to the Home page will encounter the error message defined in the table above. After clicking the link in the error message, they will be taken to another page in the application. I created a blank page with 999 ID for such a purpose. However, you can take them to any existing page.
Page Items Authorization
This is the last level of our application security, where we will apply security to individual page components. Oracle APEX allows you to apply an authorization scheme to every component you create on a page. For the sake of simplicity, we just incorporated page buttons in the Segments setup and this is the only item we will be experimenting with in this section to test page item security.
- Create another Authorization Scheme from scratch and set the following properties for it:
|
Property
|
Value
|
|
Name
|
Display
|
|
Scheme
Type
|
PL/SQL
Function Returning Boolean
|
|
PL/SQL Function Body
|
declare
Vadmin varchar2(1);
Vallow varchar2(1);
begin
select admin into Vadmin from app_users
where upper(userid)=upper(:APP_USER);
if Vadmin = 'N' then
select allow_access into Vallow from
group_detail
where segmentID=(select segmentID from
segments
where
pageID=:APP_PAGE_ID and
segmentType='Item'
and
itemRole
Like 'Display%') and
groupID=(select groupID from
app_users
where upper(userid)=upper(:APP_USER));
if Vallow='Y' then
return true;
else
return false;
end if;
else
return true;
end if;
exception
when NO_DATA_FOUND then return false;
end;
|
|
Error
Message
|
You're
not allowed to use this button
|
|
Validation
|
Once per page view
|
2. Create one more scheme named Print using the same PL/SQL code. Replace the string ‘Display%’, specified for itemRole, with ‘Print%’. The Copy button on the Authorization Schemes report page helps you create a scheme instantly from an existing one. Click the Copy button to launch the Copy Authorization Scheme wizard. On the first wizard screen, select Display from the Copy Authorization Scheme list. This is the list you want to copy. Enter Print for New Authorization Scheme Name and click the Copy button. You are taken back to the interactive report page. Click the Print scheme link in the interactive report and change the LIKE operator's string from 'Display%' to 'Print%'.
3. Create page number 2 and 3, add Display and Print buttons to these pages, and set the Authorization Scheme property for the two buttons. The buttons will disappear for all those who are not granted respective privileges.
Prior to testing your application’s authentication and authorization schemes, I would recommend you complete the
Tree With Checkboxes tutorial that shows how to grant and revoke application rights visually.
