1z0-060 New Features of Oracle Database 12c–Data Redaction

Without Redaction:

SQL> conn donghua/password       
SQL> create user hr_viewer identified by password;
SQL> grant create session to hr_viewer;
SQL> grant select on hr.employees to hr_viewer;
   
SQL> conn hr_viewer/password
Connected.
SQL>
SQL> select employee_id,salary,phone_number from hr.employees where employee_id in (107,108);

EMPLOYEE_ID     SALARY PHONE_NUMBER
----------- ---------- ------------------------------
        107       4200 590.423.5567
        108      12008 515.124.4569
 

Create policy on column hr.employees.phone_number   

SQL> conn donghua/password   
SQL> BEGIN
  2   DBMS_REDACT.ADD_POLICY(
  3     object_schema          => 'hr',
  4     object_name            => 'employees',
  5     column_name            => 'phone_number',
  6     policy_name            => 'redact_hr_employees',
  7     function_type          => DBMS_REDACT.REGEXP,
  8     function_parameters    => NULL,
  9     expression             => '1=1',
10     regexp_pattern         => '(\d\d\d).(\d\d)(\w+)',
11     regexp_replace_string  => 'XXX.XX\3',
12     regexp_position        => DBMS_REDACT.RE_BEGINNING,
13     regexp_occurrence      => DBMS_REDACT.RE_FIRST,
14     regexp_match_parameter => DBMS_REDACT.RE_CASE_INSENSITIVE,
15     policy_description     => 'Regular expressions to redact the first 5 digits of U.S. telephone numbers',
16     column_description     => 'phone_number contains employee actual phone number');
17  END;
18  /
 
SQL> conn hr_viewer/password

SQL> col phone_number for a20
SQL> select employee_id,salary,phone_number from hr.employees where employee_id in (107,108);

EMPLOYEE_ID     SALARY PHONE_NUMBER
----------- ---------- --------------------
        107       4200 XXX.XX3.5567
        108      12008 XXX.XX4.4569

Add additional column hr.employees.salary to the redaction column list

       
SQL> conn donghua/password   
SQL> BEGIN
  2   DBMS_REDACT.ALTER_POLICY(
  3     object_schema          => 'hr',
  4     object_name            => 'employees',
  5     policy_name            => 'redact_hr_employees',
  6     action                 => DBMS_REDACT.ADD_COLUMN,
  7     column_name            => 'salary',
  8     function_type          => DBMS_REDACT.RANDOM,
  9     policy_description     => 'Regular expressions to redact the first 5 digits of U.S. telephone numbers, and salary column',
10     column_description     => 'Salary information is sensitive');
11  END;
12  /

SQL> conn hr_viewer/password
Connected.
SQL>  col phone_number for a20
SQL> select employee_id,salary,phone_number from hr.employees where employee_id in (107,108);

EMPLOYEE_ID     SALARY PHONE_NUMBER
----------- ---------- --------------------
        107       1246 XXX.XX3.5567
        108       1336 XXX.XX4.4569

SQL> select employee_id,salary,phone_number from hr.employees where employee_id in (107,108);

EMPLOYEE_ID     SALARY PHONE_NUMBER
----------- ---------- --------------------
        107       3090 XXX.XX3.5567
        108       2514 XXX.XX4.4569

Schema owner without “exempt redaction policy” only see the redacted data

SQL> conn hr/password
Connected.
SQL> select employee_id,salary,phone_number from hr.employees where employee_id in (107,108);

EMPLOYEE_ID     SALARY PHONE_NUMBER
----------- ---------- --------------------
        107       1380 XXX.XX3.5567
        108      10640 XXX.XX4.4569

SQL> create table employees_bak as select * from employees;
create table employees_bak as select * from employees
                                     *
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.

Query the dictionary

SQL> conn donghua/password   

SQL> select object_owner||','||object_name||','||policy_name||','||expression||','||enable||','||policy_description
  2  from redaction_policies;

OBJECT_OWNER||','||OBJECT_NAME||','||POLICY_NAME||','||EXPRESSION||','||ENABLE||
--------------------------------------------------------------------------------
HR,EMPLOYEES,redact_hr_employees,1=1,YES    ,Regular expressions to redact the f
irst 5 digits of U.S. telephone numbers
       

SQL> select object_owner||','||object_name||','||column_name||','||function_type||','||function_parameters||','||
  2  regexp_pattern||','||regexp_replace_string||','||regexp_position||','||regexp_occurrence||','
  3  ||regexp_match_parameter||','||column_description
  4  from redaction_columns;

OBJECT_OWNER||','||OBJECT_NAME||','||COLUMN_NAME||','||FUNCTION_TYPE||','||FUNCT
--------------------------------------------------------------------------------
HR,EMPLOYEES,PHONE_NUMBER,REGEXP REDACTION,,(\d\d\d).(\d\d)(\w+),XXX.XX\3,1,1,i,
phone_number contains employee actual phone number
HR,EMPLOYEES,SALARY,RANDOM REDACTION,,,,0,0,,

Working with “exempt redaction policy” privilege

SQL> grant exempt redaction policy to hr_viewer;

Grant succeeded.

SQL> conn hr_viewer/password
Connected.
SQL> col phone_number for a20
SQL> select employee_id,salary,phone_number from hr.employees where employee_id in (107,108);

EMPLOYEE_ID     SALARY PHONE_NUMBER
----------- ---------- --------------------
        107       4200 590.423.5567
        108      12008 515.124.4569

Disable and drop the policy

SQL> conn donghua/password       
           
SQL> BEGIN
  2   DBMS_REDACT.DISABLE_POLICY(
  3     object_schema          => 'hr',
  4     object_name            => 'employees',
  5     policy_name            => 'redact_hr_employees');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2   DBMS_REDACT.DROP_POLICY(
  3     object_schema          => 'hr',
  4     object_name            => 'employees',
  5     policy_name            => 'redact_hr_employees');
  6  END;
  7  /

PL/SQL procedure successfully completed.