xp_cmdshell called from procedure WITH EXECUTE AS OWNER

5 votes
2 answers
5145 views
                          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?
                        
Asked by John Rees (206 rep)
Nov 29, 2019, 10:48 AM
Last activity: Dec 2, 2019, 04:14 PM
xp_cmdshell called from procedure WITH EXECUTE AS OWNER

Related Questions
