DBMS_RLS.ADD_POLICY - Oracle Fine-Grained Access Control

Oracle Database provides a robust security model that allows for both coarse-grained and fine-grained access control. Fine-grained access control allows you to control access to specific rows in a table, view, or materialized view based on user attributes or the context of the session. To achieve this, Oracle provides the DBMS_RLS package, which contains procedures and functions for implementing row-level security (RLS).

One of the key procedures in the DBMS_RLS package is ADD_POLICY. This procedure allows you to define a policy that determines which rows a user can access based on specified conditions. Let’s delve into the details of how to use DBMS_RLS.ADD_POLICY.

Syntax:

DBMS_RLS.ADD_POLICY (
   object_schema   IN VARCHAR2,
   object_name     IN VARCHAR2,
   policy_name     IN VARCHAR2,
   function_schema IN VARCHAR2 DEFAULT NULL,
   policy_function IN VARCHAR2 DEFAULT NULL,
   stmt_type       IN VARCHAR2 DEFAULT NULL,
   sec_relevant    IN VARCHAR2 DEFAULT 'YES',
   using_columns   IN VARCHAR2 DEFAULT NULL,
   desc_cols_owned IN BOOLEAN DEFAULT FALSE
);

Parameters:

1. object_schema - The schema name of the table, view, or materialized view that the policy applies to.
2. object_name - The name of the table, view, or materialized view that the policy applies to.
3. policy_name - The name of the policy you are adding.
4. function_schema (Optional) - The schema name of the policy function, if you’re using a function to determine access.
5. policy_function (Optional) - The name of the function that will be used to determine access. This function should return a boolean value (TRUE or FALSE) indicating whether the user has access to the row.
6. stmt_type (Optional) - Specifies the type of SQL statement the policy applies to. Valid values are SELECT, INSERT, UPDATE, DELETE, or ALL.
7. sec_relevant (Optional) - Specifies whether the policy is security-relevant. If set to YES, the policy will be applied when the user executes a SQL statement. If set to NO, the policy will not be applied. The default value is YES.
8. using_columns (Optional) - A comma-separated list of column names that the policy function will use.
9. desc_cols_owned (Optional) - A boolean value indicating whether the user must own the description of the columns used in the policy. The default value is FALSE.

Practical Examples:

Let’s consider a simple example where we have a table called employees and we want to restrict access to rows based on the user’s department.

1. Create the Table:

CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   department_id NUMBER
);

1. Create a Policy Function:

CREATE OR REPLACE FUNCTION check_department_access(p_department_id NUMBER) RETURN BOOLEAN AS
BEGIN
   IF SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') = 'HR' AND p_department_id = 10 THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;
/

This function checks if the user’s schema is ‘HR’ and if the department ID is 10. If both conditions are met, it returns TRUE, indicating that the user has access to the row.

1. Add the Policy:

```sql
EXEC DBMS_RLS.ADD_POLICY (
object_schema => ‘HR’,
object_name => ‘employees’,
policy_name => ‘department_access_policy’,
function_schema => ‘HR’,
policy_function => ‘check_department