Прохождение теста Works with SQL Server 2008. Требование 2.
Требование 2. Подключаемые к SQL Server 2008 учетные записи ISVшного приложения не должны входить в серверную роль sysadmin.
В SQL Server роли бывают уровня сервера, уровня БД и прикладные. Серверных ролей 8, не считая public, их можно посмотреть exec sp_helpsrvrole или select * from sys.server_principals where type = 'R'. Этот список фиксированный, свои роли уровня сервера создавать нельзя. Роли уровня БД бывают как преднастроенные, так и пользовательские, их можно посмотреть exec sp_helprole или select * from sys.database_principals where type = 'R'. Роли с 1 в колонке is_fixed_role фиксированные, остальные пользовательские. Как легко догадаться, серверные роли являются объединениями прав на уровне сервера, БД - внутри БД. Чтобы не вдаваться в лирическое отступление на тему, какие бывают права, просто посмотрите в качестве иллюстрации свои текущие эффективные права на уровне сервера: select * from fn_my_permissions(null, 'server'), на уровне какой-нибудь базы данных: select * from fn_my_permissions('AdventureWorks2008R2', 'database') и на уровне БД для какого-нибудь объекта: select * from fn_my_permissions('HumanResources.Department', 'object').
Прикладные роли предназначены для того, чтобы юзер мог производить какие-то действия только в контексте приложения. Например, бухгалтер имеет право update на остатки по счетам, которое выполняется из какой-нибудь программы "АРМ Бухгалтер". Если это компьютерно продвинутый бухгалтер, он может зайти на сервер под своим логином через PowerShell, ну хорошо, через Excel или Access или, в конце концов, скачать бесплатную Management Studio для Express-редакции, получив в свои руки механизм произвольных запросов, и понаворотить на нем такого, что мало не покажется. Проблема здесь в том, что права на доступ/изменение данных предполагались в рамках определенных бизнес-правил, т.е. по заранее разработанным сценариям, а не абы что хочу ворочу. Поэтому правильно будет лишить шаловливого бухгалтера прав как логина, и включать их только в начале работы приложения "АРМ Бухгалтер", что и делается при помощи прикладных ролей.
Впрочем, я отвлекся. В тесте речь идет только о серверной роли sysadmin. Она самая могучая. Абы кто туда входить не должен. В частности, пользователи, соединяющиеся с SQL Server из тестируемого приложения. Посмотреть список членов роли можно из SSMS:
рис.1
или из скрипта при помощи следующего запроса:
select p2.name, p2.type_desc from sys.server_role_members rm join sys.server_principals p1 on rm.role_principal_id = p1.principal_id
join sys.server_principals p2 on rm.member_principal_id = p2.principal_id where p1.name = 'sysadmin'
Скрипт 1
Добавление логина к или отлучение его от серверной роли выполняется на рис.1 при помощи кнопок Add/Remove или из скрипта при помощи хранимых процедур sp_addsrvrolemember / sp_dropsrvrolemember. Создайте в качестве примера новый SQLный логин ххх и добавьте его к роли sysadmin, а затем изгоните из роли и убейте:
if exists(select * from sys.server_principals where type_desc = 'SQL_LOGIN' and name = 'xxx') drop login xxx
create login xxx with password = 'SQL.NET Architecture Day', default_database = tempdb, check_policy = on, check_expiration = on
exec sp_addsrvrolemember @loginame = 'xxx', @rolename = 'sysadmin'
exec sp_helpsrvrolemember @srvrolename = 'sysadmin'
exec sp_dropsrvrolemember @loginame = 'xxx', @rolename = 'sysadmin'
exec sp_helpsrvrolemember @srvrolename = 'sysadmin'
drop login xxx
Скрипт 2
Все просто за исключением ситуаций, когда на сервере крутится туча приложений и вы порядком забыли, какие логины непосредственно имеют отношение к приложению, которое будет сейчас тестироваться. Ну открыли мы, допустим, рис.1, посмотрели на него, а как понять, кого отсюда удалить, кого оставить? Простой совет: удаляйте всех, в ком сомневаетесь. Роль sysadmin не помойка, чтобы там болтались какие-то сомнительные личности. Тем не менее, я до DPE работал в МСS и представляю, что реальная жизнь не всегда совпадает с best practices. Поэтому давайте решим такую задачу. Имеется запущенное приложение, производящее некоторую активность над базой. Например, нагрузка, которую вы потом будете имитировать в тесте Works with SQL Server 2008. Нужно отловить всех логинов, которые приходят за это время на SQL Server, и выкинуть их из роли sysadmin.
Задачу можно решать кучей способов: триггер на логон, извещение о событии, трасса профайлера. В 2008-м к ним добавились еще два: аудит и расширенные события. Первый, вообще говоря, представляет собой частный случай второго. Воспользуемся им. Создадим на сервере аудит
use master
if exists(select 1 from sys.server_audits where name = 'RegisterLogons') begin
alter server audit RegisterLogons with (state = off)
drop server audit RegisterLogons
end
create server audit RegisterLogons to file (filepath = 'c:\Temp\', maxsize = unlimited, max_rollover_files = 1, reserve_disk_space = off)
Скрипт 3
В определении аудита оговаривается, в основном, куда он пишет свои результаты. Отлавливаемые события задаются в спецификации аудита, которая подстегивается к аудиту:
if exists(select 1 from sys.server_audit_specifications where name = 'RegisterLogonSpec') begin
alter server audit specification RegisterLogonSpec with (state = off)
drop server audit specification RegisterLogonSpec
end
create server audit specification RegisterLogonSpec for server audit RegisterLogons add (SUCCESSFUL_LOGIN_GROUP) with (state = on)
Скрипт 4
Включаем созданный аудит:
alter server audit RegisterLogons with (state = on)
Скрипт 5
Засекаем время, запускаем тестируемое приложение и выполняем его штатный прогон. Засекаем время, когда прогон закончился. Читаем из результата аудита, какие логины посещали SQL Server в период работы тестируемого приложения:
select distinct(server_principal_name) from sys.fn_get_audit_file ('c:\Temp\RegisterLogons_*.sqlaudit', default, default) where event_time between '2009-11-28 14:25:00' and '2009-11-28 14:27:00'
Скрипт 6
Если других приложений в это время на сервере не крутилось, эти логины нужно выкинуть из роли sysadmin, чтобы удовлетворить Требованию 2. Если крутилось, сепарируйте на основе своего знания. Аудит здесь за вас ничего сказать не может. Например, в профайлере есть колонка ApplicationName, однако заполняется онa только если приложение себя идентифицировало в строке соединения и потому служит ненадежным критерием.
Алексей Шуленин, Microsoft