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

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

Доступ к базам данных предоставляется посредством добавления пользователей базы данных и сопоставления пользователям базы данных имен входа.

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

Рассмотренный ранее оператор CREATE LOGIN позволяет указать для логина базу данных, используемую по умолчанию.

Пример: назначается для пользователя Mary база данных Adventure Works в качестве базы данных по умолчанию.

CREATE LOGIN Mary

WITH PASSWORD = '34TY$$543',

DEFAULT_DATABASE =AdventureWorks;

Каждый пользователь базы данных сопоставляется только одному имени входа, за исключением пользователя базы данных dbo (отличие от прежних версий).

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

Члены роли sysadmin сопоставлены пользователю dbo во всех базах данных сервера.

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

Предоставление разрешений на базу данных в sql Server Management Studio.

Имя пользователя базы данных и принадлежность роли.

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

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

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

Пример: создает имя входа Peter и сопоставленного с ним пользователя в базе данных Adventure Works:

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

CREATE LOGIN Peter WITH PASSWORD='Peter123456';

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

USE AdventureWorks;

GO

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

CREATE USER Peter FOR LOGIN Peter;

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

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

SELECT HAS_DBACCESS('AdventureWorks');

Результат: 1

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

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

Следующий пример отзывает разрешение CONNECT для пользователя Peter:

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

USE AdventureWorks;

GO

--Отзываем разрешение connect для Peter on the database AdventureWorks.

REVOKE CONNECT TO Peter;

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

Управление пользователями, утратившими связь с именами входа. Пользователями, утратившими связь с именами входа, называются пользователи базы данных, которые не сопоставлены имени входа в текущем экземпляре SQL Server.

В SQL Server 2005 пользователь может утратить связь с именем входа, если сопоставленное ему имя входа будет удалено

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

Следующий пример создает пользователя, не сопоставленного имени входа.

USE AdventureWorks;

GO

--Создаем пользователя базы данных Paul в базе данных AdventureWorks

--не сопоставляя с ним имени входа в данном экземпляре SQL Server

CREATE USER Paul WITHOUT LOGIN;

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

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

USE AdventureWorks;

GO

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

GRANT CONNECT TO Guest;