[T-SQL] Dynamic Data Mask

Fala Galera,

Mascaramento de dados vem se tornando uma necessidade cada vez maior para empresas que armazenam informações sensíveis;(CPF, CNH, Número de Passaporte etc). Exibir estas informações para todos os usuários da sua aplicação pode levar a implicações jurídicas e muita dor de cabeça.

Para nos apoiar com esta demanda, o  SQL Server 2016 introduziu uma nova feature a “Dynamic Data Masking” que tem como objetivo mascarar dados para usuários que não tenham direto de vê-los, limitando a quantidade de dados sensíveis expostos.
Por ser uma configuração realizada somente a nível de banco de dados, ou seja, totalmente transparente para a cada da aplicação, o Data Masking pode, além de ser efetivo, economizar muito esforço do time de desenvolvimento.

Existem quatro tipos de mascaras disponíveis:

  1. Default: Máscara aplicada para todo o valor contido no campo.
  2. E-mail: Preparado para que apenas a primeira letra do endereço de e-mail e o sulfixo “.com” sejam exibidos.
  3. Random: Exibe um valor  inteiro randomico
  4. Custom String: Definido pelo usuário

Hands On

Para nossa demo vamos usar a base de dados de exemplo da Microsoft. Se você ainda não tem o AdventureWorks2016: Click Aqui

Você precisará estar logado como sysadmin do SQL para os primeiros passos (ficará claro o porquê em alguns instantes)

 

Para deixar as coisas um pouco mais simples, vamos fazer um select * into em uma nova entidade e depois usa-la para a demo.

use AdventureWorks2016CTP3
GO
SELECT P.FirstName, P.LastName, B.EmailAddress, PN.PhoneNumber
INTO PERSON.BLOG_PIROTO_TB1
FROM PERSON.Person P
JOIN PERSON.EmailAddress B ON P.BusinessEntityID = B.BusinessEntityID
JOIN Person.PersonPhone PN ON P.BusinessEntityID = PN.BusinessEntityID

Faça um select na nossa nova tabela e veja como estão os dados

capturar

Agora que carregamos os dados para nossa tabela PERSON.BLOG_PIROTO_TB1, vamos adicionar mascaras a seus campos.

ALTER TABLE PERSON.BLOG_PIROTO_TB1 ALTER COLUMN LASTNAME ADD MASKED WITH (FUNCTION = 'default()')

ALTER TABLE PERSON.BLOG_PIROTO_TB1 ALTER COLUMN emailAddress ADD MASKED WITH (FUNCTION = 'email()')

ALTER TABLE PERSON.BLOG_PIROTO_TB1 ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION = 'partial(2, "BL-Piroto-X", 2)')

No total estamos usando três tipos diferentes de mascaramento:

  1. Default na coluna LASTNAME
  2. Email na coluna EMAILADDRESS
  3. Custom na coluna FIRSTNAME

Faça um select na tabela e vamos ver o que mudou nos dados

capturar

Os dados continuam os mesmos? Sem mascara nenhuma?
Bem, como eu disse no início do post, a ideia aqui é que os dados sensíveis não sejam apresentados para usuários que não devem ter acesso.
Se você está conectado com uma conta de ADM você terá aceso aos dados sem máscara. (Se   é uma premissa proteger seus dados, inclusive do DBA, você pode partir para outras features de criptografia como, por exemplo, o Always Encrypted.

Vamos criar um usuário com privilégios inferiores e ver como os dados são mostrados

CREATE LOGIN SUPORTE WITH PASSWORD = 'IXn*321Mnn#'
CREATE USER SUPORTE FOR LOGIN SUPORTE
ALTER AUTHORIZATION ON SCHEMA::person TO [suporte]

Faça o login com o novo usuário “suporte” e refaça o select na tabela PERSON.BLOG_PIROTO_TB1

capturar

Agora sim 🙂
Todas as máscaras estão configuradas conforme fizemos no segundo passo.

OBS: Mesmo que o usuário faça um select * into em uma nova entidade, os dados serão armazenados tal qual o retorno do select.

Se você precisar remover uma máscara ou conceder/revogar a permissão de um usuário (não ADM) a ver o dado original, faça o seguinte:

--remover máscara
ALTER TABLE PERSON.BLOG_PIROTO_TB1 ALTER COLUMN FIRSTNAME DROP MASKED
--Permissionamento
GRANT UNMASK TO SUPORTE
REVOKE UNMASK TO SUPORTE

Por fim, para descobrir quais colunas do seu banco possuem a feature de masking habilitada, há uma nova DMV chamada: SYS.MASKED_COLUMNS

SELECT
 OBJECT_NAME(A.OBJECT_ID) AS TABELA,
 A.NAME COLUNA,
 A.MASKING_FUNCTION MASCARA
FROM SYS.MASKED_COLUMNS A

Espero que, depois desse overview, vocês possam ter uma ideia de como implementar e obter vantagens dessa feature de mascaramento de dados no SQL Server 2016.

por agora é isso.

[]’s

Piroto

 

Anúncios

TSQL – Agrupar várias linhas em uma

Fala Galera,

Estou trabalhando na atualização de alguns scripts de monitoração de SQL Server que eu uso na empresa e acabei parando em um problema:

capturar

Precisava agrupar várias linhas em uma única, separada por espaço. Moleza, né? Fazemos um cursor e tudo resolvido…

é…nop.
Acontece que, por ser um script de monitoração, ele tem de ter o mínimo de overhead possível.

Bom, depois de consultar alguns livros e fazer algumas pesquisas, a solução acabou sendo bem simples:

</pre>
order by nome for xml path('')
<pre>

mas só isso resolve meu problema? vamos ver…

Primeiro vou criar uma variavel tabular e popula-la com alguns registros fake:

DECLARE @TABLE AS TABLE (id int identity, grupo varchar(100), nome varchar(100))
insert into @TABLE (grupo, nome)
values
('Amigos do Buteco','Jose'),
('Amigos do Buteco','Rene'),
('Amigos do Buteco','Diego'),
('Amigos da Trabalho','Rodolfo'),
('Amigos da Trabalho','Ramos'),
('Amigos da Trabalho','Tatiane'),
('Amigos da Trabalho','Leonardo'),
('Amigos de infância','Henrique'),
('Amigos de infância','Lucas'),
('Amigos de infância','Carlos')

--consulta cruzando pelo nome do grupo
select distinct grupo, (
 select nome from @TABLE interno
 where interno.grupo = externo.grupo
 order by nome for xml path('')
) detalhes_
 from @TABLE externo

capturar

O resultado é bem próximo do que eu preciso, só que ainda falta remover estas tags XML.

Basta nomear a coluna como “[data()]” e o SQL Server faz o resto =D


select distinct grupo, (
 select nome as [data()] from @TABLE interno
 where interno.grupo = externo.grupo
 order by nome for xml path('')
) detalhes_
 from @TABLE externo

capturar

Por hoje é isso.

Piroto

Alex Souza

Banco de dados, Business Intelligence, Big Data, Governança de TI ... /* Um eterno aprendiz... */

Blog - Thiago Carlos de Alencar

Aprendendo SQL Server !

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Vitor Fava

SELECT (CrazyIdeas*2), (InsaneIdeas*100), MyExperience FROM MyBigHead WHERE InsaneLevel > 1000

Think Think SQL

DBCC DumpMemory 'TECH','ALL'

Gustavo Maia Aguiar

Artigos, dicas e algumas reflexões sobre o SQL Server

Kimberly L. Tripp

DBCC DumpMemory 'TECH','ALL'

Thiago Zavaschi R2

www.zavaschi.com

Blog do Luti

DBCC DumpMemory 'TECH','ALL'

Luan.Moreno a.k.a [SQL.Soul]

Lead Database Consultant at Pythian

Blog do Leka

let's make things better