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

dbms_scheduler log history - purging manually

Today, I came across a SYSAUX tablespace that was asking for more space. It was about 12Gb, which seems a bit large to me. If you use the $ORACLE_HOME/rdbms/admin/awrinfo.sql , you will get a report of the occupants.  Occupant JOB_SCHEDULER took over 11Gb space, which makes you think.

The default out-of-the-box maintainance job, uses the global attribute LOG_HISTORY to remove old dbms_scheduler job logging:

select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

Now this is default 30 days, and can be set with:

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','15');

But what if your maintainance jobs are failing for some reason (timeout/bugs?). You may need to clean it manually. The easy way to do this, is to use:

exec DBMS_SCHEDULER.PURGE_LOG(<days>, which_log=>'JOB_LOG');

so for example:

exec DBMS_SCHEDULER.PURGE_LOG(15,which_log=>'JOB_LOG');

But if the number of logs is very large, you have to do this carefully, step by step:

First determine the distribution of logging you have.

Select count(1) from dba_scheduler_job_log where log_date < sysdate – 100;

Make sure that you get an idea like:

older than 300 days	12 rows
older than 250 days	12831 rows
older than 200 days	438121 rows

Now step by step clean it, by 5 or days at a time:

exec DBMS_SCHEDULER.PURGE_LOG(150,which_log=>'JOB_LOG');
exec DBMS_SCHEDULER.PURGE_LOG(140,which_log=>'JOB_LOG');
etc.

Until you can do:

exec DBMS_SCHEDULER.PURGE_LOG(15,which_log=>'JOB_LOG');

Now you can set:

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','15');

and watch your maintainance job (PURGE_LOG) over the next days.

What if your PURGE_LOG job doesn't run? I had this, and a simple disable/enable was needed to get the right NEXT_RUN_DATE in dba_scheduler_jobs:

exec dbms_scheduler.disable('PURGE_LOG');
exec dbms_scheduler.enable('PURGE_LOG');

Now suppose that you cleaned up the job logging. After a few hours work, it's almost done. Just run:

exec DBMS_SCHEDULER.PURGE_LOG();

Which should be fairly quick (maybe 2 minutes) now.

The tables need  a shrink to release the space in the SYSAUX tablespace.

alter table sys.scheduler$_event_log enable row movement;
alter table sys.scheduler$_event_log shrink space cascade;

alter table sys.scheduler$_job_run_details enable row movement;
alter table sys.scheduler$_job_run_details space cascade;