Oracle Virtual Private Database (VPD) – 1

Virtual Private Database allows us to define constraints on tables and views based on row and column security policies. It should not be considered as a separate database because of the word database.

For example, there is an employee table in our database that stores the information of the employees. If we want to avoid accessing the information of the managers in this table, we can make VPD definitions to automatically add JOB <> ‘MANAGER’ to the end of the SELECT * FROM EMPLOYEE SQL statement.

select * from employees JOB <> 'MANAGER';

What does VPD provide us?

With VPD, we make no changes to our codes; we can define very detailed access restrictions on the basis of rows and columns in accessing tables, views, and synonyms.

Since the definition of VPD; no matter which application connects to the database, the user’s access is restricted.

• VPD definitions are not only for the SELECT command; INSERT, UPDATE, DELETE commands can also be defined separately.
• VPD definitions can be not only row-based but also column-based.

This means; Although we allow access to all rows in a table, we can display the contents of some columns as NULL.

This is called a column-based Virtual Private Database, and in accessing sensitive information; It is used to hide the columns.

Description of VPD

In order to make VPD definitions, we must have the authority to EXECUTE the DBMS_RLS package.  We associate the security function you defined with an object using the DBMS_RLS.ADD_POLICY procedure.

Only users with EXEMPT ACCESS POLICY privileges are not affected by VPD definitions.

Let’s make an example.

We will hide administrator data in HR.EMPLOYEES table. We will create a user with the authority to select this table and perform the example from that user.

We created the user and granted the necessary privileges.  Let’s connect with this user and run a query.

In the Employees table, I want to hide those with DEPARTMENT_ID of 50 in my queries.

Let’s connect to Database with SYSTEM user and create a security function.

CREATE OR REPLACE FUNCTION Hide_Shipping (v_SchemaName IN VARCHAR2, v_objectName IN VARCHAR2)
RETURN VARCHAR2
AS
condition VARCHAR2(200);
BEGIN
condition := 'DEPARTMENT_ID <> ''50''';
RETURN (condition);
END Hide_Shipping;
/

The functions used in VPD must have two parameters and these parameters must be of type VARCHAR2.

Let’s add DBMS_RLS.ADD_POLICY to our security policy.

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

As shown in the picture, when we sort by column department_id, department_id does not show the data with a value of 50

NOTE: SYS user is not affected by any security policies

The security policies defined for VPD are followed from the dba_policies view.

If we want to disable the VPD policy, we can call DBMS_RLS.ENABLE_POLICY,

BEGIN

DBMS_RLS.ENABLE_POLICY(

object_schema => 'HR',

object_name => 'EMPLOYEES',

policy_name => 'HIDE_SHIP',

enable => FALSE);

END;

/

In the next article we will continue to Virtual Private Database.  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/