Oracle Data Masking and Subsetting allows you to secure access to your data. It supports multiple modes of anonymization, data masking, and data securitization methods with some predefined functions and templates. With Oracle Data Masking and Subsetting, you can easily turn your production database in protected storage with full control over the most precious asset; your data. It might seem a little complicated at a first glance but I will show you how easily you can use that.
There are two modes in which Oracle Data Masking and Subsetting can operate:
- In-Database
- In-Export
In-Database Mode
This is rather related to permanent storage and applied mostly on non-prod and test environments (copies of production). All changes are permanent and therefore highly NOT recommended in a production environment. I have already prepared a lightweight version of this with sample scripts in the article Anonymization after PROD to NONPROD syncing. Feel free to check out this article and start with the scripts provided there.
In-Export Mode
This mode is very suitable for production environments. When you have analysts or some other users accessing your production database and want to make sure they do not see personally identifiable information (aka PII) this is definitely one option to go with. It works on a near real-time basis when users are extracting data, they are encrypted on the way to their machines. In this article, I will focus on this method and will provide some simplified scripts for you to start with.
Implementation
There are three basic steps to implement data masking and submitting and therefore protect your data in production environments.
- Define the application data model
- Select masking criteria
- Create a data masking package
Some of the parts might overlay with the article Anonymization after PROD to NONPROD syncing, but that’s fine 🙂 The brighter side is, you will already have half of your work done 🙂
Very important to note is, that this approach (I am going to describe) works only with VIEWS! Not tables! Creating the correct setup in the DB is beneficial for multiple reasons. To read more about this approach deep dive into the article Smart Database Design (check the “Data Insulation” part).
Before the data are returned to the user, Oracle will check whether or not the user is allowed (based on a defined role) to access the plain-text data and either masks them or serves them unchanged.
Define the application data model
This action item is, as a matter of fact, the most challenging and tiresome. You have to analyze the whole data* model and identify all sensitive data you want to anonymize/protect. Once you have them all identified I suggest preparing the following format.
- mask_view – list of views
- owner, view_name, flag_active
- mask_view_column – list of sensitive columns
- owner, view_name, column_name, flag_enabled, pii_category, id_mask_template
- mask_user – list of all users
- user_name, flag_masked
- mask_template – list of masking templates
- id_mask_template, mask_type, mask_params
* If you have a correct license, you can use “Secure Test Data Management” in the Enterprise Manager to help you discover sensitive data in your database.
Select masking criteria
Here, you have to define how are you going to mask data. It refers to the “list of masking templates” table. You can create a list of criteria and their corresponding mask functions. You can, of course, start with some simple setup and enhance over time.
Create a data masking package
This is the “rewarding” part for us developers 🙂 In this part, you need to create a package which will run after every deployment to your system or just regularly to be sure everything is fine and secured.
This package will do several things:
- Update the table with users for masking
- Align role-based access according to the setup in the config table (mask_user.flag_masked)
- Loop through all selected views (mask_view and mask_view_column)
- Do several checks before we proceed to the actual masking
- the columns exist
- the view exists
- the view is not invalid
- Get the view’s DDL
- Check if the view is already masked
- if yes – skip
- Recreate the view with masked columns
- there will be a function wrapped around that column (see later)
- Execute new DDL
- Do several checks before we proceed to the actual masking
See the template scripts:
Tables
CREATE TABLE oracle_world.mask_view( owner VARCHAR2(50), view_name VARCHAR2(50), flag_active CHAR(1) ); CREATE TABLE oracle_world.mask_view_column( owner VARCHAR2(50), view_name VARCHAR2(50), column_name VARCHAR2(50), flag_enabled CHAR(1), pii_category VARCHAR2(25), id_mask_template NUMBER ); CREATE TABLE oracle_world.mask_user( user_name VARCHAR2(50), flag_masked CHAR(1) ); CREATE TAbLE oracle_world.mask_template( id_mask_template NUMBER, mask_type VARCHAR2(255), mask_params VARCHAR2(255) );
Package
/***
- package spec
***/
CREATE OR REPLACE PACKAGE oracle_world.lib_mask_pii IS
-- role for all users with masked access
k#role_data_mask CHAR(14) := 'ROLE_DATA_MASK';
PROCEDURE run_ctl;
END lib_mask_pii;
/
/***
- package body
***/
CREATE OR REPLACE PACKAGE BODY oracle_world.lib_mask_pii IS
/*********************************************************************
NAME: LIB_MASK_PII
PURPOSE: To Mask PII
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------
1.0 02/07/2020 ORACLE_WORLD 1. Created this package.
***********************************************************************/
v_run_name VARCHAR2(100) := 'LIB_MASK_PII';
/****
* update table with users for masking
*****/
PROCEDURE prc_upd_mask_user(o_err_code OUT VARCHAR2) IS
BEGIN
MERGE INTO oracle_world.mask_user t
USING (SELECT username FROM dba_users) s
ON (t.user_name = s.username)
WHEN NOT MATCHED THEN
INSERT (user_name,flag_masked)
VALUES (s.username,'Y'); -- mask all by default; you can manually change that to grant exceptions
commit;
EXCEPTION
WHEN OTHERS THEN
o_err_code := SQLCODE ||';'|| SQLERRM;
dbms_output.put_line('UPD_MASK_USER: '||SQLCODE||' ErrMessage: '||SQLERRM);
END prc_upd_mask_user;
/****
* align access of users
*****/
PROCEDURE prc_align_access(o_err_code OUT VARCHAR2) IS
BEGIN
FOR i IN (SELECT
CASE
WHEN t.flag_masked = 'Y' THEN 'REVOKE '||k#role_data_mask||' FROM ' ||t.user_name
WHEN t.flag_masked = 'N' THEN 'GRANT '||k#role_data_mask||' TO ' ||t.user_name
END as fix_role
FROM oracle_world.mask_user t
--
LEFT JOIN dba_role_privs p
ON t.user_name = p.grantee
AND p.granted_role = k#role_data_mask
--
WHERE 1=1
AND ( -- fix only the invalid configuration (the rest is fine)
(t.flag_masked = 'N' AND p.granted_role = k#role_data_mask)
OR
(t.flag_masked = 'Y' AND p.granted_role IS NULL)
)
)
LOOP
EXECUTE IMMEDIATE i.fix_role;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
o_err_code := SQLCODE ||';'|| SQLERRM;
dbms_output.put_line('ALIGN_ACCESS: '||SQLCODE||' ErrMessage: '||SQLERRM);
END prc_align_access;
/****
* execute
*****/
PROCEDURE prc_do_mask (--------------------------------------
-- these two for logging purposes only
i_owner IN VARCHAR2,
i_view_name IN VARCHAR2,
--------------------------------------
i_ddl_new IN CLOB,
i_ddl_old IN CLOB
--------------------------------------
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- create masked view
EXECUTE IMMEDIATE i_ddl_new;
EXCEPTION
WHEN OTHERS THEN
BEGIN
dbms_output.put_line('DO_MASK (new): '||SQLCODE||' ErrMessage: '||SQLERRM);
-- recreate the old one
EXECUTE IMMEDIATE i_ddl_old;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('DO_MASK (old): '||SQLCODE||' ErrMessage: '||SQLERRM);
END;
END prc_do_mask;
/****
* process all
*****/
PROCEDURE prc_mask_all (o_err_code OUT VARCHAR2) IS
l_view_text_masked CLOB;
l_view_text CLOB;
l_view_comment VARCHAR2(4000 CHAR);
l_select_position INTEGER DEFAULT 1;
l_regex_cond VARCHAR2(100 CHAR);
l_masking_col VARCHAR2(300 CHAR);
-- column records
TYPE list_col_t IS TABLE OF VARCHAR2(4000);
cols list_col_t := list_col_t();
BEGIN
FOR i_view IN (
SELECT mv.owner
, mv.view_name
, mv.flag_active
, c.comments AS view_comment
, nvl(mvc.col_cnt,'0') as col_cnt
, DECODE(v.view_name, NULL, 'N', 'Y') as view_exists
, DECODE(io.object_name, NULL, 'N', 'Y') as view_is_invalid
FROM oracle_world.mask_view mv
--
LEFT JOIN (
SELECT owner
, view_name
, count(*) as col_cnt
FROM oracle_world.mask_view_column mvc
GROUP BY owner
, view_name
) mvc
ON mv.owner = mvc.owner
AND mv.view_name = mvc.view_name
--
LEFT JOIN dba_views v
ON v.owner = mv.owner
AND v.view_name = mv.view_name
--
LEFT JOIN dba_invalid_objects io
ON mv.owner = io.owner
AND mv.view_name = io.object_name
AND io.object_type = 'VIEW'
--
LEFT JOIN dba_tab_comments c
ON mv.owner = c.owner
AND mv.view_name = c.table_name
--
WHERE 1=1
AND flag_active = 'Y' -- only those for masking
)
LOOP
-- check if view has columns
IF i_view.col_cnt = 0 THEN
CONTINUE; -- skip to next
END IF;
-- check if view exists
IF i_view.view_exists = 'N' THEN
CONTINUE; -- skip to next
END IF;
-- check if view is valid
IF i_view.view_is_invalid = 'Y' THEN
CONTINUE; -- skip to next
END IF;
-- get DDL of the view
l_view_text := dbms_metadata.get_ddl('VIEW',i_view.view_name,i_view.owner);
-- check if the view is already masked
IF instr(l_view_text,'DECODE(SYS_CONTEXT(''SYS_SESSION_ROLES'', '''||k#role_data_mask||'''') > 0 THEN
CONTINUE; -- skip to next
END IF;
-- remove double quotes
l_view_text_masked := regexp_replace(l_view_text,'"');
-- save view comment
l_view_comment := 'COMMENT ON TABLE ' || i_view.owner || '.' || i_view.view_name || ' IS ''' || replace(i_view.view_comment,'''', '''''') ||'''';
-- get SELECT positoin
l_select_position := instr(upper(l_view_text_masked), 'SELECT')+1;
-- loop through columns to check masking
FOR l_view_col IN (
SELECT c.column_name
, c.comments AS column_comment
, CASE
WHEN mt.mask_type = 'HASH' THEN replace(mt.mask_params,'###',c.column_name)
ELSE mt.mask_params
END AS col_mask_params
--
FROM dba_col_comments c
--
JOIN oracle_world.mask_view_column cl
ON c.owner = cl.owner
AND c.table_name = cl.view_name
AND c.column_name = cl.column_name
--
LEFT JOIN oracle_world.mask_template mt
ON cl.id_mask_template = mt.id_mask_template
--
WHERE 1=1
AND c.owner = i_view.owner
AND c.table_name = i_view.view_name
AND cl.flag_enabled = 'Y' -- only enabled
)
LOOP
-- gather column comments
cols.EXTEND;
cols(cols.LAST) := 'COMMENT ON COLUMN ' || i_view.owner || '.' || i_view.view_name || '."' || l_view_col.column_name || '" IS ''' || replace(l_view_col.column_comment,'''', '''''') || '''';
-- prepare the mask
l_masking_col := 'DECODE(SYS_CONTEXT(''SYS_SESSION_ROLES'', '''||k#role_data_mask||'''), ''FALSE'', ' || l_view_col.col_mask_params || ', ';
-- set regex
l_regex_cond := '(\w*|)(\.|)(' || l_view_col.column_name || ')([[:space:]]|,)';
-- exec regex
l_view_text_masked := regexp_replace(l_view_text_masked, l_regex_cond, l_masking_col||'\1\2\3)\4', l_select_position,1,'i');
END LOOP;
-- execute masked view
prc_do_mask(-----------------------------
-- for logging purposes only
i_view.owner
, i_view.view_name
-----------------------------
, l_view_text_masked
, l_view_text
-----------------------------
);
-- return of original comments
FOR i IN cols.first .. cols.last
LOOP
EXECUTE IMMEDIATE cols(i);
END LOOP;
-- empty all comments
cols.DELETE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE||' Error in view masking! ErrMessage:' || SQLERRM);
END prc_mask_all;
/***
- run control
***/
PROCEDURE run_ctl IS
l_error_code varchar2(1000);
e_general EXCEPTION;
BEGIN
-- #######
-- run procedures here
-- To keep them chained, leave the IF part uncommented (error will be raised and rest of steps will be skipped)
-- otherwise, comment out the IF part and all steps will proceed consecutively
prc_upd_mask_user (l_error_code);
IF l_error_code IS NOT NULL THEN RAISE e_general; END IF;
prc_align_access (l_error_code);
IF l_error_code IS NOT NULL THEN RAISE e_general; END IF;
prc_mask_all (l_error_code);
IF l_error_code IS NOT NULL THEN RAISE e_general; END IF;
EXCEPTION
WHEN e_general THEN
dbms_output.put_line(l_error_code);
WHEN OTHERS THEN
dbms_output.put_line('Err_Other: '||SQLCODE||' ErrMessage: '||SQLERRM);
END run_ctl;
END lib_mask_pii;