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.
Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…