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
Database Security 
Reply