SQL Server 2016: Dynamic Data Masking

By | June 28, 2015

В SQL Server 2016 появилась ещё одна интересная возможность. Это скрытие данных от конечного пользователя по определенному шаблону (Dynamic Data Masking).

Примечание: всё ниже описанное работает в SQL Server 2016 CTP 2.1.

Пусть у нас есть таблица:

CREATE TABLE Contacts
ContactID INT IDENTITY(1, 1) PRIMARY KEY 
,FirstName NVARCHAR(30NOT NULL

,LastName NVARCHAR(30) MASKED WITH (FUNCTION‘partial(1,”XXXXXXX”,0)’NOT NULL

,CreditCard VARCHAR(20) MASKED WITH (FUNCTION‘partial(2,”XX-XXXX-XXXX-XX”,2)’NULL

,Email NVARCHAR(60NULL 

,BirthDate DATETIME NULL 

)

Обратите внимание на опцию MASKED в двух колонках. Именно в неё задается шаблон отображения данных. Есть несколько функций отображения данных:

Default Для разных типов данных используются различные маски по умолчанию:

  • в строках символы заменяются на X
  • используется 0 для цифр в типах данных bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real
  • используется дата 01.01.2000 00:00:00.0000000 для типов данных date, datetime2, datetime, datetimeoffset, smalldatetime, time
Email Оставляет первый символ, все остальное заменяет на X, при этом домен заменяется на .com
Custom String В этом способе вы указываете сколько символов в начале и конце строки оставить без измененя, все остальное меняется на X

Можно маски отображения задавать для колонок и после создания таблицы, есть и возможность убрать маску.

Применим к ранее созданной таблице команды:

ALTER TABLE Contacts
ALTER COLUMN BirthDate ADD MASKEDWITH (FUNCTION‘default()’)
ALTER TABLE Contacts
ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION‘default()’)
ALTER TABLE Contacts
ALTER COLUMN Email ADD MASKED WITH (FUNCTION’email()’)

Теперь добавим данные:
INSERT INTO dbo.Contacts
(FirstName,LastName,CreditCard,Email, BirthDate)
VALUES(‘Mark’,‘Twain’,‘1234-5678-1234-5678’,‘samuel@clemens.us’,’30-Nov-1835′)

Создадим пользователя с правом чтения из нашей таблицы:
CREATE USER testUser WITHOUT LOGIN
GRANT SELECT ON dbo.Contacts TO testUser 

И теперь посмотрим, что вернет нам запрос

SELECT FROM dbo.Contacts
GO
EXECUTE AS USER ‘testUser’
SELECT FROM dbo.Contacts
REVERT
GO
Как видите для владельца базы ничего не изменилось, а вот наш тестовый пользователь получил измененные данные:


Если необходимо убрать маску с колонки, то это очень просто сделать:
ALTER TABLE dbo.Contacts
ALTER COLUMN FirstName DROP MASKED

Кроме того, можно разрешить пользователю просмотр чистых данных:

GRANT UNMASK TO TestUser
EXECUTE AS USER ‘TestUser’
SELECT FROM dbo.Contacts
REVERT

Ну и чтобы отменить просмотр чистых данных:

REVOKE UNMASK TO TestUser

Есть ещё одна интересная особенность при смене типа данных в запросе:
EXECUTE AS USER ‘testUser’
SELECT LastNameCAST(LastName AS  nchar(100)) FROM dbo.Contacts
REVERT

Как видите, маска для второго значения сменилась на маску по умолчанию.

Ну и напоследок об ограничениях. Маски не поддерживаются для типов данных: char, text, varchar(max), nchar, ntext, nvarchar(max), sql_variant, uniqueidentifier, xml, cursor, rowversion, table, hierarchyid, binary, image, varbinary и spatial data.

Примечание 2: На данный момент известен баг. Если использовать псевдоним для таблицы или соединять в одном запросе несколько таблиц, то маска не срабатывает. К релизу должны исправить.

One thought on “SQL Server 2016: Dynamic Data Masking

  1. Pingback: SQL Server 2016. Знакомимся с новинками | Korshikov's

Comments are closed.