September 25, 2023 at 7:18 pm
All,
In attempts thinking that I am smarter than your average bear, I attempted to do the right thing and write a wrapper for users to be able to enable/disable certain jobs. Trying to avoid the pitfalls of allowing users access to modify everything in a job and avoid code elevation, I wrote this test harness that executes as a certificate based user with a signed sproc in msdb. As I understand, the only requirement should be to allow the user to execute the proc, and the cert user needs SQL operator role in order to run sp_update_job. Perhaps I am missing something here but the proc appears to be executing as the caller not as the cert-based user even though it is signed. I think I am missing something completely obvious here.
Below is a test harness:
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'TestJob',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
GO
if not exists (SELECT * FROM sys.certificates where NAME = 'JobExecTestCert')
begin
CREATE CERTIFICATE [JobExecTestCert]
ENCRYPTION BY PASSWORD = N'Password1'
WITH SUBJECT = N'Certificate for executing jobs wrapper'
END
IF NOT EXISTS (SELECT * FROM SYS.server_principals WHERE NAME = 'JobExecTest' AND TYPE = 'C')
BEGIN
CREATE USER JobExecTest FROM CERTIFICATE JobExecTestCert
ALTER ROLE SQLAgentOperatorRole ADD MEMBER JobExecTest
END
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE type = 'R' AND name = 'TestJob_Exec')
BEGIN
CREATE ROLE TestJob_Exec
END
GO
USE msdb
GO
CREATE OR ALTER PROCEDURE [dbo].[usp_EnableDisableTestJob] @enabled CHAR(1) AS
BEGIN
DECLARE @JobName sysname,
@enabledflag TINYINT,
@jobid UNIQUEIDENTIFIER,
SELECT @enabledflag = CASE @enabled
WHEN 'Y' THEN 1
WHEN 'N' THEN 0
ELSE 1
END
SELECT SYSTEM_USER 'system Login'
, USER AS 'Database Login'
, NAME AS 'Context'
, TYPE
, USAGE
FROM sys.user_token
EXEC [sp_update_job] @job_name = 'TestJob', @enabled = @enabledflag
END
GO
ADD SIGNATURE
TO [dbo].[usp_EnableDisableTestJob]
BY CERTIFICATE [JobExecTestCert]
WITH PASSWORD = 'Password1';
GRANT EXECUTE ON [dbo].[usp_EnableDisableTestJob] TO [TestJob_Exec]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'TestUsr')
BEGIN
CREATE LOGIN TestUsr WITH PASSWORD = 'Password1'
END
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'TestUsr')
BEGIN
CREATE USER TestUsr FROM LOGIN TestUsr
ALTER ROLE [TestJob_Exec] ADD MEMBER TestUsr
END
EXECUTE AS LOGIN = 'TestUsr';
GO
[dbo].[usp_EnableDisableTestJob]
@enabled = 'Y'
GO
SELECT SUSER_NAME()
September 26, 2023 at 3:03 pm
Is the execution as JobExecTest lost when another stored procedure is called from the signed procedure? (as opposed to for example selecting from a table or view). See https://learn.microsoft.com/en-us/sql/t-sql/statements/add-signature-transact-sql?view=sql-server-ver16#countersignatures where it states the signature is lost when another module is called. This can be overcome by countersigning, so in this case you would need to countersign [sp_update_job] with the JobExecTestCert
September 27, 2023 at 5:58 pm
Adrian, you were exactly correct! Thanks so much for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply