Virtual Private Database

Oracle has a behind the scenes functionality called the virtual private database. It is also known as fine grained access control (FGAC). The database will modify queries automatically to present a partial view of a database table. There is policy_function which returns the WHERE clause that masks the query results.

This functionality can be as simple as hiding certain rows from a result set using a more restrictive WHERE clause. It can also mask out some columns. This effectively creates a customized virtual object that is generated on the fly. This is very similar to a view. It is just that each user gets a custom view.

The FGAC can implement row level security. As such you can lock down data as you see fit. An application context is set at sign on. This is accomplished via a LOGON trigger. The rest is plug and chug by the Oracle database. Essentially you are getting logical security with the virtual private database. Good stuff. We might be using this on our project at work to implement some of the customer’s data hiding business needs.