Full Data Redaction can redact entire column data. Redacted value returned to the querying application depends on the data type of the column. Ex: Character data type would redact to single space or NUMBER data type would redact zero.
Here we are going to discuss following topics where as in my earlier article we covered Configuring Data Redaction Policies.
- Creating full data redaction policy.
- Syntax
- Examples of full data redaction policies.
- Example based on Number data type.
- Example based on Character data type.
- Altering the Default Full Data Redaction Value.
- Hands-on on altering the Default Full Data Redaction Value.
Lets start one by one:
Creating full data redaction policy
To create data redaction policy with full redaction, you need to set unction_type parameter to DBMS_REDACT.FULL in DBMS_REDACT.ADD_POLICY syntax. By default, Number would be redacted by zero (0) and character would be redacted by single space. Default values can be modify with the help of DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure.
Syntax for creating Full Data Redaction policy:
DBMS_REDACT.ADD_POLICY ( object_schema IN VARCHAR2 := NULL, object_name IN VARCHAR2, column_name IN VARCHAR2 := NULL, policy_name IN VARCHAR2, function_type IN BINARY_INTEGER := NULL, expression IN VARCHAR2, enable IN BOOLEAN := TRUE);
Examples of full data redaction policies
Example 1:
Let me demonstrate full data redaction for all the values of MOBILE column from C##SCOTT.EMP table. When any user will access the MOBILE column, expression parameter would apply policy except users who have been granted with EXEMPT REDACTION POLICY system privilege.
Let me connect to C##SCOTT user and create EMP table to simulate environment.
SQL> show user USER is "C##SCOTT" create table EMP ( empid number(5), ename varchar2(30), mobile number(11) ); Table created.
Insert below 5 records to EMP table:
SQL> insert into EMP values(1,'scott',1234567898); SQL> insert into EMP values(2,'system',1234567898); SQL> insert into EMP values(3,'sys',1234567898); SQL> insert into EMP values(4,'abc',1234567898); SQL> insert into EMP values(5,'xyz',1234567898); SQL> commit;
Query EMP table:
SQL> select * from emp; EMPID ENAME MOBILE ---------- ------------------------------ ---------- 1 scott 1234567898 2 system 1234567898 3 sys 1234567898 4 abc 1234567898 5 xyz 1234567898
Create full data redaction policy with name: ‘full_redact_mobile’ on MOBILE column of EMP table as below:
SQL> BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'C##SCOTT',
object_name => 'EMP',
column_name => 'MOBILE',
policy_name => 'full_redact_mobile',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/
function_type => DBMS_REDACT.FULL,
*
ERROR at line 7:
ORA-06550: line 7, column 24:
PLS-00201: identifier 'DBMS_REDACT' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
Above full data redaction policy creation failed with “PLS-00201: identifier ‘DBMS_REDACT’ must be declared” Because we are forgotten to grant DBMS_REDACT procedure execute permission to C##SCOTT user.
Grant EXECUTE permission to C##SCOTT user as below:
SQL> grant execute on DBMS_REDACT to C##SCOTT; Grant succeeded.
Policy created successfully after assigning execute privilege:
SQL> BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'C##SCOTT', object_name => 'EMP', column_name => 'MOBILE', policy_name => 'full_redact_mobile', function_type => DBMS_REDACT.FULL, expression => '1=1'); END; / PL/SQL procedure successfully completed.
Now query EMP table and you will find redacted MOBILE column as zero (default value)
SQL> select * from emp; EMPID ENAME MOBILE ---------- ------------------------------ ---------- 1 scott 0 2 system 0 3 sys 0 4 abc 0 5 xyz 0
This is about full data redaction in terms of NUMBER.
Example 2:
Let me demonstrate full data redaction for all the values of ENAME column from C##SCOTT.NEW_EMP table.
Note: We can’t consider same EMP table for character data redaction because according to data redaction rules, only one redaction policy can be configure per table.
Creating NEW_EMP table as below:
create table NEW_EMP ( empid number(5), ename varchar2(30), mobile number(11) );
Insert below values to NEW_EMP as below:
SQL> insert into NEW_EMP values(1,'test',1234567898); SQL> insert into NEW_EMP values(2,'test',1234567898); SQL> insert into NEW_EMP values(3,'test',1234567898); SQL> commit;
Query NEW_EMP table:
SQL> select * from new_emp; EMPID ENAME MOBILE ---------- ------------------------------ ---------- 1 test 1234567898 2 test 1234567898 3 test 1234567898
Create full data redaction policy with name:’full_redact_ename’ on ENAME column on newly created table. i.e. NEW_EMP:
SQL> BEGIN DBMS_REDACT.ADD_POLICY( object_schema => 'C##SCOTT', object_name => 'NEW_EMP', column_name => 'ENAME', policy_name => 'full_redact_ename', function_type => DBMS_REDACT.FULL, expression => '1=1'); END; /
Query NEW_EMP table and you will find ENAME column is redacted with single blank space (default) as below:
SQL> select * from new_emp; EMPID ENAME MOBILE ---------- ------------------------------ ---------- 1 1234567898 2 1234567898 3 1234567898
Altering the Default Full Data Redaction Value.
To alter, Use DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify default value. for Ex: zero is the default redacted value in case of full redaction for number data type. In case of modification of this value we need to run DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure. This modification is applicable to every data redaction policy within instance.
Note: Database bounce would require to take effect of changed value.
Find current default value by querying REDACTION_VALUES_FOR_TYPE_FULL data dictionary view. With this data dictionary view you can find out current default values of NUMBER_VALUE, BINARY_FLOAT_VALUE, BINARY_DOUBLE_VALUE, CHAR_VALUE, VARCHAR_VALUE, NCHAR_VALUE, NVARCHAR_VALUE, DATE_VALUE, TIMESTAMP_VALUE, TIMESTAMP_WITH_TIME_ZONE_VALUE, BLOB_VALUE, CLOB_VALUE and NCLOB_VALUE data type.
Lets consider hands-on to change full data redaction default value for number data type, i.e. zero (0)
Connect as sysdba and query REDACTION_VALUES_FOR_TYPE_FULL data dictionary view for default full data redacted value for NUMBER data type as below:
[oracle@OL712c ~]$ sqlplus / as sysdba SQL> select NUMBER_VALUE from REDACTION_VALUES_FOR_TYPE_FULL; NUMBER_VALUE ------------ 0
Execute ‘UPDATE_FULL_REDACTION_VALUES’ of ‘DBMS_REDACT’ procedure in order to change default value to three (3) as below:
SQL> EXEC DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (number_val => 3); PL/SQL procedure successfully completed.
Likewise you can modify following values:
binfloat_val : BINARY_FLOAT data type.
bindouble_val : BINARY_DOUBLE data type.
char_val : CHAR data type.
varchar_val : VARCHAR2 data type.
nchar_val : NCHAR data type.
nvarchar_val : NVARCHAR2 data type.
date_val : DATE data type.
ts_val : TIMESTAMP data type.
tswtz_val : TIMESTAMP WITH TIME ZONE data type.
blob_val : BLOB data type.
clob_val : CLOB data type.
nclob : NCLOB data type.
Again query REDACTION_VALUES_FOR_TYPE_FULL data dictionary view and default data redaction value for NUMBER has changed to three (3).
SQL> select NUMBER_VALUE from REDACTION_VALUES_FOR_TYPE_FULL; NUMBER_VALUE ------------ 3
Bounce back your instance in order to reflect change in your environment:
SQL> shutdown immediate; SQL> startup; SQL> exit;
Connect to C##SCOTT user and query EMP table, you will find data redacted value for NUMBER data type to three (3).
[oracle@OL712c ~]$ sqlplus c##scott/tiger SQL> select * from emp; EMPID ENAME MOBILE ---------- ------------------------------ ---------- 1 scott 3 2 system 3 3 sys 3 4 abc 3 5 xyz 3
This is about Full Data Redaction Policy, Kindly stay tune with my next article about Partial Redaction Policy.
