Data Masking by Database Role Authority

In this note; I will show you how you can hide sensitive data from users by special role authorization in Oracle database. We will create a special role HIDE_COLUMN. If this role is granted to any Oracle database user, sensitive data will be masked for these users. To mask sensitive column, Virtual Private Database (VPD) and Oracle Data Reduction feature will be used. VPD is a free feature but Oracle Data Redaction is a part of the Oracle Advanced Security and you have to pay for it.

First; Create environment and create special role HIDE Column

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

connect system/oracle


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



Create role HIDE_COLUMN;
Grant HIDE_COLUMN to user_01;

Now, Create policy function policy_rule and create policy POLICY_2 to hide CARD_NO column of CARD_TABLE with VPD. The important point is to use DBMS_SESSION.SESSION_IS_ROLE_ENABLED function for Oracle 19c database

Connect system/oracle

create or replace function policy_rule(obj_owner in varchar2, obj_name in varchar2) return varchar2
as
   predicate varchar2(200);
begin

predicate := NULL;
   if DBMS_SESSION.SESSION_IS_ROLE_ENABLED('HIDE_COLUMN')  
   then
     predicate := '1=2';
   end if;

   return predicate;
end;
/


BEGIN
    DBMS_RLS.ADD_POLICY (object_schema     => 'APPUSER',
                         object_name       => 'CARD_TABLE',
                         policy_name       => 'POLICY_2',
                         function_schema   => 'SYSTEM',
                         policy_function   => 'POLICY_RULE',
                         statement_types   => 'SELECT',
                         sec_relevant_cols => 'CARD_NO',
                         sec_relevant_cols_opt=> DBMS_RLS.ALL_ROWS
                         );
COMMIT;
END;
/

Now we will redact HOLDER column of CARD_TABLE by Oracle Data Redaction. To check the users is granted to HIDE_COLUMN role, we use SYS_CONTEXT(‘SYS_SESSION_ROLES’,’HIDE_COLUMN’) function

Connect system/oracle

BEGIN  DBMS_REDACT.add_policy( 
 object_schema => 'APPUSER', 
 object_name => 'CARD_TABLE', 
 column_name => 'HOLDER', 
 policy_name => 'REDACT_CARDNO_DATA', 
 function_type => DBMS_REDACT.full, 
 expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''HIDE_COLUMN'')=''TRUE'' '); 
 commit;
END; 
/

Connect with ad-hoc user; USER_01 and check that CARD_NO and HOLDER column is hidden. Because user_01 is granted with HIDE_COLUMN role.

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
---------------------- --------------- ----------- --------
                                       31-07-2024     10000
                                       02-02-2024     30500
                                       27-01-2025     40500
                                       02-05-2024      5000
                                       26-07-2025      7500

If we revoke HIDE_COLUMN role from user_01, User_01 can access the sensitive data. Simply If you want to hide sensitive data from any user, you should grant HIDE_COLUMN to this user.

connect system/oracle

Revoke HIDE_COLUMN from user_01;

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

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

Data Redaction Part – 2 Full Redaction

At this note ; we will start to work on Data Redaction by explaining Full Redaction . If you want to get a brief introduction about Data Redaction ; you can read the first  part of this note.

Let’s start with Full Redaction.

Put in a simple ; in Full Redaction ; The table columns are completely masked.
Numeric columns become 0 and character columns become a space character.

Let’s show it with an example. In the example ; we will redact salary column (SAL) column of  SCOTT.EMP table.  First start our application user. We will use this user to check how SAL column  is redacted.

 

Now  check the original table. ( The emp table of Scott user ).  Before redaction all columns are visible as you see.

 

 

Now by using DBMS_REDACT package we will redact SAL column. Simply by using add_policy procedure we can create a policy and add a column to this policy. The parameters of add_policy procedures are self explained. Therefore I am not given any explanation about these parameters. But the only expression parameter is very important.  The expression parameter should contain a logical expression. If the expression is TRUE, column mentioned in the column_name parameter  will be  redacted. If the expression is FALSE , the column will not redacted. In this example I use a simple logical expression which is ‘1=1’. As you know It is always TRUE. This redacts SAL column for all users ( except users who have EXEMPT REDACTION POLICY privilege – I will explain it in the next note)

Now Check redaction polices by querying REDACTION_POLICIES view.

 

If you want to query which column is redacted , you can use REDACTION_COLUMNS view.

 

Lets’s check Data redaction is working on or not. You can check it with  previously created user USERA01. Connect with this user and query; SCOTT.EMPT table

As you see; All SAL column is redacted with 0. As a result ; USERA01 can not see the salary of employees.

I hope that this small example will be a good start for  the Data Redaction. I will explain this subject with examples in the next notes.

You can find all these scripts at github

And one more thing ; If you want to get much more information about Data Redaction; you can read my book . It is written by Turkish language  but the examples will be very beneficial.

Anil Akduygu

 

 

 

 

Introduction to Data Redaction part -1

Data Redaction option is a part of Oracle Advanced Security.  Oracle Advanced Security ( OAS ) can be used after Oracle 11.2.0.4 version and  it is a licensed product. You do not need to make any special installation to use Oracle Advanced Security. Just there are some database codes ( packages) to use OAS options. In this note and the following notes I will show you can use Data Redaction option of OAS .  First let’s me explain what is Data Redaction and where you can use it.

Data Redaction can bu used for  masking sensitive data by using special security policies. Data is masked at the database level and can not be seen on the network as well. The important point is; you do not need to change your applications to mask data. Data is masked for only special logins which are defined by security policies. Applications can reach data as usual. Data can be seen from applications.  Especially ; This product is used to hide the data when connected from ad-hoc query tools ( like SQLPlus or TOAD ).

With the Data Redaction option, we use one of the following methods to hide data.

Full Redaction :  The table columns are completely masked.
numeric columns become 0 and varchar columns become a space character.

Partial Redaction : Only a certain part of the a column is redacted.
For example, a part of the column is masked with ‘*’ character

Regular Expressions:  It is used to mask  a specific part of the data for character columns of different sizes

Random Redaction:  Depending on the type of colon, the data  is redacted randomly.

No Redaction :  In this type of redaction, there is no change in the data. This redaction is used to test the effect of the redaction on the database performance.

Now I gave a brief introduction on the Data Redaction. On the following notes ; we will work on all these redaction types with examples.

Thanks.

Anıl Akduygu.

 

Masking Data according to User roles in Oracle Database with Data Redaction

At this note I will show to you how you can mask data according to the role of users by using Data Redaction. Actually I will not explain Data Redaction in detail , I assume that you already know about Data Redaction. But in the future I will give detailed information about Oracle Advanced Security and Data Redaction.

Now in this note we will use Data Redaction to mask data according to session roles. Virtual Private database can be used instead of Data Redaction. I will show it in the another note.

I will explain this subject with a sample. In the sample we have an user which holds data (rep_user) , application user ( app_user which can see all data ) and inq_user ( data will be masked for this user) . At the example; Only users which have a special role (redact_role) can not be affected by data redaction policy.

Let’s build up the environment and create users.

First ;  Create rep_user

Create app_user

Create inq_user and redact_role

 

We simple create a table with one  column and we mask this column with data redaction.

Insert some data to this table and grant this table to app_user and inq_user

Now app_user and inq_user can select this table as below

Now create Data Redaction policy to hide data

And according to our policy only users with redact_role can not be affected by this policy.

To do this grant redact_role to app_user;

 

And now app_user can see the masked data but other users( inq_user)  can not reach this data.

Lets’s test it

Masked numeric data can be seen as 0  in Data Redaction ( on default).

As you see we can hide data according to user’s role by using SYS_CONTEXT function. You can change this case according to your needs.

you can reach all scripts from github

Thanks.

Anıl Akduygu