Setup a simple Data Redaction demo

The following will give you a simple demo for data redaction.

Setup

First we start with a cleanup, so that the script may be run several times.

conn /as sysdba
-- cleanup
drop user APP_OWN cascade;
drop user MIKE cascade;
drop user BOSS cascade;
drop role ROLE_SALES;
drop role ROLE_MANAGERS;

Now create users. One for the application itself, one for the manager Boss, and one for the employee Mike:
-- create users
create user APP_OWN identified by APP_OWN;
create user MIKE identified by MIKE;
create user BOSS identified by BOSS;

grant create session, create sequence, create table, unlimited tablespace to APP_OWN;
grant execute on dbms_redact to APP_OWN;
grant create session to MIKE;
grant create session to BOSS;


We will be working with roles to get the security setup:
-- create roles
create role ROLE_SALES;
create role ROLE_MANAGERS;
grant select any table to ROLE_SALES;
grant select any table to ROLE_MANAGERS;

grant ROLE_SALES to MIKE;
grant ROLE_MANAGERS to BOSS;

Now create a table in the application schema.
-- setup demo table
conn APP_OWN/APP_OWN
create table emp (id number generated always as identity, name varchar2(30), salary number(15,2));
insert into emp (name,salary) values ('TOM',9999.99);
insert into emp (name,salary) values ('NANCY',8888.88);
commit;

-- setup policy
BEGIN
 DBMS_REDACT.ADD_POLICY  (
    OBJECT_SCHEMA => 'APP_OWN',
    object_name => 'EMP',
    policy_name => 'DEMO_EMP_POLICY',
    expression  => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''ROLE_MANAGERS'')    = ''FALSE''',
    column_name => '"SALARY"',
    function_type => DBMS_REDACT.RANDOM );
END;
/

Results

-- checking results:
col NAME format a7

conn APP_OWN/APP_OWN
select USER  from dual;
select * from APP_OWN.EMP;

conn BOSS/BOSS
select USER  from dual;
select * from APP_OWN.EMP;

conn MIKE/MIKE
select USER  from dual;
select * from APP_OWN.EMP;

This will give you the results:

Connected.

USER
------------------------------
APP_OWN


        ID NAME        SALARY
---------- ------- ----------
         1 TOM        9074.99
         2 NANCY      8315.64

Note that the application owner does not have the manager role and will not see the data itself!!!

Connected.

USER
------------------------------
BOSS


        ID NAME        SALARY
---------- ------- ----------
         1 TOM        9999.99
         2 NANCY      8888.88


As you can see, the BOSS user sees the real data.

Connected.

USER
------------------------------
MIKE


        ID NAME        SALARY
---------- ------- ----------
         1 TOM        4128.09
         2 NANCY      2698.32


And the MIKE user does not have access to the real data.

What if.... Mike tries to break into the data?

conn MIKE/MIKE
Connected.

create table MYCOPY as select * from APP_OWN.EMP;
create table MYCOPY as select * from APP_OWN.EMP
                              *
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.

Risks

!!!NOTE that sysdba has all rights, you will need to implement Database Vault to prevent the following:

conn / as sysdba
Connected.
select * from APP_OWN.EMP;

        ID NAME                               SALARY
---------- ------------------------------ ----------
         1 TOM                               9999.99
         2 NANCY                             8888.88

Also, a second weakness is the implicit way to figure out values:

conn MIKE/MIKE
Connected.

select * from APP_OWN.EMP where SALARY between 9999 and 10000;

        ID NAME                               SALARY
---------- ------------------------------ ----------
         1 TOM                                3660.5