Data Redaction in Oracle 12c

What is Data Redaction?
Oracle Data Redaction is one of the new features introduced in Oracle Database 12c. This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real time, without requiring changes to the application. Oracle Database 12c applies protection at query execution time.

How It Works?
We can create redaction policies which specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.

The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for full redaction policy.

DBMS_REDACT.ALTER_POLICY – allows changes to existing policies.
DBMS_REDACT.DISABLE_POLICY – disables an existing policy.
DBMS_REDACT.DROP_POLICY – drop an existing policy.
DBMS_REDACT.ENABLE_POLICY – enables an existing policy.
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES – change the default return value for full redaction. You must restart the database to take effect.

How many ways to protect data using data redaction?

You can protect data at the column level using one of the following methods::

Full redaction – All content of the column is protected and the type of value returned depends on the data type of the column. For numeric columns, the value zero will be returned. For columns of type character, a space will be returned. This setting can be changed at the database level.
Partial redaction – Only part of the information is changed. For example, the first digits of the credit card number are replaced by asterisks.
Regular expressions – You can use regular expressions to search for patterns of data that must be protected.
Random redaction – Returned values ??are random; each time a query is executed, the displayed data will be different.
No redaction – Allows testing the inner workings of redaction policies, with no effect on the results of current running queries. This is widely used during testing phase of redaction policies that will eventually find their way to production environments.

Can be see here using graphical presentation:

What data types can be used for data redaction?

It can be used with the following column data types: NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB, CLOB, and NCLOB.


Examples to show use of data redaction features  

— Find data redaction enabled or not

SQL> select * from redaction_policies;
no rows selected
SQL> 

SQL> GRANT EXECUTE ON sys.dbms_redact TO scott;
Grant succeeded.

SQL> connect scott/xxxxx;
Connected.
SQL> desc emp_test;
 Name                                      Null?    Type
 —————————————– ——– —————————-
 EMPNO                                     NOT NULL NUMBER(38)
 NAME                                               VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 BOSS                                               NUMBER(38)
 HIREDATE                                           VARCHAR2(12)
 SALARY                                             NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(38)

SQL> 

SQL> select * from employee;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
———- ———- ——— ———- ———— ———- ———- ———-
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.

— Applying Full Data Redaction

SQL> connect / as sysdba
Connected.
SQL> sho user
USER is “SYS”

SQL> 

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => ‘scott’,
    object_name   => ’emp_test’,
    column_name   => ‘salary’,
    policy_name   => ‘redact_sal_info’,
    function_type => DBMS_REDACT.full,
    expression    => ‘1=1’
  );
END;
/

PL/SQL procedure successfully completed.

— after data redaction

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
———- ———- ——— ———- ———— ———- ———- ———-
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.

— See the applied policies

col OBJECT_OWNER for a20
col OBJECT_NAME for a20;
col POLICY_DESCRIPTION for a10
col POLICY_NAME for a20
col EXPRESSION for a10

SQL> select * from redaction_policies;

OBJECT_OWNER         OBJECT_NAME          POLICY_NAME          EXPRESSION ENABLE  POLICY_DES
——————– ——————– ——————– ———- ——- ———-
SCOTT                EMP_TEST             redact_sal_info     1=1        YES
SQL> 

— Data Redaction default values

SET LINESIZE 250
COLUMN char_value FORMAT A10
COLUMN varchar_value FORMAT A10
COLUMN nchar_value FORMAT A10
COLUMN nvarchar_value FORMAT A10
COLUMN timestamp_value FORMAT A27
COLUMN timestamp_with_time_zone_value FORMAT A32
COLUMN blob_value FORMAT A20
COLUMN clob_value FORMAT A10
COLUMN nclob_value FORMAT A10

SELECT * FROM   redaction_values_for_type_full;

— Alter an Existing Policy
The ALTER_POLICY procedure allows you to make changes to an existing policy. The type of change being made is controlled using the ACTION parameter. Depending on the action required, the relevant parameters must be specified.

The following example changes the previously created redaction policy so that it uses partial redaction. Notice the FUNCTION_PARAMETERS are now specified to give instructions how the partial redaction should take place. For a numeric data type we specify a comma separated list of three elements (value to redact to, start point, end point), so in this case we want the first 12 characters of the number to always display as “111111111111”.

SQL> show user
USER is “SYS”
SQL> BEGIN
  DBMS_REDACT.alter_policy (
    object_schema       => ‘scott’,
    object_name         => ’emp_test’,
    policy_name         => ‘redact_sal_info’,
    action              => DBMS_REDACT.modify_column,
    column_name         => ‘salary’,
    function_type       => DBMS_REDACT.partial,
    function_parameters => ‘1,1,12’
  );
END;
/

PL/SQL procedure successfully completed.

SQL> 

— Verify

SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
———- ———- ——— ———- ———— ———- ———- ———-
      7839 KING       PRESIDENT            1981-11-17         1111                    10
      7566 JONES      MANAGER         7839 1981-04-02         1111                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         1111                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1111                    20
      7902 FORD       ANALYST         7566 1981-12-03         1111                    20
      7369 SMITH      CLERK           7902 1980-12-17          111                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         1111                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1111        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1111        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1111       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1111          0         30
      7900 JAMES      CLERK           7698 1981-12-03          111                    30
      7782 CLARK      MANAGER         7839 1981-06-09         1111                    10
      7934 MILLER     CLERK           7782 1982-01-23         1111                    10

14 rows selected.

SQL> 

— Drop an Existing Policy
The DROP_POLICY procedure is used to remove an existing redaction policy. The following example drops the redaction policy and queries the data, showing the redaction is no longer taking place.

CONN test/test@pdb1

BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => ‘scott’,
    object_name   => ’emp_test’,
    policy_name   => ‘redact_sal_info’
  );
END;
/

PL/SQL procedure successfully completed.

SQL> show user;
USER is “SCOTT”
SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
———- ———- ——— ———- ———— ———- ———- ———-
      7839 KING       PRESIDENT            1981-11-17         5200                    10
      7566 JONES      MANAGER         7839 1981-04-02         2975                    20
      7788 SCOTT      ANALYST         7566 1982-12-09         3000                    20
      7876 ADAMS      CLERK           7788 1983-01-12         1100                    20
      7902 FORD       ANALYST         7566 1981-12-03         3000                    20
      7369 SMITH      CLERK           7902 1980-12-17          800                    20
      7698 BLAKE      MANAGER         7839 1981-05-01         2850                    30
      7499 ALLEN      SALESMAN        7698 1981-02-20         1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28         1250       1400         30
      7844 TURNER     SALESMAN        7698 1981-09-08         1500          0         30
      7900 JAMES      CLERK           7698 1981-12-03          950                    30
      7782 CLARK      MANAGER         7839 1981-06-09         2450                    10
      7934 MILLER     CLERK           7782 1982-01-23         1300                    10

14 rows selected.

SQL> 

— Apply on Varchar Column

SQL> show user;
USER is “SYS”
SQL> 
SQL> BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => ‘scott’,
object_name => ’emp_test’,
column_name => ‘job’,
policy_name => ‘partially mask job’,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => ‘VVVVVVVVV,VVVVVVVVV,*,3,12’,
expression => ‘1=1’
);
END;
/   

PL/SQL procedure successfully completed.

SQL> connect scott
Enter password: 
Connected.
SQL> show user
USER is “SCOTT”
SQL> select * from emp_test;

     EMPNO NAME       JOB             BOSS HIREDATE         SALARY       COMM     DEPTNO
———- ———- ——— ———- ———— ———- ———- ———-
      7839 KING       PR*******            1981-11-17         5200                    10
      7566 JONES      MA*****         7839 1981-04-02         2975                    20
      7788 SCOTT      AN*****         7566 1982-12-09         3000                    20
      7876 ADAMS      CL***           7788 1983-01-12         1100                    20
      7902 FORD       AN*****         7566 1981-12-03         3000                    20
      7369 SMITH      CL***           7902 1980-12-17          800                    20
      7698 BLAKE      MA*****         7839 1981-05-01         2850                    30
      7499 ALLEN      SA******        7698 1981-02-20         1600        300         30
      7521 WARD       SA******        7698 1981-02-22         1250        500         30
      7654 MARTIN     SA******        7698 1981-09-28         1250       1400         30
      7844 TURNER     SA******        7698 1981-09-08         1500          0         30
      7900 JAMES      CL***           7698 1981-12-03          950                    30
      7782 CLARK      MA*****         7839 1981-06-09         2450                    10
      7934 MILLER     CL***           7782 1982-01-23         1300                    10

14 rows selected.

SQL> 

—- Let us create new table to apply data redaction on varchar column

— Create table
SQL> show user;
USER is “SCOTT”
SQL> 

create table creditcard (cust_id number(4) primary key, card_no varchar2(19), gate_name varchar2(20), expiry_date DATE, PIN number(4));

insert into creditcard values(1000,’1234-1234-1234-1234′,’VISA POWER’,’01-JAN-2022′,5555);
insert into creditcard values(1001,’2323-2323-2323-2323′,’VISA POWER’,’01-MAR-2028′,6666);
insert into creditcard values(1002,’4321-4321-4321-4321′,’RUPAY GATE’,’09-JAN-2026′,9999);
insert into creditcard values(1003,’6789-6789-6789-6789′,’VISA POWER’,’31-DEC-2022′,8888);
insert into creditcard values(1004,’1234-5678-1234-5678′,’RUPAY GATE’,’31-DEC-2024′,7777);


SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
———- ——————- ——————– —————— ———-
      1000 1234-1234-1234-1234 VISA POWER           01-JAN-22                5555
      1001 2323-2323-2323-2323 VISA POWER           01-MAR-28                6666
      1002 4321-4321-4321-4321 RUPAY GATE           09-JAN-26                9999
      1003 6789-6789-6789-6789 VISA POWER           31-DEC-22                8888
      1004 1234-5678-1234-5678 RUPAY GATE           31-DEC-24                7777

SQL> 

— Applying data redaction on varchar and number col
— Fisrt add column to data redaction

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => ‘scott’,
    object_name   => ‘creditcard’,
    column_name   => ‘PIN’,
    policy_name   => ‘redact_card_pin’,
    function_type => DBMS_REDACT.full,
    expression    => ‘1=1’
  );
END;
/

PL/SQL procedure successfully completed.

SQL> show user;
USER is “SCOTT”
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
———- ——————- ——————– —————— ———-
      1000 1234-1234-1234-1234 VISA POWER           01-JAN-22                   0
      1001 2323-2323-2323-2323 VISA POWER           01-MAR-28                   0
      1002 4321-4321-4321-4321 RUPAY GATE           09-JAN-26                   0
      1003 6789-6789-6789-6789 VISA POWER           31-DEC-22                   0
      1004 1234-5678-1234-5678 RUPAY GATE           31-DEC-24                   0

SQL> 

SQL> show user;
USER is “SYS”
SQL> 
BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => ‘scott’,
    object_name   => ‘creditcard’,
    policy_name   => ‘redact_card_pin’
  );
END;
/

PL/SQL procedure successfully completed.

— redact card_no

SQL> show user;
USER is “SYS”
SQL> 
SQL> BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => ‘scott’,
        object_name => ‘creditcard’,
        column_name => ‘card_no’,
        policy_name => ‘partially mask cardno’,
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => ‘VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,3,12’, — put ‘*’ from 3rd position to 12th position
        expression => ‘1=1’
);
END;
/

PL/SQL procedure successfully completed.

SQL> 
SQL> connect scott/scott
Connected.
SQL> show user;
USER is “SCOTT”
SQL> set lines 222
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
———- ——————- ——————– —————— ———-
      1000 12**-****-****-1234 VISA POWER           01-JAN-22                5555
      1001 23**-****-****-2323 VISA POWER           01-MAR-28                6666
      1002 43**-****-****-4321 RUPAY GATE           09-JAN-26                9999
      1003 67**-****-****-6789 VISA POWER           31-DEC-22                8888
      1004 12**-****-****-5678 RUPAY GATE           31-DEC-24                7777

SQL> 

SQL>

— Again redefine (put ‘*’ from 1st position to 12th position)

SQL> show user
USER is “SYS”
SQL> BEGIN
  DBMS_REDACT.drop_policy (
    object_schema => ‘scott’,
    object_name   => ‘creditcard’,
    policy_name   => ‘partially mask cardno’
  );
END;
/  

PL/SQL procedure successfully completed.

SQL>

SQL> BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => ‘scott’,
        object_name => ‘creditcard’,
        column_name => ‘card_no’,
        policy_name => ‘partially mask cardno’,
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => ‘VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,3,12’, — put ‘*’ from 3rd position to 12th position
        expression => ‘1=1’
);
END;
/

SQL> 
BEGIN
        DBMS_REDACT.ADD_POLICY(
        object_schema => ‘scott’,
        object_name => ‘creditcard’,
        column_name => ‘card_no’,
        policy_name => ‘partially mask cardno’,
        function_type => DBMS_REDACT.PARTIAL,
        function_parameters => ‘VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12’,
        expression => ‘1=1’
);
END;
/

PL/SQL procedure successfully completed.
SQL> 

— Now verify

SQL> show user
USER is “SCOTT”
SQL> select * from creditcard;

   CUST_ID CARD_NO             GATE_NAME            EXPIRY_DATE               PIN
———- ——————- ——————– —————— ———-
      1000 ****-****-****-1234 VISA POWER           01-JAN-22                5555
      1001 ****-****-****-2323 VISA POWER           01-MAR-28                6666
      1002 ****-****-****-4321 RUPAY GATE           09-JAN-26                9999
      1003 ****-****-****-6789 VISA POWER           31-DEC-22                8888
      1004 ****-****-****-5678 RUPAY GATE           31-DEC-24                7777

SQL>

  • September 25, 2018 | 23 views
  • Comments