The content of the article
There are different ways to show the user only the data they need. Row level security is one of the most versatile, simple and reliable. After reading this article, you will understand that it is not difficult, and you will learn how to organize the differentiation of access to records by means of the database itself without much damage to performance.
The row level security mechanism allows you to implement the differentiation of access to data by means of a database transparently for applications working with it. Even if an attacker gained direct access to the database, for example, under the account of the owner of the schema with the data, RLS may prevent him from seeing protected information. RLS policies allow you to remove entire rows from a selection or hide column values for rows that the user does not have access to. This is in contrast to the usual management of rights in the database, which can be issued only to the entire object.
How it works? When executing any query to the database, the scheduler checks if there are access policies for these tables. If so, it evaluates an additional predicate based on each policy, which it adds to the query. The predicates can be of any complexity. For example, like this:
and (512 in (select id_user from v_admin_users where id_department = 255) or 512 in (select id_user from v_bypass_rls))
The plus is that predicates work for any queries, including those made through administration tools (SQL Developer, Toad, PgAdmin, etc.) and even when exporting dumps. This is a single access control mechanism for all applications at the level of the database engine. Why is it not used so often in practice? Here are some reasons.
- There are many fewer people who know how to work with databases at a sufficient level than ordinary programmers. It is often easier and cheaper to implement access control mechanisms in the application layer.
- Transparency. If RLS is off, it may not be immediately apparent. The applications will continue to work fine, but they will be outputting more data than needed. In itself, this is not scary, but with bad processes and in combination with the previous point, it is fraught with problems.
- Additional consumption of resources for the execution of the request. Usually this factor does not play a decisive role. If RLS is really needed, it is turned on and the slightly slower work is taken for granted. But if used ineptly, you can spend many times more on RLS than on useful work.
In general, row level security is a tool for centralized data access control. It is implemented in many modern DBMS – for example, Oracle, PostgreSQL and MS SQL Server. In this article, I'll show you how it works in the first two.
Let's start with the implementation of RLS in Oracle and dive straight into practice.
We will try to implement a simple policy on a standard HR scheme. A regular user can only see their own data. The head of the department can see all the data for the department. For this we need:
- determine which employee the session corresponds to;
- create a function that calculates a predicate for it;
- configure a policy that associates a function with a table.
We will assume that the application connects to the database under the HR user account and that this program has an authentication tool that allows us to understand which employee is working with it. We will store the data about which employee is connected in a context – a special key-value storage of attributes that control applications. You can use the standard
CLIENT_IDENTIFIERbut we will create our own. To do this, you will have to create a package that will work with it.
To begin with, we will create a context on behalf of the privileged user and immediately indicate which package can change it:
CREATE CONTEXT SECURITY_CONTEXT USING HR.P_SEC_CONTEXT;
Let's create a package for working with the context:
create or replace package P_SEC_CONTEXT isprocedure set_employee(p_employee_id in number);end P_SEC_CONTEXT;/create or replace package body P_SEC_CONTEXT is procedure set_employee(p_employee_id in number) is begin dbms_session.set_context(namespace => 'SECURITY_CONTEXT', attribute => 'EMPLOYEE_ID', value => p_employee_id); end;end P_SEC_CONTEXT;/
p_sec_context.set_employee will be used by the application to set the employee code in the table
EMPLOYEESthat runs in this DB session.
When using connection pooling, you must set the context every time a new connection is received. The default value can be set in the login trigger, but it is not required. Trigger example:
create trigger tr_hr_logon after logon on HR.SCHEMAbegin p_sec_context.set_employee(null);end;/
p_sec_context.set_employee need to indicate
Continuation is available only to members
Materials from the latest issues become available separately only two months after publication. To continue reading, you must become a member of the "Xakep.ru" community.
Join the Xakep.ru community!
Membership in the community within the specified period will open you access to ALL materials of the "Hacker", will allow you to download issues in PDF, disable ads on the site and increase your personal cumulative discount!
I am already a member of "Xakep.ru"