- SQL Server 2008 – Como criptografar seus dados utilizando Transparent Data Encryption (TDE)
- SQL Server 2016 – Como criptografar seus dados utilizando Always Encrypted
- SQL Server 2016 – Mascaramento de dados com o Dynamic Data Masking (DDM)
- Lei Geral de Proteção de Dados Pessoais (LGPDP ou LGPD) aplicada a bancos de dados SQL Server
- SQL Server – Evitando consultas em determinadas colunas com o Column Level Security (CLS)
Olá pessoal,
Tudo certo?
Introdução
Neste post eu gostaria de demonstrar um recurso bem interessante do SQL Server, disponível a partir da versão 2016, que é o Dynamic Data Masking (DDM) e que nos permite mascarar e ocultar informações sensíveis de determinados usuários de forma rápida, prática.
Diferente dos recursos de criptografia do SQL Server Transparent Data Encryption (TDE) e Always Encrypted, que criptografam fisicamente os dados e arquivos do banco (até os backups e backups de log são gerados já criptografados, e os arquivos MDF, LDF e NDF também são criptografados), o mascaramento de dados Dynamic Data Masking não criptografa realmente os dados, apenas aplica uma máscara nas consultas realizadas no banco de dados para usuários não-privilegiados.
Para aplicar o mascaramento de dados, você deve executar um comando de ALTER na coluna e que deseja aplicar a máscara (o mascaramento dos dados é a nível de coluna) ou pode adicionar essa definição no CREATE da tabela.
Limitações e restrições
Não é possível definir uma regra de mascaramento para os seguintes tipos de coluna:
- Colunas criptografadas (Always Encrypted)
- FILESTREAM
- Uma máscara não pode ser configurada em uma coluna computada, mas se a coluna computada depender de uma coluna com uma MÁSCARA, ela retornará dados mascarados.
- Uma coluna com mascaramento de dados não pode ser uma chave para um índice FULLTEXT.
Tipos de máscaras
No Dynamic Data Masking, você pode definir como os dados serão mascarados utilizando algumas funções de mascaramento de dados, que serão detalhadas abaixo:
| Função | Descrição | Exemplos |
|---|---|---|
| Default | Mascaramento de acordo com os tipos de dados dos campos designados. Para os tipos de dados string, os valores originais serão substituídos pelos caracteres XXXX. É aplicável aos tipos de dados char, nchar, varchar, nvarchar, text e ntext Para os tipos de dados numéricos, o valor original será substituído pelo número 0 (zero). É aplicável aos tipos de dados bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float e real. Para os tipos de dados data e hora, será utilizada a data 1900-01-01 00:00:00.0000000. É aplicável aos tipos de dados date, datetime2, datetime, datetimeoffset, smalldatetime e time. Para os tipos de dados binary, o valor original será substituído por um único byte de valor ASCII 0. É aplicável aos tipos de dados binary, varbinary e image. | ALTER TABLE dbo.DDM ALTER COLUMN Nome ADD MASKED WITH(FUNCTION = 'default()') |
| O método de mascaramento que expõe a primeira letra de um endereço de email e o sufixo constante ".com", na forma de um endereço de email Ex: [email protected] será mascarado como [email protected] | ALTER TABLE dbo.DDM ALTER COLUMN Email ADD MASKED WITH(FUNCTION = 'email()') | |
| Random | A função de mascaramento random(numero_inicial, numero_final) permite substituir um valor numérico por um número aleatório gerado a partir de um intervalo pré-definido. Colunas que possuem tipos de dados que aceitam valores decimais podem aceitar intervalos na forma de números decimais ou inteiros na função random(). Caso o intervalo seja composto por 2 números inteiros em uma coluna que aceita valores decimais, os valores aleatórios gerados terão casas decimais também com valores aleatórios (veja os exemplos). | ALTER TABLE dbo.DDM ALTER COLUMN Salario ADD MASKED WITH(FUNCTION = 'random(0.5, 0.99)') ALTER TABLE dbo.DDM ALTER COLUMN Peso ADD MASKED WITH(FUNCTION = 'random(70, 120)') |
| Partial | A função de mascaramento partial(prefixo, preenchimento, sufixo) mostra as primeiras N letras (N = prefixo) e últimas N letras (N = sufixo) e adiciona uma cadeia S de caracteres de preenchimento personalizada no meio (S = preenchimento). Observação: se o valor original for muito curto para completar a máscara inteira, parte do prefixo ou sufixo não será exposta, ou seja, caso a string seja "Dirceu" e a função de mascaramento seja partial(4, "XXXX", 4), a string será mascarada como "XXXX", não respeitando os valores que prefixo e sufixo, que não serão mostrados. | ALTER TABLE dbo.DDM ALTER COLUMN CPF ADD MASKED WITH(FUNCTION = 'partial(0, "XXXXXXXXX", 2)') ALTER TABLE dbo.DDM ALTER COLUMN Cargo ADD MASKED WITH(FUNCTION = 'partial(3, "XXXXXXXXXXX", 4)') |
Exemplos e demonstrações
Para demonstrar melhor as funcionalidades e funções do Dynamic Data Masking, preparei um exemplo abaixo, demonstrando como criar uma tabela com alguns campos já mascarados, depois vamos visualizar esses campos com a máscara.
Vou alterar a tabela para mascarar mais colunas, visualizar os dados mascarados (agora com todas as colunas) e depois visualizar 5x o resultado para observar o comportamento da função de mascaramento random() quando executada várias vezes.
Exemplo do Dynamic Data Masking
|
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 |
Visualizando os dados originais (com usuário sysadmin):
Visualizando os dados mascarados após a criação da tabela (com usuário comum):
|
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()'), ... ) |
Visualizando os dados mascarados após todas as alterações:
|
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()') |
Segurança e Permissões
Para criar ou alterar uma tabela utilizando o Dynamic Data Masking, não é necessário nenhuma permissão especial, apenas as permissões tradicionais para criação/alteração de tabelas (CREATE TABLE e/ou ALTER).
Para adicionar, alterar ou remover a máscara de uma coluna, o usuário deve ter as permissões de ALTER ANY MASK e ALTER.
Os usuários com a permissão SELECT em uma tabela poderão visualizar os dados da tabela e as colunas mascaradas exibirão os dados mascarados. para que o usuário possa visualizar os dados originais, isto é, sem máscaras, ele deve possuir a permissão UNMASK (GRANT UNMASK TO [User]).
Além disso, a permissão CONTROL no banco de dados inclui as permissões ALTER ANY MASK e UNMASK, ou seja, usuários das roles db_owner ou sysadmin, podem visualizar os dados originais de colunas mascaradas.
Quais colunas estão mascaradas ?
Para identificar quais colunas do seu database estão mascaradas utilizando Dynamic Data Masking, basta executar a query abaixo:
|
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 |
Exemplo de retorno:
Quebrando o Dynamic Data Masking
Uma vez apresentados os recursos do Dynamic Data Masking, precisamos falar também que esse método não deve ser a única forma de garantir a segurança dos seus dados. Embora ele ajude a proteger informações sensíveis, ele possui algumas falhas que permitem que usuários não autorizados acessem essas informações utilizando técnicas de força bruta.
No exemplo acima, inseri um registro na tabela onde o salário era R$ 12.345,67. Utilizando a força bruta (ou tentativa e erro), podemos encontrar o valor aproximado desse salário (com paciência ou criando um algoritmo para isso, até o valor exato)
Exemplo 1 – Valor numérico:
Neste exemplo, vou demonstrar como é possível identificar valores numéricos mascarados a partir de simples SELECT’s na tabela. No exemplo abaixo, fiz apenas 6 consultas e já consegui descobrir um valor bem próximo do valor real. Caso eu quisesse descobrir o valor exato, bastavam mais algumas consultas para isso:
Quer descobrir o valor exato? Vamos utilizar um script simples para isso:
|
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 |
Resultado: salário do Joãozinho revelado!
Exemplo 2 – Texto
Aqui, vou demonstrar como retornar a string exata que estava mascarada utilizando uma tabela de caracteres e fazendo join letra a letra com essa tabela. Cada letra da string mascarada fará um join com a minha tabela de caracteres para descobrir qual o caractere original.
|
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) |
É isso aí, pessoal!
Espero que tenham gostado desse post e até mais!
Portuguese 
English
Dirceu, você citou formas de quebrar sem ter acesso, temos opções de deixar essa funcionalidade mais segura?
Mudando de assunto, qual das soluções de criptografia tem o melhor custo benefício e é mais utilizada?