DBMS_RLS - PolarDB - Alibaba Cloud Documentation Center

The DBMS_RLS package allows you to add a fine-grained Resource Access Management (RAM) policy for a table for flexible data isolation.

Subprograms

Subprogram	Description
ADD_POLICY Procedure	Adds a fine-grained RAM policy for a table.
ENABLE_POLICY Proceduree	Enables or disables a fine-grained RAM policy.
DROP_POLICY Procedure	Removes a fine-grained RAM policy from a table.

ADD_POLICY Procedure

This stored procedure is used to add a fine-grained RAM policy for a table.

Syntax

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,
   long_predicate           IN  BOOLEAN        DEFAULT FALSE,
   sec_relevant_cols        IN  VARCHAR2       DEFAULT NULL,
   sec_relevant_cols_opt    IN  BINARY_INTEGER DEFAULT NULL);

Parameters

Parameter	Description
object_schema	(Optional) The schema of the table. If you do not specify the `object_schema` parameter, the mode of the current user is used.
object_name	The name of the table for which you want to add the policy.
policy_name	The name of the policy that you want to add. The name must be unique for the same table.
function_schema	(Optional) The mode of the policy function. If you set this parameter to NULL, the current default mode is used. If you do not specify the `function_schema` parameter, the mode of the current user is used.
policy_function	The name of the function that generates the predicate for the policy. If the function is defined in a package, the name of the package must be included in the function name.
statement_types	(Optional) The type of statements to which the policy applies. The statements can be any combination of the `SELECT`, `INSERT`, `UPDATE`, and `DELETE` types. Default value: `SELECT UPDATE DELETE`.
update_check	(Optional) An optional parameter valid for the `INSERT` or `UPDATE` statement type. If you set the update_check parameter to TRUE, PolarDB detects inserts or updated values. Default value: FALSE.
enable	(Optional) Specifies whether to enable the policy when it is added. Default value: TRUE.
static_policy	(Optional) The policy function. If you set this parameter to TRUE, the policy function generates the same predicate string for anyone who accesses the object. Default value: FALSE. This parameter is valid only if the static policy is compatible with that of Oracle in PolarDB.
policy_type	(Optional) The policy type. Default value: NULL. The default value indicates that the value of the policy_type parameter is determined by the value of the static_policy parameter. After you configure the policy_type parameter, the value of the static_policy parameter is overwritten. This parameter is valid only if the policy type is compatible with that of Oracle in PolarDB.
long_predicate	The length of the predicate in the policy. Default value: FALSE. The default value indicates that the policy function can return the string that contains up to 4,000 bytes in length for the predicate. If you set this parameter to TRUE, the strings in the predicate can be up to 32 KB in length. This parameter is valid only if the strings in the predicate is compatible with that of Oracle in PolarDB.
sec_relevant_cols	(Optional) Enables column-level virtual privacy database (VPD) in a query. When a column specified by `sec_relevant_cols` is referenced in the query, security policies are enforced. Separate multiple column names with commas (,). By default, all columns specified for an object are referenced.
sec_relevant_cols_opt	(Optional) Determines how query results are displayed when specified columns do not match security policies. If you set the sec_relevant_cols_opt parameter to `DBMS_RLS.ALL_ROWS` and the current policy is not matched, the columns specified by `sec_relevant_cols` are displayed as NULL and the remaining columns are displayed as expected. If you set this parameter to NULL and the current policy is not matched, the entire row is not displayed. Default value: NULL.

Example

The following example shows how to add a RAM policy:

-- Create a test table.
CREATE TABLE t(a int, b int);
INSERT INTO t VALUES (1, 2);
INSERT INTO t VALUES (10, 20);
INSERT INTO t VALUES (100, 200);

-- Create a policy function.
CREATE OR REPLACE FUNCTION f(obj_schema varchar2, obj_name varchar2)
RETURN varchar2 IS
BEGIN
  RETURN 'a < 10';
END;

-- Add a policy.
BEGIN
DBMS_RLS.ADD_POLICY(object_name => 't',
                    policy_name => 'p',
                    policy_function => 'f',
                    statement_types => 'select',
                    sec_relevant_cols => 'b',
                    sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;

-- Query data. If a value of the a column in a row is less than 10, the value of the b column in the row is displayed, and the values of the b column in other rows are not displayed.
SELECT * FROM t ORDER BY b;
  a  | b
-----+---
   1 | 2
  10 |
 100 |
(3 rows)

ENABLE_POLICY Procedure

This stored procedure is used to enable or disable a fine-grained RAM policy.

Syntax

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

Parameters

Parameter	Description
object_schema	(Optional) The schema of the table. If you do not specify the `object_schema` parameter, the mode of the current user is used.
object_name	The name of the table for which you want to enable or disable the policy.
policy_name	The name of the policy that you want to enable or disable.
enable	Specifies whether to enable or disable the policy. Valid values: TRUE (default): The policy is enabled. FALSE: The policy is disabled.

Example

The following example shows how to enable or disable a policy:

-- Disable the policy.
BEGIN
DBMS_RLS.ENABLE_POLICY(object_name => 't',
                       policy_name => 'p',
                       enable => 'f');
END;

-- Query data. All values of the a and b columns are displayed.
SELECT * FROM t ORDER BY b;
  a  |  b
-----+-----
   1 |   2
  10 |  20
 100 | 200
(3 rows)

-- Enable the policy.
BEGIN
DBMS_RLS.ENABLE_POLICY(object_name => 't',
                       policy_name => 'p',
                       enable => 't');
END;

-- Query data. If a value of the a column in a row is less than 10, the value of the b column in the row is displayed, and the values of the b column in other rows are not displayed.
SELECT * FROM t ORDER BY b;
  a  | b
-----+---
   1 | 2
  10 |
 100 |
(3 rows)

DROP_POLICY Procedure

This stored procedure is used to remove a fine-grained RAM policy from a table.

Syntax

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

Parameters

Parameter	Description
object_schema	(Optional) The schema of the table. If you do not specify the `object_schema` parameter, the mode of the current user is used.
object_name	The name of the table from which you want to remove the policy.
policy_name	The name of the policy that you want to remove.

Example

The following example shows how to remove a specified policy:

-- Remove the policy.
BEGIN
DBMS_RLS.DROP_POLICY(object_name => 't',
                     policy_name => 'p');
END;

-- Query data. All values of the a and b columns are displayed.
SELECT * FROM t ORDER BY b;
  a  |  b
-----+-----
   1 |   2
  10 |  20
 100 | 200
(3 rows)