Предоставление индивидуальных (гранулярных) разрешений в sql Server Management Studio.

Управление индивидуальными разрешениями в Transact sql.

Разрешениями можно управлять при помощи инструкций GRANT, DENY и REVOKE.

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

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

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

Информация о серверных разрешениях доступна из представления каталога sys.server_permissions.

Пользователи могут получить информацию о своих разрешениях с помощью функции fn_my_permissions.

Чтобы уменьшить затраты времени и усилий на обслуживание структуры разрешений, следует назначать разрешения ролям базы данных, а не отдельным пользователям базы данных.

GRANT - выдача разрешений участнику на защищаемый объект.

Синтаксис:

GRANT { ALL [ PRIVILEGES ] }

| permission [ ( column [ ,...n ] ) ] [ ,...n ]

[ ON [ class :: ] securable ] TO principal [ ,...n ]

[ WITH GRANT OPTION ] [ AS principal ]

Аргументы:

ALL -только для совместимости с SQL 92

permission - имя разрешения.

column - указывает имя столбца таблицы, на который предоставляется разрешение. Требуются круглые скобки «()».

class - указывает класс защищаемого объекта, для которого предоставляется разрешение. Квалификатор области «::» обязателен.

securable - указывает защищаемый объект, для которого предоставляется разрешение.

TO principal - имя участника. Состав участников, которым можно предоставлять разрешения, меняется в зависимости от защищаемого объекта.

WITH GRANT OPTION - показывает, что получающему разрешению будет также дана возможность предоставлять указанное разрешение другим участникам.

AS principal - задает участника, от которого участник, выполняющий данный запрос, наследует право на предоставление разрешения.

Пример. Предоставление разрешения SELECT на таблицу (предоставляется разрешение SELECT пользователю RosaQdM на таблицу Person.Address в базе данных AdventureWorks).

USE AdventureWorks;

GRANT SELECT ON OBJECT::Person.Address TO RosaQdM;

GO

Пример. Разрешение BACKUP DATABASE пользователю Peter:

USE AdventureWorks;

GO

--Предоставляем разрешения пользователю базы данных Peter

--на резервное копирование базы данных AdventureWorks.

GRANT BACKUP DATABASE TO Peter;

Пример: разрешения SELECT, INSERT и UPDATE на таблицу Sales.Customer предоставляются пользователю Sara.

USE AdventureWorks;

GO

--Предоставляем пользователю Sara некоторые разрешения

для таблицы Sales.Customer

GRANT SELECT, INSERT, UPDATE

ON Sales.Customer TO Sara;

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

Пример:

USE AdventureWorks;

GO

--Отзываем разрешение SELECT на таблицу Sales.Customer у Sara.

REVOKE SELECT

ON Sales.Customer TO Sara;

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

Пример:

USE AdventureWorks;

GO

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

DENY DELETE

ON Sales.Customer TO Sara;

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

Предоставление разрешения удаляет DENY или REVOKE для этого разрешения на данный защищаемый объект.

REVOKE - удаляет разрешение, выданное или запрещенное ранее.

Синтаксис:

REVOKE [ GRANT OPTION FOR ]

{

[ ALL [ PRIVILEGES ] ]

|

permission [ ( column [ ,...n ] ) ] [ ,...n ]

}

[ ON [ class :: ] securable ]

{ TO | FROM } principal [ ,...n ]

[ CASCADE] [ AS principal ]

Аргументы:

GRANT OPTION FOR - указывает, что право предоставлять указанное разрешение будет отозвано. Данный аргумент необходим при использовании аргумента CASCADE.

permission - имя разрешения.

column - указывает имя столбца таблицы, для которого производится отмена разрешений. Необходимо поставить скобки.

class - указывает класс защищаемого объекта, для которого производится отмена разрешения. Необходимо ввести квалификатор области :: .

securable - указывает защищаемый объект, для которого проводится отмена разрешения.

TO | FROM principal - имя участника.

CASCADE - указывает, что разрешение также отменяется и у участников, получивших доступ через текущего участника. Аргумент CASCADE необходимо использовать совместно с аргументом GRANT OPTION FOR.

Каскадная отмена разрешения, предоставленного с помощью параметра WITH GRANT OPTION, приведет к отмене разрешений GRANT и DENY для этого разрешения.

AS principal - задает участника, от которого участник, выполняющий данный запрос, получает право на отмену разрешения.

Пример. Отмена разрешения SELECT для таблицы (отменяет разрешение SELECT у пользователя RosaQdM для таблицы Person.Address в базе данных AdventureWorks).

USE AdventureWorks;

REVOKE SELECT ON OBJECT::Person.Address FROM RosaQdM;

Используйте инструкцию REVOKE только для удаления разрешений, которые были предоставлены ранее.

DENY - запрет разрешений на объект

Синтаксис:

DENY <permission> [ ,...n ] ON

[ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]

TO <database_principal> [ ,...n ]

[ CASCADE ]

[ AS <database_principal> ]

<permission> ::=

ALL [ PRIVILEGES ] | permission [ ( column [ ,...n ] ) ]

<database_principal> ::=

Database_user | Database_role | Application_role

| Database_user_mapped_to_Windows_User

| Database_user_mapped_to_Windows_Group

| Database_user_mapped_to_certificate

| Database_user_mapped_to_asymmetric_key

| Database_user_with_no_login

Аргументы:

column - указывает имя столбца в таблице, представлении или возвращающей табличное значение функции, для которых запрещается разрешение. Круглые скобки обязательны ( ). Для столбца можно запретить только разрешения SELECT, REFERENCES и UPDATE.

ON [ OBJECT :: ] [ schema_name ] . object_name – указывает объект, для которого запрещается разрешение.

TO <database_principal> - задает участника, для которого запрещается разрешение.

CASCADE - Показывает, что запрещаемое разрешение также запрещается для других участников, которым оно было предоставлено данным участником.

AS <database_principal> - задает участника, от которого участник, выполняющий данный запрос, получает право на запрет разрешения.

Database_user - задает пользователя базы данных.

Database_user_mapped_to_Windows_User - задает пользователя базы данных, сопоставленного с пользователем Windows.

Пример. Запрет разрешения SELECT на таблицу (запрещает разрешение SELECT для пользователя RosaQdM на таблицу Person.Address в базе данных AdventureWorks).

USE AdventureWorks;

DENY SELECT ON OBJECT::Person.Address TO RosaQdM;

GO

Пример. Разрешения SELECT и UPDATE предоставляются пользователю Sara на столбцы Demographics и ModifiedDate таблицы Sales.Individual.

USE AdventureWorks;

GO

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

GRANT SELECT, UPDATE (

Demographics, ModifiedDate) ON Sales.Individual TO Sara;

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

USE AdventureWorks;

GO

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

REVOKE UPDATE (Demographics)

ON Sales.Individual TO Sara;