Безопасность на уровне строкRow-Level Security

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) нетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Графическая схема безопасности на уровне строк Row level security graphic

Безопасность на уровне строк позволяет использовать членство в группе или контекст выполнения для управления доступом к строкам в таблице базы данных.Row-Level Security enables you to use group membership or execution context to control access to rows in a database table.

Безопасность на уровне строк (RLS) упрощает проектирование и кодирование безопасности в приложении.Row-Level Security (RLS) simplifies the design and coding of security in your application. Безопасность на уровне строк помогает реализовать ограничения на доступ к строкам данных.RLS helps you implement restrictions on data row access. Например, вы можете предоставить работникам доступ только к тем строкам данных, которые связаны с работой их отдела.For example, you can ensure that workers access only those data rows that are pertinent to their department. Еще один пример — предоставление доступа для клиентов только к тем данным, которые относятся к их компании.Another example is to restrict customers' data access to only the data relevant to their company.

Логика ограничения находится на уровне базы данных, а не на отдалении от данных на другом уровне приложения.The access restriction logic is located in the database tier rather than away from the data in another application tier. Система базы данных применяет ограничения доступа каждый раз, когда выполняется попытка доступа к данным с любого уровня.The database system applies the access restrictions every time that data access is attempted from any tier. Это делает систему безопасности более надежной и устойчивой за счет уменьшения контактной зоны системы безопасности.This makes your security system more reliable and robust by reducing the surface area of your security system.

Реализуйте безопасность на уровне строк с помощью инструкции CREATE SECURITY POLICYTransact-SQLTransact-SQL и предикатов в виде встроенных функций с табличным значением.Implement RLS by using the CREATE SECURITY POLICYTransact-SQLTransact-SQL statement, and predicates created as inline table-valued functions.

Область применения: SQL ServerSQL ServerSQL Server 2016 (13.x);SQL Server 2016 (13.x) по текущую версию), База данных SQLSQL Database (получить), Хранилище данных SQLSQL Data Warehouse.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x);SQL Server 2016 (13.x) through current version), База данных SQLSQL Database (Get it), Хранилище данных SQLSQL Data Warehouse.

Примечание

Хранилище данных SQL Azure поддерживает только предикаты фильтров.Azure SQL Data Warehouse supports filter predicates only. Предикаты блокирования сейчас не поддерживаются в Хранилище данных SQL Azure.Block predicates aren't currently supported in Azure SQL Data Warehouse.

ОписаниеDescription

Безопасность на уровне строк поддерживает два типа предикатов безопасности.RLS supports two types of security predicates.

  • Предикаты фильтров автоматически фильтруют строки, доступные для операций чтения (SELECT, UPDATE и DELETE).Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).

  • Предикаты BLOCK явно блокируют операции записи (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE), которые нарушают предикат.Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

Доступ к данным на уровне строк в таблице ограничен предикатом безопасности, определяемым как встроенная функция с табличным значением.Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. Эта функция затем вызывается и принудительно исполняется политикой безопасности.The function is then invoked and enforced by a security policy. При использовании предикатов фильтров приложение не учитывает строки, отфильтрованные из результирующего набора.For filter predicates, the application is unaware of rows that are filtered from the result set. При фильтрации всех строк возвращается набор NULL.If all rows are filtered, then a null set will be returned. Что касается предикатов блокировки, любые операции, которые нарушают предикат, будут завершаться ошибкой.For block predicates, any operations that violate the predicate will fail with an error.

Предикаты фильтров применяются при считывании данных из базовой таблицы.Filter predicates are applied while reading data from the base table. Они влияют на все операции Get: SELECT, DELETE и UPDATE.They affect all get operations: SELECT, DELETE and UPDATE. Пользователи не могут выбирать, удалятьThe users can't select or delete rows that are filtered. и обновлять отфильтрованные строки.The user can't update rows that are filtered. Но они могут обновлять строки так, чтобы они были отфильтрованы позже.But, it's possible to update rows in such a way that they'll be filtered afterward. Предикаты блокировки влияют на все операции записи.Block predicates affect all write operations.

  • Предикаты AFTER INSERT и AFTER UPDATE могут блокировать обновление строк значениями, нарушающими предикат.AFTER INSERT and AFTER UPDATE predicates can prevent users from updating rows to values that violate the predicate.

  • Предикаты BEFORE UPDATE могут блокировать обновление строк, нарушающих предикат на данный момент.BEFORE UPDATE predicates can prevent users from updating rows that currently violate the predicate.

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

Предикаты фильтров и блокировки, а также политики безопасности имеют следующие особенности:Both filter and block predicates and security policies have the following behavior:

  • Вы можете определить функцию предиката, которая соединяется с другой таблицей или вызывает функцию.You may define a predicate function that joins with another table and/or invokes a function. Если политика безопасности создана с использованием команды SCHEMABINDING = ON, команда join или функция доступна из запроса и работает должным образом без каких-либо дополнительных проверок разрешений.If the security policy is created with SCHEMABINDING = ON, then the join or function is accessible from the query and works as expected without any additional permission checks. Если политика безопасности создана с использованием SCHEMABINDING = OFF, для отправки запросов в целевую таблицу пользователям потребуются разрешения SELECT и EXECUTE в этих дополнительных таблицах и функциях.If the security policy is created with SCHEMABINDING = OFF, then users will need SELECT or EXECUTE permissions on these additional tables and functions to query the target table.

  • Вы можете выполнить запрос к таблице, имеющей предикат безопасности, который определен, но отключен.You may issue a query against a table that has a security predicate defined but disabled. Все отфильтрованные или заблокированные строки не затрагиваются.Any rows that are filtered or blocked aren't affected.

  • Когда пользователь dbo, член роли db_owner или владелец таблицы выполняет запрос к таблице, для которой определена или включена политика безопасности, строки фильтруются или блокируются в соответствии с этой политикой.If a dbo user, a member of the db_owner role, or the table owner queries a table that has a security policy defined and enabled, the rows are filtered or blocked as defined by the security policy.

  • Попытка изменить схему таблицы, связанной политикой безопасности привязки к схеме, приведет к ошибке.Attempts to alter the schema of a table bound by a schema bound security policy will result in an error. Тем не менее можно изменить столбцы, на которые не ссылается предикат.However, columns not referenced by the predicate can be altered.

  • Попытки добавить предикат в таблицу, в которой уже есть один определенный предикат для указанной операции, приведет к ошибкеAttempts to add a predicate on a table that already has one defined for the specified operation results in an error. (независимо от того, включен ли предикат или отключен).This will happen whether the predicate is enabled or not.

  • Попытка изменить функцию, которая используется в качестве предиката в таблице в рамках привязанной к схеме политики безопасности, приведет к ошибке.Attempts to modify a function, that is used as a predicate on a table within a schema bound security policy, will result in an error.

  • Определение нескольких активных политик безопасности, содержащих неперекрывающиеся предикаты, завершается успешно.Defining multiple active security policies that contain non-overlapping predicates, succeeds.

Предикаты фильтров имеют следующие особенности.Filter predicates have the following behavior:

  • Определение политики безопасности, которая фильтрует строки таблицы.Define a security policy that filters the rows of a table. Приложение не учитывает отфильтрованные строки в операциях SELECT, UPDATE и DELETE,The application is unaware of any rows that are filtered for SELECT, UPDATE, and DELETE operations. включая ситуации, когда все строки исключены. Приложение может применить операцию INSERT к строкам, даже если они будут отфильтрованы во время любой другой операции.Including situations where all the rows are filtered out. The application can INSERT rows, even if they will be filtered during any other operation.

Предикаты блокировки имеют следующие особенности.Block predicates have the following behavior:

  • Предикаты блокировки для операций UPDATE разбиваются на отдельные операции BEFORE и AFTER.Block predicates for UPDATE are split into separate operations for BEFORE and AFTER. Таким образом, нельзя, например, запретить пользователям обновлять строки, добавляя значение, которое больше текущего.Consequently, you can't, for example, block users from updating a row to have a value higher than the current one. Если требуется такая логика, необходимо использовать триггеры с промежуточными таблицами DELETED и INSERTED, чтобы создать ссылки на новые и старые значения.If this kind of logic is required, you must use triggers with the DELETED and INSERTED intermediate tables to reference the old and new values together.

  • Оптимизатор не будет проверять предикат блокировки AFTER UPDATE, если не изменяется ни один из столбцов, используемых функцией предиката.The optimizer will not check an AFTER UPDATE block predicate if the columns used by the predicate function weren't changed. Пример: Алиса не должна иметь возможность изменить зарплату на значение, превышающее 100 000.For example: Alice shouldn't be able to change a salary to be greater than 100,000. Алиса может изменить адрес сотрудника, зарплата которого уже превышает 100 000, если столбцы, ссылки на которые указаны в предикате, не были изменены.Alice can change the address of an employee whose salary is already greater than 100,000 as long as the columns referenced in the predicate weren't changed.

  • Изменения не вносились для пакетных API, в том числе BULK INSERT.No changes have been made to the bulk APIs, including BULK INSERT. Это означает, что предикаты блокировки AFTER INSERT будут применяться к операциям пакетной вставки так же, как к обычным операциям вставки.This means that block predicates AFTER INSERT will apply to bulk insert operations just as they would regular insert operations.

Способы примененияUse Cases

Ниже приведены примеры конструирования использования RLS.Here are design examples of how RLS can be used:

  • Больницы могут создать политику безопасности, которая позволяет медсестрам просматривать строки данных только их пациентов.A hospital can create a security policy that allows nurses to view data rows for their patients only.

  • Банк может создать политику для ограничения доступа к строкам финансовых данных на основе бизнес-подразделения сотрудника либо на основе роли сотрудника в компании.A bank can create a policy to restrict access to financial data rows based on an employee's business division or role in the company.

  • Мультитенантное приложение может создать политику для обеспечения логического разделения строк каждого клиента от строк любых других клиентов.A multi-tenant application can create a policy to enforce a logical separation of each tenant's data rows from every other tenant's rows. Эффективность достигается путем хранения данных для многих клиентов в одной таблице.Efficiencies are achieved by the storage of data for many tenants in a single table. Каждый клиент может видеть только свои строки данных.Each tenant can see only its data rows.

Предикаты фильтров RLS функционально эквивалентны добавлению предложения WHERE .RLS filter predicates are functionally equivalent to appending a WHERE clause. Предикат может по сложности сравниваться с определением деловой практики или предложение может быть простым как WHERE TenantId = 42.The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42.

При использовании более формальных терминов можно сказать, что RLS представляет управление доступом на основе предиката.In more formal terms, RLS introduces predicate based access control. При этом поддерживаются гибкие централизованные вычисления на основе предиката.It features a flexible, centralized, predicate-based evaluation. Предикат может учитывать метаданные или другие критерии, определяемые администратором по своему усмотрению.The predicate can be based on metadata or any other criteria the administrator determines as appropriate. Предикат используется как критерий для определения, имеет ли пользователь необходимый доступ к данным, на основе пользовательских атрибутов.The predicate is used as a criterion to determine if the user has the appropriate access to the data based on user attributes. Управление доступом на основе метки можно реализовать с помощью управления доступом на основе предиката.Label-based access control can be implemented by using predicate-based access control.

PermissionsPermissions

Создание, изменение или удаление политик безопасности требует разрешения ALTER ANY SECURITY POLICY .Creating, altering, or dropping security policies requires the ALTER ANY SECURITY POLICY permission. Создание или удаление политики безопасности требует разрешения ALTER для схемы.Creating or dropping a security policy requires ALTER permission on the schema.

Кроме того, для каждого добавляемого предиката требуются следующие разрешения:Additionally the following permissions are required for each predicate that is added:

  • РазрешенияSELECT и REFERENCES для функции используются как предикат.SELECT and REFERENCES permissions on the function being used as a predicate.

  • РазрешениеREFERENCES для целевой таблицы, которая привязывается к политике.REFERENCES permission on the target table being bound to the policy.

  • РазрешениеREFERENCES для каждого столбца из целевой таблицы, используемого в качестве аргументов.REFERENCES permission on every column from the target table used as arguments.

Политики безопасности применяются ко всем пользователям, включая пользователей dbo в базе данных.Security policies apply to all users, including dbo users in the database. Пользователи dbo могут изменять или удалять политики безопасности, однако можно проводить аудит их изменений в политиках безопасности.Dbo users can alter or drop security policies however their changes to security policies can be audited. Если привилегированным пользователям (например, sysadmin или db_owner) нужно видеть все строки для устранения неполадок или проверки данных, необходимо создать политику безопасности, разрешающую эти действия.If high privileged users, such as sysadmin or db_owner, need to see all rows to troubleshoot or validate data, the security policy must be written to allow that.

Если политика безопасности создается с использованием команды SCHEMABINDING = OFF, то для отправки запроса в целевую таблицу пользователям потребуется разрешение SELECT или EXECUTE в функции предиката и любых дополнительных таблицах, представлениях и функциях, используемых в функции предиката.If a security policy is created with SCHEMABINDING = OFF, then to query the target table, users must have the SELECT or EXECUTE permission on the predicate function and any additional tables, views, or functions used within the predicate function. Если политика безопасности создана с использованием SCHEMABINDING = ON (по умолчанию), при запросе целевой таблицы пользователями эти проверки разрешений не проводятся.If a security policy is created with SCHEMABINDING = ON (the default), then these permission checks are bypassed when users query the target table.

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

  • Мы настоятельно рекомендуем создать отдельную схему для объектов, функции предиката и политики безопасности RLS.It's highly recommended to create a separate schema for the RLS objects, predicate function, and security policy.

  • Разрешение ALTER ANY SECURITY POLICY предназначено для пользователей с высоким уровнем привилегий (например, для администратора политик безопасности).The ALTER ANY SECURITY POLICY permission is intended for highly privileged users (such as a security policy manager). Администратору политик безопасности не требуется разрешение SELECT для защищаемых политиками таблиц.The security policy manager doesn't require SELECT permission on the tables they protect.

  • Избегайте преобразования типов в функциях предиката, чтобы исключить потенциальные ошибки выполнения.Avoid type conversions in predicate functions to avoid potential runtime errors.

  • Там где возможно, избегайте рекурсии в функциях предиката, чтобы не допустить снижения производительности.Avoid recursion in predicate functions wherever possible to avoid performance degradation. Оптимизатор запросов попытается обнаружить прямые рекурсии, но не обязательно найдет косвенные рекурсииThe query optimizer will try to detect direct recursions, but isn't guaranteed to find indirect recursions. (т. е. когда вторая функция вызывает функцию предиката).An indirect recursion is where a second function calls the predicate function.

  • Избегайте использования излишних соединений таблиц в функциях предиката для повышения производительности.Avoid using excessive table joins in predicate functions to maximize performance.

Избегайте реализации логики предикатов, зависящей от параметров SET конкретного сеанса: хотя такое использование на практике маловероятно, функции предиката, логика которых зависит от некоторых параметров SET отдельных сеансов, могут вызвать утечку информации, если пользователи могут выполнять произвольные запросы.Avoid predicate logic that depends on session-specific SET options: While unlikely to be used in practical applications, predicate functions whose logic depends on certain session-specific SET options can leak information if users are able to execute arbitrary queries. Например, функция предиката, которая неявно преобразует строку в datetime , может выполнять фильтрацию разных строк на основе параметра SET DATEFORMAT для текущего сеанса.For example, a predicate function that implicitly converts a string to datetime could filter different rows based on the SET DATEFORMAT option for the current session. Как правило, функции предикатов должны подчиняться следующим правилам.In general, predicate functions should abide by the following rules:

  • Функции предикатов не должны неявно преобразовать строки символов в типы данных date, smalldatetime, datetime, datetime2 или datetimeoffset (и наоборот), поскольку на эти преобразования влияют параметры SET DATEFORMAT (Transact-SQL) и SET LANGUAGE (Transact-SQL).Predicate functions should not implicitly convert character strings to date, smalldatetime, datetime, datetime2, or datetimeoffset, or vice versa, because these conversions are affected by the SET DATEFORMAT (Transact-SQL) and SET LANGUAGE (Transact-SQL) options. Вместо этого используйте функцию CONVERT и явно задайте параметр стиля.Instead, use the CONVERT function and explicitly specify the style parameter.

  • Функции предикатов не должны зависеть от значения первого дня недели, поскольку это значение зависит от параметра SET DATEFIRST (Transact-SQL).Predicate functions should not rely on the value of the first day of the week, because this value is affected by the SET DATEFIRST (Transact-SQL) option.

  • Функции предикатов не должны зависеть от арифметических или агрегатных выражений, возвращающих значение NULL в случае ошибки (например, при переполнении или делении на ноль), так как это поведение определяется параметрами SET ANSI_WARNINGS (Transact-SQL), SET NUMERIC_ROUNDABORT (Transact-SQL) и SET ARITHABORT (Transact-SQL).Predicate functions should not rely on arithmetic or aggregation expressions returning NULL if they error (such as overflow or divide-by-zero), because this behavior is affected by the SET ANSI_WARNINGS (Transact-SQL), SET NUMERIC_ROUNDABORT (Transact-SQL), and SET ARITHABORT (Transact-SQL) options.

  • Функции предикатов не должны сравнивать сцепленные строки с параметром NULL, так как это поведение определяется параметром SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).Predicate functions should not compare concatenated strings with NULL, because this behavior is affected by the SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) option.

Примечание по безопасности. Атаки на стороне каналаSecurity Note: Side-Channel Attacks

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

важно иметь в виду, что злонамеренный диспетчер политики безопасности с достаточными разрешениями для создания политики безопасности конфиденциальных столбцов и имеющий разрешения на создание или изменение встроенных функций, возвращающих табличные значения, может вступить в тайный сговор с другим пользователем, который имеет отдельные разрешения на таблицу для выполнения эксфильтрации данных с помощью злонамеренно созданных встроенных функций, возвращающих табличные значения, разработанные для использования атак на стороне канала с целью вывода данных.It is important to observe that a malicious security policy manager, with sufficient permissions to create a security policy on top of a sensitive column and having permission to create or alter inline table-valued functions, can collude with another user who has select permissions on a table to perform data exfiltration by maliciously creating inline table-valued functions designed to use side channel attacks to infer data. Такие атаки потребуют сговора (или предоставления избыточных разрешений злоумышленнику) и, скорее всего, нескольких итераций изменения политики (требующих разрешения на удаление предиката, чтобы разорвать привязку схем), а также изменения встроенных функций с табличным значением и многократного выполнения инструкций SELECT в целевой таблице.Such attacks would require collusion (or excessive permissions granted to a malicious user) and would likely require several iterations of modifying the policy (requiring permission to remove the predicate in order to break the schema binding), modifying the inline table-valued functions, and repeatedly running select statements on the target table. Мы рекомендуем назначать только необходимые разрешения и отслеживать все подозрительные действия,We recommend you limit permissions as necessary and monitor for any suspicious activity. например постоянное изменение политик и встроенных функций с табличными значениями, затрагивающее безопасность на уровне строк.Activity such as constantly changing policies and inline table-valued functions related to row-level security should be monitored.

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

Использование тщательно созданных запросов может стать причиной утечки информации.It is possible to cause information leakage through the use of carefully crafted queries. Например, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' дает возможность злоумышленнику узнать, что заработная платы Джона До (John Doe) составляет 100 000 долларов.For example, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' would let a malicious user know that John Doe's salary is $100,000. Даже при наличии предиката безопасности для предотвращения ситуаций, когда злонамеренный пользователь может напрямую выполнять запросы о заработной плате других людей, пользователь может определить, когда запрос возвращает исключение деления на ноль.Even though there is a security predicate in place to prevent a malicious user from directly querying other people's salary, the user can determine when the query returns a divide-by-zero exception.

Совместимость с разными компонентамиCross-Feature Compatibility

Как правило, безопасность на уровне строк будет работать должным образом в разных компонентах.In general, row-level security will work as expected across features. Однако существует несколько исключений.However, there are a few exceptions. В этом разделе приводится несколько замечаний и пояснений по использованию безопасности на уровне строк в некоторых других компонентах SQL ServerSQL Server.This section documents several notes and caveats for using row-level security with certain other features of SQL ServerSQL Server.

  • DBCC SHOW_STATISTICS предоставляет статистику по нефильтрованным данным и может вызвать утечку информации, которая должна быть защищена политикой безопасности.DBCC SHOW_STATISTICS reports statistics on unfiltered data, and can leak information otherwise protected by a security policy. По этой причине доступ к просмотру объектов статистики для таблицы с политикой безопасности на уровне строк ограничен.For this reason, access to view a statistics object for a table with a row-level security policy is restricted. Пользователь должен быть владельцем таблицы, членом предопределенной роли сервера sysadmin, предопределенной роли базы данных db_owner или предопределенной роли базы данных db_ddladmin.The user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

  • Filestream. Безопасность на уровне строк не совместима с компонентом Filestream.Filestream: RLS is incompatible with Filestream.

  • PolyBase. Безопасность на уровне строк поддерживается для внешних таблиц Polybase только при использовании Хранилища данных SQL Azure.PolyBase: RLS is supported with Polybase external tables for Azure SQL Data Warehouse only.

  • Таблицы, оптимизированные для памяти. Встроенная функция с табличным значением, которая используется в качестве предиката безопасности для таблицы, оптимизированной для памяти, должна быть определена с помощью параметра WITH NATIVE_COMPILATION.Memory-Optimized Tables: The inline table-valued function used as a security predicate on a memory-optimized table must be defined using the WITH NATIVE_COMPILATION option. Этот параметр позволяет блокировать функции языка, не поддерживаемые в оптимизированных для памяти таблицах, и выдавать соответствующую ошибку во время создания.With this option, language features not supported by memory-optimized tables will be banned and the appropriate error will be issued at creation time. Дополнительные сведения см. в разделе Безопасность на уровне строк в таблицах, оптимизированных для памяти статьи Вводные сведения о таблицах, оптимизированных для памяти.For more information, see the Row-Level Security in Memory Optimized Tables section in Introduction to Memory-Optimized Tables.

  • Индексированные представления. Как правило, политики безопасности можно создавать на основе представлений, а представления — на основе таблиц, связанных политиками безопасности.Indexed views: In general, security policies can be created on top of views, and views can be created on top of tables that are bound by security policies. Тем не менее индексированные представления нельзя создать на основе таблиц с политикой безопасности, так как операции поиска строк через индекс будут обходить политику.However, indexed views cannot be created on top of tables that have a security policy, because row lookups via the index would bypass the policy.

  • Отслеживание измененных данных. Система отслеживания измененных данных может вызвать утечку целых строк, которые должны быть отфильтрованы для доступа членов db_owner или пользователей, являющихся членами "шлюзовой" роли, указанной при включении этой системы для таблицы. (Примечание. Для этой функции можно явно задать значение NULL, чтобы все пользователи имели доступ к измененным данным.)Change Data Capture: Change Data Capture can leak entire rows that should be filtered to members of db_owner or users who are members of the "gating" role specified when CDC is enabled for a table (note: you can explicitly set this function to NULL to enable all users to access the change data). В результате параметр db_owner и члены такой шлюзовой роли могут просматривать все изменения данных в таблице даже при наличии политики безопасности для таблицы.In effect, db_owner and members of this gating role can see all data changes on a table, even if there is a security policy on the table.

  • Отслеживание изменений. Отслеживание изменений может вызвать утечку первичного ключа строк, которые должны быть отфильтрованы для доступа пользователей с разрешениями SELECT и VIEW CHANGE TRACKING.Change Tracking: Change Tracking can leak the primary key of rows that should be filtered to users with both SELECT and VIEW CHANGE TRACKING permissions. Доступ к фактическим значениям данных не предоставляется, становится известно только то, что столбец A был обновлен (вставлен или удален) для строки с первичным ключом B.Actual data values are not leaked; only the fact that column A was updated/inserted/deleted for the row with B primary key. Это создает проблему, если первичный ключ содержит конфиденциальные элементы, например номер социального страхования.This is problematic if the primary key contains a confidential element, such as a Social Security Number. Тем не менее на практике для получения последних данных инструкция CHANGETABLE почти всегда объединена с исходной таблицей.However, in practice, this CHANGETABLE is almost always joined with the original table in order to get the latest data.

  • Полнотекстовый поиск. Запросы, использующие следующие функции полнотекстового и семантического поиска, будут выполняться со сниженной производительностью из-за введения дополнительного соединения для применения безопасности на уровне строк и блокирования утечки первичных ключей строк, которые должны быть отфильтрованы: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.Full-Text Search: A performance hit is expected for queries using the following Full-Text Search and Semantic Search functions, because of an extra join introduced to apply row-level security and avoid leaking the primary keys of rows that should be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Индексы Columnstore. Безопасность на уровне строк совместима с кластеризованными и некластеризованными индексами columnstore.Columnstore Indexes: RLS is compatible with both clustered and nonclustered columnstore indexes. Тем не менее, поскольку безопасность на уровне строк применяет функцию, оптимизатор может изменить план запроса таким образом, чтобы пакетный режим не использовался.However, because row-level security applies a function, it is possible that the optimizer may modify the query plan so that it doesn't use batch mode.

  • Секционированные представления. Предикаты блокировки нельзя определить в секционированных представлениях, а секционированные представления нельзя создавать на основе таблиц, использующих предикаты блокировки.Partitioned Views: Block predicates cannot be defined on partitioned views, and partitioned views cannot be created on top of tables that use block predicates. Предикаты фильтров совместимы с секционированными представлениями.Filter predicates are compatible with partitioned views.

  • Темпоральные таблицы. Темпоральные таблицы совместимы с безопасностью на уровне строк.Temporal tables: Temporal tables are compatible with RLS. Тем не менее предикаты безопасности в текущей таблице не реплицируются автоматически в прежнюю таблицу.However, security predicates on the current table are not automatically replicated to the history table. Чтобы применить политику безопасности для текущей и прежней таблиц, необходимо по отдельности добавить предикат безопасности в каждую таблицу.To apply a security policy to both the current and the history tables, you must individually add a security predicate on each table.

ПримерыExamples

A.A. Сценарий для пользователей, проходящих проверку подлинности в базе данныхScenario for users who authenticate to the database

В этом примере создаются три пользователя, создается и заполняется таблица с шестью строками,This example creates three users and creates and populates a table with six rows. а затем создается встроенная функция с табличным значением и политика безопасности для таблицы.It then creates an inline table-valued function and a security policy for the table. Пример затем показывает, как фильтруются отдельные инструкции для разных пользователей.The example then shows how select statements are filtered for the various users.

Создайте три учетные записи пользователей, демонстрирующие разные возможности доступа.Create three user accounts that will demonstrate different access capabilities.

Примечание

Хранилище данных SQL Azure не поддерживает инструкцию EXECUTE AS USER, поэтому нужно создать учетные данные (CREATE LOGIN) для каждого пользователя заранее.Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so you must CREATE LOGIN for each user beforehand. Затем следует войти в систему в качестве соответствующего пользователя, чтобы проверить это поведение.Later, you will log in as the appropriate user to test this behavior.

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

Создайте таблицу для хранения данных.Create a table to hold data.

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

Заполните таблицу шестью строками данных, показывающими три заказа для каждого торгового представителя.Populate the table with six rows of data, showing three orders for each sales representative.

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;

Предоставьте доступ для чтения к таблице для каждого из пользователей.Grant read access on the table to each of the users.

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

Создайте новую схему и встроенную функцию с табличным значением.Create a new schema, and an inline table-valued function. Функция возвращает 1, если строка в столбце SalesRep та же, что и пользователь, выполняющий запрос (@SalesRep = USER_NAME()) или, если пользователь, выполняющий запрос, является пользователем Manager (USER_NAME() = 'Manager').The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (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';  

Создайте политику безопасности, добавляя функцию в качестве предиката фильтра.Create a security policy adding the function as a filter predicate. Состоянию должно быть присвоено значение ON для включения политики.The state must be set to ON to enable the policy.

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

Дайте разрешение на SELECT функции fn_securitypredicateAllow SELECT permissions to the fn_securitypredicate function

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

Теперь протестируйте предикат фильтрации при выборе из таблицы Sales, как для каждого пользователя.Now test the filtering predicate, by selected from the Sales table as each user.

EXECUTE AS USER = 'Sales1';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Sales2';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales;
REVERT;  

Примечание

Хранилище данных SQL Azure не поддерживает инструкцию EXECUTE AS USER, поэтому войдите в систему как соответствующий пользователь, чтобы проверить указанное выше поведение.Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so log in as the appropriate user to test the above behavior.

Пользователь Manager должен видеть все шесть строк.The Manager should see all six rows. Пользователи Sales1 и Sales2 должны видеть только свои продажи.The Sales1 and Sales2 users should only see their own sales.

Измените политику безопасности, чтобы отключить политику.Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter  
WITH (STATE = OFF);  

Теперь пользователи Sales1 и Sales2 могут видеть все шесть строк.Now Sales1 and Sales2 users can see all six rows.

Подключение к базе данных SQL для очистки ресурсовConnect to the SQL database to clean up resources

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

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

Б.B. Сценарии использования безопасности на уровне строк во внешней таблице Хранилища данных SQL AzureScenarios for using Row Level Security on an Azure SQL Data Warehouse external table

В этом кратком примере создаются три пользователя и внешняя таблица с шестью строками,This short example creates three users and an external table with six rows. а затем создается встроенная функция с табличным значением и политика безопасности для внешней таблицы.It then creates an inline table-valued function and a security policy for the external table. Пример показывает как фильтруются отдельные инструкции для разных пользователей.The example shows how select statements are filtered for the various users.

Создайте три учетные записи пользователей, демонстрирующие разные возможности доступа.Create three user accounts that will demonstrate different access capabilities.

CREATE LOGIN Manager WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales1 WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales2 WITH PASSWORD = 'somepassword'
GO

CREATE USER Manager FOR LOGIN Manager;  
CREATE USER Sales1  FOR LOGIN Sales1;  
CREATE USER Sales2  FOR LOGIN Sales2 ;

Создайте таблицу для хранения данных.Create a table to hold data.

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

Заполните таблицу шестью строками данных, показывающими три заказа для каждого торгового представителя.Populate the table with six rows of data, showing three orders for each sales representative.

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;

Создайте внешнюю таблицу Хранилища данных SQL Azure на основе созданной таблицы Sales.Create an Azure SQL Data Warehouse external table from the Sales table created.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword';

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

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

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

Предоставьте трем пользователям внешней таблицы разрешение SELECT.Grant SELECT for the three users external table.

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

Создайте политику безопасности для внешней таблицы, используя функцию в сеансе A в качестве предиката фильтра.Create a security policy on external table using the function in session A as a filter predicate. Состоянию должно быть присвоено значение ON для включения политики.The state must be set to ON to enable the policy.

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

Теперь протестируйте предикат фильтра, выбрав его из внешней таблицы Sales_ext.Now test the filtering predicate, by selecting from the Sales_ext external table. Выполните вход от имени каждого пользователя: Sales1, Sales2 и manager.Sign in as each user, Sales1, Sales2, and manager. Выполните следующую команду от имени каждого пользователя.Run the following command as each user.

SELECT * FROM Sales_ext;

Пользователь Manager должен видеть все шесть строк.The Manager should see all six rows. Пользователи Sales1 и Sales2 должны видеть данные только своих продаж.The Sales1 and Sales2 users should only see their sales.

Измените политику безопасности, чтобы отключить политику.Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);  

Теперь пользователи Sales1 и Sales2 могут видеть все шесть строк.Now the Sales1 and Sales2 users can see all six rows.

Подключение к базе данных Хранилища данных SQL для очистки ресурсовConnect to the SQL Data Warehouse database to clean up resources

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, чтобы очистить ресурсы.Connect to logical master to clean up resources.

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

В.C. Сценарий для пользователей, подключающихся к базе данных через приложение среднего уровняScenario for users who connect to the database through a middle-tier application

Примечание

Функции предикатов блокировки из этого примера сейчас не поддерживаются для Хранилища данных SQL Azure, поэтому вставка строк с неправильным идентификатором пользователя не блокируется для Хранилища данных SQL Azure.In this example block predicates functionality isn't currently supported for Azure SQL Data Warehouse, hence inserting rows for the wrong user ID isn't blocked with Azure SQL Data Warehouse.

В этом примере показано, как приложение среднего уровня может реализовать фильтрацию подключений, когда пользователи приложения (или клиенты) совместно используют того же пользователя SQL ServerSQL Server (приложение).This example shows how a middle-tier application can implement connection filtering, where application users (or tenants) share the same SQL ServerSQL Server user (the application). Приложение задает идентификатор пользователя текущего приложения в SESSION_CONTEXT (Transact-SQL) после подключения к базе данных, а затем политики безопасности прозрачно фильтруют строки, которые не должны быть видимыми для данного идентификатора, а также запрещают пользователю вставлять строки для другого ИД пользователя.The application sets the current application user ID in SESSION_CONTEXT (Transact-SQL) after connecting to the database, and then security policies transparently filter rows that shouldn't be visible to this ID, and also block the user from inserting rows for the wrong user ID. Другие изменения приложения не требуются.No other app changes are necessary.

Создайте таблицу для хранения данных.Create a table to hold data.

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

Заполните таблицу шестью строками данных, показывающими три заказа для каждого пользователя приложения.Populate the table with six rows of data, showing three orders for each application user.

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);  

Создайте пользователя с низким уровнем привилегий, который будет использоваться приложением для подключения.Create a low-privileged user that the application will use to connect.

-- 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 a new schema and predicate function, which will use the application user ID stored in SESSION_CONTEXT to filter rows.

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.Create a security policy that adds this function as a filter predicate and a block predicate on Sales. Предикату блокировки требуется только операция AFTER INSERT, поскольку BEFORE UPDATE и BEFORE DELETE уже отфильтрованы, а AFTER UPDATE не требуется, так как для столбца AppUserId нельзя указать другие значения из-за разрешения столбца, которое было задано ранее.The block predicate only needs AFTER INSERT, because BEFORE UPDATE and BEFORE DELETE are already filtered, and AFTER UPDATE is unnecessary because the AppUserId column cannot be updated to other values, due to the column permission set earlier.

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.Now we can simulate the connection filtering by selecting from the Sales table after setting different user IDs in SESSION_CONTEXT. На практике приложение отвечает за задание идентификатора текущего пользователя в SESSION_CONTEXT после открытия подключения.In practice, the application is responsible for setting the current user ID in SESSION_CONTEXT after opening a connection.

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  

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

DROP USER AppUser;

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

См. также:See Also

CREATE SECURITY POLICY (Transact-SQL)CREATE SECURITY POLICY (Transact-SQL)
ALTER SECURITY POLICY (Transact-SQL)ALTER SECURITY POLICY (Transact-SQL)
DROP SECURITY POLICY (Transact-SQL)DROP SECURITY POLICY (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
SESSION_CONTEXT (Transact-SQL)SESSION_CONTEXT (Transact-SQL)
sp_set_session_context (Transact-SQL)sp_set_session_context (Transact-SQL)
sys.security_policies (Transact-SQL)sys.security_policies (Transact-SQL)
sys.security_predicates (Transact-SQL)sys.security_predicates (Transact-SQL)
Создание определяемых пользователем функций (компонент Database Engine)Create User-defined Functions (Database Engine)