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