Предоставление разрешений уровня строки в SQL ServerGranting Row-Level Permissions in SQL Server

В некоторых случаях требуется более точное управление доступом к данным, чем простое предоставление, отзыв или отклонение предоставленных разрешений.In some scenarios, there is a requirement to control access to data at a more granular level than what simply granting, revoking, or denying permissions provides. Например, в приложении базы данных больницы может требоваться ограничение доступа отдельных врачей, чтобы они имели доступ к сведениям только о своих пациентах.For example, a hospital database application may require individual Doctors to be restricted to accessing information related to only their patients. Подобные требования существуют во многих областях, включая финансовые, юридические, правительственные и военные приложения.Similar requirements exist in many environments, including finance, law, government, and military applications. SQL Server 2016 помогает реализовать эти сценарии, предоставляя функциональность безопасности на уровне строк , которая упрощает и централизует логику доступа на уровне строк в политике безопасности.To help address these scenarios, SQL Server 2016 provides a Row-Level Security feature that simplifies and centralizes row-level access logic in a security policy. В более ранних версиях SQL Server аналогичная функциональность достигается путем использования представлений для внедрения фильтрации на уровне строк.For earlier versions of SQL Server, similar functionality can be achieved by using views to enact row-level filtering.

Реализация фильтрации на уровне строкImplementing Row-level Filtering

Фильтрация на уровне строк используется для приложений, которые хранят сведения в одной таблице, как в приведенном выше примере приложения больницы.Row-level filtering is used for applications storing information in a single table like in the hospital example above. Для реализации фильтрации на уровне строк в каждой строке предусмотрен столбец, в котором задается отличительный параметр, например имя пользователя, метка или другой идентификатор.To implement row-level filtering each row has a column that defines a differentiating parameter, such as a user name, label or other identifier. Вы создаете политику безопасности или представление на основе этой таблицы для фильтрации строк, к которым пользователь имеет доступ.You create either a security policy or a view on the table, which filters the rows that the user can access. Затем вы создаете параметризованные хранимые процедуры, контролирующие типы запросов, которые может выполнять пользователь.You then create parameterized stored procedures, which control the types of queries the user can execute.

В следующем примере показывается, как настроить фильтрацию на уровне строк на основе имени пользователя или имени для входа.The following example describes how to configure row-level filtering based on a user or login name:

  • Создайте таблицу и добавьте в нее столбец для хранения имени.Create the table, adding a column to store the name.

  • Включите фильтрацию на уровне строк следующим образом.Enable row-level filtering:

    • Если вы используете SQL Server версии не ниже 2016 или базу данных SQL Azure, создайте политику безопасности, которая добавляет в таблицу предикат, ограничивающий возвращаемые строки теми, которые соответствуют либо текущему пользователю базы данных (с помощью встроенной функции CURRENT_USER()), либо текущему имени для входа (с помощью встроенной функции SUSER_SNAME()).If you are using SQL Server 2016 or higher, or Azure SQL Database, create a security policy that adds a predicate on the table restricting the rows returned to those that match either the current database user (using the CURRENT_USER() built-in function) or the current login name (using the SUSER_SNAME() built-in function):

      CREATE SCHEMA Security
      GO
      
      CREATE FUNCTION Security.userAccessPredicate(@UserName sysname)
          RETURNS TABLE
          WITH SCHEMABINDING
      AS
          RETURN SELECT 1 AS accessResult
          WHERE @UserName = SUSER_SNAME()
      GO
      
      CREATE SECURITY POLICY Security.userAccessPolicy
          ADD FILTER PREDICATE Security.userAccessPredicate(UserName) ON dbo.MyTable,
          ADD BLOCK PREDICATE Security.userAccessPredicate(UserName) ON dbo.MyTable
      GO
      
    • При использовании версии SQL Server до 2016 аналогичную функциональность можно реализовать с помощью представления:If you are using a version of SQL Server prior to 2016, you can achieve similar functionality using a view:

      CREATE VIEW vw_MyTable
      AS
          RETURN SELECT * FROM MyTable
          WHERE UserName = SUSER_SNAME()
      GO
      
  • Создайте хранимые процедуры для выбора, вставки, обновления и удаления данных.Create stored procedures to select, insert, update, and delete data. Если фильтрация осуществляется с помощью политики безопасности, хранимые процедуры должны выполнять эти операции непосредственно в базовой таблице; если же фильтрация осуществляется с помощью представления, хранимые процедуры должны работать с представлением.If the filtering is enacted by a security policy, the stored procedures should perform these operations on the base table directly; otherwise, if the filtering is enacted by a view, the stored procedures should instead operate against the view. Политика безопасности или представление будет автоматически фильтровать строки, возвращаемые или изменяемые по запросам пользователя, а хранимая процедура будет обеспечивать более жесткую границу безопасности, чтобы предотвратить прямой доступ пользователей через успешно выполняемые запросы, которые могут определять наличие отфильтрованных данных.The security policy or view will automatically filter the rows returned or modified by user queries, and the stored procedure will provide a harder security boundary to prevent users with direct query access from successfully running queries that can infer the existence of filtered data.

  • Для хранимых процедур, которые вставляют данные, получайте имя пользователя при помощи той же функции, которая указана в политике безопасности или в представлении, и вставляйте это значение в столбец UserName.For stored procedures that insert data, capture the user name using the same function specified in the security policy or view, and insert that value into the UserName column.

  • Запретите роли public всякий доступ к таблицам (и представлениям, если они применяются).Deny all permissions on the tables (and views, if applicable) to the public role. Пользователи не смогут наследовать права доступа от других ролей базы данных, поскольку предикат фильтра основан на имени пользователя или имени для входа, а не на ролях.Users will not be able to inherit permissions from other database roles, because the filter predicate is based on user or login names, not on roles.

  • Предоставьте ролям базы данных право доступа EXECUTE для хранимых процедур.Grant EXECUTE on the stored procedures to database roles. Пользователи смогут иметь доступ к данным только через предоставленные хранимые процедуры.Users can only access data through the stored procedures provided.

См. такжеSee also