Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will introduce the “Virtual Private Database” feature, which is one of the security policies offered by Oracle.
With Virtual Private Database, you can hide data on a row and column basis for users determined by different security policies, and also impose restrictions on commands such as insert, update and delete.
Thanks to this feature, which solves our business quickly on the basis of users, you can also create solutions on KVKK relatively.
To create the Oracle Virtual Private rule structure, we will first need a function.
In our scenario, I will use the incoming HR schema on Oracle Database for testing purposes. Our function will be named POL_MAAS_GIZLE.
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE FUNCTION SYSTEM.POL_MAAS_GIZLE (v_SchemaName IN VARCHAR2, v_ObjectName IN VARCHAR2) RETURN VARCHAR2 AS condition VARCHAR2 (200); BEGIN condition := 'JOB_ID = ''AD_VP123'''; RETURN (condition); END POL_MAAS_GIZLE; / |
Our function has been created successfully. Let’s check.
1 | SELECT * FROM DBA_POLICIES WHERE POLICY_NAME='POL_MAAS_GIZLE'; |
Now we create our rule. In our rule, we will hide the “MAAS” (SALARY) column in the EMPLOYESS table in the HR schema.
When the query runs, it will return a NULL value. Since this value returns INTEGER, we cannot replace it with anything else.
1 2 3 4 5 6 7 8 9 10 11 12 13 | BEGIN DBMS_RLS.ADD_POLICY (object_schema => 'HR', object_name => 'EMPLOYEES', policy_name => 'POL_MAAS_GIZLE', function_schema => 'SYSTEM', policy_function => 'POL_MAAS_GIZLE', statement_types => 'SELECT', policy_type => DBMS_RLS.SHARED_STATIC, sec_relevant_cols => 'SALARY', sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS); END; / |
When you create a rule for Oracle Virtual Private database, it affects all users created for the system, such as SYS and SYSTEM.
If there is a user that you do not want to be stuck in the VPD rule, you need to specify it with GRANT.
1 2 | -- To exclude a user from VPD SQL\> GRANT EXEMPT ACCESS POLICY TO USR_ABUZER; |
We are selecting again. As you can see, the salary column has appeared.
If we want it to appear again,
1 | REVOKE EXEMPT ACCESS POLICY FROM USR_ABUZER; |
If you want to extend the constraint, you can extend it by putting “,” in the schema, table and query.
For example, the “EMAIL” part should not appear. For this, you need to add to the ” sec_relevant_cols_opt ” field.
1 2 3 4 5 6 7 8 9 10 11 12 13 | BEGIN DBMS_RLS.ADD_POLICY (object_schema => 'HR', object_name => 'EMPLOYEES', policy_name => 'POL_MAAS_GIZLE', function_schema => 'SYSTEM', policy_function => 'POL_MAAS_GIZLE', statement_types => 'SELECT', policy_type => DBMS_RLS.SHARED_STATIC, sec_relevant_cols => 'SALARY,EMAIL', sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS); END; / |
We simply masked our data with the Oracle Virtual Private Database feature.
So how can we delete a rule we wrote?
1 2 3 4 5 6 | BEGIN DBMS_RLS.DROP_POLICY (object_schema => 'HR', object_name => 'EMPLOYEES', policy_name => 'POL_MAAS_GIZLE'); END; / |
