Row-Level
Security in Oracle APEX
Introduction
In many Oracle APEX applications, controlling access to a page alone is not enough because different users may access the same page but should see only the records they are authorized to access. Row-Level Security (RLS) helps enforce this data-level restriction. For example, in a multi-tenant hotel application, users from Tenant A should see only Tenant A’s properties, while users from Tenant B should see only Tenant B’s properties. Both users can access the same report, but the data displayed is automatically restricted based on their access. RLS can work alongside APEX authorization rules to provide both feature-level and data-level security.
1. What
is Row-Level Security?
Row-Level
Security means controlling which rows a user can access, rather than
simply controlling access to an entire table or page.
Consider
a table:
Suppose
the table contains:
|
PROPERTY_ID |
TENANT_ID |
PROPERTY_NAME |
|
101 |
1 |
Hotel A |
|
102 |
1 |
Hotel B |
|
103 |
2 |
Hotel C |
|
104 |
2 |
Hotel D |
A
user from Tenant 1 should see:
Hotel
A
Hotel
B
while
a user from Tenant 2 should see:
Hotel
C
Hotel
D
The
application page can be the same, but the rows returned are different.
2. Why is RLS Important in APEX?
3.
A Simple Multi-Tenant Example
Imagine
an APEX application used by several hotel companies.
The
table contains:
4. RLS vs Authorization
Authorization and Row-Level Security (RLS) work together, but they control different levels of access. Authorization determines whether a user can access a particular page or feature—for example, an Administrator may access User Management while a Manager may not. In Oracle APEX, Authorization Schemes can be applied to pages, regions, buttons, report columns, and other components. RLS, on the other hand, determines which specific records a user can access. For example, Tenant 100 may access Records 1 and 2, while Tenant 200 can access Records 3 and 4. In simple terms, Authorization controls feature access, while RLS controls data access.
5. Basic APEX Filtering Approach
For many Oracle APEX applications, row-level access can be implemented directly in the SQL query by filtering records based on the current user's tenant or access context. For example, the query below returns only properties belonging to the current tenant:
Here, :APP_TENANT_ID represents the tenant associated with the current application session. This approach is simple and works well for smaller applications, but the filter must be consistently applied to every report, LOV, process, and other data-access query to prevent unauthorized records from being exposed.
6. The Problem with Repeating Security Filters
In a larger APEX application, the same tenant filter may need to be added to many reports, LOVs, processes, and REST APIs. For example, each query may require WHERE tenant_id = :APP_TENANT_ID. While this approach can work, it depends on developers remembering to apply the filter everywhere. If the condition is accidentally missed in even one place, users could potentially access data belonging to another tenant. Database-level Row-Level Security (RLS) provides an additional layer of protection by enforcing data-access rules centrally, reducing the risk of relying entirely on individual application queries.
7. Oracle VPD and DBMS_RLS
Oracle Database provides Virtual Private Database (VPD) for implementing fine-grained access control. The DBMS_RLS package is used to create and manage security policies that can apply a dynamic security predicate to protected tables or views. This allows the database to automatically restrict which rows a user can access based on the defined security rules. The basic flow is:
User → SQL Query → RLS Policy → Security Predicate → Authorized Rows
Instead of adding the same security condition to every application query, the database policy can enforce the restriction centrally, providing an additional layer of data protection.
8.
Create a Security Function
A
simplified example could look like this:
The
function returns a predicate such as:
tenant_id
= 100
The
exact implementation of the application context and session setup depends on
your security architecture.
9.
Create the RLS Policy
The
security function can then be associated with a table or view using DBMS_RLS.ADD_POLICY.
For
example:
Oracle
documents DBMS_RLS.ADD_POLICY as the mechanism for attaching a VPD policy to a
table, view, or synonym and specifying which statement types the policy
affects.)
10.
What Happens During a Query?
Suppose
the application executes:
But
the RLS policy can apply the security predicate automatically.
Conceptually,
Oracle evaluates the query as though it contained something similar to:
Oracle
describes VPD as adding a dynamic WHERE predicate to statements against the
protected object.
This
is one of the biggest advantages of database-enforced row-level security.
11. RLS with APEX
In an Oracle APEX application, Row-Level Security can work together with authentication and authorization to control data access. The typical flow starts with the user logging in and APEX creating a session. The application then identifies the user or tenant and establishes the appropriate security context. When an APEX report executes its SQL query, the RLS policy applies the required security rules and returns only the rows the user is authorized to access.
User Login → Authentication → APEX Session → Identify User/Tenant → Set Security Context → APEX Report → SQL Query → RLS Policy → Authorized Rows
This approach allows APEX to manage the application experience and user access, while the database enforces data-level security. Oracle APEX also supports row-level data security policies for controlling which data individual users can access.
12. RLS for SELECT, INSERT, UPDATE, and DELETE
Row-Level Security is not limited to controlling which records users can view. An RLS policy can also be applied to different SQL operations, such as SELECT, INSERT, UPDATE, and DELETE. For example, statement_types => 'SELECT,INSERT,UPDATE,DELETE' allows the security policy to be considered when users view, insert, update, or delete records. This helps ensure that the same row-level access rules are consistently applied across different types of database operations.
13. RLS and APEX Authorization Work Together
RLS does not replace APEX Authorization because they control different aspects of security. Authorization determines whether a user can access a particular page or feature, while RLS determines which records the user can access within that feature. For example, a Manager may be authorized to access the Booking Management page, while RLS ensures that the Manager can see only bookings belonging to their assigned property. Together, they provide both feature-level security and data-level security.
Authentication → Authorization → Feature Access → RLS → Authorized Records
14.
Example: Property-Based Security
Suppose
a manager is responsible for one property.
The
booking table contains:
The manager should only see bookings for their property.
The same report can then be used by managers for different properties without creating separate reports for each property.
15. Don't Rely Only on Page Items
A common approach in Oracle APEX is to use page items such as P10_TENANT_ID, P10_PROPERTY_ID, or P10_USER_ID to control data access, for example, WHERE tenant_id = :P10_TENANT_ID. While this can be useful for application logic, sensitive security decisions should not rely only on values that can be influenced by the client. For stronger security, establish the trusted user or tenant context on the server and enforce data-access rules at the database level. This is especially important in multi-tenant applications, where one incorrect value could potentially expose another tenant's data.
16. Security Testing
After implementing RLS, test more than just the normal user flow. Verify the security rules with different users, roles, and tenants to ensure that each user can access only the records they are authorized to see. For example, a Tenant A user should be able to view Tenant A records but not Tenant B records, while a Tenant B user should have the opposite access. Also test different access points such as direct URLs, reports, LOVs, Interactive Grids, REST APIs, downloads, and INSERT, UPDATE, and DELETE operations. The goal is to ensure that row-level security remains effective regardless of how the data is accessed.
Tenant A → Tenant A Records ✓ | Tenant B Records ✗
Tenant B → Tenant B Records ✓ | Tenant A Records ✗
17. Common Mistakes
Common RLS mistakes include relying only on page-item values, forgetting tenant filters in some queries, protecting reports but not APIs or LOVs, and testing only with a single user. These gaps can lead to unauthorized data access. Always apply security rules consistently and test different roles, tenants, and access paths before deploying the application.
19. Best Practices
When implementing Row-Level Security in Oracle APEX, start by clearly defining which users can access which records. Keep authentication, authorization, and data security as separate layers, using APEX Authorization Schemes for feature-level access and database-enforced policies when centralized row-level protection is required. Apply the same security rules consistently across reports, LOVs, APIs, downloads, and database operations, and avoid trusting IDs controlled by the client. Test the application with multiple users and tenants, monitor performance after introducing security policies, keep security rules centralized where practical, and document the overall data-access model for easier maintenance.
20.
Simple Security Architecture
Final Takeaway
Row-Level Security (RLS) protects the data itself. In Oracle APEX, authentication determines who the user is, authorization controls what features they can access, and RLS determines which records they can see or modify.
For a multi-tenant application, the security flow can be summarized as:
Authenticate → Authorize → Identify Tenant → Apply RLS → Access Data

Comments
Post a Comment