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

Oracle Data Reduction – Partial Reduction for Numbers

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 numbers.

At the sample l; 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 LIMIT column. At this redaction ; The value 0 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 => ‘Mask character,Starting digit position, Ending digit position’ .

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.

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

connect system/oracle

BEGIN  DBMS_REDACT.alter_policy( 
 object_schema => 'APPUSER', 
 object_name => 'CARD_TABLE', 
 column_name => 'LIMIT', 
 policy_name => 'REDACT_CARDNO_DATA',
 action => DBMS_REDACT.add_column,
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => '0,1,5'); 
 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 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      LIMIT      PARTIAL REDACTION         0,1,5                                                
APPUSER    CARD_TABLE      CARD_NO    FULL REDACTION                                                                  

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

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  31-07-2024         0
                       Alvin Newton    02-02-2024         0
                       Joseph Huges    27-01-2025         0
                       Monica Woodo    02-05-2024         0
                       Susen Senior    26-07-2025         0

Let’s change the redaction on LIMIT column. In this redaction 7 value is placed between first and second column.
To do this function_parameters parameter defined like that

function_parameters => ‘7,1,2’

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 => 'LIMIT', 
 policy_name => 'REDACT_CARDNO_DATA',
 action => DBMS_REDACT.modify_column,
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => '7,1,2'); 
 commit;
END; 
/

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      LIMIT      PARTIAL REDACTION         7,1,2
APPUSER    CARD_TABLE      CARD_NO    FULL REDACTION

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  31-07-2024     77000
                       Alvin Newton    02-02-2024     77500
                       Joseph Huges    27-01-2025     77500
                       Monica Woodo    02-05-2024      7700
                       Susen Senior    26-07-2025      7700

To drop redaction on LIMIT column use Alter_policy procedure with ;

action => DBMS_REDACT.drop_column parameter

BEGIN
       DBMS_REDACT.Alter_policy(
        object_schema => 'APPUSER',
        object_name => 'CARD_TABLE',
        column_name => 'LIMIT',
        policy_name => 'REDACT_CARDNO_DATA',
        action => DBMS_REDACT.drop_column);
        commit;
        END;
 /

Check the redaction view REDACTION_COLUMNS to control of the operation. At the next note I will give you much more information about redacting Character data types.

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 Redaction – Full Redaction

Data Redaction is a part of Oracle Advanced Security feature. Oracle Advanced Security contains Transparent Data Encryption (TDE) and Data Redaction options. By combining these two options; Oracle offers powerful data protection capabilities with one module. Data Redaction is the process of obfuscating or hiding table columns according to a defined security policies ( SQL expressions ). With Data Redaction, we protect sensitive data in our databases from ad-hoc users without making any changes in applications. Data Redaction works transparently to the applications and other database configurations. When you redact any column in the database, actually no data is encrypted in the tablespaces. Only the users are defined on the security policy can see the column in a redacted way ( or can not see data according to redaction type). If you really want to encrypt the data at the tablespaces you have to use TDE.

You can redact column the columns with data redaction by following methods.

• Full redaction
• Partial redaction
• Regular expressions
• Random redaction
• No redaction

In our case we will fully redact ( hide completely) card no data in our card table. Now we can start our demo.

Creating Application user which holds the card table.

Connect system/oracle

Create user appuser identified by appuser ;
alter user appuser quota unlimited on users;
grant resource to appuser;
grant connect to appuser;

Creating Card table and inserting sample data

Drop table Card_table;

Create table card_table ( 
   Card_No             Varchar2(20), 
   Holder              Varchar2(16), 
   Expiry_Date         Date,
   Limit               Number );

insert into card_table values ('1234-5678-3456-7845','John Carpenter',sysdate+540,10000);    
insert into card_table values ('6789-6467-5567-8967','Alvin Newton',  sysdate+360,30500);    
insert into card_table values ('1243-9087-4389-8967','Joseph Huges',  sysdate+720,40500);    
insert into card_table values ('1238-9685-4589-6543','Monica Woodo',  sysdate+450,5000);    
insert into card_table values ('1245-5620-1256-3412','Susen Senior',  sysdate+900,7500);    


commit; 

Now check the table with ad-hoc user. Now card no data is not redacted yet.

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
---------------------- --------------- ----------- --------
1234-5678-3456-7845    John Carpenter  31-07-2024     10000
6789-6467-5567-8967    Alvin Newton    02-02-2024     30500
1243-9087-4389-8967    Joseph Huges    27-01-2025     40500
1238-9685-4589-6543    Monica Woodo    02-05-2024      5000
1245-5620-1256-3412    Susen Senior    26-07-2025      7500

Now It is the time to redact card no column. DBMS_REDACT package is used for redacting table columns.
Add_Policy is the procedure of DBMS_REDACT to define a new redaction in the database.
You can understand the usage of parameters from the name of the parameters.

function_type parameter :
Type of redaction function to use. Possible values are:

• DBMS_REDACT.NONE
• DBMS_REDACT.FULL (default)
• DBMS_REDACT.PARTIAL
• DBMS_REDACT.RANDOM
• DBMS_REDACT.REGEXP

expression parameter:
Boolean expression; using either the SYS_CONTEXT function or 1=1. Redaction takes place only if this policy expression evaluates to TRUE.

The important point is you can not use all functions in the database for the expression parameter. There are some restrictions to define expression parameter.
https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/configuring-oracle-data-redaction-policies.html#GUID-CF59A02C-1DE8-439B-BC1C-7235915C6D23

You can only use below functions in the expression parameter:

• SYS_CONTEXT
• SUBSTR
• Oracle Application Express functions
• Oracle Label Security functions

Use only the following operators: AND, OR, IN, NOT IN, =, !=, <>, <, >, >=, <=
Do not use user-created functions in the expression parameter; this is not permitted.

In our example; ad-hoc user names start with USER_* . Therefore we defined our expression like below:

expression => ‘SUBSTR( SYS_CONTEXT(”USERENV”,”SESSION_USER”), 1,5 ) = ”USER_” ‘);

This expressions returns TRUE for only usernames starts with USER_ and this causes ; redaction takes place.

And now ; We will define Full Redaction

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; 
/

After redaction is defined check the table with ad-hoc user.

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  31-07-2024     10000
                       Alvin Newton    02-02-2024     30500
                       Joseph Huges    27-01-2025     40500
                       Monica Woodo    02-05-2024      5000
                       Susen Senior    26-07-2025      7500

As you see; Ad-hoc users ca not see Card No column any more. Because of the Full Redaction , all data is redacted. Other redaction types will be explained in the next notes.

Check the redacted table with application user:

connect appuser/appuser

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
---------------------- --------------- ----------- --------
1234-5678-3456-7845    John Carpenter  31-07-2024     10000
6789-6467-5567-8967    Alvin Newton    02-02-2024     30500
1243-9087-4389-8967    Joseph Huges    27-01-2025     40500
1238-9685-4589-6543    Monica Woodo    02-05-2024      5000
1245-5620-1256-3412    Susen Senior    26-07-2025      7500

Application user is not affected with redaction policy. No changes in the database and applications have been made.

In order to drop redaction policy. We use drop_policy procedure of DBMS_REDACT package.

Connect system/oracle

BEGIN
       DBMS_REDACT.DROP_POLICY(
        object_schema => 'APPUSER',
        object_name => 'CARD_TABLE',
        policy_name => 'REDACT_CARDNO_DATA');
        commit;
        END;
/
 

After that; ad-hoc users can access to card no data.

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 Virtual Private Database (VPD) to Hide Sensitive Columns

In this note; I will show you how we can hide sensitive columns by using Virtual Private Database (VPD) feature in Oracle databases. Oracle Virtual Private Database (VPD) feature filters access to data according to user defined policies. A policy is a simple function to filters access to any table. We can define VPD in horizontally or vertically. In this note we will use VPD vertically to hide a column from ad-hoc user access. VPD can be used from Oracle 11g Enterprise Edition and it is a free feature. At the same time we can user Data Redaction feature of Oracle databases but it is a part of Oracle Advanced Security. In this case you have to pay for Oracle Advanced Security option. Therefore VPD has been chosen by many companies.

Now turn back to our case; to show you how we can use VPD to hide sensitive columns; In this example our aim is to hide credit card number column from ad-hoc access. In the example we will create a simple credit card table with a sensitive column ( credit card number ) in APPUSER . In the database we use some users ( ad-hoc users) to access the database directly from ad-hoc tools by sqlplus , TOAD or any sql editors. In our example these users start with USER- prefix and generally they will be created with the name of USER-01, USER-02 … Our application user name is APPUSER.

Creating APPUSER

Connect system/oracle

Create user appuser identified by appuser ;
alter user appuser quota unlimited on users;
grant resource to appuser;
grant connect to appuser;

Creating and Inserting Data to Card Table

Connect appuser/appuser

Create table card_table ( CardNo Varchar2(16), CardDetail varchar2(16) );

 insert into card_table values ('123456','card-1');
 insert into card_table values ('678964','card-2');
 insert into card_table values ('124343','card-3');
 insert into card_table values ('123896','card-4');
 insert into card_table values ('124556','card-5');

commit;

Creating Ad-hoc users

      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;

      grant select on appuser.card_table to public;

We can see cardno data by connecting database with ad-hoc users. We have not defined our security policy to hide card no data yet

connect user_01/oracle

 select * from appuser.card_table;

CARDNO           CARDDETAIL
---------------- ----------------
123456           card-1
678964           card-2
124343           card-3
123896           card-4
124556           card-5

Defining Security Function

Simply security policy is a function which returns to predicate TRUE or FALSE.
If the returning predicate is FALSE; the sensitive column is hidden.
If the returning predicate NULL or TRUE the sensitive column can be seen.

In our example we are trying to hide sensitive data from ad-hoc users whose user name starts with USER.

Don’t forget that ; Security function must have always two varchar2 parameters.
You can change this function according to your company needs. That’s the important point.

connect system/oracle
    
CREATE OR REPLACE function POLICY_COLUMN_MASKING(obj_owner in varchar2, obj_name in varchar2) 
    return varchar2
    as
      predicate varchar2(200);
      begin
      predicate := NULL;
      if SYS_CONTEXT(‘userenv’,’POLICY_INVOKER’) like ‘USER%’
      then
      predicate := ‘1=2’;
      end if;
      return predicate;
      end;
    /

Defining Security Policy

We use DBMS_RLS package to define VPD policies. ADD_POLICY procedure is used for defining adding VPD policies.
You can understand the usage of parameters from the name of the parameters.

Hidden table and column name are defined in object_schema and object_name parameter

    object_schema => 'APPUSER',
    object_name => 'CARD_TABLE

policy_name parameter is the name of the security policy. You can name it freely.

You can use function_schema and policy_function parameters to define which security function is used.
in our example; we created it before.

statement_types => ‘SELECT’ means this security policy is used for only SELECT function.

policy_type => dbms_rls.shared_context_sensitive is a complex parameter and leave it like that.
We will discuss it in the future notes.

In order to hide cardno column you must put the name of the column into sec_revevant_cols parameter.

sec_relevant_cols=> ‘CARDNO’

sec_relevant_cols_opt=>DBMS_RLS.ALL_ROWS this parameter must always take this value to hide columns. Do not change it.

Simply our package call should be like that;

Connect system/oracle
  
BEGIN
    DBMS_RLS.ADD_POLICY(
    object_schema => 'APPUSER',
    object_name => 'CARD_TABLE',
    policy_name => 'CARDNO_HIDE',
    function_schema => 'SYSTEM',
    policy_function => 'POLICY_COLUMN_MASKING',
    statement_types => 'SELECT',
    policy_type => dbms_rls.shared_context_sensitive,
    sec_relevant_cols=> 'CARDNO',
    sec_relevant_cols_opt=>DBMS_RLS.ALL_ROWS);
    commit;
    END;
  /

Selecting CARDNO Column again and It is hidden now.

After connecting with USER_01, we can not see cardno data anymore. Because the security policy
prevents accessing CARDNO column from USER_01

connect user_01/oracle

select * from appuser.card_table;

CARDNO   CARDDETAIL
------    ----------------
          card-1
          card-2
          card-3
          card-4
          card-5

The application user is not affected by Security Policy

After connecting with application user, we can access to cardno data. As you see; without changing application, sensitive data is hidden to ad-hoc connections.

connect appuser/appuser

select * from appuser.card_table;

CARDNO           CARDDETAIL
---------------- ----------------
123456           card-1
678964           card-2
124343           card-3
123896           card-4
124556           card-5

Disabling Security Policy

We can use DBMS_RLS.DROP_POLICY procedure to disable security policies. You can understand the usage of parameters from the name of the parameters.

Connect system/oracle

BEGIN
        DBMS_RLS.DROP_POLICY(
        object_schema => 'APPUSER',
        object_name => 'CARD_TABLE',
        policy_name => 'CARDNO_HIDE');
        commit;
        END;
/
 

After disabling the security policy ad-hos users can access to sensitive data

To check it; connect with a ad-hoc user and select CARD_TABLE

connect user_01/oracle

 select * from appuser.card_table;

CARDNO           CARDDETAIL
---------------- ----------------
123456           card-1
678964           card-2
124343           card-3
123896           card-4
124556           card-5

This simple demo shows you how you can use VPD feature to hide sensitive data in Oracle databases. I prepared this demo in Oracle 19c databases. But It works from 12c to 19c without any changes.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
         0

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