Friday , July 19 2024

Virtual Private Database In Oracle

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.

Our function has been created successfully. Let’s check.

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.

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.

We are selecting again. As you can see, the salary column has appeared.

If we want it to appear again,

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.

We simply masked our data with the Oracle Virtual Private Database feature.

So how can we delete a rule we wrote?


About Buğra PARLAYAN

Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.

Leave a Reply

Your email address will not be published. Required fields are marked *