The DBMS_RLS package enables the implementation of Virtual Private Database on certain PolarDB-O database objects.

Table 1. DBMS_RLS functions and stored procedures
Function/stored procedure Function or stored procedure 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-O is partially implemented when compared to Oracle's version. PolarDB-O 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 as defined by security policies.

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-O, the policy function can be written in any language supported by PolarDB-O such as SQL, PL/pgSQL, and SPL. For example, apart from Oracle-compatible SPL languages, we can also use SQL and PL/pgSQL languages.
  • Virtual Private Database of PolarDB-O only supports one type of database objects: tables. Policies cannot be applied to views or synonyms.
The benefits of using Virtual Private Database are described as follows:
  • Virtual Private Database provides a fine-grained level of security. Database object level privileges given by the GRANT command 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 depending upon the type of SQL command (INSERT, UPDATE, DELETE, or SELECT).
  • The security policy is dynamic and can vary for each applicable SQL command 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 following note.
Note The only way security policies can be circumvented is that the user has the EXEMPT ACCESS POLICY system privilege. The EXEMPT ACCESS POLICY privilege must be granted with extreme care because a user with this privilege is exempted from all policies in the database.

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

The process for implementing Virtual Private Database is described as follows:
  • 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 command that acts upon the database object. The rows that do not satisfy the policy function predicate are filtered out from the SQL command 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 commands (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.
    Value of attribute 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-O. 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 follows:
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 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 policy_name is 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 policy_function is 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 statement_types is a comma-separated list of SQL commands to which the policy applies. Valid SQL commands are INSERT, UPDATE, DELETE, and SELECT. Default value: INSERT, UPDATE, DELETE, SELECT.
Note PolarDB-O accepts INDEX as a statement type, but it is ignored. Policies are not applied to INDEX operations in PolarDB-O.
update_check update_check applies to INSERT and UPDATE SQL commands 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 according to the policy function predicate, the INSERT or UPDATE command 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 command that invokes the same policy. Similarly, rows which qualified according to the policy prior to an UPDATE command may not appear in the result set of a subsequent SQL command that invokes the same policy.
enable
  • If enable is set to TRUE, the policy is enabled and applied to the SQL commands specified by the statement_types parameter.
  • If enable is set to FALSE, the policy is disabled and not applied to SQL commands. You can enable the policy by using the ENABLE_POLICY stored procedure. The default value is TRUE.
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.
  • The default value is 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 (NULL), the policy_type parameter setting overrides the static_policy setting.
  • PolarDB-O ignores the setting of the static_policy parameter. PolarDB-O implements only the dynamic policy, regardless of the setting of the static_policy parameter.
policy_type In Oracle, policy_type determines when the policy function is re-evaluated. Therefore, it also determines whether and when the predicate string returned by the policy function changes. The default value is NULL.
Note PolarDB-O ignores the setting of the policy_type parameter. PolarDB-O always assumes a dynamic policy.
long_predicate 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. The default value is FALSE.
Note PolarDB-O ignores the setting of the long_predicate parameter. A PolarDB-O 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 command of a type specified in statement_types. The policy is not enforced if no such columns are referenced.

The default value is 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. The default value is NULL.
Note PolarDB-O 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-O will throw an error.

Examples

This example uses the following policy function:

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 command of the type specified in the ADD_POLICY stored procedure.

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

Note This example uses the SYS_CONTEXT function to return the login 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-O does not support application contexts, but supports this specific usage of the SYS_CONTEXT function.

The following anonymous block calls the ADD_POLICY stored procedure. This is to create a policy named secure_update. Then, 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 command 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.

polardb=# \c polardb salesmgr
Password for user salesmgr: 
You are now connected to database "polardb" as user "salesmgr".
polardb=> 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 command (without a WHERE clause) is issued by the salesmgr user:

polardb=> 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.

polardb=> 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 command 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.

polardb=> 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 was set to FALSE, the preceding INSERT command would have succeeded.

The following example illustrates the use of the sec_relevant_cols parameter to apply a policy only when certain columns are referenced in the SQL command. The following policy function is used in this example, which selects rows where the employee salary is less than USD 2,000 per month.

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 command 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:

polardb=# 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, as shown in the following example:

polardb=# 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 deletes an existing policy. However, the DROP_POLICY stored procedure cannot delete the policy function and database object associated with the policy.

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 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 be deleted.

Examples

The following example deletes 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 enables or disables an existing policy on the specified database object.

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 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 be enabled or disabled.
enable 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;