Data Masking and Subsetting

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.


There are three basic steps to implement data masking and submitting and therefore protect your data in production environments.

  1. Define the application data model
  2. Select masking criteria
  3. 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:

  1. Update the table with users for masking
  2. Align role-based access according to the setup in the config table (mask_user.flag_masked)
  3. 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

See the template scripts:


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 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
    PURPOSE:      To Mask PII
      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

      MERGE INTO oracle_world.mask_user t
      USING (SELECT username FROM dba_users) s
      ON (t.user_name = s.username)
        INSERT (user_name,flag_masked)
        VALUES (s.username,'Y'); -- mask all by default; you can manually change that to grant exceptions


            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

                  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)
                   (t.flag_masked = 'Y' AND p.granted_role IS NULL)
       EXECUTE IMMEDIATE i.fix_role;

        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

    -- create masked view
    EXECUTE IMMEDIATE i_ddl_new;

          dbms_output.put_line('DO_MASK (new): '||SQLCODE||' ErrMessage: '||SQLERRM);
          -- recreate the old one
          EXECUTE IMMEDIATE i_ddl_old;

              WHEN OTHERS THEN
                dbms_output.put_line('DO_MASK (old): '||SQLCODE||' ErrMessage: '||SQLERRM);

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


      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

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

        -- gather column comments
        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
                      -- for logging purposes only
                      , i_view.view_name
                      , l_view_text_masked
                      , l_view_text

      -- return of original comments
      FOR i IN cols.first .. cols.last
        EXECUTE IMMEDIATE cols(i);
      END LOOP;

      -- empty all comments


        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;

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

        WHEN e_general THEN

            dbms_output.put_line('Err_Other: '||SQLCODE||' ErrMessage: '||SQLERRM);

  END run_ctl;

END lib_mask_pii;