Jeff Moden (4/24/2013)
opc.three (4/24/2013)
Who cares? Maybe nowhere. Maybe somewhere. But either way the files were deleted while Homer was logged in as himself. Do the same through xp_cmdshell and the delete would be logged against the SQL Server service account, or the xp_cmdshell proxy account.The real fact of the matter is that most of the world has no auditing at that level so claims of added security in that area are absolutely bogus.
And, no... having xp_CmdShell turned on doesn't open you up for requests from malicious users.
Sure it does. It gives cover for malicious users whose actions would not be easily differentiated from one other users running xp_cmdshell or automated processes running xp_cmdshell.
Unless a malicious user got in with SA privs, they can't do anything with xp_CmdShell even if it's turned on. If a malicious user gets in with SA privs, they can turn xp_CmdShell on and the logging that then occurs is simple testimony to how bad your security was. Having it turned off did nothing to increase security unless you consider the blood stain in your log to be an increase in security.
Awesome...so you're down to labeling an exposure introduced by using xp_cmdshell as "bogus" and calling audit trails a "blood stain in the log"... 😛 That's OK, dismiss, but it doesn't change the fact that xp_cmdshell is less auditable, less secure, and just a plain old bad idea when it comes to designing applications when compared to almost any other available option.
All kinds of auditing can take place at the OS level and the network level, most or all of which a DBA team will have no idea are in place let alone have access too in order to manage or defeat. Layering...
It's no use...honestly...if you want to accept the risk in using xp_cmdshell, fine, that's your prerogative. I have said it many times before, and I continue to say it, it's your right to choose. Where I take issue is where you portray xp_cmdshell as secure and safe for use in the large and general sense. It's clearly not a secure option to have enabled in an environment and is absolutely not a good choice for any kind of application development, regardless of how you think it is being leveraged directly, or indirectly via a locked down stored proc. I would also like to mention again that Microsoft has initialized the xp_cmdshell option as "disabled" since SQL 2005 and just about every "Security Best Practices" document, book or respected figure on the topic recommends leaving it disabled.
You mentioned the simple DOS DEL command and asked how PowerShell might do something better. How about deleting all the files in a sub-directory with a specific file mask that are older than n-timeunits and are in a folder anywhere in the hierarchy that starts with a Q, or some other seemingly contrived set of requirements that oddly enough slips into our real-world set of requirements? Could you do that with a mix of DOS, T-SQL and xp_cmdshell by inspecting rows in a table returned from a DIR /S and then issuing lot's of DEL, sure, but that would be gobs of code and I can do that in one line of PowerShell code. Point is, DOS is good for some things but its a blunt tool. Yeah, I used to spin up the occasional Windows Batch file back in the day, but now...why bother? Because "it works"? Yeah, "it works", for some things, but PowerShell works better and there are lots of things PowerShell can do that CmdShell will never be able to touch in terms of functionality.
But this is less a conversation about PowerShell or indictment of DOS as it is about how xp_cmdshell is a bad choice in and of itself, and it clearly is a bad choice. I love you man, but I really hope one day you will realize the error of your ways and leave xp_cmdshell behind. I know of a fantastic site where people talk about solutions that do not involve accessing the file system from within T-SQL that could serve as a great resource :rolleyes: :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato