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

Статья
09/16/2024

Область применения: 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 (по умолчанию), при запросе целевой таблицы пользователями эти проверки разрешений не проводятся.

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

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

Важно отметить, что диспетчер вредоносных политик безопасности с достаточными разрешениями на создание политики безопасности на вершине конфиденциального столбца и разрешение на создание или изменение встроенных табличных значений функций может вступить в сговор с другим пользователем, у которого есть разрешения на кражу данных, злонамеренно создавая встроенные табличные функции, предназначенные для использования атак бокового канала для вывода данных. Такие атаки потребуют сговора (или предоставления избыточных разрешений злоумышленнику) и, скорее всего, нескольких итераций изменения политики (требующих разрешения на удаление предиката, чтобы разорвать привязку схем), а также изменения встроенных функций с табличным значением и многократного выполнения инструкций 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 внешней таблицы. Войдите в систему в качестве каждого пользователя, 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'
                   ) ;

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 SCHEMACREATE 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

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

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

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

Description

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

Разрешения

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

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

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

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

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

Примеры

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

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

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

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

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

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

Обратная связь

Дополнительные ресурсы

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

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

Description

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

Разрешения

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

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

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

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

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

Примеры

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

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

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

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

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

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

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

Обратная связь

Дополнительные ресурсы