April 1, 2008 at 9:49 am
If you set up a proxy that has cmd shell privs and write a proc that does one thing well as all procs should, AND you don't allow developers to ever know the proxy login AND you don't make cmd shell available to the developers in production, it's easy.
One too many IFs...
In our organization xp_cmdshell is being used in app code to perform file-system and other OS-related operations. I suspect we are not an exception. How does one ensure that the wrong argument is not passed to xp_cmdshell in app code? You haven't answered that.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 1, 2008 at 9:52 am
Ask how many DBA's allow developers to have write privs to production.
It's not about DBAs allowing devs direct access to production. It has nothing to do with that.
It's about allowing SAFE code deployed to production, and this is where xp_cmdshell becomes a liability.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 1, 2008 at 10:06 am
Marios Philippopoulos (4/1/2008)
Ask how many DBA's allow developers to have write privs to production.
It's not about DBAs allowing devs direct access to production. It has nothing to do with that.
It's about allowing SAFE code deployed to production, and this is where xp_cmdshell becomes a liability.
Which is an interesting thought. The problem is - I'm pretty sure you have to flag that CLR as "unsafe" in order for it to be able to access the OS command prompt in this way.
The bottom line is - ANY solution allowing you to pass commands in, that will have long lasting consequences in the OS "realm" is gonig to be by nature, UNSAFE. You can parse the commands, validate, separate, dice, slice, julienne, etc... - you're still opening a door you will not fully be able to control.
I have to say - I just do not understand the fascination with having SQL server run "OS Operations". OS operations should be the purview of the OS itself, and nothing else. If the operation needs to open a query in SQL server to get info, so be it, but I just don't see SQL Server ever really needing to "get involved". Especially when it's happening in resources that could otherwise be getting used by an actual SQL Server process.
Every time I hear of a "door" like this being opened, despite all of the assurances that it's a "one-way door", there's always some smarty finding some way to use it to get back in. Security flaw indeed, and one not needed IMO.
Personally - I'd be happy if EVERY permutation of xp_cmdshell were shut off. Permanently. The only thing I would see needing would be some way to bring data in (which would work with OPENDATASOURCE, etc...).
If that means that DBA's should learn some CLR to automate free-standing Processes to deal with OS tasks, so be it. I just don't see the need for SQL to "run" these for me.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 1, 2008 at 10:12 am
Marios Philippopoulos (4/1/2008)
Ask how many DBA's allow developers to have write privs to production.
It's not about DBAs allowing devs direct access to production. It has nothing to do with that.
It's about allowing SAFE code deployed to production, and this is where xp_cmdshell becomes a liability.
It sure does... you think Developers are as concerned as the DBA about what's SAFE code? xp_CmdShell is NOT a liability because you're not gonna write code where it takes in uncheck "commands" nor will anything but a proxy account be allowed to run it NOR will any developers be able to change it because the smart DBA won't allow the access... all parts are important to "allowing {ONLY} SAFE code {to be} deployed to production".
You still haven't answer my other suggestion... instead of the DBA having to learn C# or whatever, what about the developers learning good T-SQL?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 10:20 am
Matt Miller (4/1/2008)
Every time I hear of a "door" like this being opened, despite all of the assurances that it's a "one-way door", there's always some smarty finding some way to use it to get back in. Security flaw indeed, and one not needed IMO.
Heh... then you must have a real love for DTS/SSIS and the VB Script and Active-X objects that some folks write because they don't know how to correctly use DTS/SSIS either :):D:P:hehe:;)
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 10:29 am
Jeff Moden (4/1/2008)
Matt Miller (4/1/2008)
Every time I hear of a "door" like this being opened, despite all of the assurances that it's a "one-way door", there's always some smarty finding some way to use it to get back in. Security flaw indeed, and one not needed IMO.Heh... then you must have a real love for DTS/SSIS and the VB Script and Active-X objects that some folks write because they don't know how to correctly use DTS/SSIS either :):D:P:hehe:;)
Well - true. But the difference is - DTS is actually an EXTERNAL tool. It's got a lot of hooks, etc...but it's not considered "part" of the database engine. xp_cmdshell is an entirely different level of "evil" IMO.
And activeX, well - don't get me started...:):cool::D. Let's just leave it at "I'm not a fan", and call that a massive understatement...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 1, 2008 at 10:46 am
Matt Miller (4/1/2008)
Which is an interesting thought. The problem is - I'm pretty sure you have to flag that CLR as "unsafe" in order for it to be able to access the OS command prompt in this way.
The bottom line is - ANY solution allowing you to pass commands in, that will have long lasting consequences in the OS "realm" is gonig to be by nature, UNSAFE. You can parse the commands, validate, separate, dice, slice, julienne, etc... - you're still opening a door you will not fully be able to control.
Right, no argument there. That's why I retracted my original posting earlier. Any generic solution, be it xp_cmdshell or a variant, is undesirable.
I have to say - I just do not understand the fascination with having SQL server run "OS Operations". OS operations should be the purview of the OS itself, and nothing else. If the operation needs to open a query in SQL server to get info, so be it, but I just don't see SQL Server ever really needing to "get involved". Especially when it's happening in resources that could otherwise be getting used by an actual SQL Server process.
Every time I hear of a "door" like this being opened, despite all of the assurances that it's a "one-way door", there's always some smarty finding some way to use it to get back in. Security flaw indeed, and one not needed IMO.
DBA code is filled with instances of file-system operations: backups, log-shipping etc. require logic to access a file share, view its contents and do other such manipulations.
On the app side, it is not always possible to ask dev teams to put all such code in a middle tier. Granted, this might be the ideal solution, but, it requires certain standards be put in place and the DBAs directly involved in the software design process from the start. How often does that realistically happen?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 1, 2008 at 10:52 am
Jeff Moden (4/1/2008)
You still haven't answer my other suggestion... instead of the DBA having to learn C# or whatever, what about the developers learning good T-SQL?
Both are necessary.
You mention Regular Expressions, as the only case where the CLR in SQL makes sense. Should the DBA take someone's word that their CLR code handling RegExs is harmless? Or should they be able to go in and tell what that code is doing in detail?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 1, 2008 at 10:54 am
Jeff Moden (4/1/2008)
You still haven't answer my other suggestion... instead of the DBA having to learn C# or whatever, what about the developers learning good T-SQL?
Why not both? I find that being a developer myself makes it much easier for me to work with developers and explain what they need to do and why.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 10:58 am
Marios Philippopoulos (4/1/2008)
Matt Miller (4/1/2008)
Which is an interesting thought. The problem is - I'm pretty sure you have to flag that CLR as "unsafe" in order for it to be able to access the OS command prompt in this way.
The bottom line is - ANY solution allowing you to pass commands in, that will have long lasting consequences in the OS "realm" is gonig to be by nature, UNSAFE. You can parse the commands, validate, separate, dice, slice, julienne, etc... - you're still opening a door you will not fully be able to control.
Right, no argument there. That's why I retracted my original posting earlier. Any generic solution, be it xp_cmdshell or a variant, is undesirable.
I have to say - I just do not understand the fascination with having SQL server run "OS Operations". OS operations should be the purview of the OS itself, and nothing else. If the operation needs to open a query in SQL server to get info, so be it, but I just don't see SQL Server ever really needing to "get involved". Especially when it's happening in resources that could otherwise be getting used by an actual SQL Server process.
Every time I hear of a "door" like this being opened, despite all of the assurances that it's a "one-way door", there's always some smarty finding some way to use it to get back in. Security flaw indeed, and one not needed IMO.
DBA code is filled with instances of file-system operations: backups, log-shipping etc. require logic to access a file share, view its contents and do other such manipulations.
On the app side, it is not always possible to ask dev teams to put all such code in a middle tier. Granted, this might be the ideal solution, but, it requires certain standards be put in place and the DBAs directly involved in the software design process from the start. How often does that realistically happen?
Heh... When you have an "Exceptional" DBA 😉
http://www.sqlservercentral.com/Forums/Topic476576-263-1.aspx#bm476654
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 11:05 am
Marios Philippopoulos (4/1/2008)
Jeff Moden (4/1/2008)
You still haven't answer my other suggestion... instead of the DBA having to learn C# or whatever, what about the developers learning good T-SQL?
Both are necessary.
You mention Regular Expressions, as the only case where the CLR in SQL makes sense. Should the DBA take someone's word that their CLR code handling RegExs is harmless? Or should they be able to go in and tell what that code is doing in detail?
Now we're talking... but if you have a trustworthy individual like Matt Miller or Barry writing it, all languages have the same basis... get them to walk the DBA through the source code... it's how "peer reviews" are supposed to work. I can and have trouble-shot Cobol and RPG III... do you think I can write a lick of code in it?
But, the real key here is that CLR's are not necessary for 99.9% of anything you do in a database. It can all be done in T-SQL and the performance is usually better.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 11:17 am
Jeff Moden (4/1/2008)
But, the real key here is that CLR's are not necessary for 99.9% of anything you do in a database. It can all be done in T-SQL and the performance is usually better.
That's too sweeping and absolute a statement.
I don't quite agree, based on what I know and have read.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 1, 2008 at 11:18 am
rbarryyoung (4/1/2008)
Jeff Moden (4/1/2008)
You still haven't answer my other suggestion... instead of the DBA having to learn C# or whatever, what about the developers learning good T-SQL?Why not both? I find that being a developer myself makes it much easier for me to work with developers and explain what they need to do and why.
Good idea and the principle is sound... But developers who do not know how to write effective T-SQL against the database aren't going to do any better with a CLR even if they're absolute Ninja's at the external language. C# and VB are NOT relational database languages... they're ISAM/Procedureal/RBAR. Instead of a GUI developer writing CLR's, there should be a good T-SQL developer that can do it in a stored procedure. What about things that are procedural in nature such as Running Totals? Heh... a good SQL Developer or DBA knows the tricks of the trade and the code will run faster than any CLR ever could. Again, the only possible exception that I've seen during many a test with Matt Miller and a host of others is with Matt Miller's RegEx CLR.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 11:31 am
Marios Philippopoulos (4/1/2008)
DBA code is filled with instances of file-system operations: backups, log-shipping etc. require logic to access a file share, view its contents and do other such manipulations.
Backups, log-shipping have an OS component of course, but aren't an "OS operation" in my book. they're also highly limited in scope.
On the other hand - accessing fileshares from within SQL server is just something I would strongly recommend against.
Call me strange, but I'd rather run a script from the OS side that loops through a directory, and loads data into the SQL server, than to have SQL Server "messing" elsewhere with UNC privileges, etc....
On the app side, it is not always possible to ask dev teams to put all such code in a middle tier. Granted, this might be the ideal solution, but, it requires certain standards be put in place and the DBAs directly involved in the software design process from the start. How often does that realistically happen?
Can't really argue there. Look - I'm with you as to balancing reality with my wants and likes. That being said - EVERY standard requires some amount of fighting to put into place, and requires enforcement, etc..., in short - more work. So, it doesn't always happen. That STILL doesn't make those alternatives any better...
It just happens to be one of those things that I put on my plate to eliminate/rewrite when I find them. I have existing utils in place to run as Windows Services to automate file imports based on WMI triggers, so I guess I've started to make my job easier. I can even run multiple versions under different creds if I so choose.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 1, 2008 at 11:50 am
Marios Philippopoulos (4/1/2008)
Jeff Moden (4/1/2008)
But, the real key here is that CLR's are not necessary for 99.9% of anything you do in a database. It can all be done in T-SQL and the performance is usually better.
That's too sweeping and absolute a statement.
I don't quite agree, based on what I know and have read.
It's not too sweeping or absolute when you done as much performance testing between the CLR and T-SQL methods as some of us have.
But I respect your opinion... you use CLR's... in fact, you can actually show us that CLR's are better and faster. Pick one that YOU have actually written and put into production. Describe what it does and provide the million rows (by code, not by file) of data that it does it to. I'll try to write T-SQL to do the same thing. Both of us will send the code to a very impartial Matt Miller and let him run both on his machine. I suggest that you stay away from things like Running Totals. 😉
Of course, you should also provide the source code to Matt so he can "peer review" it before running it on his machine.
Not trying to pick a fight... I'm trying to demonstrate what I'm talking about.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply