SQL Server: Пользовательские роли

Десятки лет существовала возможность определения пользовательских ролей в базах данных для облегчения управления процессом предоставления разрешений на доступ к базе данных, но на уровне экземпляра всегда существовало девять фиксированных ролей (или восемь, если вы используете версию, предшествующую SQL Server 2000, — роль bulkadmin появилась в SQL Server 2005). Теперь, с появлением SQL Server 2012 наконец появилась возможность создания нестандартных, или пользовательских, ролей сервера.

Многие годы недоступность такой функциональности была источником головной боли администраторов SQL Server. Что вы делали, если нужно было предоставить права нескольким пользователям или группам на уровне экземпляра и обеспечить синхронизацию этих прав? Допустим, вам нужно предоставить право View System State большому числу пользователей, чтобы они могли видеть информацию о блокировке в центрах разработки. Приходилось предоставлять права по отдельности каждому пользователю или доменной группе.

Если все имена входа являлись доменными, был обходной путь: можно было создать доменную группу, в которую включить всех пользователей, кому были нужны такие права (при этом надо было создавать отдельные группы для каждого сервера, если нужно было предоставить права на нескольких серверах). Можно было добавить пользователей в эту группу, создать имя входа, связанное с этой доменной группой, после чего предоставить группе право View Server State на сервере. Но это нужно было делать очень внимательно. Иначе можно было предоставить право входа пользователям, у которых такого права раньше не было. Существовала даже возможность предоставления пользователям прав, которых им предоставлять нельзя.

Создание роли средствами T/SQL

Существует много способов создания пользовательской роли на сервере, в том числе средствами T/SQL, SQL Server Management Studio и Windows PowerShell. Если нужно создать пользовательскую серверную роль с применением T/SQL, нужно воспользоваться тремя командами: инструкция Create Server Role создаст пользовательскую серверную роль, инструкция Alter Server Role добавит пользователя в роль и, наконец, инструкция Grant предоставит нужные права роли.

Использование этих трех инструкций показано в следующем коде, который создает пользовательскую серверную роль по имени ViewServerState. В роль добавляется пользователь SomeFakeLogin, и роли предоставляется право View Server State. Чтобы предоставить это право другим пользователям, достаточно просто добавить их в готовую роль инструкцией Alter Server Role.

USE [master]
GO
CREATE SERVER ROLE [ViewServerState] AUTHORIZATION [sa]
GO
ALTER SERVER ROLE [ViewServerState] ADD MEMBER [SomeFakeLogin]
GO
GRANT VIEW SERVER STATE TO [ViewServerState]
GO

Для удаления пользователя из роли используется инструкция Alter Server Role. При этом используется на параметр Add Member, а Drop Member:

ALTER SERVER ROLE [ViewServerState] DROP MEMBER [SomeFakeLogin]
GO

Когда нужно развернуть одну или более пользовательских серверных ролей на многих экземплярах SQL Server, есть несколько возможных вариантов. Наверняка вам не понравится подключаться к каждому серверу по отдельности и создавать на них пользовательские серверные роли. Один из вариантов — использование консоли SQL Server Management Studio. В ней можно выполнять сценарии на T/SQL сразу на нескольких экземплярах.

Можно также воспользоваться компонентами Windows PowerShell сервера SQL Server для развертывания новых пользовательских серверных ролей на всех экземплярах SQL Server в организации. (Существует много вариантов использования Windows PowerShell для выполнения задачи, но их описание выходит за рамки этой статьи.)

Консоль SQL Server Management Studio

Задачу можно легко выполнить средствами графического интерфейса SQL Server Management Studio. Чтобы создать пользовательскую серверную роль, подключитесь к экземпляру средствами Object Explorer. В обозревателе объектов перейдите к узлу <имя_экземпляра>/Security/Server Roles. Щелкните правой кнопкой Server Roles и выберите New Server Role. В открывшемся окне New Server Role в поле Server role name укажите имя роли, в поле Owner укажите владельца роли, после чего укажите объекты и разрешения, которые должны предоставляться членам роли (рис. 1).

Рис. 1. Укажите права, которые должна предоставлять роль

По завершении работы на странице General перейдите на страницу Members (рис. 2) и укажите имена входа SQL Server, которые должны входить в эту пользовательскую серверную роль.

Рис. 2. Определение членов роли

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

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

Рис. 3. Пользовательские роли можно включать в другие серверные роли

Чтобы организовать вложение ролей средствами T/SQL, снова потребуется инструкция Alter Server Role с параметром Add Member. Например, чтобы сделать пользовательскую серверную роль ViewServerState членом фиксированной серверной роли setupadmin, нужно изменить фиксированную серверную роль setupadmin.

ALTER SERVER ROLE [setupadmin] ADD MEMBER [ViewServerState]
GO

В пользовательских серверных ролях может быть много пользователей. Есть десятки прав уровня экземпляра, которые можно предоставить пользовательской серверной роли для упрощения управления этими правами. Можно также создать роль для младшего администратора БД, которой предоставить часть, но не все права администратора. Можно создать группу AlwaysOnAdmin, которая предоставляет право на выполнение перехода базы данных AlwaysOn (что должно выполняться в рамках SQL Server) не предоставляя всей полноты административных прав.

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