Tag Archives: Permissions

SQL Server 2016: Dynamic Data Masking

В SQL Server 2016 появляется новая возможность Dynamic Data Masking, которая позволяет ограничить видимость важных данных для непривилегированных пользователей путем ограничения области видимости. Вы сами сможете указывать, какую часть из этих данных можно отображать. При этом данные в самой базе остаются неизменными, а также не потребуются доработки со стороны существующих приложений.

Давайте рассмотрим это на примере. Допустим у нас в базе данных хранятся номера кредитных карт пользователей, и у нас есть оператор, которому необходимо видеть только 4 последних цифры этого номера (вы должны были довольно часто сталкиваться с этим, при общении с представителями банков). С помощью Dynamic Data Masking мы можем на уровне базы данных задать маску, которая скроет от оператора все цифры кредитных карт, кроме последних четырех.

Существует 4 вида функций, которые можно применять для сокрытия данных.

default – Для строковых полей отображает XXXX, для числовых типов – 0, для дат – 1 января 1900г., а для бинарных – 0x30 (это не что иное, как бинарное представление ASCII кода от символа 0). Null значения не скрываются. Давайте посмотрим на небольшой пример, как это будет работать. В примере я создаю таблицу с колонками различных типов данных, вставляю туда несколько разных значений и проверяю, как отображение работает из-под владельца базы данных и пользователя с правом только на выбор данных.

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	c1_varchar varchar(100) masked with (function = 'default()') null,
	c2_varchar_max varchar(max) masked with (function = 'default()') null,
	c3_nvarchar varchar(100) masked with (function = 'default()') null,
	c4_nvarchar_max varchar(max) masked with (function = 'default()') null,
	c5_int int masked with (function = 'default()') null,
	c6_bit bit masked with (function = 'default()') null,
	c7_uniqueidentifier uniqueidentifier masked with (function = 'default()') null,
	c8_datetime datetime masked with (function = 'default()') null,
	c9_varbinary varbinary(100) masked with (function = 'default()') null,
	c10_varbinary_max varbinary(max) masked with (function = 'default()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	c1_varchar,
	c2_varchar_max,
	c3_nvarchar,
	c4_nvarchar_max,
	c5_int,
	c6_bit,
	c7_uniqueidentifier,
	c8_datetime,
	c9_varbinary,
	c10_varbinary_max
)
values
	('q', 'q', 'qwerty_asdfg', 'qwerty_asdfg', 1, 1, newid(), getutcdate(), 0x01, 0x000102030405),
	('qw', 'qw', 'qwer', 'qwer', 2, 0, newid(), getutcdate(), 0x02, 0x00),
	('qwe', 'qwe', 'qwe', 'qwe', 3, 1, newid(), getutcdate(), 0x03, 0x02),
	('qwer', 'qwer', 'qw', 'qw', 4, 0, newid(), getutcdate(), 0x04, 0x01),
	('qwerty_asdfg', 'qwerty_asdfg', 'q', 'q', 5, 1, '00000000-0000-0000-0000-000000000000', '19000101', 0x30, 0x30),
	(null, null, null, null, null, null, null, null, null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

email – данная функция, как легко догадаться, предназначена для сокрытия адресов электронной почты и работает по следующему алгоритму: отображает первый символ адреса, а после него показывает XXX@XXXX.com, независимо от того, в каком домене у вас адрес, на конце всегда .com. Соответствующий пример для функции email будет выглядеть так:

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	email varchar(200) masked with (function = 'email()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	email
)
values
	('qwe@qwe.com'),
	('zxc@asdzxc.ru'),
	(null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

partial – позволяет более гибко управлять отображением строковых значений. Вы указываете эту функцию в формате partial(N1, “XXXXXXX”, N2), где N1 – количество символов с начала строки, которые можно показать, N2 – с конца, а между ними указываете произвольную маску, которая отобразится вместо остального текста. Если вдруг длина строки не будет превышать указанного количества открытых символов, то вместо нее просто отбразится маска. Давайте посмотрим, как, например, с помощью этой функции можно скрыть номера телефонов, кредитных карт и просто произвольной строки разной длины.

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	phone_number varchar(20) masked with (function = 'partial(3, "-XXX-XX-", 2)') null,
	credit_card char(19) masked with (function = 'partial(0, "XXXX-XXXX-XXXX-", 4)') null,
	custom_string varchar(100) masked with (function = 'partial(5, "XXXXX", 5)') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	phone_number,
	credit_card,
	custom_string
)
values
	('99112345671', '1111-1111-1111-1111', 'qwerty'),
	('99212345672', '1111-1111-1111-2222', 'qwe'),
	('99312345673', '1111-1111-1111-3333', '123456789A'),
	('99412345674', '1111-1111-1111-3333', '123456789AB'),
	('99512345675', '1111-1111-1111-4444', '123456789ABCDEF'),
	(null, null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

random – четвертая функция предназначена для сокрытия числовых типов данных, но в отличии от функции default она позволяет не просто отображать нули, а некое случайно число в заданном диапазоне. Например, для колонки, где у нас указан возраст клиентов, можно генерировать некое случайно число от 18 до 100. Используется в формате random(<начало диапазона>, <конец диапазона>). И давайте посмотрим на соответствующий пример для этой функции:

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	age tinyint masked with (function = 'random(18, 100)') null,
	month tinyint masked with (function = 'random(1, 12)') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	age,
	month
)
values
	(18, 1),
	(19, 2),
	(20, 3),
	(30, 4),
	(55, 5),
	(null, null);
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

При этом мы видим, что если запускать выборку несколько раз подряд, то каждый раз будут выводиться другие данные.

Если вы попробуете выгрузить данные из скрытых столбцов с помощью SSIS или в другую таблицу, то выгрузятся замаскированные значения. От этих сценариев данные защищены.

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	tmp varchar(100) masked with (function = 'default()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	tmp
)
values
	('qwe');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select * into #tmp from dbo.test;
select * from #tmp;
revert;
go

Поддерживаются также операции по изменению функции, удалению или добавлению маскировки для уже существующих таблиц. Также в следующем примере я бы хотел показать, что вы можете выдать пользователю право UNMASK, которое позволит ему просматривать скрытые для остальных данные.

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	tmp1 varchar(100) null,
	tmp2 varchar(100) masked with (function = 'default()') null,
	tmp3 varchar(100) masked with (function = 'default()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	tmp1,
	tmp2,
	tmp3
)
values
	('qwe1@qwe.com', 'qwe2@qwe.com', 'qwe3@qwe.com');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

execute as user = 'test_user';
select * from dbo.test;
revert;
go

alter table [dbo].[test]
alter column tmp1 add masked with (function = 'default()');
go

alter table [dbo].[test]
alter column tmp2 drop masked;
go

alter table [dbo].[test]
alter column tmp3 varchar(100) masked with (function = 'email()');
go

execute as user = 'test_user';
select * from dbo.test;
revert;

grant unmask to test_user;
execute as user = 'test_user';
select * from dbo.test;
revert;

revoke unmask to test_user;
execute as user = 'test_user';
select * from dbo.test;
revert;
go

Есть также некоторые ограничения, которые действуют на замаскированные колонки: они не могут быть зашифрованы с помощью Always Encrypted, не поддерживается FILESTREAM, COLUMN_SET, вычисляемые столбцы, а также они не могут быть ключом для FULLTEXT индекса. Как видим, ограничения довольно небольшие и вполне ожидаемые, поэтому я не считаю их критичными.

Хотя Dynamic Data Masking предназначен для скрытия данных от того, кто их не должен видеть, он не спасет вас, если пользователь будет напрямую подключаться к баз данных и запускать запросы, которые будут вычислять скрытые данные. Эта возможность является лишь дополнением к остальным функциям, обеспечивающим безопасность данных (распределение прав доступа, шифрование, аудит и т.п.). Вы должны четко понимать модель угроз для ваших данных, чтобы правильно выбрать необходимые технологии для их защиты. В конце я хочу привести пример, который может вам позволить вычислять, какие же символы стоят в строке на определенных позициях, даже если строка от вас скрыта.

use [tempdb];
go

if object_id('dbo.test', 'U') is not null
	drop table dbo.test;

create table test (
	id int not null identity(1, 1),
	tmp text masked with (function = 'default()') null,

	constraint pk_test primary key clustered ([id])
);
go

insert into dbo.[test] (
	tmp
)
values
	('qwe');
go

if exists(select * from sys.database_principals where name = 'test_user' and type = 'S' and owning_principal_id is null)
	drop user [test_user];
go

create user test_user without login;
grant select on dbo.test to test_user;
go

select * from dbo.test;
go

execute as user = 'test_user';
select *, datalength(tmp), substring(tmp, 1, 1) from dbo.test;
select * from dbo.test where substring(tmp, 1, 1) = 'q';
select * from dbo.test where substring(tmp, 1, 1) = 'w';
revert;
go

Как мы видим, длина строки тоже скрыта. Попытка выбрать первый символ с помощью функции substring тоже не увенчалась успехом, однако я могу накладывать произвольный фильтр, который может мне проверить, какие же все-таки символы стоят на определенных местах в строке. Поэтому значение строки все-таки может быть раскрыто в определенных случаях.

На этом я бы хотел закончить рассказ о Dynamic Data Masking в SQL Server 2016. Получилось довольно объемно, но, на мой взгляд, удалось рассказать все самое важное об этом функционале с примерами.