dbms_redact basicly is a 12c package which was backported to 11.2.0.4. This package allow us create very customizable masking procedures. For simplicity, let say you need to mask credit card numbers, cutting off the middle 6 digits in 16-digital cards for some user i_see only
Create test env
Lets create redact policy for user i_see, table mask_tab and column card_num
Also, keep in mind if user has EXEMPT REDACTION POLICY privilege, then policies dont applies to user (DBA role has this priv)
Create test env
SQL> create user i_see identified by "***";
User created.
SQL> grant create session,create table to i_see;
Grant succeeded.
SQL> grant execute on dbms_random to i_see;
Grant succeeded.
SQL> conn i_see/***
Connected.
SQL> set num 20
SQL> create table mask_tab (card_num varchar2(20));
Table created.
SQL> insert into mask_tab
select (round(dbms_random.value(1000000000000000, 9999999999999999)))
from dual connect by level <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select * from mask_tab;
CARD_NUM
--------------------
4315180567707642
8343744789583160
7219646392845285
1281700031661329
6939207428729261
2520681905985755
7344297730838169
9104025795743973
4902051003122352
2026891028511588
10 rows selected. Lets create redact policy for user i_see, table mask_tab and column card_num
[testhost:oracle@testdb]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 13 12:31:44 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> begin
dbms_redact.add_policy(object_schema => 'I_SEE',
object_name => 'MASK_TAB',
column_name => 'CARD_NUM',
policy_name => 'CARD_NUM_POLICY',
function_type => DBMS_REDACT.REGEXP,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''I_SEE''',
regexp_pattern => '(\d{6})\d{6}(\d+)',
regexp_replace_string => '\1******\2');
end;
11 /
PL/SQL procedure successfully completed.
SQL> select user from dual;
USER
------------------------------
SYS
SQL> select * from i_see.mask_tab;
CARD_NUM
--------------------------------------------------------------------------------
4315180567707642
8343744789583160
7219646392845285
1281700031661329
6939207428729261
2520681905985755
7344297730838169
9104025795743973
4902051003122352
2026891028511588
10 rows selected.
SQL> conn i_see/***
Connected.
SQL> select * from mask_tab;
CARD_NUM
--------------------------------------------------------------------------------
431518******7642
834374******3160
721964******5285
128170******1329
693920******9261
252068******5755
734429******8169
910402******3973
490205******2352
202689******1588
10 rows selected.
SQL> exit
Then drop policy
[testhost:oracle@testdb]$ sqlplus "/as sysdba"
SQL> begin
dbms_redact.drop_policy(
object_schema => 'I_SEE',
object_name => 'MASK_TAB',
policy_name => 'CARD_NUM_POLICY');
end;
7 /
PL/SQL procedure successfully completed.
SQL> conn i_see/***
Connected.
SQL> select * from mask_tab;
CARD_NUM
--------------------
4315180567707642
8343744789583160
7219646392845285
1281700031661329
6939207428729261
2520681905985755
7344297730838169
9104025795743973
4902051003122352
2026891028511588
10 rows selected. Also, keep in mind if user has EXEMPT REDACTION POLICY privilege, then policies dont applies to user (DBA role has this priv)
