Поделиться через


Безопасность на уровне строк

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечную точку аналитики SQL Azure Synapse Analytics в хранилище Microsoft Fabric в Microsoft Fabric

Декоративный рисунок безопасности на уровне строк.

Безопасность на уровне строк (RLS) позволяет использовать контекст членства в группах или выполнения для управления доступом к строкам в таблице базы данных.

Безопасность на уровне строк упрощает проектирование и кодирование безопасности в приложении. Безопасность на уровне строк помогает реализовать ограничения на доступ к строкам данных. Например, вы можете предоставить работникам доступ только к тем строкам данных, которые связаны с работой их отдела. Еще один пример — предоставление доступа для клиентов только к тем данным, которые относятся к их компании.

Логика ограничения находится на уровне базы данных, а не на отдалении от данных на другом уровне приложения. Система базы данных применяет ограничения доступа каждый раз, когда выполняется попытка доступа к данным с любого уровня. Это делает систему безопасности более надежной и устойчивой за счет уменьшения контактной зоны системы безопасности.

Реализуйте RLS с помощью инструкции CREATE SECURITY POLICY Transact-SQL и предикаты, созданные как встроенные табличные функции.

Безопасность на уровне строк впервые появилась в SQL Server 2016 (13.x).

Примечание.

Эта статья посвящена sql Server и платформам SQL Azure. Сведения о безопасности на уровне строк в Microsoft Fabric см. в разделе "Безопасность на уровне строк" в Microsoft Fabric.

Description

Безопасность на уровне строк (RLS) поддерживает два типа предикатов безопасности:

  • Предикаты фильтра автоматически фильтруют строки, доступные для операций чтения (SELECT, UPDATEи DELETE).

  • Блокируют предикаты явно блокируют операции записи (AFTER INSERT, AFTER UPDATE, , BEFORE UPDATE), BEFORE DELETEкоторые нарушают предикат.

Доступ к данным на уровне строк в таблице ограничен предикатом безопасности, определяемым как встроенная функция с табличным значением. Эта функция затем вызывается и принудительно исполняется политикой безопасности. При использовании предикатов фильтров приложение не учитывает строки, отфильтрованные из результирующего набора. Если отфильтровываются все строки, возвращается пустой набор. Что касается предикатов блокировки, любые операции, которые нарушают предикат, будут завершаться ошибкой.

Предикаты фильтров применяются при считывании данных из базовой таблицы. Они влияют на все операции получения: SELECT, DELETEи UPDATE. Пользователи не могут выбирать, удалять и обновлять отфильтрованные строки. Но они могут обновлять строки так, чтобы они были отфильтрованы позже. Предикаты блокировки влияют на все операции записи.

  • AFTER INSERT и AFTER UPDATE предикаты могут запретить пользователям обновлять строки до значений, которые нарушают предикат.

  • BEFORE UPDATE предикаты могут запретить пользователям обновлять строки, которые в настоящее время нарушают предикат.

  • BEFORE DELETE предикаты могут блокировать операции удаления.

Предикаты фильтров и блокировки, а также политики безопасности имеют следующие особенности:

  • Вы можете определить функцию предиката, которая соединяется с другой таблицей или вызывает функцию. Если политика безопасности создана с использованием команды SCHEMABINDING = ON (по умолчанию), соединение или функция будут доступными из запроса и будут работать должным образом без каких-либо дополнительных проверок разрешений. Если политика безопасности создана с SCHEMABINDING = OFFпомощью, пользователям потребуются SELECT разрешения на эти дополнительные таблицы и функции для запроса целевой таблицы. Если функция предиката вызывает скалярную функцию CLR, EXECUTE необходимо дополнительное разрешение.

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

  • Если пользователь dbo, член db_owner роли или владелец таблицы запрашивает таблицу с определенной и включенной политикой безопасности, строки фильтруются или блокируются, как определено политикой безопасности.

  • Попытка изменить схему таблицы, связанной политикой безопасности привязки к схеме, приведет к ошибке. Тем не менее можно изменить столбцы, на которые не ссылается предикат.

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

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

  • Определение нескольких активных политик безопасности, содержащих неперекрывающиеся предикаты, завершается успешно.

Предикаты фильтров имеют следующие особенности.

  • Определение политики безопасности, которая фильтрует строки таблицы. Приложение не знает о каких-либо строках, отфильтрованных для SELECTопераций и DELETE тUPDATE. е. Включая ситуации, когда все строки отфильтровываются. Приложение может выполнять INSERT фильтрацию строк, даже если они будут отфильтрованы во время любой другой операции.

Предикаты блокировки имеют следующие особенности.

  • Предикаты UPDATE блоков разделены на отдельные операции для BEFORE и AFTER. Например, невозможно запретить пользователям обновлять строку, чтобы иметь значение выше текущего. Если требуется такая логика, необходимо использовать триггеры с промежуточными таблицами DELETED и INSERTED, чтобы создать ссылки на новые и старые значения.

  • Оптимизатор не проверяет AFTER UPDATE предикат блока, если столбцы, используемые функцией предиката, не были изменены. Например: Алиса не должна быть в состоянии изменить зарплату, чтобы быть больше 100 000. Алиса может изменить адрес сотрудника, зарплата которого уже превышает 100 000, если столбцы, ссылки на которые указаны в предикате, не были изменены.

  • В массовые API не были внесены изменения, в том числе BULK INSERT. Это означает, что предикаты AFTER INSERT блоков применяются к операциям массового вставки так же, как они будут выполнять обычные операции вставки.

Случаи использования

Ниже приведены примеры использования безопасности на уровне строк (RLS):

  • Больницы могут создать политику безопасности, которая позволяет медсестрам просматривать строки данных только их пациентов.

  • Банк может создать политику для ограничения доступа к строкам финансовых данных на основе бизнес-подразделения сотрудника либо на основе роли сотрудника в компании.

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

Предикаты фильтров RLS функционально эквивалентны добавлению WHERE предложения. Предикат может по сложности сравниваться с определением деловой практики или предложение может быть простым как WHERE TenantId = 42.

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

Разрешения

Для создания, изменения или удаления политик безопасности требуется ALTER ANY SECURITY POLICY разрешение. Для создания или удаления политики безопасности требуется ALTER разрешение на схему.

Кроме того, для каждого добавляемого предиката требуются следующие разрешения:

  • SELECT и REFERENCES разрешения для функции, используемой в качестве предиката.

  • REFERENCES разрешение на целевую таблицу, привязанную к политике.

  • REFERENCES разрешение на каждый столбец из целевой таблицы, используемой в качестве аргументов.

Политики безопасности применяются ко всем пользователям, включая пользователей dbo в базе данных. Пользователи dbo могут изменять или удалять политики безопасности, однако можно проводить аудит их изменений в политиках безопасности. Если привилегированным пользователям (например, sysadmin или db_owner) нужно видеть все строки для устранения неполадок или проверки данных, необходимо создать политику безопасности, разрешающую эти действия.

Если политика безопасности создана с SCHEMABINDING = OFFпомощью , то для запроса целевой таблицы пользователи должны иметь SELECT или EXECUTE разрешение на функцию предиката и любые дополнительные таблицы, представления или функции, используемые в функции предиката. Если политика безопасности создана с использованием SCHEMABINDING = ON (по умолчанию), при запросе целевой таблицы пользователями эти проверки разрешений не проводятся.

Рекомендации

  • Мы настоятельно рекомендуем создавать отдельную схему для объектов, функции предиката и политики безопасности RLS. Это поможет разделить разрешения, требуемые для работы этих специальных объектов из целевых таблиц. В базах данных с несколькими клиентами может потребоваться дополнительное разделение для различных политик и функций предикатов, но это не является стандартным решением для каждого случая.

  • Разрешение ALTER ANY SECURITY POLICY предназначено для пользователей с высоким уровнем привилегий (например, диспетчера политик безопасности). Диспетчер политик безопасности не требует SELECT разрешения на таблицы, которые они защищают.

  • Избегайте преобразования типов в функциях предиката, чтобы исключить потенциальные ошибки выполнения.

  • Там где возможно, избегайте рекурсии в функциях предиката, чтобы не допустить снижения производительности. Оптимизатор запросов попытается обнаружить прямые рекурсии, но не обязательно найдет косвенные рекурсии (т. е. когда вторая функция вызывает функцию предиката).

  • Избегайте использования излишних соединений таблиц в функциях предиката для повышения производительности.

Избегайте логики предиката, зависящую от параметров SET для конкретного SET сеанса: вряд ли будет использоваться в практических приложениях, функции предиката, логика которых зависит от определенных параметров сеанса, может утечка информации, если пользователи могут выполнять произвольные запросы. Например, функция предиката, которая неявно преобразует строку в datetime , может фильтровать разные строки на SET DATEFORMAT основе параметра текущего сеанса. Как правило, функции предикатов должны подчиняться следующим правилам.

Примечание по безопасности: атаки на стороне канала

Злонамеренный диспетчер политики безопасности

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

Тщательно созданные запросы

Можно вызвать утечку информации с помощью тщательно созданных запросов, использующих ошибки для эксфильтрации данных. Например, сообщить злоумышленнику знать, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; что зарплата Джона Doe составляет ровно $ 100 000. Даже при наличии предиката безопасности для предотвращения ситуаций, когда злонамеренный пользователь может напрямую выполнять запросы о заработной плате других людей, пользователь может определить, когда запрос возвращает исключение деления на ноль.

Совместимость между функциями

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

  • DBCC SHOW_STATISTICS сообщает статистику по нефильтрованным данным и может утечь информацию в противном случае, защищенную политикой безопасности. По этой причине доступ к просмотру объектов статистики для таблицы с политикой безопасности на уровне строк ограничен. Пользователь должен иметь таблицу или пользователь должен быть членом sysadmin предопределенных ролей сервера, db_owner предопределенных ролей базы данных или db_ddladmin предопределенных ролей базы данных.

  • Filestream: RLS несовместим с Filestream.

  • PolyBase: RLS поддерживается с внешними таблицами в Azure Synapse и SQL Server 2019 CU7 или более поздних версиях.

  • Оптимизированные для памяти таблицы: встроенная табличная функция, используемая в качестве предиката безопасности для оптимизированной для памяти таблицы, должна быть определена WITH NATIVE_COMPILATION с помощью параметра. Этот параметр позволяет блокировать функции языка, не поддерживаемые в оптимизированных для памяти таблицах, и выдавать соответствующую ошибку во время создания. Дополнительные сведения см. в разделе "Безопасность на уровне строк" в таблицах, оптимизированных для памяти.

  • Индексированные представления. Как правило, политики безопасности можно создавать на вершине представлений, а представления можно создавать в верхней части таблиц, привязанных политиками безопасности. Тем не менее индексированные представления нельзя создать на основе таблиц с политикой безопасности, так как операции поиска строк через индекс будут обходить политику.

  • Запись измененных данных: запись измененных данных (CDC) может утечка целых строк, которые должны быть отфильтрованы для членов db_owner или пользователей, являющихся членами роли "gating", указанной при включении CDC для таблицы. Эту функцию можно явно задать, чтобы NULL все пользователи могли получать доступ к измененным данным. Фактически, и члены этой роли гистога могут видеть все изменения данных в таблице, db_owner даже если в таблице есть политика безопасности.

  • Отслеживание изменений: Отслеживание изменений может утечка первичного ключа строк, которые должны быть отфильтрованы пользователям с разрешениями.SELECT VIEW CHANGE TRACKING Фактические значения данных не утечки; только тот факт, что столбец A был обновлен или вставлен или удален для строки с определенным первичным ключом. Это создает проблему, если первичный ключ содержит конфиденциальные элементы, например номер социального страхования. Однако на практике это CHANGETABLE почти всегда присоединяется к исходной таблице, чтобы получить последние данные.

  • Полнотекстовый поиск. Ожидается снижение производительности для запросов с помощью следующих функций полнотекстового поиска и семантического поиска, из-за дополнительного соединения, введенного для применения безопасности на уровне строк и предотвращения утечки первичных ключей строк, которые следует фильтровать: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, , semanticsimilaritydetailstable. semanticsimilaritytable

  • Индексы columnstore: RLS совместим с кластеризованными и некластеризованными индексами columnstore. Тем не менее, поскольку безопасность на уровне строк применяет функцию, оптимизатор может изменить план запроса таким образом, чтобы пакетный режим не использовался.

  • Секционированные представления. Блокировать предикаты нельзя определить в секционированных представлениях, а секционированные представления нельзя создать на вершине таблиц, использующих предикаты блоков. Предикаты фильтров совместимы с секционированными представлениями.

  • Темпоральные таблицы: темпоральные таблицы совместимы с RLS. Тем не менее предикаты безопасности в текущей таблице не реплицируются автоматически в прежнюю таблицу. Чтобы применить политику безопасности для текущей и прежней таблиц, необходимо по отдельности добавить предикат безопасности в каждую таблицу.

Прочие ограничения

  • Microsoft Fabric и Azure Synapse Analytics поддерживают только предикаты фильтров. Предикаты блоков в настоящее время не поддерживаются в Microsoft Fabric и Azure Synapse Analytics.

Примеры

А. Сценарий для пользователей, проходящих проверку подлинности в базе данных

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

Создайте три учетные записи пользователей, демонстрирующие различные возможности доступа.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO

Создайте таблицу для хранения данных.

CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
    (
    OrderID int,
    SalesRep nvarchar(50),
    Product nvarchar(50),
    Quantity smallint
    );

Заполните таблицу шестью строками данных, показывающими три заказа для каждого торгового представителя.

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;

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

GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO

Создайте новую схему и встроенную функцию с табличным значением. Функция возвращается, когда строка в столбце совпадает 1 с пользователем, выполняющим запрос (@SalesRep = USER_NAME()) или если пользователь, выполняющий запрос, является пользователем Manager (USER_NAME() = 'Manager').SalesRep В этом примере определяемой пользователем табличной функции полезно служить фильтром политики безопасности, созданной на следующем шаге.

CREATE SCHEMA Security;
GO
  
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

Создайте политику безопасности, добавляя функцию в качестве предиката фильтра. Необходимо STATE задать для ON включения политики.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO

Разрешить SELECT разрешения функции tvf_securitypredicate :

GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;

Теперь протестируйте предикат фильтрации, выбрав из Sales.Orders таблицы в качестве каждого пользователя.

EXECUTE AS USER = 'SalesRep1';
SELECT * FROM Sales.Orders;
REVERT;
  
EXECUTE AS USER = 'SalesRep2';
SELECT * FROM Sales.Orders;
REVERT;
  
EXECUTE AS USER = 'Manager';
SELECT * FROM Sales.Orders;
REVERT;

Менеджер должен видеть все шесть строк. Пользователи Sales1 Sales2 должны видеть только свои собственные продажи.

Измените политику безопасности, чтобы отключить политику.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Теперь Sales1 и Sales2 пользователи могут видеть все шесть строк.

Подключитесь к базе данных SQL, чтобы очистить ресурсы из этого примера упражнения:

DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;

B. Сценарии использования безопасности на уровне строк во внешней таблице Azure Synapse

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

Необходимые компоненты

  1. Вам потребуется выделенный пул SQL. См. статью Создание выделенного пула SQL.
  2. Сервер, на котором размещен выделенный пул SQL, должен быть зарегистрирован с помощью идентификатора Microsoft Entra (ранее Azure Active Directory), и у вас должна быть учетная запись хранения Azure с Storage Blog Data Contributor разрешениями. Выполните действия по использованию конечных точек и правил службы виртуальной сети для серверов в База данных SQL Azure.
  3. Создайте файловую систему для учетной записи хранения Azure. Используйте обозреватель служба хранилища Azure для просмотра учетной записи хранения. Щелкните правой кнопкой мыши контейнеры и выберите "Создать файловую систему".

Создайте три учетные записи пользователей, демонстрирующие различные возможности доступа.

--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO

--run in both the master database and in your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;
CREATE USER Sales1  FOR LOGIN Sales1;
CREATE USER Sales2  FOR LOGIN Sales2 ;

Создайте таблицу для хранения данных.

CREATE TABLE Sales
    (
    OrderID int,
    SalesRep sysname,
    Product varchar(10),
    Qty int
    );

Заполните таблицу шестью строками данных, показывающими три заказа для каждого торгового представителя.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;

Создайте внешнюю таблицу Azure Synapse из только что созданной Sales таблицы.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);
  
CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

Предоставьте select для трех пользователей во внешней таблице Sales_ext , созданной вами.

GRANT SELECT ON Sales_ext TO Sales1;
GRANT SELECT ON Sales_ext TO Sales2;
GRANT SELECT ON Sales_ext TO Manager;

Создайте новую схему и встроенную табличную функцию, возможно, вы выполнили это в примере A. Функция возвращается, когда строка в столбце совпадает 1 с пользователем, выполняющим запрос (@SalesRep = USER_NAME()) или если пользователь, выполняющий запрос, является Manager пользователем (USER_NAME() = 'Manager').SalesRep

CREATE SCHEMA Security;
GO
  
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

Создайте политику безопасности для внешней таблицы, используя встроенную функцию с табличным значением в качестве предиката фильтра. Необходимо STATE задать для ON включения политики.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext
WITH (STATE = ON);

Теперь проверьте предикат фильтрации, выбрав из Sales_ext внешней таблицы. Войдите в систему в качестве каждого пользователя, Sales1Sales2и .Manager Выполните следующую команду от имени каждого пользователя.

SELECT * FROM Sales_ext;

Должно Manager отобразиться все шесть строк. Пользователи Sales1 Sales2 должны видеть только свои продажи.

Измените политику безопасности, чтобы отключить политику.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

Теперь пользователи Sales1 Sales2 могут видеть все шесть строк.

Подключитесь к базе данных Azure Synapse, чтобы очистить ресурсы из этого примера упражнения:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred;
DROP MASTER KEY;

Подключитесь к базе данных логического сервера master для очистки ресурсов:

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C. Сценарий для пользователей, подключающихся к базе данных через приложение среднего уровня

Примечание.

В этом примере функции предикатов блоков в настоящее время не поддерживаются для Microsoft Fabric и Azure Synapse, поэтому вставка строк для неправильного идентификатора пользователя не блокируется.

В этом примере показано, как приложение среднего уровня может реализовать фильтрацию подключений, где пользователи приложений (или клиенты) совместно используют одного пользователя SQL Server (приложение). Приложение задает текущий идентификатор пользователя приложения в SESSION_CONTEXT после подключения к базе данных, а затем политики безопасности прозрачно фильтруют строки, которые не должны отображаться для этого идентификатора, а также блокируют вставку строк для неправильного идентификатора пользователя. Другие изменения приложения не требуются.

Создайте таблицу для хранения данных.

CREATE TABLE Sales (
    OrderId int,
    AppUserId int,
    Product varchar(10),
    Qty int
);

Заполните таблицу шестью строками данных, показывающими три заказа для каждого пользователя приложения.

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);

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

-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;
  
-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;

Создайте новую схему и функцию предиката, которая будет использовать идентификатор пользователя приложения, хранящийся для SESSION_CONTEXT() фильтрации строк.

CREATE SCHEMA Security;
GO
  
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

Создайте политику безопасности, которая добавляет эту функцию в качестве предиката фильтра и предиката блокировки для Sales. Предикат блока требуется только потому, что он BEFORE DELETE уже фильтруется и AFTER UPDATE не требуетсяAFTER INSERT, так как BEFORE UPDATE AppUserId столбец не может быть обновлен до других значений из-за заданного ранее набора разрешений столбца.

CREATE SECURITY POLICY Security.SalesFilter
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);

Теперь мы можем имитировать фильтрацию подключений, выбрав из Sales таблицы после настройки различных идентификаторов пользователей.SESSION_CONTEXT() На практике приложение отвечает за настройку текущего идентификатора SESSION_CONTEXT() пользователя после открытия подключения. @read_only Задание параметра, чтобы 1 предотвратить повторное изменение значения, пока подключение не будет закрыто (возвращается в пул подключений).

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO
  
/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;
  
SELECT * FROM Sales;
GO
  
INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO
  
REVERT;
GO

Очистите ресурсы базы данных.

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

D. Сценарий применения таблицы подстановки для предиката безопасности

В этом примере таблица подстановки применяется для связи между идентификатором пользователя и фильтруемым значением, чтобы не пришлось указывать сам идентификатор пользователя в таблице фактов. Он создает три пользователя и создает и заполняет таблицу фактов с Sample.Salesшестью строками и таблицей подстановки с двумя строками. Затем создается встроенная функция с табличным значением, которая объединяет таблицы фактов и подстановки для получения идентификатора пользователя и политики безопасности для таблицы. Пример затем показывает, как фильтруются отдельные инструкции для разных пользователей.

Создайте три учетные записи пользователей, демонстрирующие различные возможности доступа.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;

Создайте схему Sample и таблицу фактов для Sample.Salesхранения данных.

CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
    (
    OrderID int,
    Product varchar(10),
    Qty int
    );

Sample.Sales Заполните шесть строк данных.

INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sample.Sales;

Создайте таблицу для хранения данных подстановки — в данном случае связь между Salesrep и Product.

CREATE TABLE Sample.Lk_Salesman_Product
  ( Salesrep sysname,
    Product varchar(10)
  ) ;

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

INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;

Предоставьте каждому из пользователей доступ на чтение к таблице фактов.

GRANT SELECT ON Sample.Sales TO Manager;
GRANT SELECT ON Sample.Sales TO Sales1;
GRANT SELECT ON Sample.Sales TO Sales2;

Создайте новую схему и встроенную табличную функцию. Функция возвращается 1 , когда пользователь запрашивает таблицу Sample.Sales фактов и SalesRep столбец таблицы Lk_Salesman_Product совпадает с тем, что пользователь, выполняющий запрос (@SalesRep = USER_NAME()) при присоединении к таблице Product фактов в столбце, или если пользователь, выполняющий запрос, является Manager пользователем (USER_NAME() = 'Manager').

CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate
         (@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
           RETURN ( SELECT 1 as Result
                     FROM Sample.Sales f
            INNER JOIN Sample.Lk_Salesman_Product s
                     ON s.Product = f.Product
            WHERE ( f.product = @Product
                    AND s.SalesRep = USER_NAME() )
                 OR USER_NAME() = 'Manager'
                   ) ;

Создайте политику безопасности, добавляя функцию в качестве предиката фильтра. Необходимо STATE задать для ON включения политики.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;

Разрешить SELECT разрешения функции fn_securitypredicate :

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT SELECT ON Security.fn_securitypredicate TO Sales1;
GRANT SELECT ON Security.fn_securitypredicate TO Sales2;

Теперь протестируйте предикат фильтрации, выбрав из Sample.Sales таблицы в качестве каждого пользователя.

EXECUTE AS USER = 'Sales1';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for 'Sales1' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Sales2';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for 'Sales2' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;

Должно Manager отобразиться все шесть строк. Пользователи Sales1 Sales2 должны видеть только свои собственные продажи.

Измените политику безопасности, чтобы отключить политику.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Теперь Sales1 и Sales2 пользователи могут видеть все шесть строк.

Подключитесь к базе данных SQL, чтобы очистить ресурсы из этого примера упражнения:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security;
DROP SCHEMA Sample;

Е. Сценарий безопасности на уровне строк в Microsoft Fabric

Мы можем продемонстрировать хранилище безопасности на уровне строк и конечную точку аналитики SQL в Microsoft Fabric.

В следующем примере создаются примеры таблиц, которые будут работать с хранилищем в Microsoft Fabric, но в конечной точке аналитики SQL используются существующие таблицы. В конечной точке аналитики SQL нельзя использоватьCREATE TABLE, но можно использовать CREATE SCHEMAи CREATE FUNCTIONCREATE SECURITY POLICY.

В этом примере сначала создайте схему sales, таблицу sales.Orders.

CREATE SCHEMA sales;
GO

-- Create a table to store sales data
CREATE TABLE sales.Orders (
    SaleID INT,
    SalesRep VARCHAR(100),
    ProductName VARCHAR(50),
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

-- Insert sample data
INSERT INTO sales.Orders (SaleID, SalesRep, ProductName, SaleAmount, SaleDate)
VALUES
    (1, 'Sales1@contoso.com', 'Smartphone', 500.00, '2023-08-01'),
    (2, 'Sales2@contoso.com', 'Laptop', 1000.00, '2023-08-02'),
    (3, 'Sales1@contoso.com', 'Headphones', 120.00, '2023-08-03'),
    (4, 'Sales2@contoso.com', 'Tablet', 800.00, '2023-08-04'),
    (5, 'Sales1@contoso.com', 'Smartwatch', 300.00, '2023-08-05'),
    (6, 'Sales2@contoso.com', 'Gaming Console', 400.00, '2023-08-06'),
    (7, 'Sales1@contoso.com', 'TV', 700.00, '2023-08-07'),
    (8, 'Sales2@contoso.com', 'Wireless Earbuds', 150.00, '2023-08-08'),
    (9, 'Sales1@contoso.com', 'Fitness Tracker', 80.00, '2023-08-09'),
    (10, 'Sales2@contoso.com', 'Camera', 600.00, '2023-08-10');

Создайте схему Security , функцию Security.tvf_securitypredicateи политику SalesFilterбезопасности.

-- Creating schema for Security
CREATE SCHEMA Security;
GO

-- Creating a function for the SalesRep evaluation
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'manager@contoso.com';
GO
 
-- Using the function to create a Security Policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);
GO

После применения политики безопасности и создания функции пользователи смогут просматривать собственные данные в sales.Orders таблице, где столбец SalesRep равен собственному имени пользователя, возвращаемого встроенной функцией USER_NAME().Sales2@contoso.com Sales1@contoso.com Пользователь manager@contoso.com Fabric может просматривать все данные в sales.Orders таблице.