Oracle 12c RDBMS Redaction Policies allow the RDBMS Developer or DBA the ability to obscure data to prevent viewing by none authorized users.  This procedure shows the use of 12c Redaction against a common table.

 

 

1. Logon as the oracle DBA user.

 

mylinux# sqlplus system/password

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 1 13:58:21 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

 

2. Create test user, test table, and insert dummy data for execute this procedure against with the following SQL statements.

 

create user test identified by test default tablespace users

quota unlimited on users;

grant resource to test;

grant create session to test;

create table test.emp(f_name varchar2(30),

l_name varchar2(30),

start_date date,

salary number(8,2));

insert into test.emp values(‘Bob’, ‘Smith’, sysdate-30, 45000);

insert into test.emp values(‘Nancy’,’Jones’, sysdate-628,87000);

insert into test.emp values(‘Thomas’,’Harris’,sysdate-1245,79000);

 

3. Now as the system user create a policy using the RDBMS_REDACTION package to redact the column SALARY in the table TEST.EMP.

 

BEGIN

DBMS_REDACT.ADD_POLICY( object_schema => ‘test’,

object_name => ’emp’,

column_name => ‘salary’,

policy_name => ‘redact_salary’,

function_type => DBMS_REDACT.FULL,

expression => ‘1=1’);

END;

/

 

4. Now as the TEST user, select the values from table TEST.EMP and you will notice that the SALARY columns are all zero.

 

SQL> connect test/test

Connected.

SQL> set linesize 140

SQL> select * from test.emp;

 

F_NAME                         L_NAME                         START_DAT     SALARY

—————————— —————————— ——— ———-

Bob                            Smith                          04-APR-16          0

Nancy                          Jones                          15-AUG-14          0

Thomas                         Harris                         06-DEC-12          0

 

SQL>

 

5. Logon as the sys user and grant the user TEST the privilege to see the redacted column with the privilege EXEMPT REDACTION POLICY. By default even the owner of the table cannot see the redacted column without this privilege.

 

SQL> connect / as sysdba

Connected.

SQL> grant exempt redaction policy to test;

 

Grant succeeded.

 

SQL> connect test/test

Connected.

SQL> set linesize 140

SQL> select * from test.emp;

 

F_NAME                         L_NAME                         START_DAT     SALARY

—————————— —————————— ——— ———-

Bob                            Smith                          04-APR-16      45000

Nancy                          Jones                          15-AUG-14      87000

Thomas                         Harris                         06-DEC-12      79000

 

SQL>

 

6. Now the column is protected from all users except those with exempt redaction policy privileges.

 

Larry Catt, OCP