SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLCMD


SQLCMD

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90287 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15501 Visits: 14396
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90287 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15501 Visits: 14396
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90287 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15501 Visits: 14396
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90287 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15501 Visits: 14396
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
jfgoude
jfgoude
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 299
Hany Helmy (11/11/2013)
Never used the SQLCMD before; but I guessed it right; lol :-)


exactly same answer
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search