A Practical Guide to ​Row Level Security for a ​SaaS

In the world of multi-tenant SaaS applications, data segregation is not just a feature—it's a foundational requirement. The cardinal rule is simple and absolute: one customer must never, under any circumstances, see another customer's data. While this sounds straightforward, implementing it robustly can be complex. Traditional methods like creating separate views for each client or embedding complex `WHERE` clauses throughout your application code can quickly become a maintenance nightmare. A single oversight in a query can lead to a catastrophic data leak, breaking customer trust and potentially violating data privacy regulations.

This is where Oracle's Row Level Security (RLS) comes in. Also known as the Virtual Private Database (VPD), RLS is a powerful security feature that allows you to create policies that dynamically append a `WHERE` clause to SQL statements at the database level. This approach moves the security logic from the application to the data itself, creating an inescapable, transparent, and highly scalable security model. This article provides a practical, step-by-step tutorial on implementing Row Level Security from scratch, demonstrating how to build a secure data environment for a typical multi-tenant application.

Understanding Oracle Row Level Security (RLS)

At its core, Oracle Row Level Security is a mechanism that attaches a security policy directly to a table or view. When a user runs a query (SELECT, INSERT, UPDATE, or DELETE) against that table, Oracle automatically and transparently appends a predicate—a dynamic `WHERE` clause—to the statement before it executes. This process is invisible to the end-user and the application. The application can issue a simple query like SELECT * FROM invoices;, but the database might actually execute SELECT * FROM invoices WHERE client_id = 101; based on the policy.

This powerful feature is built on three essential components that work together:

1. Application Context: A secure, session-specific namespace that stores key-value pairs. This is how we tell the database "who" the current user is—for instance, which `client_id` is active for this session.
2. Policy Function: A PL/SQL function that contains the security logic. This function reads the information from the Application Context and returns a string containing the predicate (e.g., `'client_id = 101'`).
3. Policy: The object that links the policy function to a specific table. It's created using the DBMS_RLS package and tells Oracle to apply the function's logic every time the table is accessed.

By building these three components, we can enforce fine-grained access control directly within the database, ensuring our security rules are consistently applied no matter how the data is accessed.

The Scenario: Securing Our Invoicing Application

For this tutorial, our goal is to secure a central invoices table in a multi-tenant application. This single table contains invoice data for all clients, and a client_id column distinguishes which records belong to which client. We need to implement a policy that ensures users logged in under a specific client context can only see and manipulate invoices belonging to their own client_id.

First, let's create our table and populate it with some sample data representing two different clients (client_id 101 and 102).

-- The central table for all client invoices
CREATE TABLE invoices (
  invoice_id   NUMBER PRIMARY KEY,
  client_id    NUMBER NOT NULL,
  invoice_date DATE,
  amount       NUMBER(10, 2),
  description  VARCHAR2(200)
);

-- Sample data for two different clients
INSERT INTO invoices VALUES (1, 101, SYSDATE - 10, 1500.00, 'Client 101 - Web Hosting');
INSERT INTO invoices VALUES (2, 101, SYSDATE - 5, 2500.50, 'Client 101 - Database Consulting');
INSERT INTO invoices VALUES (3, 102, SYSDATE - 8, 950.75, 'Client 102 - App Development');
INSERT INTO invoices VALUES (4, 102, SYSDATE - 2, 3200.00, 'Client 102 - Cloud Migration');
COMMIT;

Step 1: Creating the "Who Are You?" Security Context

Before we can apply a security rule, the database needs a way to know which client is currently active in a given session. An Application Context is the perfect tool for this. It’s a secure, session-level memory area that can only be populated through a trusted procedure. First, we create the context namespace itself. Then, we create a package that will be responsible for setting the `client_id` value within that context.

-- Create a context to store the client ID for the session
CREATE OR REPLACE CONTEXT client_context_ctx USING client_security_pkg;

-- Create a package to manage setting the context
CREATE OR REPLACE PACKAGE client_security_pkg AS
  PROCEDURE set_client_id (p_client_id IN NUMBER);
END client_security_pkg;
/

CREATE OR REPLACE PACKAGE BODY client_security_pkg AS
  PROCEDURE set_client_id (p_client_id IN NUMBER) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('client_context_ctx', 'client_id_attr', p_client_id);
  END set_client_id;
END client_security_pkg;
/

Now, our application can call client_security_pkg.set_client_id(101) after a user logs in, securely telling the database the identity for that specific session.

Step 2: Writing the Dynamic Security Rule (Policy Function)

This is the heart of our RLS implementation. We need to create a PL/SQL function that Oracle will call every time a query is run against the invoices table. This function's only job is to read the `client_id` from our application context and return a string formatted as a `WHERE` clause predicate. Oracle will then append this string to the user's query. If the context is not set, the function returns a predicate that ensures no rows are returned, failing securely.

-- This function generates the security predicate
CREATE OR REPLACE FUNCTION secure_invoices_policy (
  p_schema   IN VARCHAR2,
  p_object   IN VARCHAR2
) RETURN VARCHAR2
IS
  l_client_id NUMBER;
  l_predicate VARCHAR2(1000);
BEGIN
  -- Get the client ID from the application context
  l_client_id := SYS_CONTEXT('client_context_ctx', 'client_id_attr');
  
  -- If the client ID exists, create a predicate to filter by it
  IF l_client_id IS NOT NULL THEN
    l_predicate := 'client_id = ' || l_client_id;
  ELSE
    -- If no client ID is set, return a predicate that returns no rows
    l_predicate := '1 = 2';
  END IF;
  
  RETURN l_predicate;
END secure_invoices_policy;
/

Step 3: Attaching the Policy to the Table

With our context and policy function in place, the final step is to activate the policy by linking it to the invoices table. We do this using the DBMS_RLS.ADD_POLICY procedure. This procedure tells Oracle to execute our secure_invoices_policy function whenever a SQL statement (SELECT, INSERT, UPDATE, DELETE) is issued against the invoices table. We also set update_check => TRUE to ensure that users cannot insert or update rows to make them belong to another client.

-- Add the policy to the invoices table
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema    => USER, -- The schema owning the table
    object_name      => 'invoices', -- The table to protect
    policy_name      => 'invoices_client_policy', -- A name for the policy
    function_schema  => USER, -- The schema owning the function
    policy_function  => 'secure_invoices_policy', -- The policy function
    statement_types  => 'SELECT, INSERT, UPDATE, DELETE', -- Apply to these operations
    update_check     => TRUE -- Prevent inserting/updating to another client's ID
  );
END;
/

Step 4: Putting It All to the Test

The policy is now active and enforcing our security rule. Let's test it by simulating two different client sessions. Notice how we run the *exact same* query (SELECT * FROM invoices;) in both cases, but the database returns a different, filtered result set each time based on the context we set.

-- Test Case 1: Simulate a login for Client 101
BEGIN
  client_security_pkg.set_client_id(101);
END;
/

-- Now, query the table. Only invoices for client_id = 101 are returned.
SELECT * FROM invoices;

-- INVOICE_ID | CLIENT_ID | INVOICE_DATE | AMOUNT   | DESCRIPTION
-- -----------|-----------|--------------|----------|---------------------------------
-- 1          | 101       | ...          | 1500.00  | Client 101 - Web Hosting
-- 2          | 101       | ...          | 2500.50  | Client 101 - Database Consulting


-- Test Case 2: Simulate a login for Client 102 in the same session
BEGIN
  client_security_pkg.set_client_id(102);
END;
/

-- Run the exact same query again. Now only Client 102's data is visible.
SELECT * FROM invoices;

-- INVOICE_ID | CLIENT_ID | INVOICE_DATE | AMOUNT   | DESCRIPTION
-- -----------|-----------|--------------|----------|---------------------------------
-- 3          | 102       | ...          | 950.75   | Client 102 - App Development
-- 4          | 102       | ...          | 3200.00  | Client 102 - Cloud Migration

Conclusion: Transparent and Scalable Security

As we've demonstrated, implementing Oracle's Row Level Security is a straightforward process involving three key steps: creating a context, writing a policy function, and attaching the policy. The result is a powerful and elegant security solution. The most significant benefit of RLS is its transparency. Application developers don't need to worry about adding security predicates to their queries. They can simply interact with the tables, confident that the database is enforcing the correct data visibility rules automatically and securely in the background.

This centralization of security logic not only reduces the risk of programming errors leading to data leaks but also simplifies application development and maintenance. For multi-tenant SaaS applications, where data isolation is paramount, RLS provides a robust, scalable, and auditable framework that ensures your customers' data remains private and protected, building a foundation of trust for your platform.