- SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE)
- SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted
- SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM)
- General Personal Data Protection Act (LGPDP or LGPD) applied to SQL Server databases
- SQL Server - Avoiding Queries on Certain Columns with Column Level Security (CLS)
Hello people,
All right?
Introduction
In this post I would like to demonstrate a very interesting feature of SQL Server, available from the 2016 release, which is Dynamic Data Masking (DDM) and allows us to quickly and conveniently mask and hide sensitive information from certain users.
Unlike the encryption capabilities of SQL Server Transparent Data Encryption (TDE) and Always Encrypted, which physically encrypt database data and files (even backups and log backups are generated already encrypted, and MDF, LDF, and NDF files are also encrypted). Dynamic Data Masking does not really encrypt the data, it only masks database queries for non-privileged users.
To apply data masking, you must run an ALTER command on the column and you want to apply the mask (data masking is at the column level) or you can add this definition in the table CREATE.
Limitations and Restrictions
You cannot define a masking rule for the following column types:
- Encrypted Columns (Always Encrypted)
- FILESTREAM
- A mask cannot be set on a computed column, but if the computed column depends on a column with a MASK, it will return masked data.
- A data masked column cannot be a key to a FULLTEXT index.
Mask Types
In Dynamic Data Masking, you can define how data will be masked using some data masking functions, which will be detailed below:
| Function | Description | Examples |
|---|---|---|
| Default | Masking according to the data types of the designated fields. For string data types, the original values will be replaced by the characters XXXX. Applies to data types tank, nchar, varchar, nvarchar, text e ntext For numeric data types, the original value will be replaced by the number 0 (zero). Applies to data types bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float e real. For date and time data types, the date 1900-01-01 00: 00: 00.0000000 will be used. Applies to data types data, datetime2, datetime, datetimeoffset, smalldatetime e team. For binary data types, the original value will be replaced by a single ASCII 0 value byte. Applies to data types binary, varbinary e image. | ALTER TABLE dbo.DDM ALTER COLUMN Name ADD MASKED WITH (FUNCTION = 'default ()') |
| The masking method that exposes the first letter of an email address and the constant suffix ".com" in the form of an email address Ex: [email protected] will be masked as [email protected] | ALTER TABLE dbo.DDM ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email ()') | |
| Random | The random masking function (start_number, end_number) allows you to replace a numeric value with a random number generated from a predefined interval. Columns that have data types that accept decimal values can accept ranges in the form of decimal or integer numbers in the random () function. If the range is composed of 2 integers in a column that accepts decimal values, the generated random values will also have decimal places with random values (see examples). | ALTER TABLE dbo.DDM ALTER COLUMN ADD MASKED WITH Salary (FUNCTION = 'random (0.5, 0.99)') ALTER TABLE dbo.DDM ALTER COLUMN Weight ADD MASKED WITH (FUNCTION = 'random (70, 120)') |
| Partial | The partial masking function (prefix, fill, suffix) shows the first N letters (N = prefix) and last N letters (N = suffix) and adds a string S of custom fill characters in the middle (S = fill). Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed, ie if the string is "Dirceu" and the masking function is partial (4, "XXXX", 4) , the string will be masked as "XXXX", disregarding the prefix and suffix values that will not be shown. | ALTER TABLE dbo.DDM ALTER COLUMN CPF ADD MASKED WITH (FUNCTION = 'partial (0, "XXXXXXXXX", 2)') ALTER TABLE dbo.DDM ALTER COLUMN ADD MASKED WITH Position (FUNCTION = 'partial (3, "XXXXXXXXXXX", 4)') |
Examples and demos
To better demonstrate the features and functions of Dynamic Data Masking, I have prepared an example below, demonstrating how to create a table with some fields already masked, then we will view these fields with the mask.
I will alter the table to mask more columns, view the masked data (now with all columns) and then view 5x the result to observe the behavior of the random () masking function when executed multiple times.
Dynamic Data Masking Example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
-- Criação da tabela de testes do Dynamic Data Masking -- Máscaras criadas nas colunas "CPF" e "Nome" na criação na tabela IF (OBJECT_ID('dbo.Teste_DDM') IS NOT NULL) DROP TABLE dbo.Teste_DDM CREATE TABLE dbo.Teste_DDM ( CPF VARCHAR(11) MASKED WITH(FUNCTION = 'partial(0, "XXXXXXXXX", 2)'), Nome VARCHAR(60) MASKED WITH(FUNCTION = 'default()'), Cargo VARCHAR(40), Data_Nascimento DATETIME, Email VARCHAR(100), Email_Corporativo VARCHAR(100), Salario NUMERIC(15, 2), Numero_Cartao VARCHAR(16), Peso INT, Altura FLOAT, Estado_Civil SMALLINT, Genero BIT ) -- Inseração de registro na tabela de testes INSERT INTO dbo.Teste_DDM VALUES ( '12345678909', -- CPF - varchar(11) 'Joãozinho da Silva', -- Nome - varchar(60) 'Analista de Sistemas', -- Cargo - varchar(40) '1987-05-28', -- Data_Nascimento - datetime 12345.67, -- Salario - numeric(15, 2) '1234567890123456', -- Numero_Cartao - varchar(16) 85, -- Peso - int 1.81, -- Altura - float 1, -- Estado_Civil - smallint 1 -- Genero - bit ) SELECT * FROM dbo.Teste_DDM GO -- Vamos criar um usuário para conseguirmos visualizar os dados mascarados -- Lembre-se: Usuários com permissão db_owner ou sysadmin SEMPRE vão ver os dados sem máscara IF (USER_ID('Teste_DDM') IS NULL) CREATE USER [Teste_DDM] WITHOUT LOGIN GRANT SELECT ON dbo.Teste_DDM TO [Teste_DDM] -- Visualizando os dados mascarados (Como se fosse o usuário Teste_DDM, que acabamos de criar) EXECUTE AS USER = 'Teste_DDM' GO SELECT * FROM dbo.Teste_DDM GO REVERT -- Reverte as permissões para o seu usuário GO -- Vamos criar mais algumas máscaras na tabela ALTER TABLE dbo.Teste_DDM ALTER COLUMN Cargo ADD MASKED WITH(FUNCTION = 'partial(4, "XXXX", 4)') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Data_Nascimento ADD MASKED WITH(FUNCTION = 'default()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Email ADD MASKED WITH(FUNCTION = 'email()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Email_Corporativo ADD MASKED WITH(FUNCTION = 'email()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Salario ADD MASKED WITH(FUNCTION = 'random(0.5, 0.99)') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Numero_Cartao ADD MASKED WITH(FUNCTION = 'partial(4, "********", 4)') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Peso ADD MASKED WITH(FUNCTION = 'random(70, 120)') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Altura ADD MASKED WITH(FUNCTION = 'default()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Estado_Civil ADD MASKED WITH(FUNCTION = 'default()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Genero ADD MASKED WITH(FUNCTION = 'default()') -- Visualizando os dados mascarados EXECUTE AS USER = 'Teste_DDM' GO SELECT * FROM dbo.Teste_DDM SELECT * FROM dbo.Teste_DDM SELECT * FROM dbo.Teste_DDM SELECT * FROM dbo.Teste_DDM SELECT * FROM dbo.Teste_DDM REVERT |
Viewing original data (with sysadmin user):
Viewing masked data after table creation (with ordinary user):
|
1 2 3 4 5 6 |
IF (OBJECT_ID('dbo.Teste_DDM') IS NOT NULL) DROP TABLE dbo.Teste_DDM CREATE TABLE dbo.Teste_DDM ( CPF VARCHAR(11) MASKED WITH(FUNCTION = 'partial(0, "XXXXXXXXX", 2)'), Nome VARCHAR(60) MASKED WITH(FUNCTION = 'default()'), ... ) |
Viewing masked data after all changes:
|
1 2 3 4 5 6 7 8 9 10 |
ALTER TABLE dbo.Teste_DDM ALTER COLUMN Cargo ADD MASKED WITH(FUNCTION = 'partial(4, "XXXX", 4)') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Data_Nascimento ADD MASKED WITH(FUNCTION = 'default()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Email ADD MASKED WITH(FUNCTION = 'email()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Email_Corporativo ADD MASKED WITH(FUNCTION = 'email()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Salario ADD MASKED WITH(FUNCTION = 'random(0.5, 0.99)') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Numero_Cartao ADD MASKED WITH(FUNCTION = 'partial(4, "********", 4)') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Peso ADD MASKED WITH(FUNCTION = 'random(70, 120)') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Altura ADD MASKED WITH(FUNCTION = 'default()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Estado_Civil ADD MASKED WITH(FUNCTION = 'default()') ALTER TABLE dbo.Teste_DDM ALTER COLUMN Genero ADD MASKED WITH(FUNCTION = 'default()') |
Security and Permissions
To create or alter a table using Dynamic Data Masking, you do not need any special permissions, just the traditional table creation / modification permissions (CREATE TABLE and / or ALTER).
To add, change, or remove a column mask, the user must have the ALTER ANY MASK and ALTER permissions.
Users with SELECT permission on a table will be able to view table data, and masked columns will display masked data. In order for the user to be able to view the original data, ie without masks, he must have the UNMASK TO GRANT UNMASK TO [User] permission.
In addition, the CONTROL permission on the database includes ALTER ANY MASK and UNMASK permissions, ie users of roles db_owner or sysadmin can view the original data from masked columns.
Which columns are masked?
To identify which columns of your database are masked using Dynamic Data Masking, simply execute the query below:
|
1 2 3 4 5 6 7 8 9 |
SELECT A.[name], B.[name] AS table_name, A.masking_function FROM sys.masked_columns AS A JOIN sys.tables AS B ON A.[object_id] = B.[object_id] WHERE A.is_masked = 1 |
Return example:
Breaking Dynamic Data Masking
Once the features of Dynamic Data Masking are presented, we need to also say that this method should not be the only way to ensure the security of your data. While it helps protect sensitive information, it has some flaws that allow unauthorized users to access that information using brute force techniques.
In the example above, I entered a record in the table where the salary was $ 12.345,67. Using brute force (or trial and error), we can find the approximate value of this salary (with patience or by creating an algorithm for it, up to the exact amount)
Example 1 - Numeric value:
In this example, I will demonstrate how it is possible to identify masked numeric values from simple SELECT's in the table. In the example below, I only made 6 queries and I was able to discover a value very close to the real value. In case I wanted to find out the exact value, a few more queries were enough for that:
Want to find out the exact value? Let's use a simple script for this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- Simulando as permissões do usuário "Teste_DDM" EXECUTE AS USER = 'Teste_DDM' GO -- Criando uma tabela de testes com vários números para tentar fazer o JOIN IF (OBJECT_ID('tempdb..#Numeros') IS NOT NULL) DROP TABLE #Numeros CREATE TABLE #Numeros ( Valor NUMERIC(15, 2) ) INSERT INTO #Numeros VALUES(0.01) DECLARE @Contador INT = 1, @Total INT = 100, @Maior_Valor NUMERIC(15, 2) -- 33s WHILE(@Contador <= @Total) BEGIN SELECT @Maior_Valor = MAX(Valor) FROM #Numeros INSERT INTO #Numeros SELECT @Maior_Valor + ROW_NUMBER() OVER(ORDER BY [name], id) / 100.00 FROM sys.syscolumns SET @Contador += 1 END -- Verificando se foi possível identificar o valor do salário SELECT A.*, B.Valor FROM dbo.Teste_DDM A JOIN #Numeros B ON A.Salario = B.Valor |
Result: Joãozinho's salary revealed!
Example 2 - Text
Here, I will demonstrate how to return the exact string that was masked using a character table and join letter by letter with that table. Each letter of the masked string will join my character table to find out what the original character is.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
-- Executando o código abaixo como um usuário SEM permissão de UNMASK EXECUTE AS USER = 'Teste_DDM' GO ------------------------------------------------------------------------ -- Criando a tabela com todos os caracteres da tabela ASCII ------------------------------------------------------------------------ IF (OBJECT_ID('tempdb..#Caracteres') IS NOT NULL) DROP TABLE #Caracteres CREATE TABLE #Caracteres ( Caractere CHAR(1) ) -- DECLARE @Contador INT = 32, @Total INT = 255 /* Case SENSTIVE */ DECLARE @Contador INT = 32, @Total INT = 96 /* Case INSENSITIVE */ WHILE(@Contador <= @Total) BEGIN INSERT INTO #Caracteres VALUES (CHAR(@Contador)) SET @Contador += 1 END ------------------------------------------------------------------------ -- Fazendo os joins para recuperar o valor original da string mascarada ------------------------------------------------------------------------ DECLARE @Query VARCHAR(MAX), @Maior_Tamanho INT = 18, -- Tamanho máximo de caracteres da string em questão @Contador_Caractere INT = 1 SET @Query = ' SELECT A.Nome,' SET @Contador_Caractere = 1 WHILE(@Contador_Caractere <= @Maior_Tamanho) BEGIN SET @Query += ' A' + CAST(@Contador_Caractere AS VARCHAR(20)) + '.Caractere' + (CASE WHEN @Contador_Caractere < @Maior_Tamanho THEN ',' ELSE '' END) SET @Contador_Caractere += 1 END SET @Query += ' FROM dbo.Teste_DDM A' SET @Contador_Caractere = 1 WHILE(@Contador_Caractere <= @Maior_Tamanho) BEGIN SET @Query += ' LEFT JOIN #Caracteres A' + CAST(@Contador_Caractere AS VARCHAR(20)) + ' ON SUBSTRING(A.Nome, ' + CAST(@Contador_Caractere AS VARCHAR(20)) + ', 1) = A' + CAST(@Contador_Caractere AS VARCHAR(20)) + '.Caractere' SET @Contador_Caractere += 1 END -- Executa nossa query dinâmica e mostrando os valores originais EXEC(@Query) |
That's it folks!
I hope you enjoyed this post and even more!
Dirceu, you mentioned ways to break without having access, do we have options to make this feature more secure?
Changing the subject, which encryption solution is the most cost-effective and most widely used?