Introduction to Row Level Security. Examining access rights differentiation systems implemented in Oracle и PostgreSQL

Row level security allows to implement the differentiation of access rights to data using the database tools. Furthermore, this is done transparently for applications interacting with the database. Even if somebody gains direct access to the database (e.g. using the account of the data schema’s owner), RLS can prevent the attacker from viewing protected information. Unlike traditional rights management systems that grant access to the entire database objects (e.g. tables), RLS policies allow to remove entire rows from a selection or hide column values for rows the user does not have access to.

How it works? When a query to the database is executed, the parser checks whether any access policies apply to these tables. If yes, it composes an additional predicate on the basis of each policy and adds it to the query. Predicates can be of any complexity, for instance:

The main benefit is that predicates are applied to all queries, including those made by administration tools (SQL Developer, Toad, PgAdmin, etc.), and even to queries made by the backup software. This is a single access control mechanism for all applications operating at the DBMS engine level. So, the question is: why isn’t RLS used as commonly as it should? There are several reasons for this, including:

• The number of specialists skilled in database management is much less in comparison with programmers skilled in conventional languages. Therefore, in many cases, it’s easier and cheaper to implement access control mechanisms in the application layer;
• Transparency. If RLS is disabled, this may not be discovered immediately. The applications will continue running as usual, but they will provide more data than needed. This is not a big issue, but with bad processes and keeping in mind the previous paragraph, it may be risky; and 
• The execution of queries consumes extra resources. Normally, this isn’t a big deal. If RLS is really needed, it’s enabled, and a small slowdown of the system is accepted as an inevitable fact of life. But if RLS is misconfigured, it may consume times more resources than the effective work of the system.

Overall, row level security is a centralized tool for access rights management. It’s implemented in many modern database management systems, including Oracle, PostgreSQL, and MS SQL Server. In this article, I will show how it works in the first two products.

Oracle

I will use a practical approach to demonstrate how RLS is implemented in Oracle.

An example for Oracle

Let’s implement a simple policy based on a standard HR schema. Ordinary users may only see data available to them. The department head can see all the department-related data. I have to:

• identify the employee on whose behalf the session is running;
• create a function that composes a predicate for this employee; and 
• set up a policy that binds the function to the table.

I assume that the app connects to the database on behalf of an HR user account, and this app has an authentication mechanism that allows to understand which employee is working with it. The information on the connected employee will be saved in a context – a special key-value store of attributes that control applications. A standard CLIENT_IDENTIFIER could be used for this purpose, but I am going to create my own identifier. This, in turn, requires to create a package that will work with it.

First, I create a context on behalf of a privileged user and specify which package can change it:

Then I create a package to work with this context:

Now the app will use p_sec_context.set_employee to specify the code of the employee working in this DB session (i.e. the employee’s identifier in the EMPLOYEES table).

If you use CLIENT_IDENTIFIER instead of p_sec_context.set_employee, you have to specify dbms_session.set_identifier.

Next, I need a function to compose the access predicate. The text returned by this function will be added to the general condition block (WHERE) of any query that uses the table with the AND operator. This can be illustrated by the following transformation: WHERE (all my conditions are combined into one block by parentheses) AND access_predicate. The function must take two string parameters: the schema name and the table name. It must not write anything to the database (the purity level is WNDS: write no database state), but it’s not necessary to declare this using pragma RESTRICT_REFERENCES.

The maximum length of the created condition must not exceed 32 KB. Aside from this, you are free to use whatever you want: subqueries, table joins, and all the power of SQL are at your service.

Finally, I create a policy for situations when the table is queried. It has to be done by a privileged user:

After that, select * from EMPLOYEES returns zero records. I set the context and gain full access:

The added predicate is clearly visible in the execution plan.

Performance

There are two reasons why RLS slows down the system.

1. Time required to compose an additional predicate. To reduce it, you have to use caching to the maximum. The caching type is determined by the policy type. In the above example (SHARED_CONTEXT_SENSITIVE), the predicate isn’t recomposed if the session context hasn’t changed, and it won’t be composed for other objects that use the same function; and 
2. Time required to execute the additional predicate. It can be optimized similar to regular queries.

Problems and possible solutions

1. In the above example, I entrust the calling code to set the context. Therefore, nothing prevents the attacker from calling p_sec_context.set_employee with different identifiers and checking the results. However, it’s possible to complicate the malefactors’ lives by adding more data to the user code (e.g. a hash on behalf of the user and the current time) and validate the user code based on these data;
2. Some data can be accessed bypassing RLS. Oracle collects the tables’ statistics: numbers of rows, largest and smallest column values, numbers of unique values, distribution histograms, etc. The policies don’t filter the statistics in any way; accordingly, such data are available to all users. Some additional information can also be obtained by making changes in tables that have foreign keys. Finally, unique constraint validation ignores the policies, too;
3. In the above example, the code responsible for security is owned by the data schema. This enables the HR user to view and change this object. To complicate the attacker’s life, you can place objects in a separate schema. If necessary, the code can be obfuscated as well – but this won’t help much against a determined hacker; and 
4. Predicates are composed dynamically in the course of their execution; therefore, it’s difficult to debug them. Just accept this.

PostgreSQL

PostgreSQL supports RLS starting from version 9.5. Unlike Oracle, PostgreSQL policies are compilable. This simplifies their development and eliminates cache-related issues, but slightly limits their capabilities.

An example for PostgreSQL

I am going to implement the same policy as in Oracle. Ordinary users may only see data available to them. The department head can see all the department-related data. If something goes wrong, the system must neither report an error nor provide access to the data. I don’t have an HR schema; so, first of all, I have to create a minimum set of tables with data.

Then I enable RLS on the table.

Enabling RLS for the table owner. By default, the access policies are bypassed by this user.

Adding the access predicate:

Using contains the exact text to be added to the query when the policy is executed, and its syntax is checked immediately. The current_setting function returns the value of the session parameter that identifies the user. The second parameter suppresses errors if the context is not initialized. The app can set the context by executing the following query:

The setup is finished, and I review the execution plan.

Alternatively, it’s possible to create a database user for each employee user and configure the policy regulating the use of the database accounts. To do so, I have to drop the existing policy first:

If operations are regulated by several policies, they are combined together using the OR operator; therefore, if I hadn’t dropped test_security, queries to employees would look as follows: where ... ( ((employee_id = current_setting('SECURITY_CONTEXT.EMPLOYEE_ID', true)::integer or department_id in (select department_id from hr.departments where manager_id = current_setting('SECURITY_CONTEXT.EMPLOYEE_ID', true)::integer))) OR (email = current_user)).

Creating a new policy:

This policy allows users to see only their own data. To access these data, users must connect to the database using accounts matching their email addresses (e.g. SKING), not their accounts in the HR table. No additional actions with the app are required. But for each person interacting with the system, a user must be created in the database with all respective rights.

Potential issues

Similar to Oracle, the attacker who has access to the statistics can partially bypass RLS. In addition, functions declared as leakproof can be executed prior to the application of the policies. If such a function, contrary to its specifications, exposes values of its arguments, it can be used to obtain information.

Conclusions

Row level security is not a panacea and has some drawbacks. Its use is justified if:

1. More than one app works with the data;
2. Access control has to be implemented without significantly reworking the program; and 
3. Data access control has to implemented in a separate layer.

It’s preferable to use other methods if:

1. Only one application works with the data; and 
2. The database is already heavily loaded and limits the system performance.

Summarizing the above: if RLS is suitable for your case, don’t hesitate to use it. Hopefully, this article was helpful in mastering the RLS technology. Good luck.