Jeff Moden (4/3/2014)
[/b]
In that case, you should also make it so that DBAs only have read privs. 😉 There's no difference here between running a bad xp_CmdShell command and a bad TRUNCATE or a DELETE with no WHERE clause.
Hmm, maybe that is why he have the option of DML triggers for data changes and DDL triggers for schema changes...
No it doesn't. Only "SAs" can run or turn xp_CmdShell on unless one has made the horrible mistake of giving casual user privs to run it directly. Ostensibly, only DBAs have "SA" privs and they supposedly aren't "kiddies". An attacker that get's into your server as "SA" is going to hit paydirt whether they use xp_CmdShell or not. An attacker that successfully gets in as "SA" is also very likely using attack software to do such a thing and all of the attack software that I've seen turns on xp_CmdShell as soon as it makes a connection. You're statement is kind of proof that turning it off brings a false sense of security.
Think about what a script kiddie is. A script kiddie is someone who doesn't have any expertise but is using a script or code someone else wrote. The person who wrote coded certain functionality and the script kiddie makes the most of it, because it's usually push button stuff. However, when that stuff doesn't work, they don't know what to do.
So a script or program that a script kiddie uses could possibly get in with a privileged account. For instance, a keylogger trojan gets installed on a DBA's workstation. The script kiddie has the credentials, plugs them into the script, and the script attempts to execute xp_cmdshell. It fails, IDS is tripped, and you know you're under attack.
Don't assume that because someone has a privileged account, they know what to do with it. Malware is often bought and sold that does a lot of tasks for you. The scenario I describe happens frequently every day.
You and I both know that's probably not going to do a thing for anyone on most systems except provide a log that will be written testament that the security team failed to prevent an attack. An attacker doesn't need xp_CmdShell to make a self deleting job that uses an EXEC task. The attack vector the OP described is proof of that (didn't use xp_CmdShell). Having xp_CmdShell turned off will give the minor advantage that you say but I doubt it'll give most DBAs the time to do anything, never mind get an alert. The key is to keep attackers out and, if they do get in, to prevent them from attaining "SA" privs. Anything else is likely to provide a false sense of security that leads people into more lax methods.
Then you've been working with the wrong security/networking teams. IDS/IPS is typically monitoring network traffic not running on the host. It'll see the xp_cmdshell going across the network and alert. This isn't new functionality. It's been around for YEARS. Free IDS like Snort have detected it for years.
K. Brian Kelley
@kbriankelley