MASKED WITH: примеры (SQL)

Простые примеры использования

Создание таблицы с различными типами масок.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FullName NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'),
    Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
    PhoneNumber NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(2, "-XXXX-", 2)'),
    CreditCard NVARCHAR(19) MASKED WITH (FUNCTION = 'partial(0, "XXXX-XXXX-XXXX-", 4)'),
    AnnualIncome INT MASKED WITH (FUNCTION = 'random(10000, 50000)'),
    BirthDate DATE MASKED WITH (FUNCTION = 'default()')
);

Вставка тестовых данных:

INSERT INTO Customers VALUES 
(1, 'Иванов Петр Сидорович', 'petr.ivanov@example.com', '+7(912)123-45-67', '1234-5678-9012-3456', 85000, '1985-04-12');

Создание пользователя без права UNMASK и выборка от его имени:

CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON Customers TO TestUser;
EXECUTE AS USER = 'TestUser';
SELECT * FROM Customers;

Результат для пользователя без прав:

CustomerID FullName   Email              PhoneNumber   CreditCard          AnnualIncome BirthDate
1          XXXXX      pXXX@XXXX.com      +7-XXXX-XX-67  XXXX-XXXX-XXXX-3456 32768        1900-01-01

Похожие механизмы в MS SQL

1. Шифрование данных (TDE, Always Encrypted): В отличие от маскирования, шифрование изменяет данные, обеспечивая защиту как при хранении, так и при передаче. MASKED WITH лишь скрывает данные на уровне представления. Шифрование предпочтительнее для защиты данных от физического доступа.

2. Управление доступом на уровне строк (RLS): Row-Level Security позволяет ограничивать доступ к строкам таблицы на основе характеристик пользователя. MASKED WITH маскирует значения в столбцах, а RLS скрывает целые строки. Эти технологии можно комбинировать.

3. Представления (VIEWS) с пользовательской логикой: Можно создать представление, которое будет применять собственную логику маскирования (например, с помощью функций LEFT, RIGHT, REPLICATE). Это дает больше гибкости, но требует ручного поддержания логики.

Аналоги в других СУБД

Oracle: Не имеет прямой аналогии. Обычно используется Virtual Private Database (VPD) для контроля доступа или функции типа SUBSTR в представлениях.

-- Пример ручной маскировки в Oracle
CREATE VIEW masked_customers AS
SELECT id, 
       RPAD(SUBSTR(name,1,1), LENGTH(name), 'X') AS masked_name,
       REGEXP_REPLACE(email, '(.).*@(.).*\.(.*)', '\1***@\2***.\3') AS masked_email
FROM customers;

PostgreSQL: Отсутствует встроенное динамическое маскирование. Применяются представления или расширения типа pgsodium для шифрования.

MySQL: Не предоставляет нативной функциональности DDM. Используются представления.

-- Пример в MySQL
CREATE VIEW masked_customers AS
SELECT id,
       CONCAT(LEFT(name,1), REPEAT('X', LENGTH(name)-1)) AS masked_name
FROM customers;

Общее отличие: MS SQL предоставляет MASKED WITH как декларативную, встроенную функцию безопасности, не требующую написания логики в представлениях. В других СУБД эта задача обычно решается на уровне приложения или сложных представлений.

Типичные ошибки

1. Попытка маскирования неподдерживаемых типов данных.

-- Ошибка: FILESTREAM и некоторые другие типы не поддерживаются
CREATE TABLE Test (
    DataFile FILESTREAM MASKED WITH (FUNCTION = 'default()')
);

2. Применение маски к столбцу, участвующему в FULLTEXT INDEX. Маскированный столбец нельзя использовать в полнотекстовом индексе.

3. Зависимость от разрешения UNMASK. Разработчики иногда забывают, что для служебных операций (например, проверка целостности данных) учетной записи также нужно право UNMASK.

-- Пользователь без UNMASK не сможет выполнить поиск по точному соответствию
EXECUTE AS USER = 'TestUser';
SELECT * FROM Customers WHERE Email = 'petr.ivanov@example.com';
-- Вернет пустой результат, так как сравнение идет с замаскированным значением

Изменения в последних версиях

SQL Server 2016: Появилась первоначальная реализация Dynamic Data Masking с функциями default(), email(), partial(), random().

SQL Server 2017 и выше: Улучшения производительности и интеграции. В Azure SQL Database появилась возможность маскирования на основе правил безопасности для более гибких сценариев.

Важное ограничение: Функция маскирования не предназначена для защиты от атак пользователей с прямым доступом к базе данных или для постоянного шифрования данных. Она является дополнительным уровнем безопасности на уровне представления.

Расширенные примеры

1. Комбинирование с другими функциями в запросах. Для пользователя без UNMASK маскировка применяется после всех вычислений.

EXECUTE AS USER = 'TestUser';
SELECT CustomerID, 
       UPPER(FullName) AS UpperName, -- Будет показано как 'XXXXX'
       REPLACE(Email, '@example.com', '@company.com') AS CompanyEmail -- Будет показано маскированным
FROM Customers;

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

-- Создадим вторую таблицу
CREATE TABLE Orders (
    OrderID INT,
    CustomerEmail NVARCHAR(100) -- Без маски
);
INSERT INTO Orders VALUES (100, 'petr.ivanov@example.com');

-- Для пользователя без UNMASK соединение может не сработать
EXECUTE AS USER = 'TestUser';
SELECT * 
FROM Customers c 
INNER JOIN Orders o ON c.Email = o.CustomerEmail; -- Скорее всего, вернет пустой набор

3. Изменение маски существующего столбца.

ALTER TABLE Customers
ALTER COLUMN PhoneNumber NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(3, ".XXX.", 0)');

4. Использование случайной маски для числовых диапазонов. Функция random() генерирует значение для каждой строки при каждом выполнении запроса.

EXECUTE AS USER = 'TestUser';
SELECT AnnualIncome FROM Customers WHERE CustomerID = 1;
-- При первом выполнении: 24500
-- При втором выполнении: 38760

5. Удаление маски со столбца.

ALTER TABLE Customers
ALTER COLUMN FullName NVARCHAR(100) NOT NULL; -- Просто убираем MASKED WITH