Implementing Authentication and Authorization in Oracle APEX
1. Introduction and Real-World Problem
In Blog 01, we built a modular architecture using Oracle APEX Shared Components, establishing application items like G_CURRENT_TENANT_ID, dynamic Lists of Values, and reusable packages. While our user interface is dynamic, it lacks a critical layer: enterprise security.
In a real-world multi-tenant hotel management system, different users access the platform simultaneously:
Platform Super Administrators manage tenant subscriptions and system-wide configurations.
Tenant Property Managers need full administrative control over their specific hotel properties, room inventories, and staff assignments.
Front-Desk Receptionists should only check in guests and record bookings, but must be barred from altering room pricing structures or viewing overall profit margins.
External Guests or Auditors need strictly read-only views.
Without a robust access control architecture, your system faces severe security risks:
Broken Object-Level Authorization (BOLA / IDOR): A receptionist modifies the browser URL from
f?p=101:15:&APP_SESSION.::::P15_PROPERTY_ID:10toP15_PROPERTY_ID:20and gains unauthorized access to edit another hotel property's financial details.Client-Side Obscurity over Real Security: Merely hiding a "Delete Property" button with CSS or JavaScript does not protect the underlying database process. A user can still invoke the AJAX request or submit the page via developer tools.
Privilege Creep: Managing role checks through ad-hoc
IF :APP_USER = 'ADMIN' THENstatements scattered across dozens of page processes creates a maintenance headache that leads to authorization bypasses during production updates.
2. Why the Feature Matters
Securing an enterprise APEX application requires a clear division of responsibilities:
Authentication (Who are you?): Verifies the user’s identity through credentials, whether via custom database tables, enterprise LDAP, or Single Sign-On (SSO) using OAuth2/OpenID Connect.
Authorization (What are you allowed to do?): Evaluates coarse and fine-grained permissions before rendering a page, displaying a UI region, or firing a database process.
Session State Protection (Tamper-Proof Parameters): Cryptographically signs page arguments to ensure URL parameters cannot be manipulated in transit.
Server-Side Validation (Defense-in-Depth): Guarantees that business logic and validation routines run securely inside the database engine, ignoring manipulated browser payloads.
3. Architecture or Workflow Diagram
The workflow below illustrates the sequence of security checks performed by the Oracle APEX engine from the moment a user signs in to when an authorized action executes:
4. Required Database Objects
To establish strict Role-Based Access Control (RBAC), run the following script in SQL Workshop to create the security schema:
5. Complete SQL and PL/SQL Examples
A. The Core Security Package
This package handles password hashing, secure credential validation, session initialization, and runtime authorization checks using DBMS_CRYPTO.
B. Seed an Administrative User
Execute this block to create a test property manager account:
6. Step-by-Step Oracle APEX Configuration
Step 1: Configure Custom Authentication Scheme
In App Builder, navigate to Shared Components $\rightarrow$ Security $\rightarrow$ Authentication Schemes.
Click Create $\rightarrow$ select Based on a pre-configured scheme from the gallery.
Set Scheme Type to:
Custom.Under Settings:
Authentication Function Name:
app_security_pkg.authenticate_user
Under Login Processing:
Post-Authentication Procedure Name:
app_security_pkg.post_auth_setup
Click Create Authentication Scheme and ensure it is marked as Current.
Step 2: Create Reusable Authorization Schemes
Build authorization schemes in Shared Components to enforce access policies across all pages and buttons.
A. Authorization Scheme: AUTH_IS_PROP_MGR
Navigate to Shared Components $\rightarrow$ Security $\rightarrow$ Authorization Schemes.
Click Create $\rightarrow$ select From Scratch.
Name:
AUTH_IS_PROP_MGR.Set Scheme Type:
PL/SQL Function Returning Boolean.Enter PL/SQL Code:
Set Evaluation Point:
Once per session(caching the result improves application performance).Error Message:
Access Denied: You do not possess Property Manager privileges.
B. Authorization Scheme: AUTH_CAN_EDIT_PROPERTY
This scheme prevents IDOR by evaluating whether the current session is allowed to edit the property specified in the URL (P15_PROPERTY_ID).
Click Create in Authorization Schemes.
Name:
AUTH_CAN_EDIT_PROPERTY.Set Scheme Type:
PL/SQL Function Returning Boolean.Enter PL/SQL Code:
Set Evaluation Point:
Once per page view(evaluated each time parameters change).Error Message:
Security Violation: You are not authorized to view or edit this property record.
Step 3: Enforce Session State Protection (SSP)
Prevent malicious parameter manipulation by locking down page URLs.
Navigate to Shared Components $\rightarrow$ Security $\rightarrow$ Session State Protection.
Click Set Protection across the application:
Application: Set to
Enabled.Page 15 (Property Form): Set Page Access Protection to
Arguments Must Have Checksum.Page Items (
P15_PROPERTY_ID): Set Session State Protection toChecksum - Session Level.
Step 4: Apply Authorization Schemes to Components
Protect a Complete Page:
Open Page 15 in Page Designer.
In the Page Properties (right panel), scroll down to Security.
Set Authorization Scheme to
AUTH_CAN_EDIT_PROPERTY.
Protect a Sensitive Button:
Select your Delete Property button on Page 15.
Under the button properties, find Security.
Set Authorization Scheme to
AUTH_IS_PROP_MGR.Even if a receptionist inspects the DOM and removes
display:none, the APEX engine will refuse to process the button click.
7. Integration with Earlier Topics
In Blog 01, we introduced:
G_CURRENT_TENANT_IDas a global Application Item.The dynamic List of Values
LOV_ACTIVE_PROPERTIES.An Interactive Report link pointing to Page 15.
Notice how security directly integrates with those components:
Automatic Hydration: Our new post-authentication procedure (
app_security_pkg.post_auth_setup) automatically sets:G_CURRENT_TENANT_ID. Users no longer rely on URL parameters to establish their tenant context.Securing Links: When constructing clickable report links using
APEX_PAGE.GET_URL, APEX generates a cryptographic checksum automatically because Page 15 is protected by Session State Protection:If a user attempts to manually increment
P15_PROPERTY_IDin the browser bar, APEX detects the checksum mismatch and terminates the request.
8. Security and Performance Considerations
Authorization Caching:
Use Once per session evaluation for static entitlements (e.g., Is this user an Administrator?). This caches the Boolean result in memory, avoiding redundant SQL lookups on every component render.
Use Once per page view or Must Not Be Cached whenever the authorization scheme evaluates specific page-level inputs (such as v
alidating:P15_PROPERTY_IDagainst:G_CURRENT_TENANT_ID).
Server-Side Validation: Never rely solely on client-side authorization schemes. For critical updates, validate permissions inside the database transaction:
Cryptographic Salting: Never store unsalted hashes. Storing a unique cryptographic salt per user ensures identical passwords yield completely different hashes, defending your database against rainbow table attacks.
9. Common Errors and Troubleshooting
1. ORA-00001: Unique Constraint Violated during Authentication
Symptom: Login fails with an internal processing error.
Cause: User session tracking or audit triggers attempted to insert duplicate tracking rows without checking existing active sessions.
Fix: Verify user table constraints and review any audit triggers attached to
SEC_APP_USERS.
2. Invalid Checksum (Checksum Mismatch) on Page Load
Symptom: Users encounter a red APEX error banner stating: "Session state protection violation: This item has a checksum that is invalid."
Cause: A link was generated via manual string concatenation (e.g.,
'f?p='||:APP_ID||':15:'||:APP_SESSION||'::::P15_ID:'||p.id) rather than usingAPEX_PAGE.GET_URL. Manual URL building fails to generate the required checksum.Fix: Replace hardcoded URL strings with
APEX_PAGE.GET_URLso APEX can calculate and append the appropriate checksum hash (&cs=3...).
3. Infinite Redirect Loops on Login Screen
Symptom: Navigating to the application URL results in the browser looping indefinitely on the login page (
Page 9999).Cause: The login page itself was mistakenly assigned an Authorization Scheme that requires authentication, preventing unauthenticated visitors from accessing the login form.
Fix: Open Page Designer for your Login Page (typically Page 9999). Under Page Properties $\rightarrow$ Security, verify that Authorization Scheme is set to
Must Not Be Protectedor left blank, and set Page Access Protection toNo Protection.
10. Conclusion and Next Article Preview
By pairing Authentication Schemes, fine-grained Authorization Schemes, and Session State Protection, you have transformed your APEX application from a vulnerable database interface into a secure, enterprise-ready platform. Tenant data is isolated, unauthorized URL manipulation is blocked, and access privileges are centrally managed at the database level.
What’s Coming Next:
In Blog 03: "High-Performance Data Processing: Advanced PL/SQL, Background Automations, and Native APEX Workflows," we will scale our application logic. We will look at managing asynchronous transactions, configuring automated daily room-revenue rollups using APEX Automations, and building multi-level approval workflows for booking cancellations.
















Comments
Post a Comment