Oracle. Row level security

Introduction to the problem

Currently, in any organization there is a delineation of access to information based on certain knowledge of the user. Such knowledge can be the role of the user in the organization, his position or the structural unit in which the user works. Many people know that the problem of restricting access can be solved using the simplest mechanisms based on usernames, tables, views and triggers.

Consider an example:
Provide the manager with information about the organization’s clients. At the same time, the manager can only see the clients of his structural unit, but not the entire company:

create table clients ( clientid integer, clientname varchar2(30), clientphone varchar2(7), clientoffice integer ); 

The solution "in the forehead" is to create a separate presentation for each department of the company. For example:
')

 create or replace view clients_10 as select clientid, clientname, clientphone from clients where clientoffice = 10; 

So we will have to keep the number of views equal
<number of filtered tables> * <number of structure divisions> ,
as well as each user "send" to the desired data. Stupid and tedious occupation.
Let's try to improve the previous example:

 create or replace view v_clients as select clientid, clientname, clietnphone from office where clientoffice = (select useroffice from users where username = user); 

The solution is undoubtedly more interesting, but what to do when one user needs to “see” the clients of several departments? Also, a situation often arises when one user is only allowed to request data from one table, he can edit the other, but not delete, and the third view and delete. To fence a myriad of representations, triggers and nastoechny tables becomes very uncomfortable ...

RLS technology

The RLS ( row-level security or row level security ) technology provides the ability to create security policies that restrict users' access to information in the database. As mentioned above, security policies allow you to either “close” information in whole or in part, or to allow only certain operations on it. The technology was first introduced in Oracle 8i, but in later versions, its capabilities were greatly enhanced.
When a database object is associated with a security policy, access control is performed through logic included in a special PL / SQL function. Agree that it is much easier to support several software functions than dozens of views, separated by the N-th number of schemes.
When a user directly or indirectly accesses the database object (table, view), the server dynamically modifies the SQL statement, adding to it the WHERE predicate, which is returned by the security function.

Safety feature

So, now it's time to consider an example of the simplest security function. As already noted, the function task is the formation of a predicate that will be automatically added to the user's query.

 create or replace function policy_func (p_schema varchar2, p_object varchar2) return varchar2 is begin if (user = 'MGR_10_20_30 ') then return 'clientoffice in (10, 20, 30)'; elseif return 'clientoffice = (select useroffice from users where username = user)'; end if; end; 

The above function will add the WHERE clientoffice = office_no predicate to the user's SQL query, where office_no is the number of the unit in which the user works. For the user with the login MGR_10_20_30, the WHERE clientoffice in predicate will be added (10, 20, 30). Thus, this user will have access to customer information of the three divisions.

DBMS_RLS Package Procedures

Add_policy

Well, by now we have done all the necessary preparatory work, and all we have to do is add the security policy to our database. The security policy is registered with the add_policy DBMS_RLS package procedure:

 DBMS_RLS.ADD_POLICY ( object_schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2, function_schema IN VARCHAR2 NULL, policy_function IN VARCHAR2, statement_types IN VARCHAR2 NULL, update_check IN BOOLEAN FALSE, enable IN BOOLEAN TRUE, static_policy IN BOOLEAN FALSE, policy_type IN BINARY_INTEGER NULL, long_predicate IN BOOLEAN FALSE, sec_relevant_cols IN VARCHAR2 NULL, sec_relevant_cols_opt IN BINARY_INTEGER NULL ); 

Add a security policy for the clients table:

 Begin DBMS_RLS.ADD_POLICY ( object_schema => 'myuser', object_name => 'clients', policy_name => 'clients_policy', function_schema => 'myuser', policy_function => 'policy_func', statement_types => 'select, insert, update, delete', update_check => true ); End; 

Now briefly about the procedure parameters we used:
object_schema and object_name are the table, view or synonym for which the policy is added, and the database schema in which the object is located.
policy_name is the name of the security policy to be added. The name must be unique for each table or view separately.
function_schema and policy_function is the name of the security function that generates the predicate, and the database schema in which the function is located.
statement_types - statements to which the security policy applies.
update_check - the option prevents the INSERT or UPDATE operation if the INSERT or UPDATE violates the search conditions defined in the predicate.
Now we can see the security policy in action:

 select username, useroffice from users; 

For user MGR_10:

 select user from dual; 

 select * from clients; 

For user MGR_20:

 select user from dual; 

 select * from clients; 

For user MGR_10_20_30:

 select user from dual; 

 select * from clients; 

Information about views in the database is stored in views user_policies, all_policies, dba_policies. Perform a query from a user with DBA rights:

 select * from dba_policies; 

Drop_policy

Accordingly, since there is a procedure for creating a security policy, there is also a procedure for deleting it:

 DBMS_RLS.DROP_POLICY ( object_schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2 ); 

Let's try to delete our security policy:

 Begin DBMS_RLS.DROP_POLICY ( object_schema => 'myuser', object_name => 'clients', policy_name => 'clients_policy' ); End; 

Now let's check the presence of policies in the database by a user with DBA rights:

 select * from dba_policies; 

Enable_policy

In addition to the operations of adding / deleting security policies, it is possible to temporarily suspend the existing policies. To do this, use the procedure enable_policy, which puts the policy in an inactive state (enable => false) or resumes the action of the policy (enable => true):

 DBMS_RLS.ENABLE_POLICY ( object_schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2, enable IN BOOLEAN TRUE) ); 

 select * from dba_policies; 

 Begin DBMS_RLS.ENABLE_POLICY ( object_schema => 'myuser', object_name => 'clients', policy_name => 'clients_policy', enable => false ); End; 

 select * from dba_policies; 

 Begin DBMS_RLS.ENABLE_POLICY ( object_schema => 'myuser', object_name => 'clients', policy_name => 'clients_policy' ); End; 

 select * from dba_policies; 

The most attentive, I think, noticed the enable parameter of the add_policy procedure of the dbms_rls package. This parameter indicates whether the policy will be active or inactive immediately after creation. The default is true.

Refresh_policy

The procedure updates the RLS policy predicate. If a security policy is defined with a type other than DYNAMIC, the policy predicate may not be updated for some time, because it is cached in memory. Therefore, if there is a need to update the policy immediately, then you need to execute the REFRESH_POLICY procedure, which will re-execute the policy function and update the predicate in the cache.

 DBMS_RLS.REFRESH_POLICY ( object_schema IN VARCHAR2 NULL, object_name IN VARCHAR2 NULL, policy_name IN VARCHAR2 NULL ); 

Conclusion

This article discusses the simplest example, as they say, "on the fingers." In the next article, we will look at security using contexts and policy groups.

Be secure! Commit!