Логины, пользователи и роли SQL Server: как это устроено

SQL Server делит идентичность на две части. Логин позволяет подключиться к серверу; пользователь даёт этому логину идентичность внутри одной базы данных. Права выдаются пользователям (и ролям - группам пользователей) в каждой базе отдельно, а логинам - для полномочий на уровне всего сервера. sa - логин со всеми полномочиями, какие только есть. Правильная настройка для приложения - собственный логин, пользователь только в его базе, членство в db_datareader и db_datawriter (или более узкие права на схему) и EXECUTE, если оно вызывает хранимые процедуры, - и ничего на уровне сервера. Эта статья объясняет каждую часть, приводит скрипты и разбирает две проблемы, на которые рано или поздно натыкаются все: осиротевшие пользователи после восстановления и ошибки входа с бесполезными сообщениями.

Логины и пользователи: два слоя#

Каждое подключение проходит две проверки.

1. Аутентификация на сервере. Клиент предъявляет имя логина и пароль (аутентификация SQL Server) или идентичность Windows. Если логин существует, включён и пароль совпадает, соединение открыто. Логины живут в master и перечислены в sys.server_principals и sys.sql_logins.
2. Доступ к каждой базе. Когда соединение использует базу - базу по умолчанию, базу, указанную в строке подключения, или через оператор USE, - SQL Server ищет в этой базе пользователя, связанного с логином. Пользователи перечислены в sys.database_principals каждой базы, а связь устанавливается по идентификатору безопасности (SID), а не по имени.

Логин без пользователя в базе не может эту базу использовать, с двумя исключениями: члены роли сервера sysadmin, которые входят в любую базу как dbo, и базы, где включён пользователь guest, - в пользовательских базах по умолчанию он выключен, и так и должно оставаться.

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

Учётная запись sa#

sa - встроенный SQL-логин с SID 0x01, постоянный член фиксированной роли сервера sysadmin. Он может всё: создавать и удалять базы, менять конфигурацию сервера, создавать логины, читать любую таблицу и выполнять операции уровня операционной системы, доступные SQL Server. Его нельзя исключить из sysadmin и нельзя удалить.

Что это значит для вас:

• Оставьте `sa` для администрирования. Используйте его из SSMS или sqlcmd, когда управляете сервером, а не в строках подключения приложений. Утёкший пароль sa - это утёкший сервер.
• Дайте ему длинный случайный пароль и храните его в менеджере паролей, а не в текстовом файле рядом с кодом.
• Не отключайте и не переименовывайте его на сервере на хостинге, если не знаете, что от него зависит. И то, и другое возможно (ALTER LOGIN sa DISABLE;, ALTER LOGIN sa WITH NAME = ...;) и входит в стандартные советы по защите серверов, которыми вы полностью владеете. На хостинговом сервере сначала создайте другой логин sysadmin и проверьте его, иначе можете запереть себя снаружи собственного экземпляра.

В RE:NODE тариф SQL Server поставляется с паролем sa, сгенерированным для этого сервера, и созданной для вас базой, назначенной базой по умолчанию для sa, так что SQL Server Management Studio открывается сразу в ней. Это отправная точка; остальная часть статьи о том, как не использовать sa для всего подряд. Первый вход разобран в статье Подключение к SQL Server через SSMS.

Фиксированные роли сервера#

Роли сервера несут права на уровне всего сервера. Логины добавляются в них через ALTER SERVER ROLE ... ADD MEMBER.

В SQL Server 2022 появился набор более узких фиксированных ролей сервера с именами, начинающимися на ##MS_, например ##MS_ServerStateReader## (просмотр состояния сервера для мониторинга), ##MS_DefinitionReader## (просмотр определений объектов) и ##MS_DatabaseConnector## (подключение к любой базе). Они полезны для инструментов мониторинга, которым иначе выдали бы sysadmin только ради чтения динамических административных представлений.

Логин приложения не должен входить ни в одну из этих ролей.

Фиксированные роли базы данных#

Роли базы несут права внутри одной базы. Пользователи добавляются в них через ALTER ROLE ... ADD MEMBER.

db_datareader и db_datawriter покрывают все текущие и будущие таблицы в базе, что удобно и немного широковато. Для более тонкого контроля выдавайте права на схему: права на схему распространяются на каждый объект в ней, включая созданные позже.

Обратите внимание, чего нет в db_datareader и db_datawriter: EXECUTE. Приложению, которое вызывает хранимые процедуры, это право нужно выдать отдельно.

Логин с минимальными правами для приложения#

Скрипт ниже создаёт логин для приложения, даёт ему пользователя в его базе и выдаёт то, что нужно типичному веб-приложению во время работы. Запускайте его под sa или другим sysadmin.

USE [master];CREATE LOGIN [orders_app]    WITH PASSWORD = N'use-a-long-random-generated-password',         DEFAULT_DATABASE = [app],         CHECK_POLICY = ON;USE [app];CREATE USER [orders_app] FOR LOGIN [orders_app] WITH DEFAULT_SCHEMA = [dbo];ALTER ROLE [db_datareader] ADD MEMBER [orders_app];ALTER ROLE [db_datawriter] ADD MEMBER [orders_app];GRANT EXECUTE ON SCHEMA::[dbo] TO [orders_app];

У логина и пользователя могут быть разные имена; одинаковое имя делает связь очевидной, когда вы прочитаете это через год. DEFAULT_DATABASE означает, что логин окажется в нужной базе, даже если строка подключения забудет её указать.

Неудобная часть - миграции. Entity Framework Core, Flyway и подобные инструменты создают и изменяют таблицы, а логин для работы приложения выше этого не может - и так задумано. Чистый паттерн - второй логин, который использует только шаг деплоя:

USE [master];CREATE LOGIN [orders_migrate] WITH PASSWORD = N'another-long-password', DEFAULT_DATABASE = [app];USE [app];CREATE USER [orders_migrate] FOR LOGIN [orders_migrate];ALTER ROLE [db_ddladmin] ADD MEMBER [orders_migrate];ALTER ROLE [db_datareader] ADD MEMBER [orders_migrate];ALTER ROLE [db_datawriter] ADD MEMBER [orders_migrate];

Если для проекта это слишком много церемоний, один логин в db_owner своей базы всё равно намного лучше sa: он может разрушить одну базу, а не весь сервер. Как запускать миграции отдельным шагом, чтобы схема с двумя логинами была практичной, разобрано в статье Миграции EF Core в продакшене.

Нескольким приложениям на одном сервере стоит дать каждому собственные базу, логин и пользователя. Тогда утёкшая строка подключения, SQL-инъекция или неудачная миграция в одном приложении затронет одну базу.

Схемы, GRANT, DENY и проверка того, что может пользователь#

Права можно выдавать на трёх уровнях: база, схема или отдельный объект. Обычно правильная гранулярность - уровень схемы:

CREATE SCHEMA [reporting] AUTHORIZATION [dbo];CREATE ROLE [reporting_reader];GRANT SELECT ON SCHEMA::[reporting] TO [reporting_reader];CREATE USER [bi_tool] FOR LOGIN [bi_tool];ALTER ROLE [reporting_reader] ADD MEMBER [bi_tool];

Если создать собственную роль и выдавать права ей, а не пользователям напрямую, второй пользователь для отчётов - это один ALTER ROLE, а права записаны в одном месте.

DENY перекрывает GRANT из любой другой роли, с одним исключением: он не действует на членов sysadmin и владельца базы, которые обходят проверки прав. Используйте его экономно - например, чтобы запретить роли отчётов читать таблицу dbo.Payments, которую иначе включали бы права на её схему.

Чтобы увидеть, что пользователь может на самом деле, войдите от его имени:

EXECUTE AS USER = 'orders_app';SELECT * FROM fn_my_permissions(NULL, 'DATABASE');SELECT HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'DELETE') AS can_delete;REVERT;

Для аудита в обратную сторону - у кого к чему есть доступ - выведите членство в ролях на обоих уровнях. Запускайте это время от времени, особенно после того, как кому-то «временно» дали больше прав, чтобы что-то починить:

-- Server roles and their membersSELECT r.name AS server_role, m.name AS login_nameFROM sys.server_role_members AS rmJOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_idJOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_idORDER BY r.name, m.name;-- Database roles and their members, in the current databaseSELECT r.name AS database_role, m.name AS user_nameFROM sys.database_role_members AS rmJOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_idJOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_idORDER BY r.name, m.name;

Всё, что есть в sysadmin, кроме sa и логинов, которые вы сознательно создали для администрирования, и всё в db_owner, что не является логином для миграций, заслуживает вопроса.

Когда доступ должен зависеть от строки, а не от таблицы, - мультитенантное приложение, где каждый клиент должен видеть только свои заказы, - роли не подходят. Row-level security, доступная во всех редакциях, включая Express, прикрепляет к таблице функцию-предикат фильтра, так что база сама добавляет условие по тенанту к каждому запросу. Это полезная вторая линия защиты за проверками самого приложения, а не их замена.

EXECUTE AS с последующим падающим оператором - ещё и самый быстрый способ воспроизвести ошибку прав, о которой сообщает приложение, не трогая само приложение.

Пароли, политики и смена учётных данных#

CHECK_POLICY = ON применяет к логину политику паролей. В Windows это политика паролей Windows; в Linux наследовать политику Windows неоткуда, так что не рассчитывайте, что слабый пароль будет отклонён, - генерируйте длинный случайный, и вопрос не возникнет. CHECK_EXPIRATION добавляет срок действия, что больше подходит для логинов людей, чем приложений: приложение, у которого пароль истекает в 3 часа ночи, - это простой, а не безопасность.

Чтобы сменить пароль приложения без простоя, обычно используют два логина или короткое окно обслуживания:

ALTER LOGIN [orders_app] WITH PASSWORD = N'the-new-long-password';

Существующие соединения после смены пароля остаются открытыми; новый пароль нужен только новым. Обновите переменную окружения со строкой подключения, перезапустите приложение, чтобы его пул переподключился, и через некоторое время убедитесь в sys.dm_exec_sessions, что со старым паролем никто не остался подключён. Чтобы немедленно закрыть логину доступ - например, после утечки, - отключите его и завершите его сессии:

ALTER LOGIN [orders_app] DISABLE;SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'orders_app';-- KILL <session_id>; for each one

Осиротевшие пользователи после восстановления#

Пользователи связаны с логинами по SID. SQL-логин, созданный на другом сервере, имеет другой, случайно сгенерированный SID, даже при том же имени. Поэтому когда вы восстанавливаете базу с другого сервера, её пользователи приезжают со ссылками на SID, которых здесь нет. Это осиротевшие пользователи: пользователь есть в базе, логин есть на сервере, а подключение не удаётся, потому что они не совпадают.

Найдите их:

SELECT dp.name AS orphaned_userFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sidWHERE dp.type = 'S'  AND dp.authentication_type_desc = 'INSTANCE'  AND sp.sid IS NULL;

Исправьте каждого, заново связав пользователя с логином на этом сервере:

ALTER USER [orders_app] WITH LOGIN = [orders_app];

Старая процедура sp_change_users_login делает то же самое и устарела; текущий способ - ALTER USER ... WITH LOGIN. Чтобы проблема вообще не возникла, создайте логин на новом сервере с исходным SID - CREATE LOGIN [orders_app] WITH PASSWORD = N'...', SID = 0x...;, взяв SID из sys.sql_logins старого сервера. Это разобрано как часть полного переезда в статье Перенос базы на хостинг SQL Server, а само восстановление - в статье Бэкап и восстановление SQL Server.

Contained databases - другой путь обхода проблемы. С CONTAINMENT = PARTIAL и включённой серверной опцией contained database authentication можно создавать пользователей с паролями, которые живут целиком внутри базы, без логина, так что база переезжает между серверами вместе со своей аутентификацией. Это разумный выбор, когда базы переезжают часто; большинству конфигураций с одним сервером они не нужны.

Как читать ошибки входа#

Ошибка 18456, «Login failed for user», намеренно расплывчата для клиента, чтобы не помогать атакующему. Журнал ошибок сервера записывает номер state с настоящей причиной. Прочитать журнал можно через EXEC sp_readerrorlog; под sysadmin или в SSMS в разделе Management, SQL Server Logs.

State 38 и 40 выглядят как проблемы с паролем, но ими не являются. 38 обычно означает, что строка подключения указывает базу, в которой у логина нет пользователя. 40 означает, что база по умолчанию удалена или переименована, - а на сервере, где базой по умолчанию для sa является созданная для вас база, это может не давать sa подключиться, пока вы не подключитесь с master в качестве начальной базы и не сбросите её через ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [master];.

FAQ#

Чем логин отличается от пользователя в SQL Server?

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

Должно ли моё приложение подключаться как sa?

Нет. Создайте для приложения логин с пользователем в его собственной базе и только нужными ему ролями. sa может удалить любую базу и изменить конфигурацию сервера, а строка подключения приложения - именно те учётные данные, которые утекают чаще всего.

Безопасен ли db_owner для приложения?

Безопаснее, чем sa, потому что ограничен одной базой, но шире, чем нужно приложению во время работы. Он может удалять таблицы и менять права. Для работающего приложения используйте db_datareader, db_datawriter и EXECUTE, а db_owner или db_ddladmin - только для миграций.

Почему мой логин не может подключиться после восстановления базы?

Пользователь базы осиротел: он ссылается на SID логина со старого сервера. Свяжите его заново через ALTER USER [name] WITH LOGIN = [name]; или пересоздайте логин с исходным SID.

Можно ли создать пользователя только для чтения для отчётов?

Да. Создайте логин и пользователя и добавьте пользователя в db_datareader или выдайте SELECT только на ту схему, которая нужна отчётам. Хорошая практика - направлять инструменты отчётности на отдельный логин только для чтения, чтобы неправильно настроенный отчёт не мог изменить данные.