In most business applications, different people should see and do different things. A cashier can create sales but not approve refunds. A finance manager can view salary reports, but a store user cannot. In Oracle APEX, these rules are implemented with authorization schemes.
This guide explains how authorization schemes work, how to design roles for a real ERP or POS application, and the security mistakes we most often find when reviewing APEX applications.
1. Authentication vs authorization
- Authentication answers who are you? It checks the username and password, social sign-in, LDAP or SSO.
- Authorization answers what are you allowed to do? It runs after the user is signed in and decides access to pages, regions, buttons, items, processes and menu entries.
A user who signs in successfully should still see only what their role permits. Authorization schemes are defined in Shared Components → Security → Authorization Schemes.
2. Types of authorization schemes
The scheme types used most often are:
- Exists SQL Query – access is granted if the query returns at least one row.
- PL/SQL Function Returning Boolean – for logic that is more complex than a single query.
- Is In Role or Group – uses roles managed by APEX Access Control or groups provided by the authentication method.
3. Design a simple role table
For custom applications, a small role model in your own schema gives full control and is easy to report on:
CREATE TABLE app_roles (
role_code VARCHAR2(30) PRIMARY KEY,
role_name VARCHAR2(100) NOT NULL
);
CREATE TABLE app_user_roles (
username VARCHAR2(255) NOT NULL,
role_code VARCHAR2(30) NOT NULL REFERENCES app_roles,
active_flag VARCHAR2(1) DEFAULT 'Y' NOT NULL,
CONSTRAINT app_user_roles_pk PRIMARY KEY (username, role_code)
);Store usernames in upper case, because :APP_USER is normally upper case for APEX and database accounts. If you use email-based or social sign-in, compare with UPPER() on both sides to avoid case mismatches.
4. Create the schemes
An Exists SQL Query scheme named FINANCE_ADMIN:
SELECT 1
FROM app_user_roles
WHERE username = UPPER(:APP_USER)
AND role_code = 'FINANCE_ADMIN'
AND active_flag = 'Y'A PL/SQL Function Returning Boolean scheme for users who may approve refunds, where the rule depends on two roles:
DECLARE
l_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM app_user_roles
WHERE username = UPPER(:APP_USER)
AND role_code IN ('STORE_MANAGER', 'FINANCE_ADMIN')
AND active_flag = 'Y';
RETURN l_count > 0;
END;Always fill in the Error Message on each scheme with something clear, such as “You are not authorised to approve refunds. Contact your administrator.”
5. Choose the right evaluation point
Each scheme has a Validate authorization scheme setting that controls caching:
- Once per session – fastest. The result is cached until the user signs out. Role changes take effect only at the next sign-in.
- Once per page view – re-evaluated on every page request. A good default for most business roles.
- Once per component – evaluated once for each component that uses it.
- Always (no caching) – evaluated every time it is referenced. Use only for rules that depend on rapidly changing data.
If an administrator removes a user’s role and expects it to apply immediately, avoid Once per session for that scheme.
6. Apply schemes at every layer
The most common security mistake in APEX applications is protecting only what the user can see. Hiding a button does not stop a determined user from submitting the request behind it. Apply the same scheme to:
- The page itself (Page → Security → Authorization Scheme)
- The button that triggers the action
- The process that performs the action
- Any Ajax Callback processes and REST endpoints that perform the same operation
- Navigation menu and list entries, so users do not see links they cannot use
For sensitive pages, also set Page Access Protection to Arguments Must Have Checksum so users cannot change item values in the URL to open other records.
7. Check authorization from PL/SQL
Inside packages and processes, reuse the same scheme instead of duplicating the logic:
IF NOT APEX_AUTHORIZATION.IS_AUTHORIZED('FINANCE_ADMIN') THEN
RAISE_APPLICATION_ERROR(-20001, 'Not authorised to post journal entries.');
END IF;This keeps the rule in one place and makes future changes safer.
8. Row-level restrictions
Authorization schemes decide whether a user can open a page or run a process. They do not filter rows. If branch users must see only their own branch’s sales, add that condition to the report queries or use a view or Virtual Private Database policy. Combine both: the scheme controls access to the feature, and the query controls which data is visible.
9. Using APEX Access Control instead
If you prefer not to build your own role tables, APEX Access Control (added through Create Page → Feature → Access Control) creates Administrator, Contributor and Reader roles and an admin page to assign users. Roles can also be managed with the APEX_ACL API, for example APEX_ACL.ADD_USER_ROLE. It is quick to set up and works well for smaller applications; larger ERP-style systems usually outgrow it and move to a custom role model.
Security checklist
- Every page, except the login and public pages, has an authorization scheme
- Buttons and the processes behind them use the same scheme
- Ajax callbacks and REST handlers are protected
- Evaluation point matches how quickly role changes must apply
- Clear error messages defined for every scheme
- Usernames compared case-insensitively
- Row-level filters added where data must be restricted
- Session State Protection and checksums enabled for sensitive pages
Need a security review of your APEX application?
AbairTech builds and reviews Oracle APEX applications for ERP, POS, HR and finance workflows, including role design and access control. See Oracle APEX Development Services.
