Oracle Virtual Private Database (VPD) -2

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..

Deniz Parlak

I am a technical author on IT Tutorial. I am working on Oracle and Mysql databases, I also work on Datawarehouse and Big Data. If you need help, please contact deniz.parlak@yahoo.com.