Flexible VPD structures for Oracle

Published on
5,533 Points
Last Modified:
We have a data warehouse that has tables that contain data that not everyone is allowed to see.  to get around this, there are 2 views over the top of the table, one containing all data, one with restricted data and these views are then granted out to the users.  select privilege on the underlying table is not granted.

This has issues when using explain plans, it generates ORA-01039 errors, "insufficient privileges on underlying objects of the view".

To get around this, you can use VPD, virtual private database.

Now, the way that VPD is implemented ( or the way that I have implemented it ), there is a context for each type of security you wish to apply.  each context is associated with a security package which contains functions / procedures to determine which predicates to add to the query.

For a database with several types of security models, this can become an overwhelming management nightmare.

I have developed a flexible VPD solution that allows unlimited security models with unlimited predicates as well, all with only 4 tables and 2 packages.

to use this, create a logon trigger that calls "oemdba.vpd_security.set_vpd_access_levels".

feel free to take from this what you will, any suggestions / improvements will be gratefully incorporated back into what i have here.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free