Oracle Data Redaction Part – 2

Hello Everyone,

In this blog post, I will continue to give you information about oracle data redaction.

Also, we can use function_parameters with “Fixed Character Formats”. Oracle Data Redaction provides special predefined formats to configure policies that use fixed characters. As can be seen in the link I provided as an example below, we have many Partial Fixed Character Redaction Formats options.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/asoag/configuring-oracle-data-redaction-policies.html#GUID-67CCF0EC-0FB8-44B8-A753-82C1BE475ED4
10.9.3 Creating Partial Redaction Policies Using Fixed Character Formats

Let’s give an example, we create a column named “card_string2” to avoid confusion with other examples and columns. We add some values to this newly created column:

alter table DBMASTER.payment_details add card_string2 varchar2(30);

update DBMASTER.payment_details set card_string2='1234-1234-1234-1234' where id=1;
update DBMASTER.payment_details set card_string2='2345-2345-2345-2345' where id=2;
update DBMASTER.payment_details set card_string2='3456-3456-3456-3456' where id=3;
update DBMASTER.payment_details set card_string2='4567-4567-4567-4567' where id=4;
update DBMASTER.payment_details set card_string2='5678-5678-5678-5678' where id=5;
update DBMASTER.payment_details set card_string2='6786-6786-6786-6786' where id=6;
update DBMASTER.payment_details set card_string2='7897-7897-7897-7897' where id=7;
update DBMASTER.payment_details set card_string2='8910-8910-8910-8910' where id=8;
update DBMASTER.payment_details set card_string2='9998-9998-9998-9998' where id=9;
commit;

We will use “DBMS_REDACT.REDACT_CCN16_F12” for function_parameters:

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => user,
object_name => 'payment_details',
policy_name => 'redact_card_info',
column_name => 'card_string2',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => DBMS_REDACT.REDACT_CCN16_F12,
expression => '1=1');
END;
/

Output:
Now, as you can see CARD_STRING2 column shows the data by using ****-****-****-vvvv

Example on date/time column:

Month: 'M' (no masking of month) or 'm#' (mask month to a specific month, if possible), where # (the month specified by its number) is between 1 and 12
Day: 'D' (no masking of date) or 'd#' (mask day to #, if possible), # between 1 and 31
Year: 'Y' (no masking of year) or 'y#' (mask year to #, if possible), # between 1 and 9999
Hour: 'H' (no masking of hour) or 'h#' (mask hour to #, if possible), # between 0 and 23
Minute: 'M' (no masking of minute) or 'm#' (mask minute to #, if possible), # between 0 and 59
Second: 'S' (no masking of second) or 's#' (mask second to #, if possible), # between 0 and 59

function_parameters is ‘m1d1Y’, it means that

BEGIN
DBMS_REDACT.alter_policy (
object_schema => user,
object_name => 'payment_details',
policy_name => 'redact_card_info',
action => DBMS_REDACT.add_column,
column_name => 'expiry_Date',
function_type => DBMS_REDACT.partial,
function_parameters => 'm1d1Y');
END;
/

Output:
Now, as you can see our expiry_date column shows 01/01/2025.

But, If we want to show 05/05/1995, we can use these command:

BEGIN
DBMS_REDACT.alter_policy (
object_schema => user,
object_name => 'payment_details',
policy_name => 'redact_card_info',
action => DBMS_REDACT.modify_column,
column_name => 'expiry_Date',
function_type => dbms_redact.partial,
function_parameters => 'm5d5Y1995');
END;
/

Output:

If we want a user in the database (SEVAYYILMAZ) not affect by the masking process,
we can exclude the relevant user as follows.

begin
dbms_redact.alter_policy (
object_schema => user,
object_name => 'payment_details',
policy_name => 'redact_card_info',
action => dbms_redact.modify_expression,
expression => 'sys_context(''userenv'',''session_user'') != ''SEVAYYILMAZ''' );
end;
/

Output:
Now, SEVAYYILMAZ user shows the data without mask:

Now I will show you how to drop policies directly.
But first, let’s make SEVAYYILMAZ user see the mask again.

begin
dbms_redact.alter_policy (
object_schema => user,
object_name => 'payment_details',
policy_name => 'redact_card_info',
action => dbms_redact.modify_expression,
expression => 'sys_context(''userenv'',''session_user'') = ''SEVAYYILMAZ''' );
end;
/

Output:
Now, “SEVAYYILMAZ” user see the data with mask.

Drop Policies using dbms_redact.drop_policy;

begin
dbms_redact.drop_policy (
object_schema => user,
object_name => 'payment_details',
policy_name => 'redact_card_info' );
end;
/

Now, All user in the database can show the data without mask:

 

DBMASTER BİLGİ TEKNOLOJİLERİ A.Ş.
www.dbmaster.com.tr

SEVAY YILMAZ
Database Operations Manager