Data Redaction has been backported to Oracle 11.2.0.4 . See the examples below .
For the original post on Data Redaction , please see this link http://mfzahirdba.blogspot.com/2014/04/data-redaction-in-oracle.html.
I would not upgrade to 11.2.0.4 , as the support for this release ends soon ( in few months from now) . :-(
Examples below
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table employee as
2 select lpad(object_id * object_id, 9, 5) ssn , object_name ename ,
3 object_id * 2 as empid
4 from all_objects
5 where object_id > 70000 and rownum <= 10 ;
Table created.
SQL> col ename format a30
SQL> Select * from employee;
SSN ENAME EMPID
--------- ------------------------------ ----------
490014000 USER_XML_SCHEMAS 140002
490042000 DBA_XML_INDEXES 140006
490056001 ALL_XML_INDEXES 140008
490070002 ALL_XML_INDEXES 140010
490084003 USER_XML_INDEXES 140012
490098004 USER_XML_INDEXES 140014
490112006 USER_XML_COLUMN_NAMES 140016
490126008 USER_XML_COLUMN_NAMES 140018
490154012 DBA_XML_SCHEMA_IMPORTS 140022
490182016 DBA_XML_SCHEMA_INCLUDES 140026
10 rows selected.
SQL> BEGIN
2 DBMS_REDACT.ADD_POLICY(
3 object_schema => 'scott',
4 object_name => 'employee',
5 column_name => 'ssn',
6 policy_name => 'hide_emp_ssn',
7 function_type => DBMS_REDACT.FULL,
8 function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
9 expression => 'SYS_CONTEXT(''USERENV'',''CLIENT_INFO'') != ''PAYROLL''');
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> exec dbms_application_info.set_client_info('PAYROLL') ;
PL/SQL procedure successfully completed.
SQL> Select SYS_CONTEXT('USERENV','CLIENT_INFO') from dual ;
SYS_CONTEXT('USERENV','CLIENT_INFO')
--------------------------------------------------------------------------------
PAYROLL
SQL> Select * from employee;
SSN ENAME EMPID
--------- ------------------------------ ----------
490014000 USER_XML_SCHEMAS 140002
490042000 DBA_XML_INDEXES 140006
490056001 ALL_XML_INDEXES 140008
490070002 ALL_XML_INDEXES 140010
490084003 USER_XML_INDEXES 140012
490098004 USER_XML_INDEXES 140014
490112006 USER_XML_COLUMN_NAMES 140016
490126008 USER_XML_COLUMN_NAMES 140018
490154012 DBA_XML_SCHEMA_IMPORTS 140022
490182016 DBA_XML_SCHEMA_INCLUDES 140026
10 rows selected.
SQL> exec dbms_application_info.set_client_info('MIS') ;
PL/SQL procedure successfully completed.
SQL> Select SYS_CONTEXT('USERENV','CLIENT_INFO') from dual ;
SYS_CONTEXT('USERENV','CLIENT_INFO')
--------------------------------------------------------------------------------
MIS
SQL> Select * from employee;
SSN ENAME EMPID
--------- ------------------------------ ----------
USER_XML_SCHEMAS 140002
DBA_XML_INDEXES 140006
ALL_XML_INDEXES 140008
ALL_XML_INDEXES 140010
USER_XML_INDEXES 140012
USER_XML_INDEXES 140014
USER_XML_COLUMN_NAMES 140016
USER_XML_COLUMN_NAMES 140018
DBA_XML_SCHEMA_IMPORTS 140022
DBA_XML_SCHEMA_INCLUDES 140026
10 rows selected.
SQL>
No comments:
Post a Comment