What is RLS?
RLS (Row Level Security) - restriction at the level of records in the database tables. In contrast to the delimitation of the rights base on the usage of roles that actually delimit access to the specific tables of base as a whole, RLS allows configuring an access to the specific records.

Consider an elemental example in which we solve one of the most common tasks of access rights delimitation. Assume that the leaders have set the task for the programmer to delimit visibility of the records of catalog “Companies” over the users. The main criterion of the rights delimitation - “Purchasing manager” can see only the providers and “Sales manager” - only clients. If the contractor is both a client and provider, then any of these people can work with this item.

What do we have?

Three users are registered in our base, each of them has the appropriate rights configured using the roles:

In line with the task, we need to delimit an access for the users to the catalog “Companies”. Two attributes “Customer” and “Vendor” are added to the catalog with the type of “Boolean”. The attributes will indicate the type of contractor relationships to our organization.

Thereafter, the users with role “SalesManager” will see those records for which the flag “Customer” is set, and the role “PurchasingManager” will open an access to the items with flag “Vendor”. All the items will be seen by the users who have the role “FullAccess” or both of above roles.

An example is very simple, but if you face access delimitation in the records for the first time, there may be difficulties with implementation of the RLS itself, as the beginners simply do not know how to make the adjustment.

How to use RLS in the roles?

Consider the mechanism of RLS on the created role “SalesManager”. The access is customized in the role for all the actions with catalog “Companies”, except for the actions of any type of deletion (interactive and programmed). The rights customization for the object can be seen here:

Note that for the rights like “Read”, “Insert” and “Update” the rows are shown in gray and marked with a special icons on the right. These designations indicate that for these rights the mechanism of rights delimitation at the record level is used.

The principle of RLS for the rights is to add to the rights (read, insert, update or delete) the special conditions that have to tell the system during the construction of queries to the DBMS what records must be selected. In fact, the text from the access restriction description will be added by the platform automatically for any queries to the base in section “WHERE”. To better understand the above, we turn to our example.

Since we will focus on the attributes of catalog “Companies” - “Customer” and “Vendor”, then in the corresponding roles of delete, insert and update set the access restriction: “Companies WHERE Companies.Customer” and “Companies WHERE Companies.Vendor”. As a result, we will get the following restrictions:

As we can see, the text of restriction condition is very simple. However, in practice, the texts of access restriction description at the record level may contain the complex conditions in the query language, the syntax also supports pass of parameters from the session parameters and functional options.

As you can see, the syntax is quite flexible and is not limited only by the access to the database tables.

And now see the result!

Let’s see how the entered changes affected the operation in the enterprise mode. Additionally examine the change of DBMS queries with the use of RLS.

First of all, let us enter into the information base like administrator and create several contractors with different filling of the attributes:

Next, enter in turn under the user “Sales manager” and “Purchasing manager”. We see that the access to the records in the table contractors for two users is delimited as we wanted:

It is very interesting that the records with codes “00002” and “00003” are available for both users. This is due to the fact that the contractor is both the user and vendor. An interesting feature here will be that if we disable for this records the flag “Customer” under the user “Sales manager”, then during the contractor writing an error will occur:

That is because the writable item does no longer satisfy our condition in the field “Access restriction” of the role “SalesManager” and, therefore, cannot be written.

Now let us consider a database query in SQL language. If we open the form of contractor list under the full rights, then RLS will not be used and we will receive an empty query. if we will enter in the enterprise mode, for example, under the user “SalesManager”, than the query will be already changed according to the restriction conditions at the record level. Below a screenshot is displayed with comparison of resulting queries.

As we can see, the mechanism of RLS inserts additional conditions in the SQL-queries to limit the selection by condition described in the field “Access restriction” for the selected right in the role.

The fact that the system inserts in the queries the additional selection conditions allows us to conclude that the complex conditions of access restriction at the records level increase the load on the DBMS server, so you need to be careful when using them.

Have the successful experiments and thank you for your interest in the material!