One of the cornerstones of enterprise resource planning (ERP) platforms is the ability to customize data objects for a variety of users. Since the point of ERP is to extend the usefulness of a company’s data across business units and company partnerships, the platform must allow the creation of customized data objects that don’t physically exist but can be virtually generated out of objects that do exist.
Oracle meets this demand with its virtual private database (VPD) table feature. It differs somewhat from the concept of logical databases on other database platforms, which allow the virtual configuration of tabled information into logical objects that don’t actually reside in any database. The emphasis in an Oracle system is to serve distributed applications where data-sharing across business units and between partner companies is the priority, and different users must view and handle common business objects in different ways.
Multiple views for multiple users
To make the most inventive and effective use of the virtual private database table, you should implement it from design. The way these tables work is that they are shared by many users, and each user will receive a different view of the available data—that is, each one will see only what is appropriate for that user to see. This is especially important when the user is outside your company—a supplier, customer, or other partner company, for example—given the security considerations.
The underlying concept is row-level security. Oracle permits you to implement a table security policy that lets different groups of users see some portions of the table but not others. So your implementation must begin with design: Perhaps you start with a data warehouse (containing product information for your entire customer base) and you build a user group identifier into the table(s) to be accessed. (You can do this with a pseudo column; you can plug in the current user ID and base a security trigger on it.)
Why use virtual private database tables?
Imagine implementing a product database with the intent of providing product information to both internal and external user groups, including your company’s order processing department, warehouses, and accounting group, as well as all your customers, suppliers, and distributors. Now imagine providing these groups with access to inventory. The system must be secure, with only a limited range of product inventory information available to each group.
Let’s make it more complicated: The level of inventory detail will also vary by user. You’d be facing a mountain of application coding. The access routines would need to be built into the applications themselves, and you’d need multiple applications to accommodate internal and external users. Odds are you wouldn’t be scooping the data out of a single source, either, under these circumstances.
But virtual private database tables and row-level security shift this burden to the server. You can deliver all your product data from a single data warehouse whose configuration is not user-dependent in any way. The savings in design and implementation alone are compelling in this example, but the easy maintenance should clinch it for you.
The pieces of a virtual private database
You’ll also find the virtual private database technique referred to as fine-grained access control in Oracle literature. To implement it, you’ll need to make use of two features: application context and a security policy.
An application context is bound to a PL/SQL routine. It’s a simple yet brilliant mechanism by which variables named within the context can be set only by that PL/SQL routine. The context is a namespace you define with the appropriate values and their attributes; the associated PL/SQL code is the secure means of altering those values. When a set of values has been secured in this way, and context has been invoked, the values may be read but not altered, except by the PL/SQL routine.
The next step is to control that PL/SQL routine, and you do so with a security policy. In logical terms, this policy is a function that dynamically strips away unwanted data when a database table is referenced by a particular user. In this way, the function is bound to the table that it handles and is usually invoked by the statements by which that table is accessed.
The best reason to consider using VPD tables
Using VPD’s row-level security technique has real application benefit. Consider the increasing prevalence of distributed Internet apps and that your Oracle application might need to do its multiuser work in an ASP environment. Ordinarily, you’d need to have multiple database instantiations, one for each customer—or, at the very least, multiple schemas—in order to maintain security. That would be a tremendous implementation and maintenance headache. But with an Oracle app using row-level security, one database does the job and security is solid.
Another great reason to use this feature is that this security is airtight, whether your app is on the Internet or not. When security policies are implemented on tables in this fashion, the table is secure, regardless of the mechanism used to access the table. If the policy is logically invoked by the application, the data is secure.
Scott Robinson is a 20-year IT veteran with extensive experience in business intelligence and systems integration. An enterprise architect with a background in social psychology, he frequently consults and lectures on analytics, business intelligence and social informatics, primarily in the health care and HR industries.