Oracle Data Reduction – Partial Reduction for Date Data Types

This note is the sequel of Oracle Data Reduction – Full Redaction article. At the first article, I gave a brief introduction about Oracle Data Reduction feature and I implemented full redaction on card_no column . Now we will continue to redact columns which contains date data types.

At the sample ; Appuser contains Card no data and simply application tables. Appuser should reach all data without any restriction. Ad-hoc user were created with the name of USER_* . We will define all security policies against to ad-hoc users. Now create all these users , tables and data.

Connect system/oracle

Create user appuser identified by appuser ;

alter user appuser quota unlimited on users;

grant resource to appuser;
grant connect to appuser;

connect appuser/appuser

CREATE TABLE APPUSER.CARD_TABLE
(
  CARD_NO      VARCHAR2(20 BYTE),
  HOLDER       VARCHAR2(16 BYTE),
  EXPIRY_DATE  DATE,
  LIMIT        NUMBER
)
TABLESPACE USERS;

Grant select on APPUSER.CARD_TABLE to public;

SET DEFINE OFF;
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('1234-5678-3456-7845', 'John Carpenter', TO_DATE('7/31/2024 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 10000);
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('6789-6467-5567-8967', 'Alvin Newton', TO_DATE('2/2/2024 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 30500);
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('1243-9087-4389-8967', 'Joseph Huges', TO_DATE('1/27/2025 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 40500);
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('1238-9685-4589-6543', 'Monica Woodo', TO_DATE('5/2/2024 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 5000);
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('1245-5620-1256-3412', 'Susen Senior', TO_DATE('7/26/2025 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 7500);

commit;

connect system/oracle


Create user user_01 identified by oracle ;
grant connect to user_01;

Create user user_02 identified by oracle ;
grant connect to user_02;

In order to partially hide the data in the date columns, we assign a fixed value to the month, day, year, hour and minute fields.

The following format is used for this.

m: month field is hidden. To omit redaction, enter an uppercase M.
d : day field is hidden. To omit redaction, enter an uppercase D.
y : year field is hidden. To omit redaction, enter an uppercase Y.
h : time field is hidden. To omit redaction, enter an uppercase H.
m: minute field is hidden. To omit redaction, enter an uppercase M.
s: seconds field is hidden. To omit redaction, enter an uppercase S.

When these letters are used in capital letters; editing is neglected. Let’s show this with an example; make redaction on expiry_date column . In this redaction all expiry_date column is shown as 01-01-2020

Now check database repository to see defined redaction policies. Just now, we created one policy on Card_Table. REDACTION_POLICIES view is used for selecting redaction policies.

connect system/oracle

Set linesize 200

Column Object_owner        format a10
Column Object_name         format a15
Column policy_name         format a30
Column enable                   format a10
 
SELECT object_owner, object_name, policy_name,enable
  FROM REDACTION_POLICIES
 WHERE object_name NOT LIKE 'BIN%';

OBJECT_OWN OBJECT_NAME     POLICY_NAME                    ENABLE
---------- --------------- ------------------------------ ----------
APPUSER    CARD_TABLE      REDACT_CARDNO_DATA             YES

connect system/oracle

BEGIN  DBMS_REDACT.alter_policy( 
 object_schema => 'APPUSER', 
 object_name => 'CARD_TABLE', 
 column_name => 'EXPIRY_DATE', 
 policy_name => 'REDACT_CARDNO_DATA',
 action => DBMS_REDACT.add_column,
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => 'm1d1y2000'); 
 commit;
END; 
/

Now Check REDACTION_COLUMNS view to see the definitions of the redaction.

Set linesize 200

Column Object_owner        format a10
Column Object_name         format a15
Column Column_name         format a15
Column Function_Parameters format a50

SELECT object_owner,
       object_name,
       column_name,
       function_type,
       function_parameters
  FROM REDACTION_COLUMNS
 WHERE object_name NOT LIKE 'BIN%';


OBJECT_OWN OBJECT_NAME     COLUMN_NAME     FUNCTION_TYPE               FUNCTION_PARAMETERS
---------- --------------- --------------- --------------------------- --------------------------------------------------
APPUSER    CARD_TABLE      CARD_NO         FULL REDACTION
APPUSER    CARD_TABLE      EXPIRY_DATE     PARTIAL REDACTION           m1d1y2000

Connect with ad-hoc user and select card_table. Look at Expiry_date column 01-01-2000

connect user_01/oracle

alter session set nls_date_format = 'DD-MM-YYYY';

Column Card_No format a22
Column Holder  format a15
Column Expiry_Date format a11
Column Limit format 9999999


select * from appuser.card_table;


CARD_NO                HOLDER          EXPIRY_DATE    LIMIT
---------------------- --------------- ----------- --------
                       John Carpenter  01-01-2000     10000
                       Alvin Newton    01-01-2000     30500
                       Joseph Huges    01-01-2000     40500
                       Monica Woodo    01-01-2000      5000
                       Susen Senior    01-01-2000      7500

Now make another redaction on expiry_date column. At this redaction we onlt hide month and , day data. Year data will be visible with this parameter. function_parameters => ‘m1d1Y‘

connect system/oracle

BEGIN  DBMS_REDACT.alter_policy( 
 object_schema => 'APPUSER', 
 object_name => 'CARD_TABLE', 
 column_name => 'EXPIRY_DATE', 
 policy_name => 'REDACT_CARDNO_DATA',
 action => DBMS_REDACT.modify_column,
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => 'm1d1Y'); 
 commit;
END; 
/

Check the redacted columns with redaction_columns view.

connect system/oracle

Column Object_owner        format a10
Column Object_name         format a15
Column Column_name         format a15
Column Function_Parameters format a50

SELECT object_owner,
       object_name,
       column_name,
       function_type,
       function_parameters
  FROM REDACTION_COLUMNS
WHERE object_name NOT LIKE 'BIN%';



OBJECT_OWN OBJECT_NAME     COLUMN_NAME     FUNCTION_TYPE               FUNCTION_PARAMETERS
---------- --------------- --------------- --------------------------- --------------------------------------------------
APPUSER    CARD_TABLE      CARD_NO         FULL REDACTION
APPUSER    CARD_TABLE      EXPIRY_DATE     PARTIAL REDACTION           m1d1Y

After the reduction is defined, Select the table with ad-hoc users.

connect user_01/oracle

alter session set nls_date_format = 'DD-MM-YYYY';

Column Card_No format a22
Column Holder  format a15
Column Expiry_Date format a11
Column Limit format 9999999


select * from appuser.card_table;



CARD_NO                HOLDER          EXPIRY_DATE    LIMIT
---------------------- --------------- ----------- --------
                       John Carpenter  01-01-2024     10000
                       Alvin Newton    01-01-2024     30500
                       Joseph Huges    01-01-2025     40500
                       Monica Woodo    01-01-2024      5000
                       Susen Senior    01-01-2025      7500

To drop redaction on HOLDER columns use Alter_policy procedure with ;

action => DBMS_REDACT.drop_column parameter

connect system/oracle
       
BEGIN  DBMS_REDACT.alter_policy( 
 object_schema => 'APPUSER', 
 object_name => 'CARD_TABLE', 
 column_name => 'EXPIRY_DATE', 
 policy_name => 'REDACT_CARDNO_DATA',
 action => DBMS_REDACT.drop_column ); 
 commit;
END; 
/

Check the redaction view REDACTION_COLUMNS to control of the operation.

connect system/oracle

Set linesize 200

Column Object_owner        format a10
Column Object_name         format a15
Column Column_name         format a10
Column Function_Parameters format a50

SELECT object_owner,
       object_name,
       column_name,
       function_type,
       function_parameters
  FROM REDACTION_COLUMNS
WHERE object_name NOT LIKE 'BIN%';

OBJECT_OWN OBJECT_NAME     COLUMN_NAM FUNCTION_TYPE             FUNCTION_PARAMETERS
---------- --------------- ---------- ------------------------- --------------------------------------------------
APPUSER    CARD_TABLE      CARD_NO    FULL REDACTION

If you want to get much more for support to hide sensitive data in your Oracle databases you can contact with me.

Yusuf Anıl Akduygu

anil.akduygu@arbosecurity.com

yusufanilakduygu@gmail.com

Oracle Data Reduction – Partial Reduction for Character Data Types

This note is the sequel of Oracle Data Reduction – Partial Reduction for Numbers article. At the first article, I gave a brief introduction about Oracle Data Reduction feature and I implemented full redaction on card_no column . Now we will continue to redact columns which contains character data types.

At the sample ; Appuser contains Card no data and simply application tables. Appuser should reach all data without any restriction. Ad-hoc user were created with the name of USER_* . We will define all security policies against to ad-hos users. Now create all these users , tables and data.

Connect system/oracle

Create user appuser identified by appuser ;

alter user appuser quota unlimited on users;

grant resource to appuser;
grant connect to appuser;

connect appuser/appuser

CREATE TABLE APPUSER.CARD_TABLE
(
  CARD_NO      VARCHAR2(20 BYTE),
  HOLDER       VARCHAR2(16 BYTE),
  EXPIRY_DATE  DATE,
  LIMIT        NUMBER
)
TABLESPACE USERS;

Grant select on APPUSER.CARD_TABLE to public;

SET DEFINE OFF;
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('1234-5678-3456-7845', 'John Carpenter', TO_DATE('7/31/2024 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 10000);
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('6789-6467-5567-8967', 'Alvin Newton', TO_DATE('2/2/2024 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 30500);
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('1243-9087-4389-8967', 'Joseph Huges', TO_DATE('1/27/2025 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 40500);
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('1238-9685-4589-6543', 'Monica Woodo', TO_DATE('5/2/2024 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 5000);
Insert into APPUSER.CARD_TABLE
   (CARD_NO, HOLDER, EXPIRY_DATE, LIMIT)
 Values
   ('1245-5620-1256-3412', 'Susen Senior', TO_DATE('7/26/2025 2:37:57 PM', 'MM/DD/YYYY HH:MI:SS AM'), 7500);

commit;

connect system/oracle


Create user user_01 identified by oracle ;
grant connect to user_01;

Create user user_02 identified by oracle ;
grant connect to user_02;


 

Now check database repository to see already defined redaction policies. we already created one policy on Card_Table. REDACTION_POLICIES view is used for selecting redaction policies

connect system/oracle

BEGIN  DBMS_REDACT.add_policy( 
 object_schema => 'APPUSER', 
 object_name => 'CARD_TABLE', 
 column_name => 'CARD_NO', 
 policy_name => 'REDACT_CARDNO_DATA', 
 function_type => DBMS_REDACT.full, 
 expression => 'SUBSTR( SYS_CONTEXT(''USERENV'',''SESSION_USER''), 1,5 ) = ''USER_''  '); 
 commit;
END; 
/

Set linesize 200

Column Object_owner        format a10
Column Object_name         format a15
Column policy_name         format a30
Column enable              format a10
 
SELECT object_owner, object_name, policy_name,enable
  FROM REDACTION_POLICIES
 WHERE object_name NOT LIKE 'BIN%';

OBJECT_OWN OBJECT_NAME     POLICY_NAME                    ENABLE
---------- --------------- ------------------------------ ----------
APPUSER    CARD_TABLE      REDACT_CARDNO_DATA             YES

Now create a redaction on HOLDER column. At this redaction ; The value # is placed between the first and fifth columns.
To do this function_parameters parameter is used

The syntax of this parameter is given below

function_parameters => ‘Input format, Output format, Mask character ,Starting digit position, Ending digit position’

Input format: Defines how the data is currently formatted. Enter V for each character that potentially can be redacted, such as all of the digits in a credit card number. Enter F for each character that you want to format using a formatting character.

Output format: Defines how the displayed data should be formatted. Enter V for each character to be potentially redacted. Replace each F character in the input format with the character that you want to use for the displayed output

Mask character: Specifies the character to be used for the redaction

Starting digit position: Specifies the starting V digit position for the redaction.

Ending digit position: Specifies the ending V digit position for the redaction.

function_parameters parameter should be defined like that
function_parameters => ‘VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,#,1,5’

The important point is you have to use alter_policy procedure with action => DBMS_REDACT.add_column
parameter to add a new redaction to CARD_TABLE.

connect system/oracle

BEGIN  DBMS_REDACT.alter_policy( 
 object_schema => 'APPUSER', 
 object_name => 'CARD_TABLE', 
 column_name => 'HOLDER', 
 policy_name => 'REDACT_CARDNO_DATA',
 action => DBMS_REDACT.add_column,
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => 'VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,#,1,5'); 
 commit;
END; 
/

Now Check REDACTION_COLUMNS view to see the definitions of the redaction.

connect system/oracle

Set linesize 200

Column Object_owner        format a10
Column Object_name         format a15
Column Column_name         format a10
Column Function_Parameters format a50

SELECT object_owner,
       object_name,
       column_name,
       function_type,
       function_parameters
  FROM REDACTION_COLUMNS
 WHERE object_name NOT LIKE 'BIN%';



OBJECT_OWN OBJECT_NAME     COLUMN_NAM FUNCTION_TYPE             FUNCTION_PARAMETERS
---------- --------------- ---------- ------------------------- --------------------------------------------------
APPUSER    CARD_TABLE      HOLDER     PARTIAL REDACTION         VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,#,1,5
APPUSER    CARD_TABLE      CARD_NO    FULL REDACTION

After the reduction is defined, Select the table with ad-hoc users. HOLDER column values are redacted with #

connect user_01/oracle

alter session set nls_date_format = 'DD-MM-YYYY';

Column Card_No format a22
Column Holder  format a15
Column Expiry_Date format a11
Column Limit format 9999999


select * from appuser.card_table;


CARD_NO                HOLDER          EXPIRY_DATE    LIMIT
---------------------- --------------- ----------- --------
                       #####Carpenter  31-07-2024     77000
                       ##### Newton    02-02-2024     77500
                       #####h Huges    27-01-2025     77500
                       #####a Woodo    02-05-2024      7700
                       ##### Senior    26-07-2025      7700

Let’s change the redaction on CARD NO column. In this redaction # value is placed between first and tenth column.
To do this function_parameters parameter should be defined like that

function_parameters => ‘VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,#,1,10’)

And because a policy already was defined for this column; action parameter should be like that
action => DBMS_REDACT.modify_column

connect system/oracle


BEGIN  DBMS_REDACT.alter_policy( 
 object_schema => 'APPUSER', 
 object_name => 'CARD_TABLE', 
 column_name => 'CARD_NO', 
 policy_name => 'REDACT_CARDNO_DATA',
 action => DBMS_REDACT.modify_column,
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => 'VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,#,1,10'); 
 commit;
END; 

/

connect system/oracle

Column Object_owner        format a10
Column Object_name         format a15
Column Column_name         format a10
Column Function_Parameters format a50

SELECT object_owner,
       object_name,
       column_name,
       function_type,
       function_parameters
  FROM REDACTION_COLUMNS
WHERE object_name NOT LIKE 'BIN%';



OBJECT_OWN OBJECT_NAME     COLUMN_NAM FUNCTION_TYPE             FUNCTION_PARAMETERS
---------- --------------- ---------- ------------------------- --------------------------------------------------
APPUSER    CARD_TABLE      HOLDER     PARTIAL REDACTION         VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,#,1,5      

APPUSER    CARD_TABLE      CARD_NO    PARTIAL REDACTION         VVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVV,#,1,10   

After the reduction is defined, Select the table with ad-hoc users.

connect user_01/oracle

alter session set nls_date_format = 'DD-MM-YYYY';

Column Card_No format a22
Column Holder  format a15
Column Expiry_Date format a11
Column Limit format 9999999


select * from appuser.card_table;


CARD_NO                HOLDER          EXPIRY_DATE    LIMIT
---------------------- --------------- ----------- --------
##########3456-7845    #####Carpenter  31-07-2024     77000
##########5567-8967    ##### Newton    02-02-2024     77500
##########4389-8967    #####h Huges    27-01-2025     77500
##########4589-6543    #####a Woodo    02-05-2024      7700
##########1256-3412    ##### Senior    26-07-2025      7700

To drop redaction on HOLDER columns use Alter_policy procedure with ;

action => DBMS_REDACT.drop_column parameter

connect system/oracle
       
DBMS_REDACT.Alter_policy(
        object_schema => 'APPUSER',
        object_name => 'CARD_TABLE',
        column_name => 'HOLDER',
        policy_name => 'REDACT_CARDNO_DATA',
        action => DBMS_REDACT.drop_column);
        commit;


END;
/

Check the redaction view REDACTION_COLUMNS to control of the operation.

connect system/oracle

Set linesize 200

Column Object_owner        format a10
Column Object_name         format a15
Column Column_name         format a10
Column Function_Parameters format a50

SELECT object_owner,
       object_name,
       column_name,
       function_type,
       function_parameters
  FROM REDACTION_COLUMNS
WHERE object_name NOT LIKE 'BIN%';

OBJECT_OWN OBJECT_NAME     COLUMN_NAM FUNCTION_TYPE             FUNCTION_PARAMETERS
---------- --------------- ---------- ------------------------- --------------------------------------------------
APPUSER    CARD_TABLE      CARD_NO    FULL REDACTION

If you want to get much more for support to hide sensitive data in your Oracle databases you can contact with me.

Yusuf Anıl Akduygu

anil.akduygu@arbosecurity.com

yusufanilakduygu@gmail.com