Пользователи баз данных. Схемы

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

Логины и пользователи баз данных — это совершенно разные объекты. Разделение логинов и пользователей баз данных обеспечивает большую гибкость. Например, пользователь, который входит от имени одного и того же логина, сможет работать в разных базах данных от имени разных пользователей.

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

· В среде Management Studio вызвав команду «Создать пользователя…» в контекстном меню подузла «Безопасность | Пользователи» узла конкретной базы данных дерева обозревателя объектов. В открывшемся окне «Пользователь базы данных - Создать», снимок экрана с которым приведен ниже, необходимо указать два обязательных параметра: имя нового пользователя и выбрать соответствующий ему логин (Windows или SQL Server).

· При помощи команды CREATE USER.

Изменение свойств пользователя и его удаление производится из того же контейнера в Management Studio, что и создание пользователя, а также при помощи команд ALTER USER/DROP USER.

В SQL Server 2000 и в более старых версиях объект пользователя базы данных нес на себе двойную нагрузку: во-первых, он использовался для предоставления разрешений в базе данных, а во-вторых, имя пользователя базы данных использовалось для идентификации объектов. Например, обращение к таблице по полному ее имени могло выглядеть так: SELECT * FROM MyServer.MyDatabase.User1.Table1;

Если разработчик использовал в коде приложения просто имя объекта (например, SELECT * FROM Table1), то при подключении к серверу из этого приложения от имени другого пользователя могли возникнуть проблемы, поскольку вместо User1 подставлялось текущее имя пользователя (а если объект с таким полным именем не был обнаружен, то имя специального пользователя dbo).

Начиная с версии SQL Server 2005 эти две функции разделены. Для предоставления разрешений в базе данных, как и раньше, используется объект пользователя, а для именования объектов в базе данных используется специальный объект схема. Запрос с использованием полного формата имени в SQL Server теперь должен выглядеть так:

SELECT * FROM MyServer.MyDatabase.Schema1.Table1;

Схема формально определяется как набор объектов в базе данных, объединенных общим пространством имен. Проще всего представить себе схему как некий логический контейнер в базе данных, которому могут принадлежать таблицы, представления, хранимые процедуры, пользовательские функции, ограничения целостности, пользовательские типы данных и другие объекты базы данных. Этот контейнер удобно использовать как для именования объектов и их логической группировки, так и для предоставления разрешений. Например, если в базе данных есть набор таблиц со связанными данными, удобно поместить их в одну схему и предоставлять пользователям разрешения на эту схему (т. е. на этот набор таблиц).

Пользователю можно назначить схему по умолчанию. В эту схему SQL Server будет по умолчанию помещать объекты, которые создает этот пользователь. Кроме того, искать объекты, к которым обращается пользователь (например, в случае запроса вида SELECT * FROM Table1), SQL Server также будет в первую очередь в его схеме по умолчанию.

Применение схемы дает ряд дополнительных преимуществ по сравнению со старым подходом:

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

· несколько пользователей (через группы Windows или роли баз данных) могут владеть одной и той же схемой. При этом один пользователь может являться владельцем сразу нескольких схем;

· при удалении пользователя из базы данных не придется переименовывать его объекты;

· упрощается предоставление разрешений для наборов объектов в базе данных.

Список схем можно увидеть в подузле «Безопасность | Схемы» узла конкретной базы данных дерева обозревателя объектов Management Studio.

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

· dbo (от database owner) — пользователь-владелец базы данных. Он автоматически создается для того логина, от имени которого была создана эта база данных. Конечно же, как владелец, он получает полные права на свою базу данных (при помощи встроенной роли базы данных db_owner);

· guest (гость) — этот специальный пользователь предназначен для предоставления разрешений всем логинам, которым не соответствует ни один пользователь в базе данных. По умолчанию у этого пользователя нет права login для базы данных, и, следовательно, работать он не будет. Этот пользователь используется чаще всего для предоставления разрешений логинам на какие-то учебные/тестовые базы данных или на базы данных-справочники, доступные только на чтение;

· INFORMATION_SCHEMA — этому пользователю не может соответствовать ни один логин. Его единственное значение — быть владельцем схемы INFORMATION_SCHEMA, в которой хранятся представления системной информации для базы данных;

· sys — этому специальному пользователю, как и INFORMATION_SCHEMA, не могут соответствовать логины. Он является владельцем схемы sys, которой принадлежат системные объекты базы данных.

Роли базы данных

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

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

Вначале перечислим встроенные роли баз данных:

· public — эта специальная роль предназначена для предоставления разрешений сразу всем пользователям базы данных. Специально сделать пользователя членом этой роли или лишить его членства невозможно. Все пользователи базы данных получают права этой роли автоматически.

· db_owner — этой роли автоматически предоставляются полные права на базу данных. Изначально права этой роли предоставляются только специальному пользователю dbo, а через него — логину, который создал эту базу данных;

· db_accessadmin — роль для сотрудника, ответственного за пользователей базы данных. Этот сотрудник получит возможность создавать, изменять и удалять объекты пользователей баз данных, а также создавать схемы. Других прав в базе данных у него нет;

· db_securityadmin — эта роль дополняет роль db_accessadmin. Сотрудник с правами этой роли получает возможность назначать разрешения на объекты базы данных и изменять членство во встроенных и пользовательских ролях. Прав на создание и изменение объектов пользователей у этой роли нет;

· db_backupoperator — эта роль дает право выполнять резервное копирование базы данных;

· db_ddladmin — эта роль применяется в редких ситуациях, когда пользователю необходимо дать право создавать, изменять и удалять любые объекты в базе данных, не предоставляя прав на информацию, которая содержится в существующих объектах;

· db_datareader и db_datawriter — эти встроенные роли дают право просматривать и изменять соответственно (в том числе добавлять и удалять) любую информацию в базе данных. Очень часто пользователю необходимо дать права на чтение и запись информации во всех таблицах базы данных, не предоставляя ему лишних административных разрешений (на создание и удаление объектов, изменение прав и т. п.). Самый простой вариант в этой ситуации — воспользоваться этими двумя ролями.

· db_denydatareader и db_denydatawriter — эти роли противоположны ролям db_datareader и db_datawriter. Роль db_ denydatareader явно запрещает просматривать какие-либо данные, а db_denydatawriter запрещает внесение изменений. Явный запрет всегда имеет приоритет перед явно предоставленными разрешениями. Обычно эти роли используются в ситуации, когда "разрешаем всем, а потом некоторым запрещаем".

Как уже говорилось ранее, в отличие от серверных ролей, роли баз данных вы можете создавать самостоятельно. Это можно сделать из контекстного меню узла «Безопасность | Роли | Роли базы данных» обозревателя объектов в Management Studio или при помощи команды CREATE ROLE.

Кроме имени роли, при создании можно также указать ее владельца (если это не указано, владельцем роли автоматически станет создавший ее пользователь базы данных). Если вы создаете роль при помощи графического интерфейса, вы можете сразу назначить роль владельцем схемы в базе данных и назначить пользователей, которые будут обладать правами этой роли.

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