The ADD_POLICY procedure is used to add fine-grained access control policies for tables or views.
Applicability
This content applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support it.
Syntax
DBMS_RLS.ADD_POLICY(
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
function_schema IN VARCHAR2 := NULL,
policy_function IN VARCHAR2,
statement_types IN VARCHAR2 := NULL,
update_check IN BOOLEAN := FALSE,
enable IN BOOLEAN := TRUE,
static_policy IN BOOLEAN := FALSE,
policy_type IN BINARY_INTEGER := NULL,
long_predicate IN BOOLEAN := FALSE,
sec_relevant_cols IN VARCHAR2 := NULL,
sec_relevant_cols_opt IN BINARY_INTEGER := NULL,
namespace IN VARCHAR2 := NULL,
attribute IN VARCHAR2 := NULL);
Parameters
Parameter |
Description |
|---|---|
| object_schema | The name of the schema to which the table or view belongs. Default isNULL, indicating the current schema. |
| object_name | The name of the table or view. |
| policy_name | The name of the policy. |
| function_schema | The schema to which the policy function belongs. Default isNULL, indicating the current schema. |
| policy_function | The name of the policy function. This function generates predicate conditions to be added to the query. |
| statement_types | SQL statement type of the application policy, such asSELECT、INSERT、UPDATE、DELETE. Default value:NULL, which indicates all types. |
| update_check | Whether to check the policy after insertion or update. Default isFALSE. |
| enable | Whether to enable the policy after it is added. Default:TRUE. |
| static_policy | Whether it is a static policy. A static policy generates the same predicate for all users and is executed only once. The default value isFALSE. |
| policy_type | Policy type. The following constants are defined:STATIC=1、SHARED_STATIC=2、CONTEXT_SENSITIVE=3、SHARED_CONTEXT_SENSITIVE=4、DYNAMIC=5. |
| long_predicate | Whether the predicate exceeds 2,000 bytes. Default isFALSE. |
| sec_relevant_cols | A comma-separated list of security-related column names. If specified, the security policy applies only to these columns. |
| sec_relevant_cols_opt | Security-related column options. |
| namespace | Namespace. |
| attribute | Attribute. |
Examples
-- Create a test table and policy function.
CREATE TABLE hr.employees(
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
dept_id NUMBER);
CREATE OR REPLACE FUNCTION hr.auth_emps(
schema_var IN VARCHAR2,
table_var IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN 'dept_id = SYS_CONTEXT(''USERENV'', ''DEPARTMENT_ID'')';
END;
/
-- Add Security Policy
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'emp_policy',
function_schema => 'hr',
policy_function => 'auth_emps',
statement_types => 'SELECT, UPDATE, DELETE');
END;
/