patternsqlMinor
Best practices on dealing with devs that want to use XP_CMDSHELL to BCP OUT
Viewed 0 times
wantdevswithxp_cmdshelldealingbcppracticesthatuseout
Problem
This is not a technical question. I would like to know the best practice to use in a place where devs want to have SA for everything.
The most recent event is:
Devs want to use BCP OUT to export data to a txt file really fast.
What should I think about it? Should I say "why you need this to be THIS fast?"
I'm sure there's a lot of alternatives to export data to .TXT file. they can be slower but it does the job.
We don't use Windows login here, only logins, so I can't create a proxy account (I'm reading something about this).
Note: They use Delphi to write application program code.
The most recent event is:
Devs want to use BCP OUT to export data to a txt file really fast.
What should I think about it? Should I say "why you need this to be THIS fast?"
I'm sure there's a lot of alternatives to export data to .TXT file. they can be slower but it does the job.
We don't use Windows login here, only logins, so I can't create a proxy account (I'm reading something about this).
Note: They use Delphi to write application program code.
Solution
There are two things at play here
For 1, management has to be okay with any developer walking out the door with whatever data they export to a text file. This might be sensitive PII, it might be protected IP, or just customer lists that a competitor would want.
For 2, any one of these developers that has sysadmin can change settings, shut down the server, take backups, run DBCC commands, and a whole bunch of other stuff. If management is going to let them do that, then management and the developers have to be on the on call rotation to fix things when a developer with sysadmin messes something up.
Also, xp_cmdshell doesn't just write files out, it can interact with the file system, executables, and nearly anything else you can dream of. You could use it to map a network drive to a DropBox folder and... Well, you get the idea.
BCP doesn't necessarily need xp_cmdshell to work. Maybe time to talk about the process behind that.
Permissions
A bcp out operation requires SELECT permission on the
source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any
of the following is true:
Hope this helps!
- Data security
- Server health
For 1, management has to be okay with any developer walking out the door with whatever data they export to a text file. This might be sensitive PII, it might be protected IP, or just customer lists that a competitor would want.
For 2, any one of these developers that has sysadmin can change settings, shut down the server, take backups, run DBCC commands, and a whole bunch of other stuff. If management is going to let them do that, then management and the developers have to be on the on call rotation to fix things when a developer with sysadmin messes something up.
Also, xp_cmdshell doesn't just write files out, it can interact with the file system, executables, and nearly anything else you can dream of. You could use it to map a network drive to a DropBox folder and... Well, you get the idea.
BCP doesn't necessarily need xp_cmdshell to work. Maybe time to talk about the process behind that.
Permissions
A bcp out operation requires SELECT permission on the
source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any
of the following is true:
Hope this helps!
Context
StackExchange Database Administrators Q#204204, answer score: 7
Revisions (0)
No revisions yet.