Grant EXECUTE to user on XP_CMDSHELL

Test · 发表于 2013-5-21 14:33:41

For my example code (which follows), the first thing you need to do is to setup a Windows user called "TestDummy" with just normal user privs.  You also need to setup a user called SqlCmdUser and give it privs to whatever drives you want someone with the ability to run xp_CmdShell to be able to see.

Now, I create a test database in the following code.  Everywhere it says "yourdomainname", you'll need to replace with the domain name of your Windows server.

You need to be REAL careful with this code.  This is code I did a demo with to prove that xp_CmdShell could actually be used safely and I wanted to make it easy to rerun.  If you don't review the DROPs in this code before you run it, you're crazy.

You must also have enabled xp_CmdShell before you do any of this.  You can do that with the following code which comes straight out of Books Online.

WARNING!!! THIS CODE TURNS ON XP_CMDSHELL.  HAVING IT TURNED OFF WON'T ACTUALLY HELP YOU IF YOU HAVE ANY PUBLIC FACING OR UNTRUSTWORTHY INTERIOR USERS THAT HAVE "SA" PRIVS!!!  INSTEAD OF MESSING AROUND WITH THIS CODE, YOU SHOULD BE FIXING YOUR SECURITY, INSTEAD!!!

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

复制代码

Here's my code. It does everything else. Again, REVIEW THE DROPs before you run it. Replace "yourdomainname" with the domain name of your, well, your domain.

The code does a bunch of one-time setup to build the correct users. There's some display code in there to show that the only privs the TestDummy user has is "PUBLIC" with EXECUTE privs on a sample stored proc that does a simple "DIR" command. You can, of course, to change the stored procedure to take a full DOS command but I still recommend that you limit the user to just those things the (s)he needs to do.

The code also does a demonstration that shows that it can execute the stored procedure that contains a call to xp_CmdShell but can't execute xp_CmdShell itself. As usual, for more information, see the comments in the code and Books Online.

--===== Make sure none of the test objects I use exist ahead of time so that we can see that this all actually works
SELECT '****************************** Making sure the things we need don''t already exist. ******************************';
USE MASTER;
DROP DATABASE MyTester; --BE REAL CAREFUL HERE!!! Drops the database I tested against
EXEC sp_xp_cmdshell_proxy_account NULL; --Drops the cmd shell proxy just to be sure.
DROP USER [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.
DROP LOGIN [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.
DROP USER [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.
DROP LOGIN [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.
GO
-----------------------------------------------------------------------------------------------------------------------
--===== Recreate my test database and the user which only has "public" privs.
-- I believe the DEFAULT_SCHEMA is important here.
SELECT '****************************** Creating [MyTester] DB and TestDummy. ******************************';
CREATE DATABASE [MyTester];
GO
USE [MyTester];
CREATE LOGIN [yourdomainname\TestDummy] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTester], DEFAULT_LANGUAGE=[us_english];
CREATE USER [yourdomainname\TestDummy] FOR LOGIN [yourdomainname\TestDummy] --This just maps the database for the user
GO
--===== This just displays how limited the TestDummy user is
EXEC sp_helpuser [yourdomainname\TestDummy];
GO
-----------------------------------------------------------------------------------------------------------------------
--===== Now we build the Login and proxy account using the SqlCmdUser I built in Windows on my box at home.
-- IMPORTANT!!! A step we cannot skip is that we have to build a user from the SqlCmdUser login.
-- NOTE THAT THIS MUST BE A SINGLE USER AND NOT A WINDOWS GROUP!
SELECT '****************************** Building/Granting Proxy user stuff. ******************************';
USE [master];
CREATE LOGIN [yourdomainname\SqlCmdUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
CREATE USER [yourdomainname\SqlCmdUser] FOR LOGIN [yourdomainname\SqlCmdUser] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_xp_cmdshell_proxy_account 'yourdomainname\SqlCmdUser','SqlCmdUser';
--===== Very important here... we have to grant access to xp_CmdShell to the new Window's user...
GRANT EXECUTE ON xp_CmdShell to [yourdomainname\SqlCmdUser];
GO
--===== This just displays how limited even the SqlCmdUser is!!!!
EXEC sp_helpuser [yourdomainname\SqlCmdUser];
GO
-----------------------------------------------------------------------------------------------------------------------
-- ********** NOTE THAT EVERYTHING ABOVE IS AS WE HAD IT BEFORE! **********
-- ********** NOTE THAT THE ONLY THING WE HAVE TO DO IN THE STORED PROCS (SEE BELOW
-- ********** IS TO INCLUDE "WITH EXECUTE AS OWNER"
-- heh... And Bob's your Uncle!
-----------------------------------------------------------------------------------------------------------------------
--===== Create a stored procedure in the new "MyTester" database that uses xp_CmdShell.
-- Keep in mind that, right now, we're signed in as a member of "dbo".
USE [MyTester];
GO
DROP PROCEDURE dbo.GetDirInfo;
GO
CREATE PROCEDURE dbo.GetDirInfo
WITH EXECUTE AS OWNER
AS
EXEC xp_cmdshell 'DIR C:\';
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;
;
GO
--===== Give the general puplic privs to run the sproc.
GRANT EXECUTE ON dbo.GetDirInfo TO PUBLIC
;
GO
-----------------------------------------------------------------------------------------------------------------------
--===== Now, show that the "TestDummy" user can execute the proc but not xp_cmdshell itself.
-- Simulate logging in as a user with low privs...
EXECUTE AS LOGIN = 'yourdomainname\TestDummy'
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;
-- This works... (which is what we want)
PRINT REPLICATE('=',80);
PRINT '********** Testing execution of dbo.GetDirInfo **********'
EXEC dbo.GetDirInfo
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;
-- This doesn't... (which is also what we want)
PRINT REPLICATE('=',80);
PRINT '********** Testing execution of xp_CmdShell directly **********'
EXEC xp_cmdshell 'DIR C:\'
;
GO
--===== Test complete... go back to normal.
REVERT
;

复制代码

账号		自动登录	找回密码
密码			注册

Grant EXECUTE to user on XP_CMDSHELL

浏览过的版块