2 Ocak 2020 Perşembe

Oracle Transparent Sensitive Data Protection

The aim of this post is creating a sensitive type, associate it to columns and combine it to "Oracle Sensitive Data Protection".

In our scenario, there is an application user called "APPUSER" and it has privileges to select and see the original data but we dont want any other users (even the owner of table) will see the sensitive data of the related tables.

Our sensitive column is TCKN and its datatype is "varchar2"

Create tables with sensitive column:
CREATE TABLE HR.employees (first_name varchar2(20), 
                           last_name varchar2(20), 
                           telephone_num varchar2(20), 
                           TCKN varchar2(11));

CREATE TABLE HR.accounts (TCKN varchar2(11), 
                          branch_id number(5), 
                          deposit number(10));

Insert data:
insert into HR.employees values ('Emre','Ozturk','222-333-444','11223344550');
insert into HR.employees values ('Ahmet','Yilmaz','444-555-666','22334455660');
insert into HR.employees values ('Okan','Demirel','666-777-888','33445566770');
insert into HR.employees values ('Alper','Akin','000-111-222','44556677880');
commit;

insert into HR.accounts values ('11223344550',120,2450);
insert into HR.accounts values ('22334455660',156,2450);
insert into HR.accounts values ('33445566770',134,2450);
insert into HR.accounts values ('44556677880',197,2450);
commit;

Create our sensitive type:
BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE(sensitive_type => 'TYPE_FOR_TCKN',
                                      user_comment   => 'Type for Identitiy Number columns using a varchar data type');
END;

We can check the created type:
select * from DBA_SENSITIVE_COLUMN_TYPES ;

Add our 1st sensitive column to associate the sensitive type:
BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(schema_name    => 'HR',
                                        table_name     => 'EMPLOYEES',
                                        column_name    => 'TCKN',
                                        sensitive_type => 'TYPE_FOR_TCKN',
                                        user_comment   => 'Associate EMPLOYEES table for sensitive column');
END;

Create the Transparent Sensitive Data Protection Policy:

For this policy, we will redact the TCKN column of first and last 3 digits. Only "appuser" will see the original data:
DECLARE
  redact_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
  policy_conditions      DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
BEGIN
  redact_feature_options('expression') := 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') !=''APPUSER''';
  redact_feature_options('function_type') := 'DBMS_REDACT.PARTIAL';
  redact_feature_options('function_parameters') := 'FFVVVVVVFFF,**VVVVVV***,*,1,1';
  policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'VARCHAR2';
  DBMS_TSDP_PROTECT.ADD_POLICY('PARTIAL_TCKN_POL',
                               DBMS_TSDP_PROTECT.REDACT,
                               redact_feature_options,
                               policy_conditions);
END;

Associate the Policy with a Sensitive Type:
BEGIN
     DBMS_TSDP_PROTECT.ASSOCIATE_POLICY(
     policy_name        => 'PARTIAL_TCKN_POL',
     sensitive_type     => 'TYPE_FOR_TCKN',
     associate          => TRUE);
END;

Enable Protection:
BEGIN
  DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
END;

Select table with appuser:
SQL> conn appuser/ora123;
Connected.

SQL> select * from hr.employees;

FIRST_NAME           LAST_NAME            TELEPHONE_NUM        TCKN
-------------------- -------------------- -------------------- -----------
Emre                 Ozturk               222-333-444          11223344550
Ahmet                Yilmaz               444-555-666          22334455660
Okan                 Demirel              666-777-888          33445566770
Alper                Akin                 000-111-222          44556677880

Select table with HR:
SQL> conn hr/hr
Connected.
 
SQL> select * from hr.employees;

FIRST_NAME           LAST_NAME            TELEPHONE_NUM        TCKN
-------------------- -------------------- -------------------- -----------
Emre                 Ozturk               222-333-444          ***23344***
Ahmet                Yilmaz               444-555-666          ***34455***
Okan                 Demirel              666-777-888          ***45566***
Alper                Akin                 000-111-222          ***56677***


Now associate this sensitive type to TCKN column of another table:
BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(schema_name    => 'HR',
                                        table_name     => 'ACCOUNTS',
                                        column_name    => 'TCKN',
                                        sensitive_type => 'TYPE_FOR_TCKN',
                                        user_comment   => 'Sensitive column addition of TCKN_type');
END;

Disable and Enable the protection:
BEGIN
  DBMS_TSDP_PROTECT.DISABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
  DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
END;

Now query the table with HR:
SQL> conn appuser/ora123
Connected.

SQL> select * from hr.accounts

TCKN         BRANCH_ID    DEPOSIT
----------- ---------- ----------
11223344550        120       2450
22334455660        156       3650
33445566770        134       1480
44556677880        197       8690

SQL> conn hr/hr
Connected.

SQL> select * from hr.accounts

TCKN         BRANCH_ID    DEPOSIT
----------- ---------- ----------
***23344***        120       2450
***34455***        156       3650
***45566***        134       1480
***56677***        197       8690

Hiç yorum yok:

Yorum Gönder