DBMS_REDACT

http://docs.oracle.com/database/121/ARPLS/d_redact.htm#ARPLS73800
Tested ON:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
1.) Create users:
create user red1 identified by red1;
 grant connect, resource , select any table to red1;
create user red2 identified by red2;
 grant connect, resource , select any table to red2;
grant unlimited tablespace to red1;
grant execute on sys.dbms_redact TO red1;
2.) Create table:
create sequence red1.seq_info_id minvalue 1 maxvalue 999999999999 nocache order increment by 1;
create table red1.info
 (info_id number,
 passport varchar2(10),
 dt_create date,
 account_no number)
 tablespace users;
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'AA0000', sysdate, '123456789');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'AA9999', sysdate, '123789456');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'BB0000', sysdate, '123456000');
insert into red1.info
 values
 (red1.seq_info_id.nextval, 'CC1234', sysdate, '123456888');
COMMIT;
SELECT * FROM red1.info;
 INFO_ID PASSPORT   DT_CREAT ACCOUNT_NO
 ---------- ---------- -------- ----------
 1 AA0000     29/02/16  123456789
 2 AA9999     29/02/16  123789456
 3 BB0000     29/02/16  123456000
 4 CC1234     29/02/16  123456888
3.) ADDING POLICY:
 conn red1/red1
Connected.
 RED1 @ > show user
 USER is "RED1"
BEGIN
 DBMS_REDACT.add_policy(
 object_schema => 'RED1',
 object_name   => 'INFO',
 column_name   => 'PASSPORT',
 policy_name   => 'REDACT_INFO',
 function_type => DBMS_REDACT.full,
 expression    => '1=1'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREAT ACCOUNT_NO
 ---------- ---------- -------- ----------
 1            29/02/16  123456789
 2            29/02/16  123789456
 3            29/02/16  123456000
 4            29/02/16  123456888
4.) Checking the POLICY (as sysdba) or need to GRANT SELECT to RED1.
select * from REDACTION_POLICIES
OBJECT_OWNER                   OBJECT_NAME                    POLICY_NAME                    EXPRESSION                               ENABLE  POLICY_DESCRIPTION
 ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ------- ------------------------------
 RED1                           INFO                           REDACT_INFO                    1=1                                      YES
5.) Changing the POLICY:
conn red1/red1
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.modify_column,
 column_name         => 'PASSPORT',
 function_type       => DBMS_REDACT.partial,
 function_parameters => 'VVVVVV,VVVVVV,#,1,4'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16  123456789
 2 ####99     29-FEB-16  123789456
 3 ####00     29-FEB-16  123456000
 4 ####34     29-FEB-16  123456888
6.) Adding Another Column
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.add_column,
 column_name         => 'ACCOUNT_NO',
 function_type       => DBMS_REDACT.partial,
 function_parameters => '0,1,9'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16          0
 2 ####99     29-FEB-16          0
 3 ####00     29-FEB-16          0
 4 ####34     29-FEB-16          0
7.) Filtering by CONTEXT - Column PASSPORT:
BEGIN
 DBMS_REDACT.alter_policy (
 object_schema       => 'RED1',
 object_name         => 'INFO',
 policy_name         => 'REDACT_INFO',
 action              => DBMS_REDACT.modify_expression,
 column_name         => 'PASSPORT',
 expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''RED1'''
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 AA0000     29-FEB-16  123456789
 2 AA9999     29-FEB-16  123789456
 3 BB0000     29-FEB-16  123456000
 4 CC1234     29-FEB-16  123456888
conn red2/red2
17:53:51 SQL> SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 ####00     29-FEB-16          0
 2 ####99     29-FEB-16          0
 3 ####00     29-FEB-16          0
 4 ####34     29-FEB-16          0
8.) Droping POLICY:
conn red1/red1
BEGIN
 DBMS_REDACT.drop_policy (
 object_schema => 'RED1',
 object_name   => 'INFO',
 policy_name   => 'REDACT_INFO'
 );
 END;
 /
SELECT * FROM red1.info;
INFO_ID PASSPORT   DT_CREATE ACCOUNT_NO
 ---------- ---------- --------- ----------
 1 AA0000     29-FEB-16  123456789
 2 AA9999     29-FEB-16  123789456
 3 BB0000     29-FEB-16  123456000
 4 CC1234     29-FEB-16  123456888
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s