Data Masking in SQL Server vs Oracle

By: Pablo Echeverria | Updated: 2022-07-27 | Comments | Related: > Security

Problem

There are many cases when data needs to be masked to hide some or all of the data for users working with data. This can be done using Dynamic Data Masking in SQL Server and using Data Redaction in Oracle. In this article, we will look at how these can be used to hide data or mask data.

Solution

Both Oracle and SQL Server allow you to mask sensitive data and display only a portion or completely randomize the result. In Oracle, Data Redaction allows you to control every aspect of query execution, so it can be based on user, role, weekday, time, IP, etc. In SQL Server, Dynamic Data Masking only allows displaying it when permission is granted, which is sufficient in most cases, and in newer versions of SQL Server, this permission is more granular, but still not very flexible.

In this tip, we will see how to mask data in SQL Server 2019 and Oracle 19c on Windows. Take note you can infer the real values using iterative ad-hoc queries, and you need paid software like Data Vault or encryption with keys stored outside of the database to prevent DBAs from looking at the real data.

Masking Data in Oracle with Data Redaction

To mask data, use the function below to create a data redaction policy.

DBMS_REDACT.ADD_POLICY ( 
 object_schema               IN VARCHAR2 := NULL, 
 object_name                 IN VARCHAR2 := NULL, 
 policy_name                 IN VARCHAR2, 
 policy_description          IN VARCHAR2 := NULL, 
 column_name                 IN VARCHAR2 := NULL, 
 column_description          IN VARCHAR2 := NULL, 
 function_type               IN BINARY_INTEGER := DBMS_REDACT.FULL, 
 function_parameters         IN VARCHAR2 := NULL, 
 expression                  IN VARCHAR2, 
 enable                      IN BOOLEAN := TRUE, 
 regexp_pattern              IN VARCHAR2 := NULL, 
 regexp_replace_string       IN VARCHAR2 := NULL, 
 regexp_position             IN BINARY_INTEGER :=1, 
 regexp_occurrence           IN BINARY_INTEGER :=0, 
 regexp_match_parameter      IN VARCHAR2 := NULL);

Create a Data Redaction Policy

For example, if we want to redact data for a table all the time (this is why the expression 1 equals 1 is used), we can use the code below. This will hide the data in the NAME column for the object we are using.

Note: you can't redact data for tables and views owned by SYS, but you can for SYSTEM:

EXEC DBMS_REDACT.ADD_POLICY(object_schema=>'SYSTEM', object_name=>'LOGSTDBY$SKIP_SUPPORT', policy_name=>'myPolicy', column_name=>'NAME', expression=>'1=1');

Here we can test this to see what happens. I am creating a new user for testing and then selecting the data.

CREATE USER MYDB IDENTIFIED BY MyPwd; 
GRANT CREATE SESSION TO MYDB; 
GRANT SELECT ON SYSTEM.LOGSTDBY$SKIP_SUPPORT TO MYDB; 
CONNECT MYDB/MyPwd
 
SELECT NAME FROM SYSTEM.LOGSTDBY$SKIP_SUPPORT WHERE ROWNUM<=10;

We can see that for the NAME column no data is displayed when the query runs.

select from system logstdby$skip_support

To view the data, you need to configure the "expression" parameter, as this mandates who can view data based on username, role, IP, day of week, time of day, etc. However, anyone granted the EXEMPT REDACTION POLICY system privilege can read the actual data, so by default users with the DBA role get this granted through the EXP_FULL_DATABASE role.

Note the function type and function parameters allow you to create custom masking, as well as using regexp parameters.

The "expression" can include anything, so below this will mask data for users don't have a role named ADMIN:

expression=>'SYS_CONTEXT(''SYS_SESSION_ROLES'', ''ADMIN'') = ''FALSE'''

This will mask data for a specific user:

expression=>'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''MYDB'''

This expression can be further defined using weekday, hour of day, IP, etc.

Modify a Data Redaction Policy

To modify an existing policy, you can use the code below specifying the "action" parameter which can be ADD_COLUMN, MODIFY_COLUMN, DROP_COLUMN, MODIFY_EXPRESSION, SET_POLICY_DESCRIPTION or SET_COLUMN_DESCRIPTION:

EXEC DBMS_REDACT.ALTER_POLICY(object_schema=>'SYSTEM', object_name=>'LOGSTDBY$SKIP_SUPPORT', policy_name=>'myPolicy', action=>DBMS_REDACT.ADD_COLUMN, column_name=>'REG', expression=>'1=1');

Drop a Data Redaction Policy

To drop the policy, you can use the code below:

EXEC DBMS_REDACT.DROP_POLICY('SYSTEM', 'LOGSTDBY$SKIP_SUPPORT', 'myPolicy');

View Existing Data Redaction Policies

To view existing policies, you can use the code below:

SELECT * FROM redaction_policies;

To view the configuration for FULL redaction you can use the code below:

SELECT varchar_value FROM REDACTION_VALUES_FOR_TYPE_FULL;

Oracle Data Redaction Documentation

Here is a link to the official Oracle Data Redaction documentation.

Masking Data in SQL Server with Dynamic Data Masking

SQL Server Dynamic Data Masking can be specified at the table creation or table modification, it can't be used for views.

Note the data masking function allows some customization but is still very limited compared to Oracle. The UNMASK privilege is more granular in SQL Server 2022 but still doesn't allow the use of additional logic.

There are five ways to mask data with the FUNCTION clause:

Default - depeding on data type it will either be all X or all 0 (zeros)
Email - shows first letter of email and the rest X except for the domain and @ sign
Random - random value for numbers
Partial - this allows for a custom string format
Datetime - various options to hide all or part of a date (SQL 2022)

Create Table with Dynamic Data Masking

Here is an example where the FirstName column is masked using partial. This will show the first charater, then xxxxx and then the last character of the string.

CREATE TABLE dbo.TestTable( 
    ID        int IDENTITY(1,1), 
    FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL, 
    LastName  varchar(100) NOT NULL 
       );

Modify Dynamic Data Masking on Table

Here is another example, we will create a new table from a view. Then we will modify the table and mask the login_name column.

USE [master]; 
SELECT * INTO DM_EXEC_SESSIONS_2 FROM SYS.DM_EXEC_SESSIONS; 

ALTER TABLE DM_EXEC_SESSIONS_2 
   ALTER COLUMN [login_name] NVARCHAR(128) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NOT NULL;

Below we can test how this works. I will create a new user just for testing that has limited permissions on the table and then query the new table.

USE [master]; 
CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON SCHEMA::dbo TO TestUser; 

-- use the new user for testing
EXECUTE AS USER = 'TestUser';
-- query the table 
SELECT login_name FROM dbo.DM_EXEC_SESSIONS_2; 
-- revert back to real user
REVERT;

Here is the output. Where the login_name is just "xxxxx".

select login name from dm_exec_sessions_2 masked

Using UNMASK to View Data

To view the data, you can grant the UNMASK privilege as follows.

GRANT UNMASK TO TestUser;

-- use the new user for testing
EXECUTE AS USER = 'TestUser';
-- query the table 
SELECT login_name FROM dbo.DM_EXEC_SESSIONS_2; 
-- revert back to real user
REVERT;

Here is the output. We can now see the real values.

select login name from dm_exec_sessions_2 unmasked

View Masked Columns

Use this to find out what tables and columns have masking in place.

SELECT mc.name, t.name as table_name, mc.is_masked, mc.masking_function  
FROM sys.masked_columns AS mc  
JOIN sys.tables AS t ON mc.[object_id] = t.[object_id]  
WHERE mc.is_masked = 1;

Drop Masking

To drop masking for a column use the ALTER TABLE statement.

ALTER TABLE dbo.DM_EXEC_SESSIONS_2   
   ALTER COLUMN login_name DROP MASKED;

SQL Server Dynamic Masking Documentation

Here is a link to the SQL Server Dynamic Masking documentation.

Next Steps

Oracle Data Redaction documentation can be found here.
SQL Server Dynamic Data Masking documentation can be found here.
Here are more tips on SQL Server Data Masking:

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-07-27

SQL Server vs Oracle: Data Masking