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_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 |
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 |
update_check | (Optional) An optional parameter valid for the |
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_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 |
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_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:
|
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_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)