Oracle 12c Data Redaction. Hiding information from unprivileged users

The task of sharing data access in information systems always arises. One way or another, it needs to be addressed. If access to the database is possible only from the application server, then you can assign this responsibility to it. But almost always there is a need for direct access to data, for example, for analysts or system supplier personnel.
The article discusses the possibility of partial concealment of information, access to which is strictly limited. Immediately remember about the 152-FZ .

Starting with version 8, there is a Virtual Private Database (VPD) in Oracle Database. VPD allows, depending on different conditions, to hide part of the table or view records from users. The disadvantage stems from the merits - concealment is possible only for full records. This is done by adding additional conditions in the where part of the sql query.

In Oracle 12c, the ability to change the values ​​of fields returned by the sql query (in whole or in part) is added, depending on the conditions. This feature is called Oracle Data Redaction and consists of applying a special policy .

All management of Data Redaction is concentrated in the DBMS_REDACT package. It is important that to apply a policy to an object, you do not need to have any access to the object itself. EXEMPT REDACTION POLICY privileges are not covered by this mechanism.
')
Consider the use of Data Redaction in the following example:

There is a table CLIENT_INFO containing personal data - date of birth, phone, email and credit card number. In the database there is a role R_VIP, whose owners see the full information. The rest should see the edited version.

Create a policy called redact_client_info and add a rule according to which the date of birth is replaced by the first number:

Add a condition to the policy to show only the last 5 digits of the phone:

Add a condition to hide the email domain in the policy:

Finally, we mask the credit card number:

Execute select from a user who does not have the R_VIP role:

 SQL> select f_name, name, birthday, email, phone, ccard from test.client_info; F_NAME NAME BIRTHDAY EMAIL PHONE CCARD ---------- ---------- --------------- --------------- --------------- ----------------   01-MAY-86 ivan@xxxxx.com ******34589 576788XXXXXX6776 

And from a user with this role:

 SQL> select f_name, name, birthday, email, phone, ccard from test.client_info; F_NAME NAME BIRTHDAY EMAIL PHONE CCARD ---------- ---------- --------------- --------------- --------------- ----------------   15-MAY-86 ivan@dom2.ru 79763334589 5767881897856776 

As a result, all the records are visible in the queries, but the values ​​of the fields for the user not having the R_VIP role are changed.

Plus the use of Data Redaction in preserving the semantics of data, but at the same time the unsuitability of specific values ​​for unauthorized use.

Important addition

In the course of further discussion and study of the work of this mechanism, it became clear that the data is masked only during the withdrawal. That is, in the where part, open data is used. And as a result, you can get all the open data by substituting your function in where:

 create table log (s varchar2(128)); create or replace function f(ds varchar2) return varchar2 as PRAGMA AUTONOMOUS_TRANSACTION; begin insert into log values(ds); commit; return ds; end; SQL> select name, email from client_info c where f(email) like '%dom%'; NAME EMAIL ---------- ---------------  ivan@xxxxx.com SQL> select * from log; S ---------------- ivan@dom2.ru ivan@gmail.com