Implement row-level security with the DBMS_RLS package - PolarDB

The DBMS_RLS package enables Virtual Private Database (VPD) on PolarDB database objects. VPD provides fine-grained access control at the row level, enforced transparently across all applications without requiring code changes.

PolarDB implements a subset of the Oracle DBMS_RLS package. The following stored procedures are supported:

Stored procedure	Description
`ADD_POLICY(object_schema, object_name, policy_name, function_schema, policy_function [, statement_types [, update_check [, enable [, static_policy [, policy_type [, long_predicate [, sec_relevant_cols [, sec_relevant_cols_opt ]]]]]]]]]])`	Adds a security policy to a database object.
`DROP_POLICY(object_schema, object_name, policy_name)`	Removes a security policy from a database object.
`ENABLE_POLICY(object_schema, object_name, policy_name, enable)`	Enables or disables a security policy.

How VPD works

A security policy is the association between a policy function and a database object (typically a table). When a SQL statement accesses that object, the database engine calls the policy function, retrieves the WHERE clause predicate it returns, and appends it as an AND condition to the statement. Rows that do not satisfy the predicate are filtered out of the result set.

VPD offers several advantages over standard object-level privileges:

Row-level control: GRANT-based privileges control access to an entire table. VPD restricts access down to individual rows.
Statement-specific policies: Apply different policies for INSERT, UPDATE, DELETE, and SELECT operations.
Dynamic enforcement: Policies are evaluated per statement and can vary based on factors such as the session user.
Transparent enforcement: All applications—including new ones—are subject to the policy without any code changes.
No bypass path: Even superusers cannot circumvent an active policy unless explicitly granted the EXEMPT ACCESS POLICY system privilege.

Note The only way to bypass a security policy is to hold the EXEMPT ACCESS POLICY system privilege. A user with this privilege is exempted from all policies in the database. Grant this privilege with caution.

Policy functions

A policy function defines the rules of the security policy. It must meet these requirements:

Two input parameters of type VARCHAR2: the first for the schema name, the second for the database object name.
A VARCHAR2 return type.
Returns a string in the form of a valid WHERE clause predicate.

In PolarDB, policy functions can be written in any language the database supports: Oracle-compatible SPL, SQL, or PL/pgSQL.

Note VPD supports tables only. Policies cannot be applied to views or synonyms.

Implement VPD

To implement VPD on a table:

Create a policy function that returns a WHERE clause predicate based on the session context or other conditions.
Use ADD_POLICY to associate the function with a table and specify which SQL statement types it applies to.
Use ENABLE_POLICY to enable or disable the policy at any time after creation.
Use DROP_POLICY to remove a policy when it is no longer needed. Dropping a policy does not delete the policy function or the database object.

After creating policies, query the catalog views compatible with Oracle databases to see which policies are active.

SYS_CONTEXT

The SYS_CONTEXT function is commonly used inside policy functions to return information about the current session. Syntax:

SYS_CONTEXT(namespace, attribute)

Parameters:

`namespace` — VARCHAR2. The only valid value is USERENV. Any other value returns NULL.

`attribute` — VARCHAR2. Supported values:

Attribute value	Equivalent PostgreSQL value
`SESSION_USER`	`pg_catalog.session_user`
`CURRENT_USER`	`pg_catalog.current_user`
`CURRENT_SCHEMA`	`pg_catalog.current_schema`
`HOST`	`pg_catalog.inet_host`
`IP_ADDRESS`	`pg_catalog.inet_client_addr`
`SERVER_HOST`	`pg_catalog.inet_server_addr`

Note PolarDB supports the special use of SYS_CONTEXT with the USERENV namespace, but does not support application contexts.

Set up the example table

The examples in this topic use a modified copy of the emp table named vpemp. Run the following to create the table and a salesmgr role:

CREATE TABLE public.vpemp AS SELECT empno, ename, job, sal, comm, deptno FROM emp;
ALTER TABLE vpemp ADD authid VARCHAR2(12);
UPDATE vpemp SET authid = 'researchmgr' WHERE deptno = 20;
UPDATE vpemp SET authid = 'salesmgr' WHERE deptno = 30;
SELECT * FROM vpemp;

Output:

 empno | ename  |    job    |   sal   |  comm   | deptno |   authid
-------+--------+-----------+---------+---------+--------+-------------
  7782 | CLARK  | MANAGER   | 2450.00 |         |     10 |
  7839 | KING   | PRESIDENT | 5000.00 |         |     10 |
  7934 | MILLER | CLERK     | 1300.00 |         |     10 |
  7369 | SMITH  | CLERK     |  800.00 |         |     20 | researchmgr
  7566 | JONES  | MANAGER   | 2975.00 |         |     20 | researchmgr
  7788 | SCOTT  | ANALYST   | 3000.00 |         |     20 | researchmgr
  7876 | ADAMS  | CLERK     | 1100.00 |         |     20 | researchmgr
  7902 | FORD   | ANALYST   | 3000.00 |         |     20 | researchmgr
  7499 | ALLEN  | SALESMAN  | 1600.00 |  300.00 |     30 | salesmgr
  7521 | WARD   | SALESMAN  | 1250.00 |  500.00 |     30 | salesmgr
  7654 | MARTIN | SALESMAN  | 1250.00 | 1400.00 |     30 | salesmgr
  7698 | BLAKE  | MANAGER   | 2850.00 |         |     30 | salesmgr
  7844 | TURNER | SALESMAN  | 1500.00 |    0.00 |     30 | salesmgr
  7900 | JAMES  | CLERK     |  950.00 |         |     30 | salesmgr
(14 rows)

CREATE ROLE salesmgr WITH LOGIN PASSWORD 'password';
GRANT ALL ON vpemp TO salesmgr;

ADD_POLICY

Associates a policy function with a database object to create a security policy.

Requires superuser privileges.

ADD_POLICY(
  object_schema    VARCHAR2,
  object_name      VARCHAR2,
  policy_name      VARCHAR2,
  function_schema  VARCHAR2,
  policy_function  VARCHAR2
  [, statement_types      VARCHAR2
  [, update_check         BOOLEAN
  [, enable               BOOLEAN
  [, static_policy        BOOLEAN
  [, policy_type          INTEGER
  [, long_predicate       BOOLEAN
  [, sec_relevant_cols    VARCHAR2
  [, sec_relevant_cols_opt INTEGER ]]]]]]]])

Parameters

`object_schema`

The name of the schema that contains the database object to which the policy applies.

`object_name`

The name of the database object. A given database object can have more than one policy applied to it.

`policy_name`

The name of the policy. The combination of database object (identified by object_schema and object_name) and policy name must be unique within the database.

`function_schema`

The schema that contains the policy function.

Note If the policy function belongs to a package, function_schema must be the schema in which the package is defined.

`policy_function`

The name of the policy function. The same function can be referenced by more than one policy.

Note If the policy function belongs to a package, use dot notation: package_name.function_name.

`statement_types`

A comma-separated list of SQL statement types to which the policy applies. Valid values: INSERT, UPDATE, DELETE, SELECT. Default: INSERT,UPDATE,DELETE,SELECT.

Note PolarDB accepts but ignores the INDEX statement type. Policies cannot be applied to index operations.

`update_check`

Applies to INSERT and UPDATE statements only.

When set to TRUE, the policy is also applied to newly inserted rows and to the modified image of updated rows. If a new or modified row does not satisfy the policy predicate, the INSERT or UPDATE statement throws an exception and no rows are inserted or modified.
When set to FALSE, the policy is not applied to newly inserted rows or the modified image of updated rows. A newly inserted row may therefore not appear in the result set of a subsequent statement that invokes the same policy.

Default: FALSE.

`enable`

When set to TRUE, the policy is enabled immediately and applied to the statement types specified in statement_types.
When set to FALSE, the policy is created but disabled. Use ENABLE_POLICY to enable it later.

Default: TRUE.

`static_policy`

In Oracle, when set to TRUE, the policy function is evaluated once per database object on its first invocation, and the resulting predicate is cached for reuse. When set to FALSE, the policy function is re-evaluated for every invocation.

Note PolarDB always uses dynamic policy evaluation. The static_policy parameter is accepted but ignored.

Default: FALSE.

`policy_type`

In Oracle, determines when the policy function is re-evaluated. Overrides static_policy when set to a non-NULL value.

Note PolarDB always uses dynamic policy evaluation. The policy_type parameter is accepted but ignored.

Default: NULL.

`long_predicate`

In Oracle, when set to TRUE, predicates can be up to 32 KB. Otherwise, predicates are limited to 4 KB.

Note PolarDB ignores this parameter. Policy functions can return predicates of unlimited length.

Default: FALSE.

`sec_relevant_cols`

A comma-separated list of columns in object_name. When specified, the policy is enforced only if a SQL statement references at least one of the listed columns. If no listed column is referenced, the policy is not applied.

Default: NULL (equivalent to listing all columns).

`sec_relevant_cols_opt`

In Oracle, when set to DBMS_RLS.ALL_ROWS (INTEGER value 1), all rows are returned but the sec_relevant_cols columns are set to NULL for rows where the policy predicate evaluates to false.

Note PolarDB does not support DBMS_RLS.ALL_ROWS. Setting sec_relevant_cols_opt to 1 throws an error.

Default: NULL.

Examples

Row-level filtering by session user

This policy function restricts access to rows where the authid column matches the current session user:

CREATE OR REPLACE FUNCTION verify_session_user (
    p_schema  VARCHAR2,
    p_object  VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
    RETURN 'authid = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
END;

The function returns the predicate authid = SYS_CONTEXT('USERENV', 'SESSION_USER'), which is dynamically appended as an AND condition to each qualifying SQL statement.

Note SYS_CONTEXT returns attributes of the current session. USERENV is a built-in namespace representing the current session context. PolarDB supports SYS_CONTEXT with USERENV but does not support custom application contexts.

The following anonymous block creates a policy named secure_update on the vpemp table. The policy applies to INSERT, UPDATE, and DELETE statements, and update_check is enabled so that newly inserted or modified rows must also satisfy the predicate:

DECLARE
    v_object_schema    VARCHAR2(30) := 'public';
    v_object_name      VARCHAR2(30) := 'vpemp';
    v_policy_name      VARCHAR2(30) := 'secure_update';
    v_function_schema  VARCHAR2(30) := 'polardb';
    v_policy_function  VARCHAR2(30) := 'verify_session_user';
    v_statement_types  VARCHAR2(30) := 'INSERT,UPDATE,DELETE';
    v_update_check     BOOLEAN      := TRUE;
    v_enable           BOOLEAN      := TRUE;
BEGIN
    DBMS_RLS.ADD_POLICY(
        v_object_schema,
        v_object_name,
        v_policy_name,
        v_function_schema,
        v_policy_function,
        v_statement_types,
        v_update_check,
        v_enable
    );
END;

Connect as the salesmgr user and run a SELECT to see the current table:

\c polardb salesmgr
Password for user salesmgr:
You are now connected to database "polardb" as user "salesmgr".
SELECT * FROM vpemp;

Output:

 empno | ename  |    job    |   sal   |  comm   | deptno |   authid
-------+--------+-----------+---------+---------+--------+-------------
  7782 | CLARK  | MANAGER   | 2450.00 |         |     10 |
  7839 | KING   | PRESIDENT | 5000.00 |         |     10 |
  7934 | MILLER | CLERK     | 1300.00 |         |     10 |
  7369 | SMITH  | CLERK     |  800.00 |         |     20 | researchmgr
  7566 | JONES  | MANAGER   | 2975.00 |         |     20 | researchmgr
  7788 | SCOTT  | ANALYST   | 3000.00 |         |     20 | researchmgr
  7876 | ADAMS  | CLERK     | 1100.00 |         |     20 | researchmgr
  7902 | FORD   | ANALYST   | 3000.00 |         |     20 | researchmgr
  7499 | ALLEN  | SALESMAN  | 1600.00 |  300.00 |     30 | salesmgr
  7521 | WARD   | SALESMAN  | 1250.00 |  500.00 |     30 | salesmgr
  7654 | MARTIN | SALESMAN  | 1250.00 | 1400.00 |     30 | salesmgr
  7698 | BLAKE  | MANAGER   | 2850.00 |         |     30 | salesmgr
  7844 | TURNER | SALESMAN  | 1500.00 |    0.00 |     30 | salesmgr
  7900 | JAMES  | CLERK     |  950.00 |         |     30 | salesmgr
(14 rows)

Issue an unqualified UPDATE as salesmgr:

UPDATE vpemp SET comm = sal * .75;

Output:

UPDATE 6

The policy restricts the UPDATE to rows where authid = 'salesmgr', matching the session user. Only those six rows are modified.

Verify the result:

SELECT * FROM vpemp;

Output:

 empno | ename  |    job    |   sal   |  comm   | deptno |   authid
-------+--------+-----------+---------+---------+--------+-------------
  7782 | CLARK  | MANAGER   | 2450.00 |         |     10 |
  7839 | KING   | PRESIDENT | 5000.00 |         |     10 |
  7934 | MILLER | CLERK     | 1300.00 |         |     10 |
  7369 | SMITH  | CLERK     |  800.00 |         |     20 | researchmgr
  7566 | JONES  | MANAGER   | 2975.00 |         |     20 | researchmgr
  7788 | SCOTT  | ANALYST   | 3000.00 |         |     20 | researchmgr
  7876 | ADAMS  | CLERK     | 1100.00 |         |     20 | researchmgr
  7902 | FORD   | ANALYST   | 3000.00 |         |     20 | researchmgr
  7499 | ALLEN  | SALESMAN  | 1600.00 | 1200.00 |     30 | salesmgr
  7521 | WARD   | SALESMAN  | 1250.00 |  937.50 |     30 | salesmgr
  7654 | MARTIN | SALESMAN  | 1250.00 |  937.50 |     30 | salesmgr
  7698 | BLAKE  | MANAGER   | 2850.00 | 2137.50 |     30 | salesmgr
  7844 | TURNER | SALESMAN  | 1500.00 | 1125.00 |     30 | salesmgr
  7900 | JAMES  | CLERK     |  950.00 |  712.50 |     30 | salesmgr
(14 rows)

Because update_check is TRUE, inserting a row with authid = 'researchmgr' fails—the new row does not satisfy the policy predicate for the salesmgr session user:

INSERT INTO vpemp VALUES (9001,'SMITH','ANALYST',3200.00,NULL,20, 'researchmgr');

Output:

ERROR:  policy with check option violation
DETAIL:  Policy predicate was evaluated to FALSE with the updated values

If update_check is set to FALSE, the same INSERT succeeds.

Column-level VPD

Use sec_relevant_cols to enforce a policy only when specific columns appear in the SQL statement. This example restricts rows returned when sal or comm is selected, keeping only employees with a salary below 2000:

CREATE OR REPLACE FUNCTION sal_lt_2000 (
    p_schema  VARCHAR2,
    p_object  VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
    RETURN 'sal < 2000';
END;

Create the policy with sec_relevant_cols set to 'sal,comm':

DECLARE
    v_object_schema     VARCHAR2(30) := 'public';
    v_object_name       VARCHAR2(30) := 'vpemp';
    v_policy_name       VARCHAR2(30) := 'secure_salary';
    v_function_schema   VARCHAR2(30) := 'polardb';
    v_policy_function   VARCHAR2(30) := 'sal_lt_2000';
    v_statement_types   VARCHAR2(30) := 'SELECT';
    v_sec_relevant_cols VARCHAR2(30) := 'sal,comm';
BEGIN
    DBMS_RLS.ADD_POLICY(
        v_object_schema,
        v_object_name,
        v_policy_name,
        v_function_schema,
        v_policy_function,
        v_statement_types,
        sec_relevant_cols => v_sec_relevant_cols
    );
END;

A query that does not reference sal or comm returns all 14 rows—the policy is not triggered:

SELECT empno, ename, job, deptno, authid FROM vpemp;

Output:

 empno | ename  |    job    | deptno |   authid
-------+--------+-----------+--------+-------------
  7782 | CLARK  | MANAGER   |     10 |
  7839 | KING   | PRESIDENT |     10 |
  7934 | MILLER | CLERK     |     10 |
  7369 | SMITH  | CLERK     |     20 | researchmgr
  7566 | JONES  | MANAGER   |     20 | researchmgr
  7788 | SCOTT  | ANALYST   |     20 | researchmgr
  7876 | ADAMS  | CLERK     |     20 | researchmgr
  7902 | FORD   | ANALYST   |     20 | researchmgr
  7499 | ALLEN  | SALESMAN  |     30 | salesmgr
  7521 | WARD   | SALESMAN  |     30 | salesmgr
  7654 | MARTIN | SALESMAN  |     30 | salesmgr
  7698 | BLAKE  | MANAGER   |     30 | salesmgr
  7844 | TURNER | SALESMAN  |     30 | salesmgr
  7900 | JAMES  | CLERK     |     30 | salesmgr
(14 rows)

A query that includes sal or comm triggers the policy and filters out rows where sal >= 2000:

SELECT empno, ename, job, sal, comm, deptno, authid FROM vpemp;

Output:

 empno | ename  |   job    |   sal   |  comm   | deptno |   authid
-------+--------+----------+---------+---------+--------+-------------
  7934 | MILLER | CLERK    | 1300.00 |         |     10 |
  7369 | SMITH  | CLERK    |  800.00 |         |     20 | researchmgr
  7876 | ADAMS  | CLERK    | 1100.00 |         |     20 | researchmgr
  7499 | ALLEN  | SALESMAN | 1600.00 | 1200.00 |     30 | salesmgr
  7521 | WARD   | SALESMAN | 1250.00 |  937.50 |     30 | salesmgr
  7654 | MARTIN | SALESMAN | 1250.00 |  937.50 |     30 | salesmgr
  7844 | TURNER | SALESMAN | 1500.00 | 1125.00 |     30 | salesmgr
  7900 | JAMES  | CLERK    |  950.00 |  712.50 |     30 | salesmgr
(8 rows)

DROP_POLICY

Removes a security policy from a database object. Dropping a policy does not delete the policy function or the database object itself.

Requires superuser privileges.

DROP_POLICY(
  object_schema  VARCHAR2,
  object_name    VARCHAR2,
  policy_name    VARCHAR2
)

Parameters

Parameter	Description
`object_schema`	The schema of the database object to which the policy applies.
`object_name`	The name of the database object.
`policy_name`	The name of the policy to remove.

Example

Remove the secure_update policy from the public.vpemp table:

DECLARE
    v_object_schema  VARCHAR2(30) := 'public';
    v_object_name    VARCHAR2(30) := 'vpemp';
    v_policy_name    VARCHAR2(30) := 'secure_update';
BEGIN
    DBMS_RLS.DROP_POLICY(
        v_object_schema,
        v_object_name,
        v_policy_name
    );
END;

ENABLE_POLICY

Enables or disables an existing security policy on a database object.

Requires superuser privileges.

ENABLE_POLICY(
  object_schema  VARCHAR2,
  object_name    VARCHAR2,
  policy_name    VARCHAR2,
  enable         BOOLEAN
)

Parameters

Parameter	Description
`object_schema`	The schema of the database object to which the policy applies.
`object_name`	The name of the database object.
`policy_name`	The name of the policy to enable or disable.
`enable`	Set to `TRUE` to enable the policy, or `FALSE` to disable it.

Example

Disable the secure_update policy on the public.vpemp table:

DECLARE
    v_object_schema  VARCHAR2(30) := 'public';
    v_object_name    VARCHAR2(30) := 'vpemp';
    v_policy_name    VARCHAR2(30) := 'secure_update';
    v_enable         BOOLEAN      := FALSE;
BEGIN
    DBMS_RLS.ENABLE_POLICY(
        v_object_schema,
        v_object_name,
        v_policy_name,
        v_enable
    );
END;