Проблема: Мне нужно проверить разрешения в моих базах данных, но я хочу создать скрипт, чтобы иметь возможность запустить его в случае восстановления. У меня есть логины, роли и пользователи, но мне нужно извлечь разрешения. Как я могу сделать это легко?
Решение:
Решение, которое мы выбираем, зависит от версии SQL Server, с которой мы работаем. Поскольку SQL Server 2005 изменил модель безопасности, введя объекты безопасности, нам придется использовать разные подходы в зависимости от того, работаем ли мы с SQL Server 2000 или SQL Server 2005 и выше. Давайте начнем с SQL Server 2000.
SQL Server 2000
В SQL Server 2000 есть отличная системная хранимая процедура sp_helprotect, которая извлекает основные разрешения для нас. Одна из ее преимуществ заключается в том, что она легко преобразует битовую карту, найденную в sysprotects для разрешений на уровне столбца, поэтому нам не нужно повторять эту работу. Однако sp_helprotect является хранимой процедурой, поэтому нам нужно получить ее вывод во временную таблицу, чтобы мы могли работать с ним.
Следовательно, первый шаг – создать временную таблицу, которую мы будем использовать:
-- Временная таблица для хранения результатов sp_helprotect
CREATE TABLE #TempPerms (
[Owner] sysname NOT NULL,
[Object] sysname NOT NULL,
[Grantee] sysname NOT NULL,
[Grantor] sysname NOT NULL,
[ProtectType] NVARCHAR(9) NOT NULL,
[Action] NVARCHAR(100) NOT NULL,
[Column] NVARCHAR(300) NOT NULL,
[WithGrant] VARCHAR(18) NULL DEFAULT ''
);
-- Позволим sp_helprotect сделать работу за нас
INSERT INTO #TempPerms ([Owner], [Object], [Grantee], [Grantor], [ProtectType], [Action], [Column])
EXEC sys.sp_helprotect;
После создания временной таблицы следующим шагом является написание запроса, который будет создавать T-SQL выражения для выполнения, если нам понадобится восстановить разрешения такими, какими они были при проверке. Теперь есть один маленький трюк, который мы реализуем, и это в случаях, когда GRANT был выполнен с опцией WITH GRANT. Это сообщается sp_helprotect как Grant_WGO, поэтому нам нужно сделать две вещи:
- Заменить ‘Grant_WGO’ в ProtectType на ‘Grant’
- Заполнить столбец WithGrant значением ‘ WITH GRANT OPTION’ для случаев, когда ProtectType был ‘Grant_WGO’
Мы можем сделать это просто с помощью оператора UPDATE, например:
-- Обработка ситуации, когда разрешение было GRANT и использовалась опция WITH GRANT OPTION
UPDATE #TempPerms
SET [ProtectType] = 'Grant',
[WithGrant] = ' WITH GRANT OPTION'
WHERE [ProtectType] = 'Grant_WGO';
И теперь довольно просто извлечь разрешения. Нам придется использовать CASE в SELECT на основе Owner, потому что если Owner равен ‘.’, это означает разрешение на уровне базы данных. Нам также нужно проверить, установлено ли значение Column на (All+New), (All) или указывает столбцы, и это требует вложенного CASE. Это может показаться немного сложным, но на самом деле это не так. Вот запрос, который извлекает информацию из временной таблицы и создает скрипт восстановления:
-- Создание и вывод разрешений
SELECT CASE [Owner]
WHEN '.' THEN ProtectType + ' ' + [Action] + ' TO [' + [Grantee] + ']' + [WithGrant] + ';'
ELSE CASE [Column]
WHEN '(All+New)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
WHEN '(All)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
WHEN '.' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
ELSE ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] ([' + [Column] + ']) TO [' + Grantee + ']' + [WithGrant] + ';'
END
END AS 'Permissions'
FROM #TempPerms;
SQL Server 2005 и выше
SQL Server 2005 и выше немного сложнее. Нам нужно беспокоиться о большем количестве типов разрешений, а также о разрешениях, таких как SELECT, применяемых как на уровне базы данных, так и на уровне схемы, о которых нам не приходилось беспокоиться в SQL Server 2000. Кроме того, у нас нет хорошей хранимой процедуры, подобной sp_helprotect, которая делала бы всю тяжелую работу за нас. Однако у нас есть множество каталоговых представлений, из которых мы можем извлечь информацию. Основой всего этого является sys.database_permissions вместе с sys.database_principals и sys.objects.
Давайте сначала рассмотрим запрос, а затем разберем его:
SELECT CASE dperms.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
ELSE state_desc
END + ' ' + permission_name + ' ON ' +
CASE dperms.class
WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'
WHEN 1 THEN CASE dperms.minor_id
WHEN 0 THEN 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + ']'
ELSE 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'
END
WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'
WHEN 4 THEN 'USER::[' + USER_NAME(major_id) + ']'
WHEN 24 THEN 'SYMMETRIC KEY::[' + symm.[name] + ']'
WHEN 25 THEN 'CERTIFICATE::[' + certs.[name] + ']'
WHEN 26 THEN 'ASYMMETRIC KEY::[' + asymm.[name] +']'
END + ' TO [' + dprins.[name] + ']' +
CASE dperms.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'
ELSE ';'
END COLLATE database_default AS 'Permissions'
FROM sys.database_permissions dperms
INNER JOIN sys.database_principals dprins ON dperms.grantee_principal_id = dprins.principal_id
LEFT JOIN sys.columns col ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id
LEFT JOIN sys.objects obj ON dperms.major_id = obj.object_id
LEFT JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
LEFT JOIN sys.asymmetric_keys asymm ON dperms.major_id = asymm.asymmetric_key_id
LEFT JOIN sys.symmetric_keys symm ON dperms.major_id = symm.symmetric_key_id
LEFT JOIN sys.certificates certs ON dperms.major_id = certs.certificate_id
WHERE dperms.type <> 'CO' AND dperms.major_id > 0;
Первое, на что следует обратить внимание, это то, что разные классы означают, что нам нужно изменить наши T-SQL выражения соответственно. Я выбрал основные: база данных, схема, объекты, пользователи и объекты шифрования – сертификаты и ключи. Это заставляет нас делать дополнительные соединения, что делает часть FROM более сложной, чем она есть на самом деле. Это все для того, чтобы получить имена различных объектов безопасности, чтобы они соответствовали классам. В остальном структура довольно простая, за исключением двух моментов, о которых я расскажу в следующем абзаце.
Одна вещь, которая действительно помогает, это то, что некоторые столбцы каталогового представления читаются человеком, например, state_desc. Поэтому мы можем использовать это в нашу пользу при создании скриптов.
Два исключения – это то, как обрабатываются разрешения на уровне столбцов и тот факт, что если вы посмотрите в sys.database_permissions, вы заметите, что есть значения major_id, которые меньше нуля.
В случае разрешений на уровне столбцов вместо битовой карты каждый столбец сообщается отдельно. major_id соответствует object_id в sys.objects, а minor_id соответствует column_id в sys.columns. Если minor_id равно 0, это означает весь стол или представление. Но если minor_id имеет значение больше 0, то нам нужно создать T-SQL, отражающий разрешения на уровне столбцов. Вот причина использования оператора CASE для minor_id.
Второе исключение связано с отрицательными значениями major_id. Они соответствуют системным объектам, поэтому я исключил их, потому что только в самых редких случаях мы будем изменять разрешения на системные объекты.
Если вы задаетесь вопросом о том, почему исключается условие type = ‘CO’, это соответствует возможности подключения к базе данных. Обычно это обрабатывается с помощью оператора CREATE USER, поэтому я исключил его из запроса.
И вот все! С помощью этих скриптов вы можете легко проверить и создать скрипты разрешений в ваших базах данных SQL Server, обеспечивая наличие плана резервного копирования в случае восстановления.