Robust Access Control At The Database Layer
Many Companies struggle with the difficult problem of providing fine grained access control at the database layer to sensitive data . We will discuss Oracle’s Virtual Private Database feature which provides a robust way of handling this problem for all types of database users.
Security vs Effectiveness
Securing access to sensitive data is critical. But facilitating the access to all the data that employees, customers, and vendors need to do their jobs effectively is equally important.
Meeting these competing goals can be difficult because sensitive data is sprinkled throughout our databases. We have social security numbers in employee tables, credit card numbers in customer tables, health data in patient tables, financial data in billing tables. In addition to protecting data for our own best interest, regulations such as PCI DSS, HIPPA, and FISMA, require us to protect our sensitive data, both from outsiders as well as insiders.
So we are left struggling to find ways to give users proper access without giving too much away. This is not easy. Standard security features of relational databases enable us to grant privileges on a full table table to a given user or role, but not on a column or row level. In other words, security at the database layer typically provides all or nothing access to a table. There are a number of traditional workarounds that are used to deal with this.
Views
We can use views to provide restricted access to the data. Views can be designed to restrict access to a subset of columns , and they can restrict access to certain rows using a where clause. But there are a number of limitations to this approach. When views are used to restrict access to certain columns, we have to create a different set of views for each user or group that may require a different set of columns. When used to restrict access to certain rows, then we either have to create different views for each group of users, as discussed earlier, or we have to specify potentially complex logic in the where clause definitions to determine proper access.
Application Security
Another very common approach is to use the application to restrict the rows or columns that can be seen by the user. The user never directly connects to the table via the application, so the application implements all of the security rules and only displays the allowable rows and columns.
One drawback of this approach is that the security enforcement has to be implemented in each report, screen, or form that accesses the database. This adds complexity and time to the development and testing effort and can result in security gaps if development and testing are lacking.
Oracle Virtual Private Database
Oracle’s VPD feature addresses these security gaps. This tool gives the administrator the ability to create rich security access policies that provide fine grained access to both rows and columns of a table to a user or group of users. This is applied at the database layer and will be in effect regardless of how a user connects to the virtual private database. There is no dependence on specific views or on specific applications. It is non-trivial to set up Oracle VPD, but it provides a high level of security when done properly. At a high level here is how you set it up. The steps below are performed for each table, view, or synonym that you are protecting.
- Create an Application Context that is used to set and store session related information for use by the policy function.
- Create a Policy Function that is used to help Oracle create the proper where clause that it will attach to the select statement on a table. This can use information from the Application Context (a set of Name-Value pairs that Oracle stores in Memory) that can provide session information about a user such as employee or customer number. This enables you to restrict information to a specific user or class of user.
- Create a Policy that attaches the Policy Function you just created to the table, view, or synonym that you are protecting. After you create a policy that attaches a function to a database object, that function will be used to automatically add a where clause to any DML statement (select, insert, update, or delete statements) that are executed against the object . Different policies can be applied for each statement type. In addition to the listed DML statement types, index-related DDL statements can also be controlled in a similar fashion.
Controlling evaluation of the policy functions
Policy functions optionally gather information from the environment (from application contexts), and then they create a where clause that will be used with the statement types designated in the policy function. If this information is gathered, and if the where clause is constructed each time a query is executed, then it can have a negative performance impact. To mitigate this, you can control how often the policy function is evaluated. This can be when the session begins, or each time the statement is executed, or it can occur whenever the application context has changed. The choice of when to evaluate the function will depend on the variables involved and how frequently they may change in your environment.
Policy Groups
Policy groups enable you to more easily manage your policy functions. You can create a group of policies that can be enabled as a group in one context (ie for one application), and a different policy group that can be enabled in a different context (for a different application). This enables you to have different security schemes depending on the application being used, but the use of default policy groups can ensure that security is always in place.
Non-Database Users
Some users are not known to the database, as is common in web applications where a single account connects to the database and the application performs the authentication. Enforcing security on these types of users typically must be done at the application layer. However, using the CLIENT_IDENTIFIER attribute of the application context enables you to restrict data for non-database users using Oracle VPD. Note, however, that there is a whole different set of security implications for those applications, one of which is discussed in my article about Oracle Real Application Security.
SQL Server RLS (Row Level Security)
SQL Server provides similar functionality with its RLS functionality. While RLS can be also be used to specify fine grained security controls at the row level, RLS has limitations and its feature set is not as rich as Oracle’s VPD. I plan to go into more detail on SQL Server RLS in a future blog post.
Oracle VPD – Worth the Effort?
We have seen that Oracle Virtual Private Database is a robust toolset for providing fine grained row and column level protection for sensitive data. Implementing Oracle VPD will require a lot of planning and will take time to implement. A good part of it will require developing a robust security policy, which should be done regardless of how it is to be enforced, and the remainder will be in setting up the Policy Groups, Policies, and the code to implement the Application Context.
If you would like to discuss the pros and cons of Oracle VPD for your organization chat or request a meeting or send a note to [email protected].
