SQL Server 2016 | Олонцев Сергей

В 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. Получилось довольно объемно, но, на мой взгляд, удалось рассказать все самое важное об этом функционале с примерами.

Несомненно, правильная или, наоборот, неправильная настройка tempdb может очень сильно повлиять на производительность SQL Server. Эта системная база данных является глобальным ресурсом и доступна всем пользователям, которые подключаются к SQL Server, и предназначена, чтобы хранить следующие данные:

Временные объекты, созданные пользователями: временные локальные или глобальные таблицы, табличные переменные, временные процедуры и курсоры.
Внутренние объекты, которые создаются движком SQL Server, например, рабочие таблицы (worktable), которые используются для хранения временных результатов сортировки, а также скрытые буферные таблицы (spool).
Версии строк, которые генерируются при модификации данных в базе, где используются оптимистичные уровни изоляции.
Версии строк, которые генерируются при онлайн перестроении индексов, AFTER триггеров или MARS (Multiple Active Result Sets).

Т.е. база tempdb может активно использовать даже запросами, которые явно не создают никаких временных объектов. Я сейчас не планирую описывать все тонкости конфигурации этой базы, а лишь сосредоточусь на тех изменениях, которые появились в SQL Server 2016.

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

Многие из вас должны быть в курсе, что раньше по умолчанию создавался всего 1 файл данных под tempdb. В ситуациях, когда несколько сессий активно работают с этой системной базой, возникала конкуренция за внутренние ресурсы (contention). Поэтому появилось очень много рекомендаций, сколько же файлов лучше создавать под базу tempdb. По умолчанию инсталлятор руководствуется следующей формулой: минимум из 2х значений, количество ядер на вашей системе и 8. Т.е., если у вас меньше 8 ядер, то будет предложено создать столько файлов, сколько у вас в системе ядер. В остальных случаях будет просто предложено создать 8 файлов, независимо от того, сколько у вас ядер.

Ядер CPU	Количество файлов в tempdb
2	2
4	4
8	8
32	8

Возможно, и даже скорее всего, что 8 файлов для многопроцессорных нагруженных серверов будет мало (нет универсальной формулы, которая бы ответила на этот вопрос, нужно смотреть на характер нагрузки на ваш сервер), но по умолчанию это вполне разумные настройки. Создавать файлов больше, чем у вас ядер в системе, не будет иметь практического смыла, ввиду того, что одновременно с tempdb не будет работать больше сессий, чем у вас ядер в системе.

Далее, что еще бросается в глаза. По умолчанию размер файлов составляет 8 Мб и прирост 64 Мб. Опять же, в большинстве случаев эти цифры необходимо будет увеличить в зависимости от ваших потребностей. Хорошо, на мой взгляд, что в инсталляторе отсутствуют параметры прироста в процентах, что практически всегда является плохой практикой.

Еще стоит отметить, что вы можете указать несколько директорий, в которых будут созданы файлы данных. Файлы будут распределены по этим директориям по алгоритму round-robin. Например, вы указали создать 8 файлов данных и разместить их в 3х директориях. В этом случае установщик расположит их следующим образом:

Файл данных	Директория
tempdb.mdf	1
tempdb_mssql_2.ndf	2
tempdb_mssql_3.ndf	3
tempdb_mssql_4.ndf	1
tempdb_mssql_5.ndf	2
tempdb_mssql_6.ndf	3
tempdb_mssql_7.ndf	1
tempdb_mssql_8.ndf	2

Улучшения в производительности при работе с tempdb

Также в работу tempdb были внесены следующие изменения, направленные на оптимизацию и ускорение выполнения запросов:

Кэширование временных объектов позволяет запросам, которые постоянно удаляют и создают временные объекты работать быстрее и уменьшают конкуренцию за системные ресурсы. В последних версиях SQL Server можно было регулярно видеть изменения и улучшения этого механизма.
Уменьшена нагрузка на журнал транзакций в tempdb, снижено количество требуемых I\O операций.
Доработан алгоритм накладывания latch’ей при выделении страниц, уменьшено их количество.
При приращении tempdb теперь одновременно будет увеличен размер всех файлов (отпадает необходимость включать флаг трассировки 1117). Опция AUTOGROW_ALL_FILES включена по умолчанию и не может быть изменена. Это поможет избежать разбалансирования размеров файлов при постоянно приросте tempdb.
Для временных объектов идет выделение только экстентами (блоками по 8 страниц, 64 кб). Отпадает необходимость включать флаг трассировки 1118. Это также поможет в большей части случаев.

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

Олонцев Сергей

Блог об обработке и анализе данных

Tag Archives: SQL Server 2016

SQL Server 2016: Dynamic Data Masking

SQL Server 2016: конфигурация tempdb во время установки

Улучшения в производительности при работе с tempdb