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.
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>
To be added more ...
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>
To be added more ...
This comment has been removed by a blog administrator.
ReplyDelete