Oracle 12c - Data Redaction

Env:

Virtualbox + Oracle Linux 64bit 6.4 + Oracle database 12.1

 

Introduction:

 

A new security feature is intorudced in 12c, one of top-10 favourite new features of Tom Kyte. It's also known as data masking. Data redaction hides sensitive data from low-privileged users. For example, your credit card number, date of birth should be masked in a CRM application.

 

Data redaction takes places on the fly, it does not change the data in the database.Data redaction does not apply to users with "EXEMPT REDACTION POLICY". SYSDBA and DBA are not affected by data redaction.

 

Adding a new redaction policy:

 

begin
  dbms_redact.add_policy(object_schema => 'HR',
                         object_name => 'EMPLOYEES',
                         column_name => 'SALARY',
                         policy_name => 'SALARY_REDACTION',
                         function_type => dbms_redact.FULL,
                         expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR'' OR SYS_CONTEXT(''USERENV'',''SESSION_USER'') IS NULL'
                         );
end;

 

User needs execute privilege on dbms_redact. Even if the user is the owner of the object. Say, user hr wanted to add a redaction policy to table employees, hiding column salary. But he's not allowed to do so until he gets select privilege on dbms_redact. See the error as follows:

 

ORA-06550: line 6, column 43:
PLS-00201: identifier 'DBMS_REDACT' must be declared

 

SQL> connect  sys/123456@pdborcl as sysdba;
Connected.
SQL> show user;
USER is "SYS"
SQL> grant execute on dbms_redact to hr;

Grant succeeded.

Execute the add_policy again, you're all set.

 

Observing policies in the database:

select * from redaction_policies;

 

Examine the data redaction:

Login as nobody who has select privilege on hr.employees.

 

SQL>  select first_name, last_name, salary from hr.employees where rownum <= 3;

FIRST_NAME      LAST_NAME      SALARY
-------------------- ------------------------- ----------
Steven       King       99999
Neena       Kochhar       99999
Lex       De Haan       99999