Управление сиротскими пользователями в SQL Server: Подробное руководство

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

Фон

Чтобы подключиться к базе данных SQL Server, пользователь должен иметь действующий логин в главной базе данных. Этот логин аутентифицирует пользователя и сопоставляется с пользователем базы данных для авторизации. Сопоставление поддерживается с использованием идентификатора безопасности (SID), что гарантирует правильную связь между пользователями базы данных и логинами сервера.

Исключения из правила сопоставления логинов и пользователей включают:

1. Пользователи заключенных баз данных: Аутентифицируются на уровне базы данных, устраняя зависимость от логинов сервера. Эти пользователи увеличивают портативность базы данных, но требуют отдельного восстановления для каждой базы данных.
2. Гостевые аккаунты: Включены по умолчанию, они позволяют доступ пользователям без явного сопоставления, но, как правило, не рекомендуются по соображениям безопасности.
3. Членство в группах Microsoft Windows: Члены групп Windows могут получать доступ к базам данных, если группа добавлена в качестве пользователей.

Пользователь базы данных становится сиротой, когда:

• Соответствующий логин удален.
• База данных мигрирована или восстановлена без связанных логинов.
• Отображение SID между пользователем и входом неверное.

Что такое сиротские пользователи?

Сиротский пользователь возникает, когда:

• Пользователь базы данных существует, но соответствующего входа SQL Server в базе данных master нет.
• SID пользователя базы данных и SID входа не совпадают.

Такие сценарии нарушают аутентификацию и авторизацию, что может привести к ошибкам или ограниченному доступу.

Обнаружение сиротских пользователей

Для SQL Server

Используйте следующий запрос для определения сиротских пользователей:

SELECT dp.type_desc, dp.sid, dp.name AS user_name  

FROM sys.database_principals AS dp  

LEFT JOIN sys.server_principals AS sp  

    ON dp.sid = sp.sid  

WHERE sp.sid IS NULL  

    AND dp.authentication_type_desc = 'INSTANCE';

Для базы данных Azure SQL или Synapse Analytics

1. Получите SID входов в базе данных master:

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. Получите SID пользователей в целевой базе данных:

SELECT name, sid, principal_id

FROM sys.database_principals 

WHERE type = 'S' 

  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

  AND authentication_type_desc = 'INSTANCE';

3. Сравните списки, чтобы выявить несоответствия.

Устранение сиротских пользователей

1. Повторное создание отсутствующего входа с SID 

Если вход был удален:

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. Сопоставление сиротского пользователя с существующим входом 

Если вход существует, но не сопоставлен:

ALTER USER <user_name> WITH LOGIN = <login_name>;

3. Изменение пароля пользователя 

После повторного создания входа, обновите его пароль:

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

Используя sp_FindOrphanedUser

Хранимая процедура sp_FindOrphanedUser является мощным инструментом для обнаружения и устранения сиротских пользователей. Она предоставляет:

1. Список сиротских пользователей.
2. Автоматически сгенерированные сценарии для исправлений.
3. Сведения о схемах, принадлежащих сиротским пользователям.

Получите код пакета из sp_FindOrphanedUser.

Запуск процедуры

По умолчанию:

EXEC sp_FindOrphanedUser;

Для конкретной базы данных:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

Примеры сценариев сиротских пользователей

Пример 1: Пользователь без входа

Сценарий

Логин был удален, оставив базового пользователя сиротой.

• Обнаружить сиротского пользователя:
  MS SQL

   

  EXEC sp_FindOrphanedUser;

• Воссоздайте логин:
  MS SQL

   

  СОЗДАТЬ ЛОГИН [User1] С ПАРОЛЕМ = 'SecurePassword123';

  ИСПОЛЬЗОВАТЬ [ExampleDB];

  ИЗМЕНИТЬ ПОЛЬЗОВАТЕЛЯ [User1] С ЛОГИНОМ = [User1];

Пример 2: Несоответствие SID

Сценарий

SID входа не совпадает с SID пользователя.

• The mismatch is the presence of `

  ` outside the main nested structure defined by `

  ` and `

  `. The other tags are all properly nested within each other.

• Исправьте сопоставление SID:
  MS SQL

  ИСПОЛЬЗОВАТЬ [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

Пример 3: Сиротевший пользователь, владеющий схемой

Сценарий

Сиротевший пользователь предотвращает удаление схемы.

• Определение владельца схемы:
  MS SQL

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Переназначить владельца схемы:
  MS SQL

   

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

Лучшие практики

• Использование пользователей баз данных в контейнерах: Избегайте зависимости от серверных логинов.
• Синхронизация логинов: Всегда мигрируйте или восстанавливайте логины во время миграций баз данных.
• Регулярные проверки: Периодически проверяйте наличие потерянных пользователей для поддержания безопасности.

Заключение

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