Подписывание хранимой процедуры сертификатом уровня сервера

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

create login testUser with password = 'testdata', check_policy = off, default_database = Data

create user testUser from login testUser with default_schema = dbo

Напишем для решения задачи такую процедуру:

use Data

go

if sessionproperty ( N'quoted_identifier' ) = 0

       set quoted_identifier on

go

if sessionproperty ( N'ansi_nulls' ) = 0

       set ansi_nulls on

go

create proc dbo.KillWrongSessions

as

begin

       set nocount, xact_abort on

       declare @sess int, @sql nvarchar ( max )

       declare cur cursor local static forward_only for

             select block.session_id

             from

                    sys.dm_exec_requests sess

                           inner join

                    sys.dm_exec_sessions block on sess.blocking_session_id = block.session_id

             where sess.blocking_session_id <> 0 and block.[host_name] = 'EDYNAK'

       open cur

       fetch next from cur into @sess

       while @@fetch_status = 0

       begin

             set @sql = 'kill ' + cast ( @sess as nvarchar ( 100 ) )

             --print @sql

             exec sp_executesql @sql

             fetch next from cur into @sess

       end

       close cur

       deallocate cur

end

go

grant exec on object::dbo.KillWrongSessions to testUser

use master

grant view server state to testUser

В процедуре происходит отключение соединений, которые кого-либо блокируют и при этом работают на определенном хосте. Это и есть наш критерий низко-приоритетного соединения.

Смоделируем ситуацию когда происходит блокирование. Создим таблицу:
create table dbo.Data ( i int )
На компьютере с именем EDYNAK запустим такой код:

begin tran

insert into dbo.Data ( i )

       select 1

Другое соединение пытается прочитать данные и становится в очередь:

select *

from dbo.Data

Теперь попробуем запустить процедуру от имени testUser:

exec as login = 'testUser'

exec dbo.KillWrongSessions

go

revert
Происходит ошибка с таким описанием:

Msg 6102, Level 14, State 1, Line 77

User does not have permission to use the KILL statement.
Как видно предоставление доступа через grant не обеспечивает наличия прав на kill через процедуру. Что же делать? Можно включить логин testUser в роль setupadmin, но тогда он сможет отлючать любые соединения. Помогут сертификаты!

Создадим в базе данных master самоподписанный сертификат:

use master

create certificate KillWrongSessCert

       encryption by password = N'$%#$T$#TE'

       with subject = N'Сертификат для отключения только некоторых соединений.',

             start_date = '2013-12-31',

             expiry_date = '2015-12-31'
Мы задали защиту сертификата через пароль, указали сроки его действия (это опционально), задали описание. Теперь на основе созданного сертификата создадим логин:
create login KillWongSessLog from certificate KillWrongSessCert

Под данным логином никто не сможет подключиться к серверу, поэтому ему можно спокойно дать доступ на kill любых соединений:
alter server role processadmin add member KillWongSessLog

Пока мы работали с базой данных master. Чтобы перейти к нашей базе Data требуется создать резервеую копию сертификата:

backup certificate KillWrongSessCert to file = 'C:\Users\Edynak\Desktop\KillWrongSessCert.cer'

       with private key

       (

             file = 'C:\Users\Edynak\Desktop\KillWrongSessCert.pvk',

             encryption by password = N'#$#D',

             decryption by password = N'$%#$T$#TE'

       )
В этом коде мы задали место хранения резервной копии сертификата, указали для дешифровки тот же пароль, который используется для защиты сертификата, а также задали пароль для хранения приватного ключа. Восстановим сохраненную копию в базе Data:

use Data

create certificate KillWrongSessCert

       from file = 'C:\Users\Edynak\Desktop\KillWrongSessCert.cer'

       with private key

       (

             file = 'C:\Users\Edynak\Desktop\KillWrongSessCert.pvk',

             encryption by password = N'$%#$T$#TE',

             decryption by password = N'#$#D'

       )

Теперь осталось подписать хранимую процедуру созданным сертификатом:
add signature to dbo.KillWrongSessions by certificate KillWrongSessCert with password = N'$%#$T$#TE'

Здесь также указывается пароль, защищающий сертификат.
Запускаем код:

exec as login = 'testUser'

exec dbo.KillWrongSessions

go

revert

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