Управление доступом к базам данных SQL Server

Одного предоставления доступа к экземпляру SQL Server будет недостаточно для приложения, которому необходим доступ к данным. Предоставив доступ к экземпляру SQL Server, необходимо предоставить доступ к определенным базам данных. Доступ к базам данных предоставляется посредством добавления пользователей базы данных и сопоставления пользователям базы данных имен входа. Каждое имя входа сопоставляется пользователю базы данных для каждой базы данных, к которой этому имени входа нужен доступ. Каждый пользователь базы данных сопоставляется только одному имени входа, за исключением пользователя базы данных dbo.

Примечание. В предыдущих версиях SQL Server можно было использовать для сопоставления нескольких имен входа одному пользователю базы данных системную хранимую процедуру sp_addalias. Так можно поступить и в SQL Server 2005. Однако эту функцию использовать не рекомендуется, поскольку она считается устаревшей и, возможно, будет удалена из следующих версий программы.

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

Пользователи базы данных - это участники уровня базы данных. Все имена входа, за исключением серверной роли sysadmin, должны быть сопоставлены пользователям, которые, в свою очередь, сопоставлены базе данных, к которой им нужен доступ. Члены роли sysadmin сопоставлены пользователю dbo во всех базах данных сервера.

Добавляем пользователя базы данных

Добавить пользователя базы данных можно при помощи инструкции CREATE USER. Следующий пример кода Transact-SQL создает имя входа Peter и сопоставленного с ним пользователя в базе данных Adventure Works:

• Создаем имя входа Peter

  CREATE LOGIN Peter WITH PASSWORD='Tyu87IOR0';
• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Добавляем пользователя базы данных Peter, который сопоставлен имени входа Peter в БД AdventureWorks.

  CREATE USER Peter FOR LOGIN Peter;

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

Проверить, имеет ли текущее имя входа доступ к базе данных, можно при помощи следующей инструкции:

SELECT HAS_DBACCESS('AdventureWorks');

Чтобы получить информацию о пользователях базы данных, можно воспользоваться представлением каталога sys.database_principals.

Если нужно временно отключить доступ пользователя к базе данных, можно отозвать разрешение CONNECT для этого пользователя. Следующий пример отзывает разрешение CONNECT для пользователя Peter:

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

  USE AdventureWorks;
  GO
• Отзываем разрешение connect для Peter в AdventureWorks.

  REVOKE CONNECT TO Peter;

Удалить пользователя в базы данных можно при помощи инструкции DROP USER.

Предупреждение. В SQL Server 2005 не допускается удаление пользователя, который является владельцем схемы базы данных. О схемах рассказывается далее в этой лекции.

Управляем пользователями, утратившими связь с именами входа

Пользователями, утратившими связь с именами входа, называются пользователи базы данных, которые не сопоставлены имени входа в текущем экземпляре SQL Server. В SQL Server 2005 пользователь может утратить связь с именем входа, если сопоставленное ему имя входа будет удалено. Чтобы получить информацию о таких пользователях, можно выполнить следующий код:

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

  USE AdventureWorks;
  GO
• Составляем отчет обо всех пользователях базы данных, утративших связь с именем входа

  EXECUTE sp_change_users_login @Action='Report';

В SQL Server 2005 допускается создание пользователя, не сопоставленного имени входа, с помощью фразы WITHOUT LOGIN. Пользователи, созданные с помощью фразы WITHOUT LOGIN, не считаются пользователями, утратившими связь с именем входа. Эта возможность может быть очень полезна в ситуации, когда необходимо изменить контекст выполнения какого-либо модуля. О контексте выполнения рассказывается далее в этой лекции. Следующий пример создает пользователя, не сопоставленного имени входа.

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

  USE AdventureWorks;
  GO
• Создаем пользователя базы данных Paul в базе данных AdventureWorks
• не сопоставляя с ним имени входа в данном экземпляре SQL Server instance

  CREATE USER Paul WITHOUT LOGIN;

Включаем пользователя guest

Когда имя входа, которое не имеет сопоставленного пользователя, пытается соединиться с базой данных, SQL Server предпринимает попытку подключения с использованием пользователя Guest. Пользователь Guest создается по умолчанию без предоставления разрешений. Можно включить пользователя guest, предоставив ему разрешение CONNECT, как показано ниже.

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

  USE AdventureWorks;
  GO
• Предоставляем пользователю Guest доступ к базе данных AdventureWorks.

  GRANT CONNECT TO Guest;

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

Предоставление разрешений на базу данных

Создав пользователей базы данных, необходимо управлять разрешениями для этих пользователей. Это можно осуществить, добавляя пользователей в роли базы данных или предоставляя самим пользователям гранулярные разрешения.

Создаем роли базы данных

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

Можно добавлять роли базы данных, если нужно сгруппировать пользователей в соответствии с требованиями определенных разрешений. Следующий пример кода создает роль базы данных с именем Auditors и добавляет пользователя Peter в эту новую роль.

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

  USE AdventureWorks;
  GO
• Создаем роль Auditors в базе данных AdventureWorks.

  CREATE ROLE Auditors;
  GO
• Добавляем пользователя Peter к роли Auditors

  EXECUTE sp_addrolemember "Auditors", "Peter";

Управляем ролями базы данных

Выполнив запрос системной функции IS_MEMBER, можно проверить, принадлежит ли текущий пользователь к какой-либо роли базы данных. В следующем примере мы проверим, принадлежит ли текущий пользователь к роли базы данных db_owner.

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

  USE AdventureWorks;
  GO
• Проверяем, принадлежит ли текущий пользователь к роли db_owner

  SELECT IS_MEMBER ("db_owner");

  Совет. Системную функцию IS_MEMBER можно использовать для проверки того, принадлежит ли текущий пользователь также к определенной группе Windows, как показано в следующем примере:

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

    USE AdventureWorks;
    GO
  • Проверяем, принадлежит ли текущий пользователь к группе Managers в домене ADVWORKS

    SELECT IS_MEMBER ("[ADVWORKS\Managers]");

  Удалить пользователей из роли базы данных можно при помощи системной хранимой процедуры sp_droprolemember. Если нужно удалить роль базы данных, то используется инструкция DROP ROLE. Следующий код удаляет пользователя Peter из роли базы данных Auditors, а затем удаляет роль Auditors.
• Изменяем контекст соединения на базу данных AdventureWorks.

  USE AdventureWorks;
  GO
• Удаляем пользователя Peter из роли Auditors

  EXECUTE sp_droprolemember "Auditors", "Peter";
• Удаляем роль Auditors из текущей базы данных

  DROP ROLE Auditors;

  Предупреждение.SQL Server 2005 не допускает удаления роли, в которой имеются члены. Прежде чем удалять роль базы данных, необходимо удалить из нее всех пользователей.

Предоставляем гранулярные разрешения базы данных

В качестве альтернативы использованию фиксированных ролей базы данных можно предоставлять гранулярные разрешения пользователям и ролям базы данных. Разрешениями можно управлять при помощи инструкций GRANT, DENY и REVOKE. В следующем примере мы предоставим разрешение BACKUP DATABASE пользователю Peter:

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

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

  GRANT BACKUP DATABASE TO Peter;

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