Предоставление временных прав с помощью SQL Server Agent Jobs

http://blogs.technet.com/lyudmila_fokina/archive/2009/06/11/sql-server-agent-jobs.aspx

Людмила Фокина

Довольно распространенная ситуация – пользователю срочно нужно получить некоторые данные для отчета; в «нормальной жизни» он не имеет прав просматривать эти данные непосредственно, но в данный момент, например, его коллега (который имеет соответствующие права), в отпуске, отчет нужен уже вчера, все в панике…

Что делает в такой ситуации администратор? Ну, например, он предоставляет данному пользователю права на просмотр данных и записывает себе на бумажке: «Отменить права Васи просматривать годовой отчет». Дальше все просто – бумажка теряется, Вася на веки остается с правами, которые ему не полагаются.

Есть способ дать пользователю временные права, которые автоматически будут истекать в определенное  время.

Допустим, у нас есть пользователь Bob и процедура p_test, которую ему срочно нужно исполнить. Воспользуемся SQL Server Agent Jobs. Вы даете пользователю права на исполнение этой процедуры (GRANT EXECUTE ON p_test TO Bob) и создаете Job, который будет исполняться один раз в назначенное время, и будет отменять права пользователя на исполнение.

Создать Job можно через UI SQL Server Management Studio (SQL Server Agent -> Jobs -> New Job…) или программно. Например, следующий скрипт создает Job для отмены права пользователя Bob исполнять процедуру p_test и программирует ее исполнение (создает Schedule) один раз 10.06.2009 в 23:59:59

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job @job_name=N'RevokeTempPerm',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'To revoke temporary permissions',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa', @job_id = @jobId OUTPUT

 

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RevokeTempPerm',

            @step_id=1,

            @cmdexec_success_code=0,

            @on_success_action=1,

            @on_success_step_id=0,

            @on_fail_action=2,

            @on_fail_step_id=0,

            @retry_attempts=0,

            @retry_interval=0,

            @os_run_priority=0, @subsystem=N'TSQL',

            @command=N'REVOKE EXECUTE ON p_test TO Bob',

            @database_name=N'db_source',

            @flags=0

 

EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RevokeTempPerm',

            @enabled=1,

            @freq_type=1,

            @freq_interval=0,

            @freq_subday_type=0,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=0,

            @active_start_date=20090610,

            @active_end_date=99991231,

            @active_start_time=0,

            @active_end_time=235959,

            @schedule_uid=N'0882904e-9eaa-46ee-b8f2-ccfb56b88492'

 

EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

GO

Аналогично можно создавать Job автоматически предоставляющий определенные права пользователям в определенное время.

Побочным эффектом такого метода является то, что со временем такие Job's могут накапливаться. Соответственно, можно будет создать процедуру исполняющуюся время от времени и удаляющую просроченные Job, либо переиспользовать  их меняя график их выполнения (Schedule).