Автор: Андрей Коршиков
Ссылка на статью на сайте автора: ссылка
В SQL Server 2016 появилась ещё одна интересная возможность. Это скрытие данных от конечного пользователя по определенному шаблону (Dynamic Data Masking).
Примечание: всё ниже описанное работает в SQL Server 2016 CTP 2.1.
Пусть у нас есть таблица:
CREATE TABLE Contacts
( ContactID INT IDENTITY(1, 1) PRIMARY KEY
,FirstName NVARCHAR(30) NOT 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(60) NULL
,BirthDate DATETIME NULL
)
Обратите внимание на опцию MASKED в двух колонках. Именно в неё задается шаблон отображения данных. Есть несколько функций отображения данных:
| Default | Для разных типов данных используются различные маски по умолчанию:
|
| Оставляет первый символ, все остальное заменяет на 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 LastName, CAST(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: На данный момент известен баг. Если использовать псевдоним для таблицы или соединять в одном запросе несколько таблиц, то маска не срабатывает. К релизу должны исправить.
