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..
Do you want to learn Oracle Database for Beginners, then read the following articles.
1,250 views last month, 1 views today