During a recent project, the requirements included data security and access by users in different regional locations. Most users were able to access the data using the BI tool (OBIEE) for financial reports. Another user had access using SQL Developer and could view all of the data in the system. The worst scenario of them all was a user accessing the data with a VB script in Excel. :-)
Virtual Private Database (VPD) is an Oracle feature that allows the database to control the security on the system. Based on the needs of the application, you can define the security to tables using context and package policies for different levels of access. The most common method of security is based on the user’s ID, but not limited to this access rule.
By establishing a table with User name and key details, you can relate access from an Online application, external access tool (i.e. Toad, SQL-Developer, ODBC connection) by user and hopefully this removes the concern of putting security rules on each of the access points.
The basic steps to implement VPD are:
- Build your security table that contains the User ID
- Create a profile (this controls which users can access the requests)
- Set profile value to 'N' at site level.
- create the Oracle context
- Create the Oracle context package
- Create policy package
- Add policy
The basic approach for this type of security is to centralize the security layer and provide a single point of control. VPD gives you the ability to provide access to a table by row or even column level security. Since the VPD rules are applied at runtime, the challenges is what security rule was applied. To account for this issue, you may want to impersonate the user access by setting the context to another user.
Enhancing or replacing other options:
Views and Roles are other basic methods used in protecting and securing data access. Just so we are clear, these methods are perfectly acceptable for limiting the access to data. If a user has access to a view based on a READ role, the user is only able to see that information.
So don’t get caught in the end, secure the data with VPD!