Jeff Moden (7/14/2012)
But [font="Arial Black"]considering the security threat involved[/font] in it, we do enable it for a while (couple of milliseconds), execute the command & disable it in sql modules. The sql modules are either signed with certificates or executes in super users context thus bit safe.
See? That's the whole stigma of xp_CmdShell. What security threat? "Everyone" says it's a security threat but, if your system is properly locked down, then it's actually NOT a security threat. If your system isn't properly locked down, then the use of xp_Cmdshell pales in comparison to the other things you really should be worried about.
Part of the stigma that xp_Cmdshell is a threat is because people actually make the huge mistake of giving low prived users the ability to directly run and do whatever they want with xp_CmdShell. To me, that's just insane. In fact and to me, having any non DBA users with any more than just PUBLIC privs or PUBLIC with "DataReader" privs is insane. In most cases, even the "DataReader" priv isn't necessary at all. That includes so called "super users". Unless they need DBO privs to do their job (and they usually don't), there's just no reason for it. In any case, they should never have SA privs and they certainly should NOT be given the priv to run xp_CmdShell directly. The SA level of privilege should be reserved for production DBAs and then only when they actually need it.
Although the use of signed certificates is one way to go, if your system is actually and correctly locked down, there usually isn't a reason to bother with signed certificates either even when someone needs to run a stored proc that contains a call to something like xp_CmdShell. In a properly locked down system, users will only have PUBLIC privs and they'll belong to Database Roles that give them EXECUTE privs on only those stored procedures that they need to execute to get their jobs done.
The only time that xp_CmdShell is a threat is when you give people the privs to use it directly and that's easy to avoid even though it's used in stored procedures that those users can execute.
Yeah, yeah... there will be those that say that someone could make a mistake in the future and give a non DBA user elevated privs or the privs to run xp_CmdShell directly and that would open a security gap because of xp_CmdShell. If that happens, then you don't have a properly locked down system anymore. People explain that away by saying "it's possible to hire people who don't actually know what they're doing". Yes it is if you don't know how to hire people that do. And, if you do create such a security gap, you have a whole lot more to worry about than just xp_CmdShell. If you're publicly traded and you get hacked well enough for someone to use xp_CmdShell, then you'll need to explain to the SEC and a couple of other agencies how you let someone hack you at the SA level. And, consider this... if you get hacked, it'll likely be at the SA level and someone who does that can turn xp_CmdShell on just like you.
I've even seen people who rename the xp_CmdShell related DLL to make it supposedly "impossible" to use it even if they do get hacked at the SA level. Guess what? That's NOT going to stop them from getting to the command line. There IS a super simple hack to easily get to the command line through OPENROWSET if you can get into a system at the SA level.
Stop worrying about xp_CmdShell and start worrying about properly locking your system down. Start by making all non DBA users and Logins have only PUBLIC privs with EXECUTE privs on only the stored procs they need to run.
Security is a full time and very necessary job. Just do it.