201612191816Oracle 12c Data Redaction Example

Oracle 12c Data Redaction Example

Create a test sample data as follows:
SQL> conn sys@pdb1 as sysdba
Enter password:
Connected.

SQL> grant execute on dbms_redact to testuser;
Grant succeeded.

SQL> conn testuser@pdb1
Enter password:

Connected. 

SQL> create table redaction_tab
  2  (id number(10),

  3   name varchar2(30),
  4   phone varchar2(30),
  5   ins_time date default sysdate
  6  );

SQL> insert into redaction_tab
  2  values(1,'Tom','1234-1000-2000',sysdate);

1 row created.

SQL> insert into redaction_tab
  2  values(2,'Bob','2345-2000-3000',sysdate);

1 row created.

SQL> insert into redaction_tab
  2  values(3,'Cat','3456-3000-4000',sysdate);

1 row created.

SQL> commit;
Commit complete.

 

SQL> col name format a20
SQL> col phone format a20
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

The following result shows the returning data without redaction:
SQL> select * from redaction_tab;
redaction_pic1.jpg

Add a data redaction policy named “REDACTION_POLICY_TEST1 to the tableREDACTION_TAB” and redact the column “NAME”
with FULL Redaction Policy as follows:

SQL> BEGIN
  2    DBMS_REDACT.ADD_POLICY
  3  (

  4    OBJECT_SCHEMA => 'TESTUSER',
  5    OBJECT_NAME   => 'REDACTION_TAB',
  6    POLICY_NAME   => 'REDACTION_POLICY_TEST1',
  7    COLUMN_NAME  => 'NAME',
  8    FUNCTION_TYPE  => DBMS_REDACT.FULL,
  9    EXPRESSION    => '1=1',

 10    ENABLE        => TRUE
 11  );
 12  END;
 13  /

PL/SQL procedure successfully completed. 

Here’s the result of the STRING format with full redaction:

SQL> select * from redaction_tab;
redaction_pic2.JPG 

Let’s alter the data redaction policy - “REDACTION_POLICY_TEST1 and add the column “INS_TIME”
with FULL Redaction Policy as follows:

SQL> BEGIN
  2    DBMS_REDACT.ALTER_POLICY
  3    (

  4      OBJECT_SCHEMA => 'TESTUSER',
  5      OBJECT_NAME   => 'REDACTION_TAB',
  6      POLICY_NAME   => 'REDACTION_POLICY_TEST1',
  7      COLUMN_NAME   => 'INS_TIME',
  8      ACTION        => DBMS_REDACT.ADD_COLUMN,
  9      EXPRESSION    => '1=1'

 10    );
 11  END;
 12  / 

PL/SQL procedure successfully completed.

Here’s the result of the DATE format with full redaction:

SQL> select * from redaction_tab;

redaction_pic3.jpg

 

Let’s alter again the data redaction policy - “REDACTION_POLICY_TEST1 and add another column “PHONE”
with PARTIAL Redaction Policy as follows:

 SQL> BEGIN
  2    DBMS_REDACT.ALTER_POLICY
  3    (

  4      OBJECT_SCHEMA       => 'TESTUSER',
  5      OBJECT_NAME         => 'REDACTION_TAB',
  6      POLICY_NAME         => 'REDACTION_POLICY_TEST1',
  7      COLUMN_NAME       => 'PHONE',
  8      ACTION              => DBMS_REDACT.ADD_COLUMN,
  9      FUNCTION_TYPE       => DBMS_REDACT.PARTIAL,
 10      FUNCTION_PARAMETERS => 'VVVVFVVVVFVVVV,VVVV-VVVV-VVVV,*,3,10',
 11      EXPRESSION           => '1=1'

 12    );
 13  END;
 14  / 

PL/SQL procedure successfully completed.

NOTE:

V: Stands for every character from the input string that can be redacted.
F: Stands for every character from the input string that can be considered as a separator.

3: Specifies the start position of the V character for redaction.
10: Specifies the end position of the V character for redaction.


Here’s the result of the STRING format with partial redaction:

SQL> select * from redaction_tab;
redaction_pic4.JPG



[Rerference]
https://docs.oracle.com/cd/E11882_01/network.112/e40393/redaction_config.htm#ASOAG10490

 



回應
廣告
學習種子





Powered by Xuite
    沒有新回應!
關鍵字