Управление пользователями, ролями, разрешениями, средствами SQL Server.

Автор: Алексей

Дата: 2011-03-30

Проверки подлинности SQL Server. При использовании сетевых библиотек Named Pipes и Multiprotocol необходимо сначала пройти проверку подлинности в домене Windows, а затем только подключится к серверу баз данных. Если компьютер, не входит в домен соединится не получится. Библиотека TCP/IP Sockets, не требует проверки подлинности в домене Windows. При подключении достаточно вести логин и пароль. Установить режим проверки подлинности можно при установке SQL сервера. Рассмотрим наиболее популярные типы аутентификации, проверка подлинности windows и смешенный режим. Проверка подлинности Windows: Подключение к серверу осуществляется с помощью средств защиты Windows, учётные записи и группы, брандмауэры, прокси-серверы и т.д. Смешенный режим: Подключение к серверу осуществляется с учётом средств защиты Windows или SQL Server. Используется для использования клиентов не Windows систем например Linux. Считается что защита слабее, чем при использовании проверки подлинности Windows.

Смена режимов проверки подлинности. Запустите Enterprise Manager, выберите свой сервер и щелкнув правой кнопкой мыши по значку, в меню выберете Свойства (Properties). Перейдите на вкладку Security и выберете необходимый режим.

Пользователи и роли SQL Server.

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

Встроенные пользователи на уровне сервера:

BULTIN/Administrator — автоматически предоставляются права системного администратора SQL Server. Если компьютер входит в домен, в эту группу автоматически попадает группа Domain Admins (Администраторы домена), и, таким образом, администраторы домена по умолчанию обладают полными правами на SQL Server. Если такая ситуация нежелательна, то этот логин можно удалить. Но и в этом случае администраторам домена получить доступ к данным SQL Server будет несложно.
nt aothority\netwokk sebvice — от имени учетной записи в Windows Server 2003 работают приложения ASP.NET, в том числе и службы Reporting Services (в Windows 2000 для этой цели используется учетная запись aspnet). Этот логин Windows используется для подключения к SQL Server Reporting Services. Ему автоматически предоставляются необходимые права на базы данных master, msdb и на базы данных, используемые Reporting Services.
nt authority\system — это локальная учетная запись операционной системы. Службы SQL Server работают от имени локальной системной учетной записи. Server обращается к самому себе. обладает с правами системного администратора SQL Server.
sa (от System Administrator) — Создается по умолчанию, обладает правами системного администратора SQL Server, и отобрать права и удалить нельзя. Его можно переименовать или отключить. Если для SQL Server будет настроена аутентификация только средствами Windows, то использовать этот логин для подключения к серверу не удастся.

Встроенные роли сервера:

public – роль не представлена в списке серверных ролей. Эту роли автоматически получают все, кто подключился к SQL Server, лишить пользователя членства в этой роли нельзя. Обычно эта роль используется для предоставления разрешений всем пользователям данного сервера;
Sysadmin — Выполняет любые действия в любой БД. По умолчанию учетная запись sa – абсолютные права. Все члены группы Windows Administrators являются членами данной роли. BULTIN/Administrator – доступ к серверу всем членам административно группы windows.
Securityadmin — Управляет идентификаторами пользователей, может создавать БД и читать журналы, может создавать логины изменять пароли, предоставлять разрешения в базах пользователям, но не администраторам.
Serveradmin –Конфигуририрует SQL Server с помощью системной хранимой процедуры sp_configure и может останавливать службы SQL Server. Роль предназначена для операторов, которые обслуживают сервер, им можно изменять настройки сервера но нельзя изменять данные и разрешения.
Setupadmin — Устанавливает и изменяет параметры конфигурации удаленных и связанных серверов, а также параметры репликации. Кроме того, пользователи могут также включать некоторые хранимые процедуры, например sp_serveroption, в число процедур, выполняемых при запуске системы. Рекомендуется добавить сюда членов встроенной группы Windows Server Operators
Processadmin – Управляет процессами, выполняющимися в рамках SQL. закрытия пользовательских подключений к серверу (например, зависших).
Diskadmin – Управляет файлами БД на диске
Dbcreator – Создает, и модифицирует БД. Предоставляется учетным записям, используемым приложениями, которые хранят свою информацию на SQL Server. (Создатель БД становится автоматически её владельцем)
Bulkadmin — роль для сотрудников, которые выполняют массовые загрузки данных в таблицы SQL Server;

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

dbo (от database owner) — пользователь-владелец базы данных. Автоматически создается для того логина, от имени которого была создана база данных. Как владелец, он получает полные права на свою базу данных (при помощи встроенной роли базы данных dbovmer). В SQL Server 2005 (в отличие от предыдущих версий) dbo может удалить свою базу данных;
guest (гость) — предоставляет разрешения всем логинам, которым не соответствует ни один пользователь в базе данных. По умолчанию у этого пользователя нет права login для базы данных, и, следовательно, работать он не будет. Пользователь используется для предоставления разрешений логинам на какие-то учебные/тестовые базы данных или на базы данных — справочники, доступные только на чтение;
inpqrmation_schema — пользователь не может соответствовать ни один логин. Его предназначение быть владельцем схемы information_schema, в которой хранятся представления системной информации для базы данных;
sys — этому пользователю, как и information_schema, не могут соответствовать логины. Он является владельцем схемы sys, к которой принадлежат системные объекты базы данных.

Роли базы данных (используются для доступа к работе с базами данных):

db_owner — Может выполнять любые задачи в БД SQL Server. Имеет те же права, что владельцы БД и участники роли DBO.
db_accessadmin — Может добавлять в БД и удалять из нее пользователей, создавать схемы. Права даются сотруднику отвечающему за пользователей БД.
db_securityadmin — Может управлять разрешениями, ролями, записями участников ролей и создателей объектов в БД (используя операторы GRANT, REVOKE и DENY)
db_ddladmin — Может добавлять, изменять и удалять объекты (используя операторы CREATE, ALTER и DROP) не предоставляя прав на информацию, которая содержится в существующих объектах. Применяется когда пользователю необходимо дать право создавать, изменять и удалять любые объекты в базе данных.
db_backupoperator — Может выполнять команды DBCC, инициировать процесс фиксации транзакций, создавать резервные копии (используя операторы DBCC, CHECKPOINT и BACKUP Transact-SQL). Выполнять резервное копирование базы данных
db_datareader — Может считывать извлекать информацию данные из пользовательских таблиц и представлений в БД (имеет право использовать оператор SELECT)
db datawriter — Может изменять или удалять данные из пользовательских таблиц и представлений в БД (имеет право использовать операторы INSERT, UPDATE и DELETE). Применяется когда необходимо дать права на чтение и запись информации во всех таблицах базы данных, не предоставляя ему лишних административных разрешений (на создание и удаление объектов, изменение прав и т. п.).
db denydatareader — Не может считывать данные из пользовательских таблиц представлений в БД (не имеет права использовать оператор SELECT). Эта роль может использоваться с ролью db_ddladmin, чтобы предоставить администратору право создавать объекты, принадлежащие роли DBO, и при этом запретить чтение важных или секретных данных, содержащихся в этих объектах. Применяется когда необходимо дать ограниченные права пользователю.
db denydatawriter — Не может изменять или удалять данные из пользовательских таблиц в БД (не имеет права использовать операторы INSERT, UPDATE и DELETE), является приоритетом над разрешениями. Применяется для запрета некоторым пользователям, у которых установлены более обширные права.
public – Предоставления разрешений сразу всем Существующим пользователям базы данных.

Создание учётных записей с помощью мастер Create Login Wizard.

Запускаем SQL Server Enterprise Manager, щелкните по значку сервера, в правом окне выберите Administer SQL Server затем create a login. Запустится мастер.
Или в меню Tools, выбираем Wizard… откроется окно выбора мастеров.

Выберите мастера Create Login Wizard из диалогового окна Select Wizard

Выберите режима проверки подлинности для создаваемой учетной записи

Задайте логин и пароль. Для аутентификации windows просмотрите рисунок ниже.

Предоставление grant запрет deny доступа к серверу для новой учетной записи

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

Управление ролями и пользователями с помощью Enterprise Manager

Управление ролями и пользователями на уровне сервера:
Чтобы управлять непосредственно из Enterprise Manager выберете раздел Security и в нём Logins.

Выберете new Login. откроется следующее окно.

Задайте необходимые вам параметры. Название учётной записи, выберете аутентификацию, Введите пароль, выберете БД. На вкладке Server Roles – задаются роли для сервера, на вкладке database Assess — задаются роли для конкретных баз данных, чтобы задать роль сначала выберите БД установив галочку. В нижней области, установите галочки на необходимых ролях. Для просмотра членов роли на выбранных вкладках нажмите на кнопку Свойства (Properties)

Управление пользователями на уровне базы данных:
Предварительно создадим пользователя на уровне сервера как представлено выше. Но ему не зададим не каких ролей. Выберите, радел Databases, затем необходимую базу данных и пункт Users. Затем в текущей БД добавим пользователя New Database User.
В открывшемся окне из списка Login name выберем необходимого пользователя и зададим роли.

Создание пользовательской роли (группы) для конкретной БД:

Выберите, радел Databases, в ней необходимую БД. Переедем на пункт Rules. Создадим новую роль New Database Role.
В открывшемся окне зададим имя новой роли. Нажмём кнопку Add из списка выберем необходимых пользователей для текущей БД.

Просмотра информации и редактирование учётных записей пользователей и ролей: Можно просмотреть в дереве консоли Enterprise Manager. Откройте раздел Security, выберете необходимый подраздел Logis – пользователи, server rolts .- роли на уровне сервера, для просмотра ролей на уровне БД зайдите в необходимую БД и выберите раздел Roles, для просмотра пользователей в раздел users. Двойным шелком мыши по выбранному элементу вы можете просмотреть информацию или отредактировать. Кликнув правой кнопкой мыши, откроется всплывающее меню, в котором можно удалить, создать новое, просмотреть свойства выбранных элементов.

Привилегии или разращения задаются только на уровне базы данных. Заданные пользователю фиксированные роли наследуют (автоматически принимают) определённые разрешения которые назначены в их рамках.

Разрешения для выполнения операторов:
Выберете базу данных. Откройте свойства щелкнув правой кнопкой мыши (Properties). В открывшемся окне выбрать вкладку Permissions. Щелчком мыши в ячейках можете разрешить знак галочки, запретить знак крестик запрет имеет приоритет перед ролями.

Create Table –создавать таблицы Create View –создавать представления Create SP –создавать хранимые процедуры Create Default – создавать значения по умолчанию Create Rule – создавать правела Backup DB –резервирование баз данных

Backup Log –резервирование журнала транзакций

Разрешения доступа к объектам БД:
Выполнение операций с таблицами, представлениями, функциями и хранимыми процедурами. Выберете БД, затем к примеру таблицы (Tables). В правом окне выберете таблицу двойным щелчком мыши и нажмите копку Permissions. Задайте разрешения пользователям или ролям. Чтобы отображались все элементы БД выберете раздел ролей или пользователей как показано на рисунке ниже.
Выбираем раздел в текущей БД User. В правом окне выбираем пользователя двойным щелчком мыши. В открывшемся окне нажимаем кнопку Permissions.

ALTER — Вносить любые изменения в свойства объекта (за исключением смены владельца. CONTROL – Разрешения на полные права как на сам объект, так и на информацию в нем. DRI или REFERENCES — разрешение, которое можно предоставить для проверки ограничений целостности. Например, пользователь может добавлять данные в таблицу с внешним ключом, а на таблицу с первичным ключом ему нельзя предоставлять права на просмотр. В этом случае на таблицу с первичным ключом ему можно предоставить право references — и он сможет производить вставку данных в таблицу с внешним ключом, не получая лишних прав на главную таблицу. Это разрешение можно предоставлять на таблицы, представления, столбцы и функции. SELECT – Просмотр данных INSERT – добавление новых данных UPDATE – обновление данных DELETE – Удаление данных EXEC – запуск, выполнение хранимых процедур и пользовательских функций TAKE CWNERSHIP — право на принятие на себя владения данным объектом. Владелец автоматически обладает полными правами на свой объект. Такое право можно назначить для любых объектов. VIEW DEFINITION — право на просмотр определения для данного объекта. Предусмотрено для таблиц, представлений, процедур и функций. GRANT – разрешение, предоставлено явно WITH GRANT — разрешение не только предоставлено данному пользователю, но он также получил право предоставлять это разрешение другим пользователям. Можно предоставлять, конечно, только вместе с grant

DENY — явный запрет на выполнение действия, определенного данным разрешением. Как в любых системах безопасности, явный запрет имеет приоритет перед явно предоставленными разрешениями.

Коменнтарий