Oracle Database offers robust features for implementing data masking, enabling you to safeguard sensitive information by controlling access based on user roles.

The primary methods for achieving this are:

Oracle Data Redaction (Dynamic Data Masking): This is the most common and recommended approach for real-time, on-the-fly masking based on user privileges or conditions.
Oracle Virtual Private Database (VPD) / Fine-Grained Access Control (FGAC): While primarily for row-level security, VPD can also be used for column masking (though Data Redaction offers more robust masking options for columns).
Static Data Masking (for non-production environments): This is used for permanently altering data in copies of your production database (e.g., for testing, development, or training environments).

Let's delve into the first two, as they are most relevant for achieving selective masking in a live production environment.

1. Oracle Data Redaction (Dynamic Data Masking)

Oracle Data Redaction masks sensitive data in real-time as it's queried, without altering the underlying data in the database. This is ideal for your requirement of allowing some users to see all data while masking it for others.

How it works:

You define redaction policies on specific columns of a table.
These policies specify a masking format (e.g., full redaction with asterisks, partial redaction, random numbers, nulls).
You also define a policy expression (a WHERE clause) that determines when the redaction should apply. This expression can leverage user roles, session attributes, IP addresses, or any other relevant context.

Steps to implement Data Redaction:

Identify Sensitive Columns: Determine which columns contain sensitive data that needs to be masked.

Define Redaction Policies: Use the DBMS_REDACT package to create policies.

Example: Masking the SALARY column in HR.EMPLOYEES for users who are NOT in the FULL_ACCESS_ROLE.

 -- 1. Grant necessary privileges to a user who will create the policies (e.g., a DBA or security admin)
 GRANT EXECUTE ON DBMS_REDACT TO security_admin;
 GRANT REDACT ANY DATA TO security_admin; -- Required to create/manage redaction policies

 -- 2. Create a role for users who should see all data
 CREATE ROLE full_access_role;
 GRANT SELECT ON hr.employees TO full_access_role; -- Grant regular select privilege

 -- 3. Create a user who should see masked data (and grant them select on the table)
 CREATE USER limited_user IDENTIFIED BY password;
 GRANT CREATE SESSION TO limited_user;
 GRANT SELECT ON hr.employees TO limited_user;

 -- 4. Create the redaction policy
 BEGIN
   DBMS_REDACT.ADD_POLICY(
     object_schema   => 'HR',
     object_name     => 'EMPLOYEES',
     column_name     => 'SALARY',
     policy_name     => 'MASK_SALARY_FOR_LIMITED_USERS',
     function_type   => DBMS_REDACT.FULL, -- Full redaction (e.g., '*********')
     expression      => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') NOT IN (''SYS'', ''SYSTEM'') AND NOT DBMS_SESSION.IS_ROLE_ENABLED(''FULL_ACCESS_ROLE'')'
   );
 END;
 /

 -- You can also use other function types like:
 -- DBMS_REDACT.PARTIAL (for partial masking)
 -- DBMS_REDACT.RANDOM (for random values)
 -- DBMS_REDACT.NONE (to not redact at all, but policy still exists)

Explanation of the expression:

SYS_CONTEXT('USERENV', 'SESSION_USER') NOT IN ('SYS', 'SYSTEM'): This ensures that the SYS and SYSTEM users (typically DBAs) are not affected by the masking.

NOT DBMS_SESSION.IS_ROLE_ENABLED('FULL_ACCESS_ROLE'): This is the crucial part. If the current user has the FULL_ACCESS_ROLE enabled, the condition evaluates to FALSE, and the data is not redacted for them. For any other user (who doesn't have FULL_ACCESS_ROLE enabled), the condition is TRUE, and the SALARY column will be masked.

Test the Policies:

As limited_user:
```
  CONNECT limited_user/password;
  SELECT employee_id, salary FROM hr.employees;
```
You should see the SALARY column masked (e.g., ********* or NULL depending on the function type).

As a user with FULL_ACCESS_ROLE enabled:

  CONNECT your_full_access_user/password; -- Assuming 'your_full_access_user' has been granted FULL_ACCESS_ROLE
  GRANT FULL_ACCESS_ROLE TO your_full_access_user;
  SELECT employee_id, salary FROM hr.employees;

You should see the actual SALARY values.

2. Oracle Virtual Private Database (VPD) / Fine-Grained Access Control (FGAC)

VPD is a powerful feature that allows you to apply security policies directly to tables, views, or synonyms. While often used for row-level security, it can also be used for column masking by rewriting queries to return NULL or a fixed value for sensitive columns.

How it works:

You create a policy function that returns a WHERE clause predicate or a column expression.
This policy function is associated with a table (or view) using DBMS_RLS.ADD_POLICY.
Whenever a user queries the protected object, Oracle transparently modifies the SQL statement with the predicate or column expression returned by the policy function.

Steps to implement Column Masking with VPD:

Create a Policy Function: This function determines what data is visible or how it's masked.

 -- Create a user to own the policy objects (optional, but good practice)
 CREATE USER vpd_owner IDENTIFIED BY password;
 GRANT CREATE SESSION, CREATE PROCEDURE, CREATE FUNCTION, CREATE CONTEXT TO vpd_owner;
 GRANT EXECUTE ON DBMS_RLS TO vpd_owner;
 GRANT EXECUTE ON DBMS_SESSION TO vpd_owner;

 -- Grant select on the target table to vpd_owner so the policy function can access it
 GRANT SELECT ON hr.employees TO vpd_owner;

 -- Connect as vpd_owner to create the package and function
 CONNECT vpd_owner/password;

 CREATE OR REPLACE PACKAGE hr_security_pkg AS
   FUNCTION mask_salary_func(
     schema_name IN VARCHAR2,
     object_name IN VARCHAR2
   ) RETURN VARCHAR2;
 END hr_security_pkg;
 /

 CREATE OR REPLACE PACKAGE BODY hr_security_pkg AS
   FUNCTION mask_salary_func(
     schema_name IN VARCHAR2,
     object_name IN VARCHAR2
   ) RETURN VARCHAR2 IS
     v_predicate VARCHAR2(4000);
   BEGIN
     -- Check if the user has the 'FULL_ACCESS_ROLE'
     IF DBMS_SESSION.IS_ROLE_ENABLED('FULL_ACCESS_ROLE') THEN
       v_predicate := NULL; -- No masking, return all data
     ELSE
       -- Mask the SALARY column: replace with NULL or a fixed value
       -- Note: For more complex masking like partial or random, Data Redaction is better.
       -- For VPD column masking, you often set the column to NULL or a literal.
       v_predicate := '1=1'; -- Apply to all rows
       -- To mask the column to NULL:
       DBMS_RLS.ADD_POLICY_CONTEXT(
         schema_name => schema_name,
         object_name => object_name,
         policy_name => 'mask_salary_policy', -- Needs to match the policy name
         namespace   => 'SYS_CONTEXT',
         attribute   => 'USERENV',
         value       => 'SESSION_USER',
         column_name => 'SALARY',
         column_expression => 'NULL' -- Or any other literal, e.g., '''********'''
       );
     END IF;
     RETURN v_predicate;
   END mask_salary_func;
 END hr_security_pkg;
 /

Important Note on VPD for Column Masking:

Direct column masking in VPD (setting a column to NULL or a literal in the policy function) is less flexible than Data Redaction. A more common approach with VPD for column-level security is to create a view that conditionally selects the original or masked column value, and then apply the VPD policy on that view. However, Oracle Data Redaction is specifically designed for dynamic data masking and is generally the preferred solution for column-level obfuscation.

Add the Policy:

 -- Connect as a user with DBA privileges or the owner of the table if DBMS_RLS is granted
 CONNECT sys/password AS SYSDBA;

 BEGIN
   DBMS_RLS.ADD_POLICY (
     object_schema    => 'HR',
     object_name      => 'EMPLOYEES',
     policy_name      => 'mask_salary_policy',
     function_schema  => 'VPD_OWNER',
     policy_function  => 'HR_SECURITY_PKG.MASK_SALARY_FUNC',
     statement_types  => 'SELECT',
     sec_relevant_cols => 'SALARY', -- Specify the column to be affected
     sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS -- Show all rows, but mask specified columns
   );
 END;
 /

Test the Policies: Same as with Data Redaction, test with limited_user and a user with FULL_ACCESS_ROLE.

Recommendations:

For Dynamic Data Masking (real-time masking):
- Oracle Data Redaction is the recommended and most straightforward approach. It's purpose-built for this and offers a variety of masking formats (full, partial, random, regular expression, null). It's simpler to implement for column masking than VPD.
For Row-Level Security (filtering rows) or more complex access control:
- Oracle Virtual Private Database (VPD) is the go-to solution. It excels at transparently adding WHERE clauses to queries to restrict which rows a user can see.
For Non-Production Environments (permanent data alteration):
- Use Oracle Data Masking and Subsetting Pack (part of Oracle Enterprise Manager or Oracle Data Safe).¹³ This is a static data masking solution that physically changes the data in a non-production copy of your database. It's crucial for compliance in test/dev environments.

By combining these Oracle security features, you can effectively implement granular data masking policies to meet your specific security and compliance requirements.

How to Implement Data Masking for Table Column Values by User Roles

1. Oracle Data Redaction (Dynamic Data Masking)

2. Oracle Virtual Private Database (VPD) / Fine-Grained Access Control (FGAC)

Recommendations:

Subscribe to my newsletter

FARHAN SIDDIQUI

FARHAN SIDDIQUI