Implement row-level security with the DBMS_RLS package - PolarDB - Alibaba Cloud - PolarDB

The DBMS_RLS package lets you implement a virtual private database (VPD) on specific PolarDB database objects.

Table 1. DBMS_RLS functions and stored procedures
Function or stored procedure	Type	Return type	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 ]]]]]]]])	Stored procedure	N/A	Adds a security policy to a database object.
DROP_POLICY(object_schema, object_name, policy_name)	Stored procedure	N/A	Removes a security policy from a database object.
ENABLE_POLICY(object_schema, object_name, policy_name, enable)	Stored procedure	N/A	Enables or disables a security policy.

The DBMS_RLS package in PolarDB is a partial implementation compared to the Oracle version. PolarDB only supports the functions and stored procedures listed in the table above.

A virtual private database is a feature for enforcing fine-grained access control through security policies. Fine-grained access control in a virtual private database allows you to control data access at the individual row level.

A policy function defines the rules that encode a security policy. This is an SPL function that has specific input parameters and a return value. A security policy associates this named policy function with a specific database object, typically a table.

Note

In PolarDB, you can write a policy function in any language that PolarDB supports—such as SQL and PL/pgSQL—as well as the Oracle-compatible SPL.
Currently, PolarDB VPD supports applying security policies only to tables. Policies do not apply to views or synonyms.

A virtual private database provides the following benefits:

VPD provides fine-grained, row-level security. While the GRANT command controls access to an entire database object, VPD controls access to individual rows within that object.
You can apply different security policies based on the type of SQL statement, such as INSERT, UPDATE, DELETE, or SELECT.
Security policies are dynamic. The rules can change based on factors such as the user session that requests access to the database object.
Policies are applied transparently to all applications that access the database object. You do not need to modify your application code to enforce the policies.
Once enabled, a security policy cannot be bypassed by any application, except with the system privilege described in the note below.
Not even a superuser can bypass an enabled security policy without the specific system privilege described in the note below.

Note The only way to bypass a security policy is to grant a user the EXEMPT ACCESS POLICY system privilege. Grant this privilege with extreme caution, as it allows the user to bypass all security policies in the database.

The DBMS_RLS package provides stored procedures to create, remove, enable, and disable security policies.

To implement a virtual private database, follow these steps:

Create a policy function. The function must accept two VARCHAR2 input parameters: the schema that contains the database object and the name of the database object. The function must return a VARCHAR2 value, which is a string containing a WHERE clause predicate. This predicate is dynamically appended as an AND condition to SQL statements that affect the object. This filters any rows from the result set that do not meet the criteria.
Use the ADD_POLICY stored procedure to create a security policy that associates the policy function with a database object. You can specify the SQL statement types (INSERT, UPDATE, DELETE, or SELECT) that the policy applies to, whether to enable it on creation, and whether to check new or updated rows against the policy.
Use the ENABLE_POLICY stored procedure to enable or disable an existing security policy.
Use the DROP_POLICY stored procedure to remove a security policy. This action does not delete the underlying policy function or database object.

After you create policies, you can view them in the Oracle-compatible catalog views.

The function is often used with DBMS_RLS. The syntax is as follows:

SYS_CONTEXT(namespace, attribute)

namespaceThe parameter is a value. The only supported value is . Any other value returns .

The attribute parameter is a VARCHAR2 value. The following table lists the valid attribute values.

Attribute value	Equivalent 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 The examples for the DBMS_RLS package use a modified copy of the emp sample table provided with PolarDB. A role named salesmgr is also created and granted all privileges on this table. You can create the vpemp table (the copy of emp) and the salesmgr role by running the following SQL statements.

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;

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

The ADD_POLICY stored procedure creates a new security policy by associating a policy function with a database object.

Only a superuser can execute the ADD_POLICY stored procedure.

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

Parameter	Description
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 to which the policy applies. You can apply more than one policy to a single database object.
policy_name	The name assigned to the policy. The combination of the database object (identified by `object_schema` and `object_name`) and the policy name must be unique in the database.
function_schema	The name of the schema that contains the policy function. Note If the policy function belongs to a package, `function_schema` must be the name of the schema where the package is defined.
policy_function	The name of the SPL function that defines the policy rules. The same function can be used in multiple policies. Note If the function belongs to a package, `policy_function` must include the package name using dot notation (`package_name`.`function_name`).
statement_types	A comma-separated list of SQL statements to which the policy applies. Valid statements are `INSERT, UPDATE, DELETE, and SELECT`. The default is `INSERT,UPDATE,DELETE,SELECT`. Note PolarDB accepts `INDEX` as a statement type but ignores it. In PolarDB, policies cannot be applied to index operations.
update_check	The `update_check` parameter applies only to `INSERT` and `UPDATE` statements. If `update_check` is set to `TRUE`, the policy is applied to newly inserted rows and the post-update image of updated rows. If a new or modified row does not satisfy the policy function's predicate, the `INSERT` or `UPDATE` statement fails with an exception, and the transaction is rolled back. If `update_check` is set to `FALSE` (the default), the policy is not checked against new or updated rows. Consequently, a newly inserted row might not be visible in a subsequent query under the same policy. Similarly, a row visible before an `UPDATE` might become invisible after it.
enable	If `enable` is set to `TRUE` (the default), the policy is enabled upon creation and applied to the statements specified by the `statement_types` parameter. If `enable` is set to `FALSE`, the policy is created but disabled. You can enable it later using the ENABLE_POLICY stored procedure.
static_policy	In Oracle, if `static_policy` is `TRUE`, the policy is static. The policy function is evaluated only once for each database object. The resulting predicate string is saved in memory and reused for all subsequent invocations of the policy on that object. In Oracle, if `static_policy` is `FALSE` (the default), the policy is dynamic. The policy function is re-evaluated for every invocation. Note The `policy_type` parameter, introduced in Oracle 10g, is intended to replace the `static_policy` parameter. In Oracle, setting `policy_type` to a non-NULL value overrides the `static_policystatic_policy` value. PolarDB implements only dynamic policies and ignores the `static_policy` parameter setting.
policy_type	In Oracle, `policy_type` determines when the policy function is re-evaluated. The default is `NULL`. Note PolarDB implements only dynamic policies and ignores the `policy_type` parameter setting.
long_predicate	In Oracle, if `long_predicate` is `TRUE`, the predicate can be up to 32 KB. Otherwise, it is limited to 4,000 bytes. The default is `FALSE`. Note The `long_predicate` parameter is ignored by PolarDB. A PolarDB policy function can return a predicate of practically unlimited length.
sec_relevant_cols	`sec_relevant_cols` is a comma-separated list of columns from `object_name`. This parameter enables column-level VPD. The policy is enforced only if a SQL statement of a type listed in `statement_types` references one of these columns. If the statement does not reference any of the listed columns, the policy is not applied. The default value is `NULL`. This has the same effect as specifying all columns of the database object in `sec_relevant_cols`.
sec_relevant_cols_opt	In Oracle, setting `sec_relevant_cols_opt` to `DBMS_RLS.ALL_ROWS` (an integer constant with a value of 1) causes columns listed in `sec_relevant_cols` to return NULL for any rows where the policy predicate is false. If this parameter is not set to `DBMS_RLS.ALL_ROWS`, those rows are not returned in the result set. The default is `NULL`. Note PolarDB does not support the `DBMS_RLS.ALL_ROWS` option. If you set `sec_relevant_cols_opt` to `DBMS_RLS.ALL_ROWS` (the integer value 1), PolarDB raises an error.

Example

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;

This function generates the predicate authid = SYS_CONTEXT('USERENV', 'SESSION_USER'), which is appended to the WHERE clause of any SQL statement affected by the security policy.

This restricts the statement to rows where the authid column matches the current session user.

Note In this example, SYS_CONTEXT is used to get the current logon username. In Oracle, the SYS_CONTEXT function returns an attribute from an application context. The first parameter is the application context name, and the second is the attribute name. USERENV is a special, built-in namespace that describes the current session. PolarDB does not support custom application contexts but does support this specific use of the SYS_CONTEXT function.

The following anonymous block calls the ADD_POLICY stored procedure to create a security policy named secure_update. This policy applies the verify_session_user function to the vpemp table for all INSERT, UPDATE, and DELETE statements.

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;

After the policy is created, a user starts a session as salesmgr. The following query shows the contents of the vpemp table.

\c polardb salesmgr
Password for user salesmgr: 
You are now connected to database "polardb" as user "salesmgr".
SELECT * FROM vpemp;
 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)

The salesmgr user issues an UPDATE statement without a WHERE clause:

UPDATE vpemp SET comm = sal * .75;
UPDATE 6

Instead of updating all rows, the policy restricts the operation to rows where the authid column contains salesmgr, as specified by the policy's predicate authid = SYS_CONTEXT('USERENV', 'SESSION_USER').

A subsequent query shows that the comm column was updated only for rows where authid is salesmgr. All other rows are unchanged.

SELECT * FROM vpemp;
 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 the update_check parameter was set to TRUE, the following INSERT statement fails. The policy's predicate evaluates to FALSE for the new row because the authid value researchmgr does not match the session user salesmgr.

INSERT INTO vpemp VALUES (9001,'SMITH','ANALYST',3200.00,NULL,20, 'researchmgr');
ERROR:  policy with check option violation
DETAIL:  Policy predicate was evaluated to FALSE with the updated values

Had you set update_check to FALSE, the INSERT statement would have succeeded.

The following example demonstrates how to use the sec_relevant_cols parameter to apply a policy only when specific columns are referenced. The policy function in this example returns a predicate that selects rows where the employee's salary is less than 2,000.

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

This code creates a policy that applies only when a SELECT statement references the sal or comm column:

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;

If a query does not reference the sal or comm column, the policy is not applied, and all 14 rows from the vpemp table are returned:

SELECT empno, ename, job, deptno, authid FROM vpemp;
 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)

If a query references either the sal or comm column, the policy is applied. The result set is filtered to include only rows where sal is less than 2,000, as shown below:

SELECT empno, ename, job, sal, comm, deptno, authid FROM vpemp;
 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

The DROP_POLICY stored procedure removes an existing security policy. It does not remove the associated policy function or database object.

Note Only a superuser can execute the DROP_POLICY stored procedure.

DROP_POLICY(object_schema VARCHAR2, object_name VARCHAR2,

  policy_name VARCHAR2)

Parameters

Parameter	Description
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 to which the policy applies.
policy_name	The name of the policy to delete.

Example

The following example deletes 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

The ENABLE_POLICY stored procedure enables or disables an existing security policy on a specified database object.

Note Only a superuser can execute the ENABLE_POLICY stored procedure.

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

Parameters

Parameter	Description
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 to which the policy applies.
policy_name	The name of the policy to enable or disable.
enable	Specifies whether to enable or disable the policy. Set to `TRUE` to enable the policy. Set to `FALSE` to disable the policy.

Example

The following example disables 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;