Sql-server – xp_cmdshell called from procedure WITH EXECUTE AS OWNER

sql serverxp-cmdshell

I am trying to understand details of wrapping xp_cmdshell functionality in user defined stored procs, so that other users can just be given execute permission to the stored procs rather than xp_cmdshell.

The steps in this scenario are:

  1. xp_cmdshell is already enabled and a xp_cmdshell proxy has been created
  2. A user with db_owner membership creates a stored proc WITH EXECUTE AS
    OWNER which calls xp_cmdshell
  3. The user executes the stored proc and therefore executes arbitrary shell code

This is unexpected to me. I would not expect a user with only db_owner to be able to achieve this. (Obviously assuming xp_cmdshell has already been enabled by a sysadmin.)

When the database owner is changed from sa to another low privileged login, then the user stored proc is no longer able to call xp_cmdshell.

USE MASTER;
CREATE DATABASE testdb;
CREATE LOGIN testuser WITH PASSWORD = 'password', CHECK_POLICY=OFF;
CREATE LOGIN dummyuser WITH PASSWORD = 'password', CHECK_POLICY=OFF;
SELECT * from sys.credentials WHERE NAME LIKE '%cmdshell%';
-- returned: 101 ##xp_cmdshell_proxy_account## .....

USE testdb;

EXEC sp_changedbowner 'sa';  
CREATE USER testuser FOR LOGIN testuser;
ALTER ROLE db_owner ADD MEMBER testuser;

EXECUTE AS LOGIN = 'testuser';
GO

CREATE PROCEDURE [dbo].[testproc]
WITH EXECUTE AS OWNER
AS
    SELECT SUSER_NAME() as [SUSER_NAME()], USER_NAME() as [USER_NAME()];  
    exec xp_cmdshell 'echo %time%';
GO

SELECT SUSER_NAME() as [SUSER_NAME()], USER_NAME() as [USER_NAME()];  

EXEC dbo.testproc; 
-- returned: sa dbo  proving that the call to xp_cmdshell has succeeded

EXEC xp_cmdshell 'echo %time%';
-- returned: The EXECUTE permission was denied on the object 'xp_cmdshell'

REVERT

EXEC sp_changedbowner 'dummyuser';  
EXECUTE AS LOGIN = 'testuser';
EXEC dbo.testproc; 
-- returned: The EXECUTE permission was denied on the object 'xp_cmdshell'
-- proving that the sysadmin role of the database owner is relevent

REVERT

Note that I have not granted execute xp_cmdshell permission to any particular user.

I thought that enabling xp_cmdshell was ok if care was taken to only grant execute xp_cmdshell permission carefully, but my example seems to show otherwise.

Since a sysadmin is often a database owner, does this example show a serious security problem, or am I misunderstanding something?

Best Answer

does this example show a serious security problem, or am I misunderstanding something?

You are misunderstanding what is actually going on here.

the database owner is changed from sa to another low privileged login, then the user stored proc is no longer able to call xp_cmdshell.

That is true in this case, but that is merely due to how you did (or more accurately: did not) configure things (more on that in a moment).

I thought that enabling xp_cmdshell was ok if care was taken to only grant execute xp_cmdshell permission carefully,

It is.

but my example seems to show otherwise.

I would disagree with that. This is just a matter of not understanding the security mechanism, and this does happen to be a tricky case of it, so that is quite understandable.

A few things:

  1. EXECUTE AS OWNER is not specifically the issue. You could have done EXECUTE AS N'dbo' to get the same effect.

  2. The database being owned by a sysadmin is not specifically the issue. You could have done EXECUTE AS N'other' where other is the name of a User that is associated with a Login that has a User in [master] that has been granted execute on xp_cmdshell to get the same effect.

  3. No, server level permissions are not being granted. To prove this, add the following two lines to your test stored procedure:

    EXEC sp_configure N'allow updates', 1; RECONFIGURE;
SELECT * FROM sys.dm_exec_sessions;

    Go ahead and change the db owner back to sa and execute the proc. You will get errors on the sp_configure and the RECONFIGURE, and you will only get 1 row back from sys.dm_exec_sessions, your session's row, because you do not have the VIEW SERVER STATE server level permission, something sa definitely has.

What you are experiencing is a function of two things:

  1. You enabled the proxy account which lets non-sysadmins accomplish this if they have execute permission (not sure why you enabled this, unless you don't want to be using the SQL Server service account, which is a good idea, but doesn't work for sysadmins)
  2. the DB was owned by a login that was associated with a user present in master that was granted permission to exec xp_cmdshell (this associated is derived from the EXECUTE AS combined with the DB ownership)

To prove this, exec the following (at the end of your current test code in the question, with dummyuser owning the DB):

REVERT;
USE [master];
CREATE USER [dummyuser] FOR LOGIN [dummyuser];
GO
GRANT EXEC ON xp_cmdshell TO [dummyuser];
USE [testdb];

Then do:

EXECUTE AS LOGIN = 'testuser';
EXEC dbo.testproc;

and you will now, instead of getting the permission denied error, either have success with xp_cmdshell, or you will get the following error if the proxy account is not configured:

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line YYYYY [Batch Start Line XXXXX]
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Even better would be to do the following, which removes the permission from dummyuser, creates a new low-privileged account, and grants that account the xp_cmdshell permission:

USE [master];
CREATE LOGIN [cmdshell] WITH PASSWORD = 'password', CHECK_POLICY=OFF;
CREATE USER [cmdshell] FOR LOGIN [cmdshell];
GRANT EXEC ON xp_cmdshell TO [cmdshell];

REVOKE EXEC ON xp_cmdshell FROM [dummyuser];

USE [testdb];
CREATE USER [cmdshell] FOR LOGIN [cmdshell];

And then, change the EXECUTE AS clause in the stored procedure to be WITH EXECUTE AS 'cmdshell'. Execute the proc again and you will either have success or the proxy account error if it isn't set up. In this case:

  1. the database owner does not have the permission
  2. you aren't tied in any way to a sysadmin login.

This works because the EXECUTE AS user is associated with a login that has a user in master that has been granted execute on xp_cmdshell.

ALSO, this behavior is most likely tied to the object (i.e. xp_cmdshell) truly existing in the mssqlsystemresource database. You can't create an object in [master], grant a user (the same being used as the EXECUTE AS user of the stored procedure, even if that user is dbo / OWNER and the database is owned by sa) EXEC on the user proc in [master], and have it work (not without enabling TRUSTWORTHY, which you shouldn't do). Not even if the user stored procedure in [master] is marked as a system stored procedure (I've tried).

Of course, ideally you would use Module Signing to take care of the permission instead of EXECUTE AS (either associate with sysadmin server role to use SQL Server service account, or associate with user in [master] that has been granted EXECUTE on xp_cmdshell to use proxy account, assuming that it has been configured).

Related Question