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

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

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

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

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

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

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

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

Note

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

Description

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Блочные предикаты ведут себя следующим образом:

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

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

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

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

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

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

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

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

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

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

Permissions

Для создания, изменения или удаления политик безопасности требуется 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 параметров: хотя такие функции-предикаты вряд ли используются в практических приложениях, они могут приводить к утечке информации, если пользователи способны выполнять произвольные запросы. Например, функция предиката, которая неявно преобразует строку в datetime, может фильтровать разные строки в зависимости от параметра SET DATEFORMAT для текущего сеанса. Как правило, функции предикатов должны подчиняться следующим правилам.

  • Предикатные функции не должны неявно преобразовывать символьные строки в типы date, smalldatetime, datetime, datetime2 или datetimeoffset и наоборот, поскольку на эти преобразования влияют параметры SET DATEFORMAT (Transact-SQL) и SET LANGUAGE (Transact-SQL). Вместо этого используйте функцию CONVERT и явно укажите параметр стиля.

  • Функции предиката не должны полагаться на значение первого дня недели, так как это значение влияет на SET DATEFIRST параметр (Transact-SQL) .

  • Функции предиката не должны полагаться на арифметические или агрегирующие выражения, возвращающие NULL , если они ошибаются (например, переполнение или деление на ноль), так как это поведение влияет на SET ANSI_WARNINGS параметры (Transact-SQL), SET NUMERIC_ROUNDABORT (Transact-SQL)и SETSET ARITHABORT (Transact-SQL) .

  • Функции-предикаты не должны сравнивать конкатенированные строки с NULL, поскольку это поведение зависит от параметра SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

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

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

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

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

Можно вызвать утечку информации с помощью тщательно созданных запросов, использующих ошибки для эксфильтрации данных. Например, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; позволит злоумышленнику узнать, что зарплата John 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 с помощью параметра. Этот параметр позволяет блокировать функции языка, не поддерживаемые в оптимизированных для памяти таблицах, и выдавать соответствующую ошибку во время создания. Дополнительные сведения см. в разделе "Безопасность на уровне строк" в таблицах, оптимизированных для памяти.

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

  • Сбор измененных данных: функция Change Data Capture (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.

Examples

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

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

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

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) или если пользователь, выполняющий запрос, является пользователем Manager (@SalesRep = USER_NAME()).USER_NAME() = 'Manager' Этот пример определяемой пользователем табличной функции удобно использовать в качестве фильтра для политики безопасности, создаваемой на следующем шаге.

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;

Теперь проверьте предикат фильтрации, выполнив SELECT из таблицы 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

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

Prerequisites

  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) или если пользователь, выполняющий запрос, является @SalesRep = USER_NAME() пользователем (Manager).USER_NAME() = 'Manager'

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 внешней таблицы. Войдите как каждый пользователь: Sales1, Sales2 и Manager. Выполните следующую команду от имени каждого пользователя.

SELECT * FROM Sales_ext;

Manager должен видеть все шесть строк. Пользователи Sales1Sales2 должны видеть только свои продажи.

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

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

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

Подключитесь к базе данных 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. Сценарий для пользователей, подключающихся к базе данных через приложение среднего уровня

Note

В этом примере функциональность блокирующих предикатов в настоящее время не поддерживается в 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. Для предиката блока нужен только AFTER INSERT, поскольку BEFORE UPDATE и BEFORE DELETE уже отфильтрованы, а AFTER 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;

Теперь проверьте предикат фильтрации, выполнив SELECT из таблицы 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;

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

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

В следующем примере создаются примеры таблиц, которые работают с хранилищем в Microsoft Fabric. Используйте существующие таблицы в конечной точке аналитики SQL в Microsoft Fabric. В конечной точке аналитики SQL в Microsoft Fabric нельзя использовать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

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

Связанный контент

  • Last updated on