DBMS_RLS Package

We use DBMS_RLS to create, modify, or delete security policies with VPD.  The DBMS_RLS.ADD_POLICY procedure allows us to create a VPD security policy. The definition of parameters used in this procedure is as follows.

If we want a user to be unaffected by VPD definitions, we must authorize that user to EXEMPT ACCESS POLICY.

GRANT EXEMPT ACCESS POLICY TO TEST_USER;

REVOKE EXEMPT ACCESS POLICY TO TEST_USER;

Column Based VPD Identification

If we want the VPD definition to work only if some columns are selected, we define the column-based VPD definition.  Column names to be hidden in the sec_relevant_cols parameter must be entered to define the column-based VPD definition.

Let’s try to hide the SALARY information of people whose JOB_ID is IT_PROG in the Employees table

sqlplus / as sysdba

conn system/oracle

CREATE OR REPLACE FUNCTION Hide_salary(v_SchemaName IN VARCHAR2, v_ObjectName IN VARCHAR2)
RETURN VARCHAR2
AS
condition VARCHAR2(200);
BEGIN
condition := 'JOB_ID <> ''IT_PROG''';
RETURN(condition);
END Hide_salary;
/

BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'Hide_salary',
function_schema => 'SYSTEM',
policy_function => 'Hide_salary',
statement_types => 'SELECT',
policy_type => dbms_rls.SHARED_STATIC,
sec_relevant_cols => 'SALARY');
END;
/

SELECT * FROM DBA_POLICIES WHERE POLICY_NAME='HIDE_SALARY';

Let’s query the employee table with JOB_ID value IT_PROG,

select * from HR.employees WHERE job_id = 'IT_PROG';

As you can see in the picture, we are no longer able to view records with JOB_ID IT_PROG.

A different method of column-based VPD definition is used to mask data

When sec_relevant_cols_opt is set to dbms_rls.ALL_ROWS, VPD definitions work differently.  In this case, all records are retrieved.  Only if the columns in the sec_relevant_cols parameter value FALSE from the security function in the VPD definition are displayed as NULL.  With Oracle 11.2.0.4, you can use Oracle’s Data Redaction option to hide data.  

I will discuss this in the next articles.

Let’s delete the VPD definition we just defined,

BEGIN
DBMS_RLS.DROP_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'Hide_salary');
END;
/

Let’s define the new VPD that masks the Salary column

BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'HIDE_SALARY',
function_schema => 'SYSTEM',
policy_function => 'HIDE_SALARY',
statement_types => 'SELECT',
policy_type => dbms_rls.SHARED_STATIC,
sec_relevant_cols => 'SALARY',
sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
END;
/

You will see the records in the EMPLOYEES table with the JOB_ID of IT_PROG, but the SALARY values will be NULL.

Let’s finish this article here, and in the next article we’ll see how to define VPD Policy groups.

See you soon..

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/