Oracle 12c Data Redaction Example－DanBrother的部落格

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;

Add a data redaction policy named “REDACTION_POLICY_TEST1” to the table “REDACTION_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;

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:

select * from redaction_tab;

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;