Oracle Data Redaction Part – 1

Hello Everyone,

In this blog post, I will give you information about oracle data redaction. We need to use dbms_redact package. The DBMS_REDACT package provides an interface to Oracle Data Redaction, which enables you to mask (redact) data that is returned from queries issued by low-privileged users or an application.

DBMS_REDACT Package:

Some procedures we use in this blog examples:

Procedures	Description
ADD_POLICY Procedure	This procedure defines a Data Redaction policy for a table or view
ALTER_POLICY Procedure	This procedure alters an existing Data Redaction policy for a table or view
DISABLE_POLICY Procedure	This procedure disables a Data Redaction policy
ENABLE_POLICY Procedure	This procedure re-enables a Data Redaction policy
DROP_POLICY Procedure	This procedure drops a Data Redaction policy by removing a masking policy from the table or view

Table – 1: Content of DBMS_REDACT Package

Some parameters of function_type we use in this blog examples:

Parameters of function_type	Value	Description
NONE	0	No redaction
FULL	1	Redact to fixed values
PARTIAL	2	Partial redaction, redact a portion of the column data
RANDOM	4	Random redaction, each query results in a different random value

Table – 2: Values for function_type Parameter of DBMS_REDACT.ADD_POLICY

Some Action Parameters of DBMS_REDACT.ALTER_POLICY we use in this blog examples:

Parameters of action	Description
ADD_COLUMN	Add a column to the redaction policy
DROP_COLUMN	Drop a column from the redaction policy
MODIFY_COLUMN	Modify a column in the redaction policy to change the redaction function_type or the function_parameters

Table – 3: Values for action Parameter of DBMS_REDACT.ALTER_POLICY

In order to perform the test operations, first a table will be created and some values will be inserted into table.
USER LIST:
DBMASTER USER – Redacting user
SEVAYYILMAZ USER- JUST READONLY USER (will see the data masked

And also, we need the EXECUTE privilege (DBMASTER user) on the DBMS_REDACT package in order to execute its subprograms.

CREATE USER DBMASTER IDENTIFIED BY <password>;
GRANT EXECUTE ON SYS.DBMS_REDACT TO DBMASTER;
GRANT DBA TO DBMASTER;

CREATE USER SEVAYYILMAZ IDENTIFIED BY <password>;
GRANT CONNECT TO SEVAYYILMAZ;
GRANT RESOURCE TO SEVAYYILMAZ;
ALTER USER SEVAYYILMAZ DEFAULT ROLE ALL;
GRANT SELECT ANT TABLE TO SEVAYYILMAZ;

---

The following grant must be given to the user with whom the redaction process will be performed. Connect DBMASTER user;

grant execute on sys.dbms_redact to DBMASTER;

create table payment_details (
  id          number       not null,
  customer_id number       not null,
  card_no     number       not null,
  card_string varchar2(19) not null,
  expiry_date date         not null,
  sec_code    number       not null,
  valid_date  date,
  constraint payment_details_pk primary key (id)
);

insert into payment_details values (1, 1001, 1234123412341234, '1234-1234-1234-1234', trunc(add_months(sysdate,12)), 123, null);
insert into payment_details values (2, 1002, 2345234523452345, '2345-2345-2345-2345', trunc(add_months(sysdate,12)), 234, null);
insert into payment_details values (3, 1003, 3456345634563456, '3456-3456-3456-3456', trunc(add_months(sysdate,12)), 345, null);
insert into payment_details values (4, 1004, 4567456745674567, '4567-4567-4567-4567', trunc(add_months(sysdate,12)), 456, null);
insert into payment_details values (5, 1005, 5678567856785678, '5678-5678-5678-5678', trunc(add_months(sysdate,12)), 567, null);
insert into payment_details values (6, 1006, 6786786786786786, '6786-6786-6786-6786', trunc(add_months(sysdate,12)), 678, null);
insert into payment_details values (7, 1007, 7897897897897891, '7897-7897-7897-7897', trunc(add_months(sysdate,12)), 789, null);
insert into payment_details values (8, 1008, 8910111213141516, '8910-8910-8910-8910', trunc(add_months(sysdate,12)), 910, null);
insert into payment_details values (9, 1009, 9998887776665554, '9998-9998-9998-9998', trunc(add_months(sysdate,12)), 112, null);
commit;

---

Add Policy:

begin
  dbms_redact.add_policy(
    object_schema =>  schema_name,
    object_name   => 'table_name',
    column_name   => 'column_name',
    policy_name   => 'policyname_whatyouwant',
    function_type => dbms_redact.functiontype
  );
end;
/

Full Redaction:

A full redaction policy is placed on the CARD_NO column with an expression of “1=1”.

begin
  dbms_redact.add_policy(
    object_schema =>  user,
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => dbms_redact.full,
    expression    => '1=1'
  );
end;
/

---

Partial Redaction:
function_parameters is display 1st to 12th digits as 1.

begin
  dbms_redact.alter_policy (
    object_schema       => user,
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => dbms_redact.modify_column,
    column_name         => 'card_no',
    function_type       => dbms_redact.partial,
    function_parameters => '1,1,12'
  );
end;
/

---

Another Example: function_parameters is display 1st to 12th digits as 6.

begin
  dbms_redact.alter_policy (
    object_schema       => user,
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => dbms_redact.modify_column,
    column_name         => 'card_no',
    function_type       => dbms_redact.partial,
    function_parameters => '6,1,12'
  );
end;
/

---

Let’s give an another example:
Show the values of the digits from 1 to 12 with a # sign.

begin
  dbms_redact.alter_policy (
    object_schema       => user,
    object_name         => 'payment_details',
    policy_name         => 'redact_card_info',
    action              => dbms_redact.add_column,
    column_name         => 'card_string',
    function_type       => dbms_redact.partial,
    function_parameters => 'vvvvfvvvvfvvvvfvvvv,vvvv-vvvv-vvvv-vvvv,#,1,12'
  );
end;
/

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

SEVAY YILMAZ
Database Operations Manager