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,
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:
EXECUTE AS OWNER is not specifically the issue. You could have done
EXECUTE AS N'dbo' to get the same effect.
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.
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:
- 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)
- 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):
CREATE USER [dummyuser] FOR LOGIN [dummyuser];
GRANT EXEC ON xp_cmdshell TO [dummyuser];
EXECUTE AS LOGIN = 'testuser';
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
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];
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:
- the database owner does not have the permission
- you aren't tied in any way to a
This works because the
EXECUTE AS user is associated with a login that has a user in
master that has been granted execute on
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
OWNER and the database is owned by
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
xp_cmdshell to use proxy account, assuming that it has been configured).