DBMS_RLS - PolarDB - Alibaba Cloud Documentation Center

The DBMS_RLS package enables Virtual Private Database (VPD) to run on specific PolarDB database objects.

Table 1. DBMS RLS function and stored procedures
Function or stored procedure	Type	Return value 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 partially implemented when compared with the package provided by Oracle. PolarDB only supports the functions and stored procedures that are listed in the preceding table.

Virtual Private Database adopts fine-grained access control that uses security policies. Fine-grained access control in Virtual Private Database means that access to data can be controlled down to specific rows.

The rules that encode a security policy are defined in a policy function. This policy function is an SPL function with specific input parameters and return values. The security policy is the association of the policy function to a particular database object, typically a table.

Note

In PolarDB, you can write policy functions in all languages supported by PolarDB. For example, apart from Oracle-compatible SPL languages, you can also use SQL and PL/pgSQL languages.
The database objects supported by PolarDB Virtual Private Database are tables. Policies cannot be applied to views or synonyms.

The following list describes the benefits of using Virtual Private Database.

Virtual Private Database provides a fine-grained level of security. Database object level privileges given by the GRANT statement determine access privileges to the entire instance of a database object. In contrast, Virtual Private Database provides access control for the individual rows of a database object instance.
A different security policy can be applied based on the type of SQL statement, such as INSERT, UPDATE, DELETE, or SELECT.
The security policy is dynamic and can vary for each applicable SQL statement affecting the database object. The security policy is determined by multiples factors, such as the session user of the application accessing the database object.
Invocation of the security policy is transparent to all applications that access the database object. Therefore, you do not need to modify individual applications to apply the security policy.
After a security policy is enabled, no applications (including new applications) can circumvent the security policy except by the system privilege that is mentioned in the following note.
Even superusers cannot circumvent the security policy except by the system privilege that is mentioned in the note.

Note The only way security policies can be circumvented is that the user has the EXEMPT ACCESS POLICY system privilege. Exercise caution when you grant the EXEMPT ACCESS POLICY permission. A user who has this permission is exempted from all policies in the database.

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

Implement Virtual Private Database based on the following process:

Create a policy function. The function must have two input parameters of type VARCHAR2. The first input parameter is used for the schema that contains the database object to which the policy is to be applied. The second input parameter is used for the name of the database object. The function must have a VARCHAR2 return type. The function must return a string in the form of a WHERE clause predicate. This predicate is dynamically appended as an AND condition to the SQL statement that acts upon the database object. The rows that do not satisfy the policy function predicate are filtered out from the SQL statement result set.
Use the ADD_POLICY stored procedure to define a new policy, which associates a policy function with a database object. You can use the ADD_POLICY stored procedure to specify the types of SQL statements (INSERT, UPDATE, DELETE, or SELECT) to which the policy is to apply. You can specify whether to enable the policy at the time of its creation. You can also specify whether the policy can apply to newly inserted rows and the modified image of updated rows.
Use the ENABLE_POLICY stored procedure to disable or enable an existing policy.
Use the DROP_POLICY stored procedure to delete an existing policy. The DROP_POLICY stored procedure does not delete the policy function or the associated database object.

After policies are created, they can be viewed in the catalog views that are compatible with Oracle databases.

The SYS_CONTEXT function is often used with the DBMS_RLS package. Syntax:

SYS_CONTEXT(namespace, attribute)

namespace is of the VARCHAR2 data type. The only valid value is USERENV. If another value is specified for this parameter, the function returns NULL.

attribute is of the VARCHAR2 data type. The following table lists available values of the attribute parameter.


Parameter 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 of the DBMS_RLS package use a modified copy of the sample emp table provided with PolarDB. A role named salesmgr is granted all privileges on the table. You can create the modified copy of the emp table named vpemp and the salesmgr role, as shown in the following example.

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 is used to create a policy by associating a policy function with a database object.

You must be a superuser to call 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 is to be applied.
object_name	The name of the database object to which the policy is to be applied. A database object can have more than one policy applied to it.
policy_name	The name assigned to the policy. The combination of the database object (identified by `object_schema` and `object_name`) and policy name must be unique within the database.
function_schema	The name of the schema that contains the policy function. Note The policy function may belong to a package. In this case, `function_schema` must contain the name of the schema in which the package is defined.
policy_function	The name of the SPL function that defines the rules of the security policy. The same function may be specified in more than one policy. Note The policy function may belong to a package. In this case, `policy_function` must also contain the package name in dot notation (`package_name`.`function_name`).
statement_types	The comma-separated list of SQL statements to which the policy applies. Valid SQL statements are `INSERT, UPDATE, DELETE, and SELECT`. Default value: `INSERT,UPDATE,DELETE,SELECT`. Note PolarDB accepts but ignores the statements of the INDEX type. Policies cannot be applied to index operations in PolarDB.
update_check	`update_check` applies to INSERT and UPDATE SQL statements only. If `update_check` is set to TRUE, the policy is applied to newly inserted rows and to the modified image of updated rows. If a new or modified row does not qualify based on the policy function predicate, the `INSERT` or `UPDATE` statement throws an exception and no rows are inserted or modified. If `update_check` is set to FALSE, the policy is not applied to newly inserted rows or the modified image of updated rows. Therefore, a newly inserted row may not appear in the result set of a subsequent SQL statement that invokes the same policy. Similarly, rows which qualified based on the policy prior to an `UPDATE` statement may not appear in the result set of a subsequent SQL statement that invokes the same policy.
enable	If `enable` is set to TRUE, the policy is enabled and applied to the SQL statements specified by the `statement_types` parameter. Default value: TRUE. If `enable` is set to FALSE, the policy is disabled and not applied to SQL statements. You can enable the policy by using the ENABLE_POLICY stored procedure.
static_policy	In Oracle, if `static_policy` is set to TRUE, the policy is static. The policy function is evaluated once per database object the first time it is invoked by a policy on the database object. The resulting predicate string of the policy function is saved in memory. In this case, when the database server instance is running, the predicate string can be reused for all invocations of that policy on that database object. In Oracle, if `static_policy` is set to FALSE, the policy is dynamic. The policy function is re-evaluated and the predicate string of the policy function is re-generated for all invocations of the policy. Default value: FALSE. Note In Oracle 10g, the `policy_type` parameter was introduced, which is intended to replace the `static_policy` parameter. In Oracle, if the `policy_type` parameter is not set to its default value of NULL, the `policy_type` parameter value overrides the `static_policy` value. PolarDB only implements the dynamic policy and ignores the value of the `static_policy` parameter.
policy_type	In Oracle, `policy_type` determines when the policy function is re-evaluated. Default value: NULL. Note PolarDB only implements the dynamic policy and ignores the value of the `policy_type` parameter.
longpredicate	In Oracle, if `long_predicate` is set to TRUE, predicates can be up to 32 KB in length. Otherwise, predicates are limited to 4 KB in length. Default value: FALSE. Note PolarDB ignores the value of the `longpredicate` parameter. A PolarDB policy function can return a predicate of unlimited length for all practical purposes.
sec_relevant_cols	`sec_relevant_cols` is a comma-separated list of columns of `object_name`. This parameter provides column-level Virtual Private Database for the listed columns. The policy is enforced if a listed column is referenced in an SQL statement of a type specified in `statement_types`. The policy is not enforced if no such columns are referenced. Default value: NULL. The same effect is achieved if all columns of the database object are included in `sec_relevant_cols`.
sec_relevant_cols_opt	In Oracle, if `sec_relevant_cols_opt` is set to `DBMS_RLS.ALL_ROWS` (INTEGER constant of value 1), the columns listed in `sec_relevant_cols` return NULL on all rows where the applied policy predicate is false. If `sec_relevant_cols_opt` is not set to `DBMS_RLS.ALL_ROWS`, these rows will not be returned in the result set. Default value: NULL. Note PolarDB does not support the `DBMS_RLS.ALL_ROWS` function. If `sec_relevant_cols_opt` is set to `DBMS_RLS.ALL_ROWS` (INTEGER value of 1), PolarDB will throw an error.

Examples

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 added to the WHERE clause of each SQL statement of the type specified in the ADD_POLICY stored procedure.

This limits the effect of the SQL statement to rows where the content of the authid column is the same as the session user.

Note The SYS_CONTEXT function is used in this example to return the logon user name. In Oracle, the SYS_CONTEXT function returns attributes of an application context. The first parameter of the SYS_CONTEXT function is the name of an application context. The second parameter is the name of an attribute set within the application context. USERENV is a special built-in namespace that describes the current session. PolarDB supports the special use of the SYS_CONTEXT function, but does not support application contexts.

The following anonymous block calls the ADD_POLICY stored procedure to create a policy named secure_update. The policy will be applied to the vpemp table by using the verify_session_user function regardless of whether an INSERT, UPDATE, or DELETE SQL statement is provided when the vpemp table is referenced.

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 terminal session is started by the salesmgr user. The following query shows the content 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)

An unqualified UPDATE statement (without a WHERE clause) is issued by the salesmgr user:

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

Instead of updating all rows in the table, the policy restricts the effect of the update to rows where the authid column contains the salesmgr value. The salesmgr value is specified by the policy function predicate authid = SYS_CONTEXT('USERENV', 'SESSION_USER').

The following query shows that the comm column has been changed for rows where authid contains 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)

The following INSERT statement throws an exception because the update_check parameter was set to TRUE in the ADD_POLICY stored procedure. The policy is invalid because the researchmgr value specified for the authid column does not match the salesmgr session user.

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

If update_check is set to FALSE, the preceding INSERT statement takes effect.

The following example illustrates the use of the sec_relevant_cols parameter to apply a policy only when specific columns are referenced in the SQL statement. The following policy function is used in this example to select rows where the employee salary value is less than 2000.

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

The policy is created so that it is enforced only if a SELECT statement includes 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. The following query returns all 14 rows of the vpemp table:

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 the query references the sal or comm column, the policy is applied to the query. This query deletes rows where sal is greater than or equal to 2000, A similar output is displayed:

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 is used to delete an existing policy. However, the DROP_POLICY stored procedure cannot delete the policy function and database object associated with the policy.

Note You must be a superuser to execute the DROP_POLICY stored procedure.

DROP_POLICY(object_schema VARCHAR2, object_name VARCHAR2,

  policy_name VARCHAR2)

Parameters


Parameter	Description
object_schema	The schema name of 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 be deleted.

Examples

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';
BEGIN
    DBMS_RLS.DROP_POLICY(
        v_object_schema,
        v_object_name,
        v_policy_name
    );
END;

ENABLE_POLICY

The ENABLE_POLICY stored procedure is used to enable or disable an existing policy on the specified database object.

Note You must be a superuser to 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 schema name of 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 be enabled or disabled.
enable	Enables or disables the policy. If the enable parameter is set to TRUE, the policy is enabled. If the enable parameter is set to FALSE, the policy is disabled.

Examples

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;