SQL Server – Is EXECUTE AS OWNER in msdb a Security Risk?

best practicesSecuritysql server

I've been tasked with creating a system that allows non-privileged users to start and stop SQL Server Agent jobs. I'm creating stored procedures in the dbo schema in the msdb database using the WITH EXECUTE AS OWNER construct to run EXEC dbo.sp_start_job <JobID>;, etc.

The Books Online topic about EXECUTE AS says this is a "best practice":

Specify a login or user that has the least privileges required to perform the operations defined in the module. For example, do not specify a database owner account unless those permissions are required.

In my case, EXECUTE AS OWNER indicates [sa], since those modules are in the dbo schema, in the msdb database, which is always owned by [sa].

Am I inadvertently creating a security risk by doing this?

I'm not passing in any parameters that could be used in a SQL injection attack since the only parameter ever being passed in is a UNIQUEIDENTIFIER representing the job affected.

The user running the stored procedures that have EXECUTE AS OWNER have no access to the msdb database aside from membership in a database role I've explicitly created which GRANTs them the EXECUTE permission on the stored procs.

One of the conditions of this project is the users in question should not be able to run the SQL Server Agent GUI tools in any way. This rules out giving them membership in the built-in SQLAgentUserRole role. I'd also rather not modify the permissions on dbo.sp_start_job, etc, since I'm trying to not modify anything that SQL Server itself depends on (other than obviously adding items to the msdb database itself).

The code in question is here.

Best Answer

In and of itself, no, you are probably not adding a security risk. As long as no one but a sysadmin can alter the procedure and as long as the ONLY thing the procedure can do is start a job I don't see a problem.

The risks come in with the fact that MSDB is (and is supposed to be) marked as TRUSTWORTHY. That means that by using EXECUTE AS OWNER you are able to impersonate the SERVER level permissions. In this case since it's sa that means you could create a procedure to grant another user sysadmin.

That does mean you do want to limit permissions to MSDB though.

Related Solutions

Sql-server – SQL Server – DBO without Backup

Grant the user SELECT, INSERT, UPDATE, DELETE, EXECUTE and VIEW DEFINITION rights to the database (or the schema). This will grant the user the rights to all the objects within the database (or the schema).

You'll probably also need to give them rights like ALTER ANY OBJECT within the database as well so that they can modify the objects.

Execute Permission Denied on Object sp_start_job in SQL Server

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.) So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

Create the procedure in msdb.

Create a user for this user's login in msdb:

USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

Grant the user execute privileges on the stored procedure:
```
GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;
```

Test it - either by calling the procedure from another database:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

Result should be:

I am really...
---------------
sysadminaccount

Validate that this doesn't expose anything else in msdb to this user:
```
USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;
```
Result should be...

Msg 229, Level 14, State 5, Line 21
The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).

Best Answer

Related Solutions

Sql-server – SQL Server – DBO without Backup

Execute Permission Denied on Object sp_start_job in SQL Server

Related Question