Безопасность на уровне строк
Область применения: 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
основе параметра текущего сеанса. Как правило, функции предикатов должны подчиняться следующим правилам.
Функции предиката не должны неявно преобразовывать символьные строки в дату, smalldatetime, 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) и SET ARITHABORT (Transact-SQL).Функции предиката не должны сравнивать объединенные строки с
NULL
, так как это поведение влияет на параметр SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).
Примечание по безопасности: атаки на стороне канала
Злонамеренный диспетчер политики безопасности
важно иметь в виду, что злонамеренный диспетчер политики безопасности с достаточными разрешениями для создания политики безопасности конфиденциальных столбцов и имеющий разрешения на создание или изменение встроенных функций, возвращающих табличные значения, может вступить в тайный сговор с другим пользователем, который имеет отдельные разрешения на таблицу для выполнения эксфильтрации данных с помощью злонамеренно созданных встроенных функций, возвращающих табличные значения, разработанные для использования атак на стороне канала с целью вывода данных. Такие атаки потребуют сговора (или предоставления избыточных разрешений злоумышленнику) и, скорее всего, нескольких итераций изменения политики (требующих разрешения на удаление предиката, чтобы разорвать привязку схем), а также изменения встроенных функций с табличным значением и многократного выполнения инструкций 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
В этом кратком примере создаются три пользователя и внешняя таблица с шестью строками, а затем создается встроенная функция с табличным значением и политика безопасности для внешней таблицы. Пример показывает как фильтруются отдельные инструкции для разных пользователей.
Необходимые компоненты
- Вам потребуется выделенный пул SQL. См. статью Создание выделенного пула SQL.
- Сервер, на котором размещен выделенный пул SQL, должен быть зарегистрирован с помощью идентификатора Microsoft Entra (ранее Azure Active Directory), и у вас должна быть учетная запись хранения Azure с
Storage Blog Data Contributor
разрешениями. Выполните действия по использованию конечных точек и правил службы виртуальной сети для серверов в База данных SQL Azure. - Создайте файловую систему для учетной записи хранения 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
внешней таблицы. Войдите в систему в качестве каждого пользователя, Sales1
Sales2
и .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 FUNCTION
CREATE 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
таблице.
Связанный контент
- CREATE SECURITY POLICY (Transact-SQL)
- ALTER SECURITY POLICY (Transact-SQL)
- DROP SECURITY POLICY (Transact-SQL)
- CREATE FUNCTION (Transact-SQL)
- SESSION_CONTEXT (Transact-SQL)
- sp_set_session_context (Transact-SQL)
- sys.security_policies (Transact-SQL)
- sys.security_predicates (Transact-SQL)
- Создание пользовательских функций (ядро СУБД)
- GRANT, предоставление разрешений на объект (Transact-SQL)