Description:
To implement Role-based Authorization in Oracle APEX Application, in version 5.
Application Item: USER_ROLE
Table: ROLE_ACCESS_CONTROL
The purpose of this table is to store the privileges on Regions, Buttons, and Items for each role. Page access is controlled through the Region access (Even if one Region in a Page is given access, its Page will appear; Components cannot be created without a “Region”;)
| Column Name | Data Type | Comments |
| ROLE_ACCESS_ID | NUMBER | Primary Key |
| USER_ROLE | VARCHAR2(30) | Role Name |
| APP_ID | NUMBER | APEX System Variable |
| PAGE_ID | NUMBER | APEX System Variable |
| REGION_ID | NUMBER | APEX System Variable |
| COMPONENT_ID | VARCHAR2(30) | APEX5 System Variable |
| COMPONENT_NAME | VARCHAR2(30) | APEX5 System Variable |
| COMPONENT_TYPE | VARCHAR2(30) | APEX5 System Variable |
| DISPLAY_IND | CHAR(1) | Flag to Display/Hide a Component |
| READONLY_IND | CHAR(1) | Flag to set Read-Only for an Item |
| CREATED_BY | NUMBER | WHO columns |
| CREATED_DATE | DATE | |
| UPDATED_BY | NUMBER | |
| UPDATED_DATE | DATE |
User Interface:
Authorization Schemes to be created:
- List_Access – To control the Menu items
- Role_authorization – To control access to all components
- Role_authorization_readonly_item – To control the read-onlyproperty of an item, through the authorization scheme.
To enable the menu, in the Navigation:
Set the SECURITY è AUTHORIZATION SCHEME with the custom authorization scheme created (“ROLE_AUTHORIZATION”) for all Pages, Regions, and Buttons.
Set the READ ONLY property as below, for all the Items which needs to be controlled as READ-ONLY:
Summary:
Oracle Application Express (APEX) has two mechanisms for providing system security to be implemented on its application. The two mechanisms are authentication and authorization. The authentication mechanism already discussed in the previous article, which is implemented on the login page. Authorization is used to create deeper security control. It might be implemented on tab, page, or region. The combined usage of authentication and authorization will create an application with confidence security.
On Oracle APEX, we can create two kinds of authorization. We can create authorization with Access Control List (ACL) or with custom authorization.
queries?
Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.
