Пользователи баз данных. Схемы
После создания
логинов следующая задача — спуститься
на уровень базы данных и создать пользователей
базы данных. Пользователи баз данных
— это специальные объекты, которые создаются
на уровне базы данных и используются
для предоставления разрешений в базе
данных (на таблицы, представления, хранимые
процедуры и т.д.).
Логины и
пользователи баз данных — это
совершенно разные объекты. Разделение
логинов и пользователей баз данных обеспечивает
большую гибкость. Например, пользователь,
который входит от имени одного и того
же логина, сможет работать в разных базах
данных от имени разных пользователей.
Создать пользователя
базы данных можно:
- В среде 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.
Кроме имени
роли, при создании можно также
указать ее владельца (если это не
указано, владельцем роли автоматически
станет создавший ее пользователь базы
данных). Если вы создаете роль при помощи
графического интерфейса, вы можете сразу
назначить роль владельцем схемы
в базе данных и назначить пользователей,
которые будут обладать правами
этой роли.
Встроенным
ролям назначены предопределенные
права, изменить которые невозможно.
Предоставление прав пользовательским
ролям производится точно так
же, как и обычным пользователям
базы данных.
Предоставление прав на объекты
в базе данных
Работа с
разрешениями производится одинаково
для всех объектов базы данных:
- на вкладке «Разрешения» свойств этого
объекта (эта вкладка предусмотрена не
для всех объектов, для которых можно предоставить
разрешения);
- на странице «Защищаемые объекты» окна
свойств пользователя или роли;
- при помощи команд GRANT (предоставить разрешение),
DENY (явно запретить что-то делать) и REVOKE
(отменить явно предоставленное разрешение
или запрет).
Начиная c версии
2005, в SQL Serve появилась возможность предоставлять
разрешения на уровне схемы. К схеме в
SQL Server могут относиться таблицы, представления,
хранимые процедуры, пользовательские
функции, ограничения целостности, пользовательские
типы данных и другие объекты, на которые
приходится предоставлять разрешения
чаще всего. Если вы назначите пользователю
разрешения на схему, то он получит разрешения
на все объекты этой схемы.
Далее перечислены
разрешения, которые можно предоставить
на уровне схемы. Мы приведем только разрешения
для этого объекта, поскольку разрешения
схемы включают в себя разрешения, которые
можно предоставить другим объектам, например,
разрешения SELECT для таблиц и представлений
и EXECUTE для хранимых процедур.
- ALTER — возможность вносить любые
изменения в свойства объекта (за исключением
смены владельца).
- CONTROL — тот, кому предоставлено такое
разрешение, получает полные права как
на сам объект, так и на информацию в нем.
- DELETE — возможность удалять существующую
информацию в таблицах. Применяется к
таблицам, представлениям и столбцам.
- EXECUTE — право запускать на выполнение.
Применяется к хранимым процедурам и функциям.
- INSERT — право на вставку новых данных
в таблицы. Применяется к таблицам, представлениям
и столбцам.
- REFERENCES — разрешение, которое можно
предоставить для проверки ограничений
целостности. Например, пользователь может
добавлять данные в таблицу с внешним
ключом, а на таблицу с первичным ключом
ему нельзя предоставлять права на просмотр.
В этом случае на таблицу с первичным ключом
ему можно предоставить право REFERENCES —
и он сможет производить вставку данных
в таблицу с внешним ключом, не получая
лишних прав на главную таблицу. Это разрешение
можно предоставлять на таблицы, представления,
столбцы и функции.
- SELECT — право на чтение информации.
Предоставляется для таблиц, представлений,
столбцов и табличных функций.
- TAKE OWNERSHIP — право на принятие на себя
владения данным объектом. Владелец автоматически
обладает полными правами на свой объект.
Такое право можно назначить для любых
объектов.
- UPDATE — возможность вносить изменения
в существующие записи в таблице. Предоставляется
на таблицы, представления и столбцы.
- VIEW DEFINITION — право на просмотр определения
для данного объекта. Предусмотрено для
таблиц, представлений, процедур и функций.
Для каждого
разрешения мы можем установить три
значения:
- GRANT — разрешение предоставлено явно;
- WITH GRANT — разрешение не только предоставлено
данному пользователю, но он также получил
право предоставлять это разрешение другим
пользователям. Можно предоставлять, конечно,
только вместе с GRANT;
- DENY — явный запрет на выполнение
действия, определенного данным разрешением.
Как в любых системах безопасности, явный
запрет имеет приоритет перед явно предоставленными
разрешениями.
Из кода Transact-SQL
разрешения можно предоставлять командами
GRANT, DENY и REVOKE. Например, чтобы предоставить
пользователю User1 возможность просматривать
данные в таблице Table1 в схеме dbo, можно
воспользоваться командой:
GRANT SELECT ON dbo.Table1 TO User1;
Лишить его
ранее предоставленного права можно
при помощи команды:
REVOKE SELECT ON dbo.Table1 TO User1;
Некоторые рекомендации,
связанные с предоставлением
разрешений:
- В большинстве реальных задач используются
десятки и даже сотни таблиц и других объектов
базы данных. Предоставлять каждому пользователю
разрешения на каждый из этих объектов
очень неудобно. Если есть возможность,
удобнее использовать разрешения на уровне
схемы или всей базы данных.
- Существует общий принцип: не стоит обращаться
из клиентского приложения к таблицам
базы данных напрямую. Для изменения данных
лучше использовать хранимые процедуры,
а для запросов на чтение — хранимые процедуры
или представления. Причина проста: если
потребуется поменять структуру вашей
базы данных (например, какую-то таблицу
поделить на текущую и архивную или добавить
новый столбец) не потребуется вносить
изменения в клиентское приложение. Это
следует помнить и при предоставлении
разрешений. Отметим также, что при помощи
хранимых процедур можно очень просто
реализовать дополнительные проверки
в добавление к обычным разрешениям;
- SQL Server позволяет настраивать разрешения
на уровне отдельных столбцов. На практике
лучше не пользоваться такими разрешениями
из-за падения производительности и усложнения
системы разрешений. Если пользователю
можно видеть не все столбцы в таблице
(например, ему не нужны домашние телефоны
сотрудников), то правильнее будет создать
представление или хранимую процедуру,
которые будут отфильтровывать ненужные
столбцы.
Задание для самостоятельной работы:
- Создайте логин SQL Server «Admin» и назначьте
ей роль sysadmin;
- Создайте в базе данных роль «Saler» и назначьте
ей разрешения на выборку данных из
всех таблиц, изменение данных в таблицах
Order, OrdItem и запуск хранимой процедуры spr_getOrders;
- Создайте логин SQL Server «Ivanov» и сопоставьте
его с одноименным пользователем в базе
данных Sales. Назначьте созданному пользователю
роль Saler;
- Протестируйте созданные логины.