Chapter 29. DBMS

Chapter 29. DBMS_RLS

Table of Contents

29.2.1. ADD_POLICY
29.2.2. DROP_POLICY
29.2.3. ENABLE_POLICY
29.2.4. REFRESH_POLICY

This chapter briefly introduces the DBMS_RLS package, and describes how to use the procedures of the package.

29.1. Overview

DBMS_RLS configures and manages Tibero virtual private database.

In a virtual private database, access to rows returned by a schema object (table, view, and synonym) is restricted by dynamically creating a conditional statement for the schema object. A dynamic conditional statement can be created using a PSM function that returns a string, and a policy can be implemented by associating the function with the added security policy. The following is an example.

DBMS_RLS.ADD_POLICY ('scott', 'emp', 'pol1', 'secadm', 'emp_sec', 'select');

In this example, whenever select is executed on the emp table in the scott schema using pol1 policy, Tibero dynamically creates a conditional statement by calling secadm's emp_sec function. The select statement is executed by appending the conditional statement as a where clause.

To dynamically create a conditional statement, the current status (or condition) must be checked, which is generally done using an application context function. For more information, refer to the DBMS_SESSION package and the SYS_CONTEXT built-in function.

29.2. Procedures

This section describes the procedures provided by the DBMS_RLS package, in alphabetical order.

29.2.1. ADD_POLICY

Adds a security policy to a table, view, or synonym in a virtual private database. This procedure causes the current transaction to be committed immediately.

A policy function must be implemented using the following function prototype.

FUNCTION policy_function (object_schema IN VARCHAR2, object_name VARCHAR2) 
    RETURN VARCHAR2;

The object_schema and object_name are the name of the schema and object, respectively, containing the table, view, or synonym to which the policy is applied.

Details about the ADD_POLICY procedure are as follows:

Prototype

DBMS_RLS.ADD_POLICY 
(
    object_schema         IN VARCHAR2 DEFAULT NULL,
    object_name           IN VARCHAR2,
    policy_name           IN VARCHAR2,
    function_schema       IN VARCHAR2 DEFAULT NULL,
    policy_function       IN VARCHAR2,
    statement_types       IN VARCHAR2 DEFAULT NULL,
    update_check          IN BOOLEAN DEFAULT FALSE,
    enable                IN BOOLEAN DEFAULT TRUE,
    static_policy         IN BOOLEAN DEFAULT FALSE,
    policy_type           IN BINARY_INTEGER DEFAULT NULL,
    sec_relevant_cols     IN VARCHAR2 DEFAULT NULL,
    set_relevant_cols_opt IN BINARY_INTEGER DEFAULT NULL
);

Parameter

Parameter	Description
object_schema	Schema containing the table, view, and synonym. (Default value: current user schema)
object_name	Name of the table, view, or synonym.
policy_name	Name of the policy to add. Must be unique in the schema.
function_schema	Schema of the function that creates the conditional clause. (Default value: current user schema)
policy_function	Name of the function that creates the conditional clause. If the function is in a package, specify the package name.
statement_types	Types of SQL statement to apply the policy to. Combination of SELECT, INSERT, UPDATE, and DELETE can be specified. (Default value: SELECT, INSERT, UPDATE, and DELETE)
update_check	Option to perform update check during a SQL INSERT or UPDATE. If set to TRUE, it is checked whether an inserted or updated value satisfies the policy's conditional clause. (Default value: FALSE)
enable	Option to activate the policy when it is added. (Default value: TRUE)
static_policy	Policy type. This parameter is used only when policy_type is not specified. TRUE: STATIC (Default value) FALSE: DYNAMIC
policy_type	Policy type. (Default value: NULL) Options are: STATIC: used when the returned conditional clause is not relevant during runtime. The policy function is executed once initially, and the result is saved in shared memory. SHARED_STATIC: same as STATIC, but it reuses the policy execution result if the policy function has already been executed apart from the target schema object. CONTEXT_SENSITIVE: re-execute a policy function if an application context is changed while executing a SQL statement. The saved conditional statement is deleted when the session terminates. SHARED_CONTEXT_SENSITIVE: same as CONTEXT_SENSITIVE, but it reuses the policy execution result if the policy function has already been executed apart from the target schema object. DYNAMIC: always execute the policy function. (Default value)
sec_relevant_cols	Enables the column-level VPD function. Names of columns to mask with NULL according to the result returned by the conditional statement function. Names can be delimited by a comma or blank space. This parameter can be specified for a table or view, but not for a synonym. Default value: NULL (column-level VPD is not used)
sec_relevant_cols_opt	Either NULL (default value) or dbms_rls.ALL_ROWS. If the column-level VPD function is enabled using set_relevant_cols, this parameter value must be set to dbms_rls.ALL_ROWS. Otherwise, it must be set to NULL.

Example

BEGIN
    DBMS_RLS.ADD_POLICY (
        object_schema=>'scott',
        object_name=>'emp',
        policy_name=>'pol1',
        function_schema=>'secadm',
        policy_function=>'emp_sec',
        statement_types=>'insert,update',
        policy_type=>DBMS_RLS.CONTEXT_SENSITIVE);
END;
/

29.2.2. DROP_POLICY

Drops a security policy from a table, view, and synonym in a virtual private database. This procedure causes the current transaction to be committed immediately.

Details about the DROP_POLICY procedure are as follows:

Prototype

DBMS_RLS.DROP_POLICY 
(
    object_schema   IN VARCHAR2 DEFAULT NULL,
    object_name     IN VARCHAR2,
    policy_name     IN VARCHAR2
);

Parameter

Parameter	Description
object_schema	Schema containing the table, view, and synonym. (Default value: current user schema)
object_name	Name of the table, view, or synonym.
policy_name	Name of the policy to drop.

29.2.3. ENABLE_POLICY

Enables or disables a security policy of a table, view, and synonym in a virtual private database. This procedure causes the current transaction to be committed immediately.

Details about the ENABLE_POLICY procedure are as follows:

Prototype

DBMS_RLS.ENABLE_POLICY 
(
    object_schema   IN VARCHAR2 DEFAULT NULL,
    object_name     IN VARCHAR2,
    policy_name     IN VARCHAR2,
    enable          IN BOOLEAN DEFAULT TRUE
);

Parameter

Parameter	Description
object_schema	Schema containing the table, view, and synonym. (Default value: current user schema)
object_name	Name of the table, view, or synonym.
policy_name	Name of the policy to enable.
enable	TRUE: Enable policy. FALSE: Disable policy.

29.2.4. REFRESH_POLICY

Invalidates execution plans associated with a security policy and all policy function executions results stored in memory. This causes a SQL statement associated with the policy to be reparsed and the policy function to be re-executed upon execution.

If this procedure is executed for a disabled policy, an error is returned.

Details about the REFRESH_POLICY procedure are as follows:

Prototype

DBMS_RLS.REFRESH_POLICY 
(
    object_schema   IN VARCHAR2 DEFAULT NULL,
    object_name     IN VARCHAR2,
    policy_name     IN VARCHAR2
);

Parameter

Parameter	Description
object_schema	Schema containing the table, view, and synonym. (Default value: current user schema)
object_name	Name of the table, view, or synonym.
policy_name	Name of the policy to refresh.

Prev		Next
Chapter 28. DBMS_RESULT_CACHE	Home	Chapter 30. DBMS_ROWID