12c DATA Redaction with DBMS_REDACT to hide sensitive data.

We know that oracle has some high security options like database vault and Virtual private database.

While they are very comprehensive and make the database highly secure, It will still take a DBA/Architect to understand the complete Architecture of the application in order to implement those options. This is something that cannot be implemented quickly or without much thought to the application as a whole. This could be one reason why i am a bit behind on the hands-on of database vault.

On the other hand, Data Redaction is like a security option where the data is scrambled only during the display part of the data.  To understand it better, think of redaction similar to to_char or to_data function in the select part of the sql query. The data is only scrambled  during the select.

Some examples are 

 social_security       543-46-2457        xxx-xx-2457                     -- partial 

 email_id               abc@oracle.com   xxx@oracle.com               -- RegExpression

 Account_balance    640000                0                                    -- Full

 Random_number    3423545              5245663                          -- Random

 The redaction is enabled at a session level based on defined policy. 

 Can we hide data from table owner and expose data only to a application.?

 One common situation is to prevent the data being read,processed or stolen from with-in the organization. One of the greatest risk for data loss is when the password of a high privileged user is compromised and sencitive data is stolen. This is a common scenario and i will try to implement a simple policy which can prevent that..

 The idea is to redact sensitivity data from every session that access the data including the owner of the table and add exception only for the application to view data.

Sounds simple ? 

Yes.. 

As I mentioned earlier, the redaction policy is activated when the session is connected to the database or plugable database. Redaction takes place only if this policy expression evaluates to TRUE. This expression must be based on SYS_CONTEXT values from a specified namespace. 

The expression that needs to be true includes SYS_CONTEXT= user, module, service ,machine.  or 1=1.

In a ideal world, you could set  expression='SYS_CONTEXT=CLIENT_IDENTIFIER' which is coded into the application. but to demonstrate a simple Cut&Paste scenario,I opted to have the session expression evaluate to true when connected to default service_name. And if you need to connect to the same database through an application, simply add a service to the database and connect through that servece by only changing the tnsnames.ora This way, you can have a simple redaction of data with no application code change or manual intervention once the policy is set. 

SQL> set pages 0
SQL> conn sys/oracle@pdb1 as sysdba
Connected.
SQL> --create two users
SQL>
SQL> drop user vijay cascade;

User dropped.

SQL> create user vijay identified by oracle;

User created.

SQL> grant connect,resource to vijay;

Grant succeeded.

SQL> grant execute on dbms_redact to vijay;

Grant succeeded.

SQL> grant unlimited tablespace to vijay;

Grant succeeded.

SQL> drop user vijay1 cascade;

User dropped.

SQL> create user vijay1 identified by tiger;

User created.

SQL> grant connect,resource to vijay1;

Grant succeeded.

SQL> conn vijay/oracle@pdb1
Connected.
SQL> -- as first user, create a table with SS column
SQL> create table  creditcard_info (customer_name,SS) as select object_name, object_id from all_objects;

Table created.

SQL> grant select on vijay.creditcard_info to public;

Grant succeeded.

SQL> -- select from the table and observe SS column
SQL> col customer_name format a20
SQL> col ss format 9999
SQL> select * from vijay.creditcard_info fetch first 5 rows only;
ORA$BASE             133
DUAL                 142
DUAL                 143
MAP_OBJECT           348
SYSTEM_PRIVILEGE_MAP 445

SQL> -- create redact policy where service_name is default service_name
SQL> declare
  2  service_name varchar2(10);
  3  begin
  4   select SYS_CONTEXT('USERENV','SERVICE_NAME') into service_name from dual;
  5   DBMS_REDACT.ADD_POLICY (policy_name   => 'Redact_SS',object_schema => 'VIJAY',
  6  object_name   => 'CREDITCARD_INFO', column_name   => 'SS',
  7  expression    => 'SYS_CONTEXT(''USERENV'',''SERVICE_NAME'') ='''||service_name||'''',
  8  function_type => DBMS_REDACT.FULL);
  9  end;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- observe that even the table owner is not able view the column content
SQL> select * from vijay.creditcard_info fetch first 5 rows only;
ORA$BASE              0
DUAL                  0
DUAL                  0
MAP_OBJECT            0
SYSTEM_PRIVILEGE_MAP  0

SQL> -- connect as second user and that user cannot see column as well
SQL> conn vijay1/tiger@pdb1
Connected.
SQL>
SQL> col customer_name format a20
SQL> col ss format 9999
SQL> select * from vijay.creditcard_info fetch first 5 rows only;
ORA$BASE              0
DUAL                  0
DUAL                  0
MAP_OBJECT            0
SYSTEM_PRIVILEGE_MAP  0

SQL>
SQL> -- NOW, connect to sys and create a new service to the same pdb
SQL> conn sys/oracle@pdb1 as sysdba
Connected.
SQL> exec DBMS_SERVICE.stop_service('pdb1_app');

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_SERVICE.DELETE_SERVICE(
  3       service_name => 'pdb1_app');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2       DBMS_SERVICE.CREATE_SERVICE(
  3       service_name => 'pdb1_app',
  4       network_name => 'pdb1_app');
  5    DBMS_SERVICE.start_service('pdb1_app');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- Now test your application can access thorugh this new service and can see the rows..
SQL>
SQL> conn vijay/oracle@localhost:1521/pdb1_app
Connected.
SQL>
SQL> col customer_name format a20
SQL> col ss format 9999
SQL> select * from vijay.creditcard_info fetch first 5 rows only;
ORA$BASE               133
DUAL                   142
DUAL                   143
MAP_OBJECT             348
SYSTEM_PRIVILEGE_MAP   445

if you have a middle tier and an application to test this,,  you can have multiple AND condition in the expression validity to make it even more secure. For example, you could have a condition of where hostname = middletier_hostname and service=pdb1_app.

e.g.

expression    =>  'SYS_CONTEXT (''USERENV'', ''HOST'') = ''localhost.localdomain'' and SYS_CONTEXT(''USERENV'',''SERVICE_NAME'') =''pdb1_app''',

I want to point out, that this is a easy way to prevent  users  from accessing data outside of the intended application. This still does not prevent privileged users who have  "EXEMPT REDACTION POLICY"  system privilege including DBA roles. This is a first step that can prevent hacking by under-privileged users. To get a more comprehensive security, DB vault, Virtual Private Database and Fine Grain Access Control are additional options.