Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SQLCMD Expand / Collapse
Author
Message
Posted Wednesday, November 20, 2013 10:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
opc.three (11/20/2013)

Chances are I could accomplish this through a SQLCLR. I could definitely setup an Agent job that a low-priv user could run by executing a stored proc.


Heh... who on this good green Earth with even an ounce of concern for security would allow that to happen in an uncontrolled manner? Yes, I agree that there are many ways that, as an SA prived DBA, I could allow that to happen (emphasis on uncontrolled manner). That's part of my point. It either takes a person with SA privs to use it, never mind enable it. The exception to the enabling rule is that someone with Control Server privs could also enable it. Any DBA that gives a non-DBA those privs should be fired for reasons of bad security. The exception to direct usage is if some DBA is dumb enough to grant usage privs to a non-DBA user to execute xp_CmdShell directly. The DBA should be fired for that mistake, as well.

In a controlled manner, I see no problem with, for example, giving the user privs to do a DIR on a limited set of directories through a stored procedure that uses xp_CmdShell. I would never, however, give them privs to run xp_CmdShell directly.

And I understand about the backup thing. My question to you was do you know of any xp that can delete text or other files? I do. It's called xp_CmdShell


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516319
Posted Wednesday, November 20, 2013 10:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 7,098, Visits: 12,606
Jeff Moden (11/20/2013)
opc.three (11/20/2013)

Chances are I could accomplish this through a SQLCLR. I could definitely setup an Agent job that a low-priv user could run by executing a stored proc.


Heh... who on this good green Earth with even an ounce of concern for security would allow that to happen in an uncontrolled manner? Yes, I agree that there are many ways that, as an SA prived DBA, I could allow that to happen. That's part of my point. It either takes a person with SA privs to use it, never mind enable it. The exception to the enabling rule is that someone with Control Server privs could also enable it. Any DBA that gives a non-DBA those privs should be fired for reasons of bad security. The exception to direct usage is if some DBA is dumb enough to grant usage privs to a non-DBA user to execute xp_CmdShell directly. The DBA should be fired for that mistake, as well.

Ahhh, some common ground. And to that end, just because you can, doesn't mean you should

And I understand about the backup thing. My question to you was do you know of any xp that can delete text or other files? I do. It's called xp_CmdShell

And my question to you is, why would you ever need to delete text files on the host operating system's file system using T-SQL? The answer is you don't.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1516321
Posted Wednesday, November 20, 2013 10:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
opc.three (11/20/2013)
Jeff Moden (11/20/2013)
opc.three (11/20/2013)

Chances are I could accomplish this through a SQLCLR. I could definitely setup an Agent job that a low-priv user could run by executing a stored proc.


Heh... who on this good green Earth with even an ounce of concern for security would allow that to happen in an uncontrolled manner? Yes, I agree that there are many ways that, as an SA prived DBA, I could allow that to happen. That's part of my point. It either takes a person with SA privs to use it, never mind enable it. The exception to the enabling rule is that someone with Control Server privs could also enable it. Any DBA that gives a non-DBA those privs should be fired for reasons of bad security. The exception to direct usage is if some DBA is dumb enough to grant usage privs to a non-DBA user to execute xp_CmdShell directly. The DBA should be fired for that mistake, as well.

Ahhh, some common ground. And to that end, just because you can, doesn't mean you should

And I understand about the backup thing. My question to you was do you know of any xp that can delete text or other files? I do. It's called xp_CmdShell

And my question to you is, why would you ever need to delete text files on the host operating system's file system using T-SQL? The answer is you don't.


You're correct. I don't. I could use Powershell or SSIS or DOS or PERL or VB/VBScript, Java Script, or any of a thousand other tools... but I prefer not to have code scattered all over. If I can do it all from a single point, then things are a whole lot easier to manage and automate.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516322
Posted Wednesday, November 20, 2013 10:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 7,098, Visits: 12,606
Jeff Moden (11/20/2013)
opc.three (11/20/2013)
Jeff Moden (11/20/2013)
opc.three (11/20/2013)

Chances are I could accomplish this through a SQLCLR. I could definitely setup an Agent job that a low-priv user could run by executing a stored proc.


Heh... who on this good green Earth with even an ounce of concern for security would allow that to happen in an uncontrolled manner? Yes, I agree that there are many ways that, as an SA prived DBA, I could allow that to happen. That's part of my point. It either takes a person with SA privs to use it, never mind enable it. The exception to the enabling rule is that someone with Control Server privs could also enable it. Any DBA that gives a non-DBA those privs should be fired for reasons of bad security. The exception to direct usage is if some DBA is dumb enough to grant usage privs to a non-DBA user to execute xp_CmdShell directly. The DBA should be fired for that mistake, as well.

Ahhh, some common ground. And to that end, just because you can, doesn't mean you should

And I understand about the backup thing. My question to you was do you know of any xp that can delete text or other files? I do. It's called xp_CmdShell

And my question to you is, why would you ever need to delete text files on the host operating system's file system using T-SQL? The answer is you don't.


You're correct. I don't. I could use Powershell or SSIS or DOS or PERL or VB/VBScript, Java Script, or any of a thousand other tools... but I prefer not to have code scattered all over. If I can do it all from a single point, then things are a whole lot easier to manage and automate.

That's fair and I completely understand your position. But none of the tools you mention suffer from the security or auditing problems xp_cmdshell does. None of them intrinsically obfuscate the executors identity, potentially elevating their permissions in the process. So, it's clear you value the things you value, automation and simplicity of code management, more than leaving the security and auditing challenges xp_cmdshell brings with it on the sidelines. That's perfectly OK but that doesn't make those things any less present, or make it any less irresponsible for you to advocate a tool without the lengthy disclaimers it deserves, and those are some of the points I have been making for years now.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1516323
Posted Wednesday, November 20, 2013 10:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
That's just it. There are no security challenges to it. I don't allow users to use xp_CmdShell directly. I don't allow apps to execute it directly (never mind them having SA privs). They have no chance of elevating their privs because they cannot use it that way but, even if they could, they wouldn't get far because I also limit what the SQL Server login and the SQL Server Agent login have privs to do. So far as audit goes, you can bet your sweet bippy that the stored procs that use xp_CmdShell log who called them. Heh... I even do that with some of the stored procs that don't call xp_CmdShell.

As for auditing, lets ask the question about how many apps that have insert/update/delete privs that don't pass the identity of the person using the app. Now That's a concern and that includes SSIS.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516324
Posted Wednesday, November 20, 2013 11:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 7,098, Visits: 12,606
Jeff Moden (11/20/2013)
That's just it. There are no security challenges to it. I don't allow users to use xp_CmdShell directly. I don't allow apps to execute it directly (never mind them having SA privs). They have no chance of elevating their privs because they cannot use it that way but, even if they could, they wouldn't get far because I also limit what the SQL Server login and the SQL Server Agent login have privs to do. So far as audit goes, you can bet your sweet bippy that the stored procs that use xp_CmdShell log who called them. Heh... I even do that with some of the stored procs that don't call xp_CmdShell.

And the sysadmin members? ...oh that's right, you argue that you must trust them implicitly.

As for auditing, lets ask the question about how many apps that have insert/update/delete privs that don't pass the identity of the person using the app. Now That's a concern and that includes SSIS.

If you're doing it right then your internal apps are written such that your app users maintain their identity all the way through the stack and in a SQL Server context ORIGINAL_LOGIN() will be your friend. If you're dealing with a public-facing website that allows users to contribute or manage content and you don't have individual database Logins per web-user (which you won't if you want to scale up leveraging conection pooling of any kind) then you have a whole different set of auditing challenges.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1516326
Posted Thursday, November 21, 2013 8:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
opc.three (11/20/2013)
[quote]And the sysadmin members? ...oh that's right, you argue that you must trust them implicitly.


Jeez, Orlando. If you can't trust an SQL Server Admin or a Windows Admin to do the job you hired them for, then they shouldn't be sysadmin members or you need to fire them for being untrustworthy, etc., because they can't do their job right if they can't be trusted. It's that simple. Sure, you can setup some really strict auditing but who's going to audit the person who sets that up and monitors the logs?

You have to trust someone or it's time to turn off the computers and go home.

If you're doing it right then your internal apps are written such that your app users maintain their identity all the way through the stack and in a SQL Server context ORIGINAL_LOGIN() will be your friend. If you're dealing with a public-facing website that allows users to contribute or manage content and you don't have individual database Logins per web-user (which you won't if you want to scale up leveraging conection pooling of any kind) then you have a whole different set of auditing challenges.


Correct. In comparison to the number of apps that are written in such a thoughtful and security-wise fashion, what percentage of apps have you seen that are written incorrectly? Like I said, that's a real concern.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516453
Posted Thursday, November 21, 2013 10:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 7,098, Visits: 12,606
Jeff Moden (11/21/2013)
opc.three (11/20/2013)
[quote]And the sysadmin members? ...oh that's right, you argue that you must trust them implicitly.


Jeez, Orlando. If you can't trust an SQL Server Admin or a Windows Admin to do the job you hired them for, then they shouldn't be sysadmin members or you need to fire them for being untrustworthy, etc., because they can't do their job right if they can't be trusted. It's that simple. Sure, you can setup some really strict auditing but who's going to audit the person who sets that up and monitors the logs?

You have to trust someone or it's time to turn off the computers and go home.

Not quite. This is why network security, database admin and system admin are separate groups within a large organization. Like I said, for you your approach works and in most shops "xp_cmdshell is safe" is true enough, but irresponsible to simply bandy about.

If you're doing it right then your internal apps are written such that your app users maintain their identity all the way through the stack and in a SQL Server context ORIGINAL_LOGIN() will be your friend. If you're dealing with a public-facing website that allows users to contribute or manage content and you don't have individual database Logins per web-user (which you won't if you want to scale up leveraging conection pooling of any kind) then you have a whole different set of auditing challenges.


Correct. In comparison to the number of apps that are written in such a thoughtful and security-wise fashion, what percentage of apps have you seen that are written incorrectly? Like I said, that's a real concern.

Absolutely agree. It is a real shortcoming in many app designs. From personal experience and reading the experiences of others I suspect the security in most apps that interact with SQL Server are poorly thought our or poorly implemented when it comes to database security and auditing. From my perspective, the introduction of xp_cmdshell into any stack immediately brings the app into that poorly thought out or poorly implemented category due to its security and auditing shortcomings.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1516512
Posted Friday, November 22, 2013 3:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:24 AM
Points: 1,144, Visits: 299
Hany Helmy (11/11/2013)
Never used the SQLCMD before; but I guessed it right; lol


exactly same answer
Post #1516703
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse