Oracle 12c Data Redaction. Hide information from unprivileged users
The task of sharing access to data 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 this responsibility can be assigned to it. But almost always there is a need for direct access to data, for example, for analysts or personnel of the system supplier.
The article considers the possibility of partial concealment of information, access to which is strictly limited. Here we recall about 152-FZ .
Starting with version 8, Oracle Database has a Virtual Private Database (VPD). VPD allows, depending on different conditions, to hide part of the table or view records from users. The disadvantage stems from the merits - hiding is possible only complete records. This is done by adding additional conditions to the where part of the sql query.
Oracle 12c adds the ability to change the field values returned by sql query (in whole or in part), depending on the conditions. This feature is called Oracle Data Redaction and consists in applying special policies .
All Data Redaction management is concentrated in the DBMS_REDACT package . The important thing is that to apply policy to an object, you do not need to have any access to the object itself. EXEMPT REDACTION POLICY privilegesare not subject to 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, the owners of which see the full information. The rest should see the edited version.
We create a policy called redact_client_info and add a rule according to which the day in the date of birth is replaced by the 1st number:
We add the condition to show only the last 5 digits of the phone to the policy:
Add the condition to hide the email domain in the policy:
Finally, mask the credit card number:
We execute select from the user who does not have the R_VIP role:
And from a user with this role:
As a result, all records are visible in the requests, but the field values for a user who does not have the R_VIP role are changed.
The advantage of using Data Redaction is to preserve the semantics of data, but at the same time, the unsuitability of specific values for unauthorized use.
In the course of further discussion and study of the operation of this mechanism, it turned out that the data is masked only at the output. That is, the where part uses open data. And as a result, you can get all open data by substituting your function in where:
The article considers the possibility of partial concealment of information, access to which is strictly limited. Here we recall about 152-FZ .
Starting with version 8, Oracle Database has a Virtual Private Database (VPD). VPD allows, depending on different conditions, to hide part of the table or view records from users. The disadvantage stems from the merits - hiding is possible only complete records. This is done by adding additional conditions to the where part of the sql query.
Oracle 12c adds the ability to change the field values returned by sql query (in whole or in part), depending on the conditions. This feature is called Oracle Data Redaction and consists in applying special policies .
All Data Redaction management is concentrated in the DBMS_REDACT package . The important thing is that to apply policy to an object, you do not need to have any access to the object itself. EXEMPT REDACTION POLICY privilegesare not subject to 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, the owners of which see the full information. The rest should see the edited version.
table and test record
CREATE TABLE CLIENT_INFO
(
ID NUMBER,
F_NAME VARCHAR2(64), /* фамилия*/
NAME VARCHAR2(64), /* имя */
S_NAME VARCHAR2(64), /* отчество */
BIRTHDAY DATE, /* дата рождения */
PHONE VARCHAR2(32), /* телефон */
EMAIL VARCHAR2(64), /* email */
CCARD VARCHAR2(32), /* номер кредитной карты */
CONSTRAINT "CLIENT_INFO_PK" PRIMARY KEY ("ID")
);
insert into CLIENT_INFO values(1, 'Иванов', 'Иван', 'Иванович', to_date('15-05-1986', 'DD-MM-YYYY'), '79763334589', '[email protected]', '5767881897856776');
We create a policy called redact_client_info and add a rule according to which the day in the date of birth is replaced by the 1st number:
policy
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'TEST',
object_name => 'CLIENT_INFO',
column_name => 'BIRTHDAY',
policy_name => 'redact_client_info',
function_type => DBMS_REDACT.PARTIAL, /* Частичное маскирование */
function_parameters => 'Md01Y', /* Маска изменений */
expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''R_VIP'') = ''FALSE''' /* Условие - замена при отсутствии роли R_VIP */
);
END;
/
We add the condition to show only the last 5 digits of the phone to the policy:
policy
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'TEST',
object_name => 'CLIENT_INFO',
column_name => 'PHONE',
policy_name => 'redact_client_info',
function_type => DBMS_REDACT.REGEXP, /* Маскирование с помощью регулярного выражения */
regexp_pattern => '\d+(\d{5})$',
regexp_replace_string => '******\1',
regexp_position => DBMS_REDACT.RE_BEGINNING,
regexp_occurrence => DBMS_REDACT.RE_ALL,
expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''R_VIP'') = ''FALSE''',
action => DBMS_REDACT.ADD_COLUMN
);
END;
/
Add the condition to hide the email domain in the policy:
policy
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'TEST',
object_name => 'CLIENT_INFO',
column_name => 'EMAIL',
policy_name => 'redact_client_info',
function_type => DBMS_REDACT.REGEXP,
regexp_pattern => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS, /* Используем готовое */
regexp_replace_string => DBMS_REDACT.RE_REDACT_EMAIL_DOMAIN, /* Используем готовое */
expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''R_VIP'') = ''FALSE''',
action => DBMS_REDACT.ADD_COLUMN
);
END;
/
Finally, mask the credit card number:
policy
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'TEST',
object_name => 'CLIENT_INFO',
column_name => 'CCARD',
policy_name => 'redact_client_info',
function_type => DBMS_REDACT.REGEXP,
regexp_pattern => DBMS_REDACT.RE_PATTERN_CC_L6_T4,
regexp_replace_string => DBMS_REDACT.RE_REDACT_CC_MIDDLE_DIGITS,
expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''R_VIP'') = ''FALSE''',
action => DBMS_REDACT.ADD_COLUMN
);
END;
/
We execute select from the 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 [email protected] ******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 [email protected] 79763334589 5767881897856776
As a result, all records are visible in the requests, but the field values for a user who does not have the R_VIP role are changed.
The advantage of using Data Redaction is to preserve 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 operation of this mechanism, it turned out that the data is masked only at the output. That is, the where part uses open data. And as a result, you can get all 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
---------- ---------------
Иван [email protected]
SQL> select * from log;
S
----------------
[email protected]
[email protected]
