SQL Server vs Oracle: Data Masking
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.
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:
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.
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:
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".
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.
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;
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.
- 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:
- SQL Server Dynamic Data Masking Discovery and Implementation
- Use Dynamic Data Masking in SQL Server 2016 CTP 2.1
- SQL Server Data Masking with DbDefence
- Understand the Limitations of SQL Server Dynamic Data Masking
- Configure and Customize SQL Azure Dynamic Data Masking
- Masking Personal Identifiable SQL Server Data
- SQL Server Functions to Mask Confidential Data using CLR or PowerShell
About the author
View all my tips
Article Last Updated: 2022-07-27