Управление доступом к таблицам и столбцам

Таблица и столбцы хранят данные, которые извлекают и создают приложения. Управление доступом к этим данных осуществляется через иерархию разрешений SQL Server 2005. Управлять этой иерархией разрешений можно при помощи инструкций GRANT, DENY и REVOKE.

GRANT. Разрешает роли или пользователю выполнять операции, определенные в момент предоставления разрешения.

DENY. Запрещает пользователю или роли определенные разрешения и предотвращает наследование этих разрешений от других ролей..

REVOKE. Отзывает ранее запрещенные или предоставленные разрешения.

Изменение прав доступа к таблице

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

Предоставляем доступ к таблице

Доступ пользователям базы данных и ролям можно предоставить с помощью инструкции GRANT. В следующем примере разрешения SELECT, INSERT и UPDATE на таблицу Sales.Customer предоставляются пользователю Sara (код для управления доступом к таблицам в этом и следующих разделах имеется в файлах примеров под именем ManagingAccessToTables.sql.).

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Предоставляем пользователю Sara некоторые разрешения
• для таблицы Sales.Customer table.

  GRANT SELECT,INSERT,UPDATE
  ON Sales.Customer TO Sara;

Ограничиваем доступ к таблице

Если нужно не допустить доступ пользователя к таблице, то можно столкнуться с двумя ситуациями. Если вы до этого предоставили пользователю разрешение на эту таблицу, то для удаления ранее предоставленных разрешений следует воспользоваться инструкцией REVOKE. Например:

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Отзываем разрешение SELECT на таблицу Sales.Customer у Sara.

  REVOKE SELECT
  ON Sales.Customer TO Sara;

Однако пользователь может сохранить отозванное разрешение вследствие принадлежности к роли, которой предоставлено данное разрешение. В этом случае необходимо использовать инструкцию DENY, чтобы запретить доступ этому пользователю. Например:

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Запрещаем пользователю Sara разрешение DELETE на таблицу Sales.Customer. независимо от того, какие разрешения этот пользователь мог унаследовать от роли.

  DENY DELETE
  ON Sales.Customer TO Sara;

Предоставление доступа к отдельным столбцам

В SQL Server 2005 есть возможность предоставить или отклонить доступ к отдельным столбцам, вместо того, чтобы работать со всей таблицей. Эта возможность предоставляет гибкость в отклонении доступа, например, к конфиденциальным данным в некоторых столбцах. Разрешения, которыми можно управлять для столбцов таблицы, перечислены в табл. 3.3.

Предоставляем доступ к столбцам

Доступ к отдельным столбцам можно предоставить с помощью инструкции GRANT. В следующем примере разрешения SELECT и UPDATE предоставляются пользователю Sara на столбцы Demographics и Modified Date таблицы Sales.Individual. (Код для управления доступом к столбцам таблицы в этом и следующих разделах имеется в файлах примеров под именем ManagingAccessToColumns. sql.)

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Предоставляем разрешения SELECT и UPDATE пользователю Sara на определенные столбцы таблицы Sales.Individual

  GRANT SELECT,UPDATE (
  Demographics, ModifiedDate) ON Sales.Individual TO Sara;

Отзываем доступ к столбцам

Аналогично, можно отозвать доступ к отдельным столбцам при помощи инструкции REVOKE. Не забывайте, что если нужно не допустить получения пользователем разрешения, необходимо использовать инструкцию DENY.

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Отзываем ранее предоставленные или запрещенные разрешения на столбец Demographics для пользователя Sara.

  REVOKE UPDATE (Demographics)
  ON Sales.Individual TO Sara;

Управление доступом к программируемым объектам

Такие программируемые объекты, как хранимые процедуры и определяемые пользователем функции, имеют свой контекст безопасности. Чтобы выполнять хранимые процедуры, функции и сборки, пользователям базы данных необходимы разрешения. После того, как ядро базы данных выполнит проверку на наличие разрешений на выполнение программируемого объекта, оно проверяет наличие разрешений на выполнение операций, в которых используются программируемые объекты. Когда объекты базы данных последовательно обращаются друг к другу, эта последовательность формирует цепочку владения. О цепочках владения рассказывается далее в этой лекции.

Управление безопасностью для хранимых процедур

Хранимые процедуры - это, вероятно, те объекты базы данных, которые наиболее часто используются разработчиками. Как и другие объекты базы данных, хранимые процедуры являются субъектами системы безопасности. Разработчику необходимо иметь разрешение на выполнение такой операции, как создание хранимой процедуры, а пользователям нужны соответствующие разрешения, чтобы выполнять эту хранимую процедуру. В табл. 3.4 перечислены разрешения, которые могут быть предоставлены для хранимой процедуры.

Выполняем хранимую процедуру

Когда приложение совершает вызов для выполнения хранимой процедуры, SQL Server проверяет, имеет ли текущий пользователь базы данных разрешение EXECUTE для этой хранимой процедуры. В следующем примере разрешение EXECUTE для хранимой процедуры dbo.uspGetBillOfMaterials предоставляется пользователю Sara. (Код в этом и следующих разделах можно найти в файлах примеров под именем ManagingAccessToProgrammableObjects.sql.)

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Предоставляем пользователю Sara разрешение EXECUTE для хранимой процедуры.

  GRANT EXECUTE On dbo.uspGetBillOfMaterials TO Sara;

Аналогично, если вам нужно не допустить выполнение каким-либо пользователем хранимой процедуры, вы можете отозвать ( REVOKE ) или запретить ( DENY ) разрешение EXECUTE для этого пользователя.

Управление безопасностью определяемых пользователем функций

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

Выполняем функции, возвращающие табличное значение

Когда пользователь выполняет функцию, возвращающую табличное значение, SQL Server проверяет, имеет ли этот пользователь разрешение SELECT для данной таблицы. Эти разрешения для функции можно предоставить тем же способом, каким предоставляется разрешение SELECT для таблиц. В следующем примере пользователю Sara предоставляется разрешение SELECT на определяемую пользователем функцию dbo.ufnGetContactInformation.

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Предоставляем Sara разрешение на выполнение пользовательской функции.

  GRANT SELECT ON dbo.ufnGetContactInformation TO Sara;

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

Выполняем функцию, которая возвращает скалярное значение

Для выполнения функции, возвращающей скалярное значение, пользователю необходимо иметь разрешение EXECUTE для этой функции. Разрешение EXECUTE для функции, возвращающей скалярное значение, предоставляется тем же способом, что и разрешение EXECUTE для хранимой процедуры. В следующем примере пользователю Sara предоставляется разрешение EXECUTE для функции dbo.ufnGetStock.

• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Предоставляем Sara разрешение на выполнение пользовательской функции.

  GRANT EXECUTE ON dbo.ufnGetStock TO Sara;