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:
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):
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:
- the database owner does not have the permission
- 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).