Oracle Row Level Security: Part 1
by Pete Finnigan
In this short paper I want to explore the rather interesting row level security feature added to Oracle 8i and above, starting with version 8.1.5. This functionality has been described as fine grained access control or row level security or virtual private databases but they all essentially mean the same thing. We will come back to this shortly but before we do that lets get to what this paper is about. This paper is meant as an overview; a taster in fact of what row level security can be used for and how it can be used, with some simple examples to illustrate. I want to also discuss some of the issues with row level security. Finally, I also want to show how to view what row level security components have been implemented in the database and also touch on how to view how the actual database queries are altered by the row level security functionality in the oracle optimizer.
The example program code used in this paper is available at http://www.petefinnigan.com/sql.htm.
So many names
There seem to be many conflicting names for Row Level Security throughout its existence in Oracle. Why is this? Or more importantly does it really matter to us, the users of this technology? Of course not! The API package interface is called DBMS_RLS (for row level security) so we will stick to that for now.
There is also another name bandied about - label security - this is in fact another piece of functionality and is a replacement for the old trusted Oracle product. Label security is used by the more security minded customers of Oracle who are interested in higher levels of data protection and is an add-on for Oracle enterprise edition. A GUI tool called policy manager was added in 9i as part of enterprise manager and allows the setting up of label security. Label security is implemented on top of VPD. For label security the programs are pre-created and are implemented on a single column value. It is this column that represents the label. Because Oracle has already coded the functionality atop of VPD, label security can be implemented without programming expertise.
One other name that gets associated with virtual private databases, fine grained access control and row level security is fine grained auditing. This has nothing to do with fine grained access other than it works in a similar way and is managed by an API package DBMS_FGA and allows auditing to work at the row level. Fine grained auditing will not be discussed further here.
What can row level security be used for?
Why would a user of Oracle's database products want to use the row level security functionality? Well one of the main uses is to allow all of the data to be stored in one database for different departments or even for a hosting company to store data for different companies in one database. Previously this could have been done with Oracle by using either database views or database triggers or a combination of both. In general this leads to complex applications with a lot of code repetition.
If an application needed to cater to a number of departments that should only be able to access differing sets of data then a set of views would be created for each group of business users. These would have hard coded where clauses that implemented the business rules. Instead, database triggers would be utilized to cater for data manipulations. Grouping business users together to be able to use these sets of views and triggers tended to lead to the use of shared accounts.
Maintenance becomes difficult as adding a new business group, or in the case of multiple company's data in the same database, leads to the need to replicate and alter a whole set of views, triggers and associated code.
Auditing of individual user actions becomes a problem with the built in audit features as users share database accounts. To enable business users to be audited a whole set of authentication, application roles and audit features are generally implemented by the developers.
Where is all of this code? It could be in the client applications, or more recently in a middle tier application server. Maintenance is clearly a big issue! So is security - what would happen if a user connected to the database with a tool such as SQL*Plus or even MS Excel via ODBC and used one of the shared accounts? When applications control the differing business roles from manager down to lowest data in-putter then there is an issue. The shared database account needs to be able to see and use all of the functionality for every application role. So, when a direct connection is made to the database using one of these accounts a security hole exists.
The scenario I have painted is just one possibility; there are many others and similar issues will be obvious in those as well. Row level security can be a solution to some of these problems. Here are a few of the advantages of row level security:
To close this section, it is worth noting that row level security does not make the old methods totally redundant, they can still be valid and be the best solution in some cases.
So how does it work - a brief example
Row level security is based around the idea of having a defined security policy function that is attached to a database table or view execute each time data in the table or view is queried or altered. This function returns an additional piece of SQL called a predicate that is attached to the original SQL's where clause before the SQL is used. This is done in the query optimizer and is actually done when the SQL is parsed and executed. When the SQL is executed it is actually the modified SQL that is executed on behalf of the user. This means that the policy function controls which rows of data are returned. The process can be thought of as a system trigger that is executed when a table is accessed that has a policy defined. This also means that it is now possible to use this functionality to write select triggers against tables.
Application contexts can also be used within the policy function to define which predicate is returned to the optimizer. These application contexts are stored in a namespace for each user and can be set in name/value pairs to identify which groups of users belong to which where clause! It is not mandatory though to use an application context.
Next let's look at a simple example of the use of row level security to see how it works. First start by creating a user to use for the test and grant some basic privileges needed. The user needs to be able to create a table and add data, create package procedures, create a context and access the row level security API and session packages.
In general there are a number of basic actions required in setting up and use of fine grained access control. These basic steps could be described as follows and be applied to our example:
In our case we will use a simple test table called transactions that we will create for the purpose. This table holds some financial details for a fictitious company. These financial transactions include dates, credits, debits, transaction types and cost centres.
In the real world an application would probably want to protect tables that hold critical data or data that pertained to different divisions of an organisation or... at the design stage data that should be viewed by certain groups of users should be identified and any tables or views that store or reveal that data should be candidates for row level security.
The test table can be created and data can now be populated:
Now that there is a table to protect and some data, let's move on to the next stage.
Defining the rules to be implemented is the next stage. This should revolve around which groups of people / workers should be able to access what data and how. It is possible to define differing rules on a table or view for reading data, writing data, updating data or deleting it.
In the case of our simple example we shall define the following rules. Any user in the accounts section can only view all transactions in the accounts cost centre. Any user who is employed as a clerk can view only cash cost centre transactions. Managers can view all transactions and finally any user who is not any of the above three cannot view any transactions. Existing records can be added and changed in the same groups with the same rules.
The security context is called an application context in Oracle and is a namespace that has name/value pairs. The only way to set a context is through the PL/SQL package that is bound to that context. Using a package bound to the context that has predefined rules to control setting of the context stops a malicious user from spoofing the context that she wants to gain better access.
Creating the context is reasonably easy - it should be associated with the PL/SQL package that will be used to set values in this context for users. The package does not have to exist yet to be able to create the context. Here is the code:
In a real application the function used to set the application or security context would set the context for the logged in user. Remember this is the function that was bound to the context when it was created. This allows a trusted method to set a user's context in the sense that the context can only be set through this function so it is controlled. The context could be set at logon time with a logon database trigger or could be executed by a client application or from the middle tier on the application server or in any other number of ways.
The setting of the context can be based on many things such as the time of day, the identity of the user who is logged on, where the user is logged in from, either the IP address (only if TCP is used) or the terminal. Here is an example of retrieving the IP Address:
Many other application-based values could also be used such as department numbers, employee numbers, company job grade, desk location... if it is stored in the database then potentially it could be used to set the context. As discussed, in a real application we would code logic to check that the correct role had been set for a user but for our simple example we will simply provide a package that allows the setting of the application role accountant, manager or clerk. Here it is:
As stated the context consists of name/value pairs. In this case we will define a variable name of app_role and set the relevant values in the package body:
OK, the context is now sorted, let's write the predicate function.
This is the core functionality of the row level security implementation. This function is what checks the context for the current user in line with the business rules defined above and implemented in the functions to set the security context. The function then, based on the rights of the user executing the select statement or update, insert or delete returns a predicate. This predicate is a dynamic piece of SQL that is appended to the where clause of the executing SQL by the Oracle optimizer at the time the SQL is parsed and executed.
It is possible to write and define separate policy functions for select, insert, update and delete for each table or view. This means that differing security rules can be defined for each type of access to the data. It is also possible to have multiple policies for each object and it is possible to define policy groups for objects - we will not go to this level of detail in this paper.
Policy functions always have to have the same signature as they are called by the optimizer for us. They can be thought of as a call back function. The function has to accept two varchar parameters for the schema owner and the object name and return a varchar string. The contents of the parameters can be used in anyway by the function. The prototype should be:
For our example we will use just one function for all four types of access for now; this is to keep the example simple. The function can now be written as follows:
Now create the package body for the policy:
Almost all of the pieces are now in place before we can test the functionality. Next the policy function needs to be registered with the database and specifically with the table being secured. In a real application this stage is just the same as the example with one call needed to a sys owned package called DBMS_RLS. This package is the API interface to the Oracle kernel Row Level Security functionality. The package prototype can be found in
For the example used in this paper, the same policy function will be used for all access methods to keep the example and testing reasonably simple. Here is the call to register the policy:
As mentioned above, the security context can be set automatically and one useful way to do this is by using a database logon trigger. An example piece of code to achieve this using one of the example application context procedures would be:
Testing the example
Finally it is time to test the policies that have been set up and see if they actually restrict access as planned. First of all we will log in as the owner of the table and see if we can see the records:
Strange, shouldn't we see four records as the user vpd is the owner of this table? Well actually the result is correct as currently no application role (security context) has been set for this user and the business security rules that were defined stated that when no application role is set then no data should be accessed. Next set the clerk role and examine what is visible:
Great! We are on track as we have correctly been only able to see the CASH transactions as defined for anyone working in as a clerk. What would happen if the context were to be set directly by using dbms_session, and not the associated package? Here is what happens:
This shows that Oracle will only allow the context to be set using the correctly associated function - for this example set_vpd_context.
Next let's set the application role to accountant and test that only ACCOUNTS transactions can be viewed.
Again success, only ACCOUNTS transactions can be viewed. This is correct behaviour for our applications security rules. Next set the application role to manager and check that all of the transactions can be viewed.
The last results show that all transactions can be seen, which is again the correct behaviour. Many more tests could be performed to show how row level security works but we do not have space to be exhaustive here. I want to show just two more tests with our simple application. The first demonstrates inserting a new record and the behaviour of one of the dbms_rls.add_policy parameters. Because we set the same policy function for all access methods the same visibility rules apply. If we set the context to accountant and then try and insert a CASH transaction it should fail. Let's try:
This is the correct behaviour as access for an accountant should be restricted to only inserting ACCOUNTS transactions. Let's try:
Success! The behaviour for inserts and updates can be modified when specifying the policy functions to the kernel. The parameter update_check is defaulted to FALSE but if it is set to TRUE as we did when adding the policy function, no data can be inserted or updated that should not be viewable. If we change this parameter back to its FALSE setting and try inserting the transaction above again it should not fail!
This parameter allows a slight circumvention of the policy rules defined! Beware of this parameter and set it cautiously or at least understand its behaviour.
One last demonstration in this test section:
As you can see the SYS user or any user such as system connected as sysdba bypasses all row level security policies. Beware of access by any user as SYS or as sysdba if protection of data from all users is important.
Concluding part one
We've seen some of the advantages of Oracle's row level security, what it can be used for, and looked at a simple example of how it works. Next week in Part Two we'll conclude this short article series by testing the policies that have been setup, demonstrate a few of the data dictionary views that allow for management and monitoring, cover some other issues and features, and then see if the data can be viewed by hackers or malicious users through the use of trace files.
About the author
Pete Finnigan is the author of the book "Oracle security step-by-step - A survival guide to Oracle security" published in January 2003 by the SANS Institute (see http://store.sans.org). Pete Finnigan is the founder and CTO of PeteFinnigan.com Limited (http://www.petefinnigan.com) a UK based company that specialises in auditing the security of client's Oracle databases world-wide and provides consultancy in all areas of Oracle security design, configuration and development.
View more articles by Pete Finnigan on SecurityFocus.
This article originally appeared on SecurityFocus.com -- reproduction in whole or in part is not allowed without expressed written consent.