Denying DML operations from another server

Question

Denying DML operations from another server

SQLHeap

Hall of Fame

Points: 3572
More actions
February 24, 2014 at 6:08 am

#304514

So, I have this developer that created a job on a dev server that executes a number of ssis packages that I handed over and he/she didn't change any connection information. So he/she updated production (loving it right now :pinch:). How can I deny any updates, inserts, or deletes if the command comes from a particular dev server?
There is an exception to every rule, except this one...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The best method for that is to have the prod database servers segmented away from the dev servers via firewall/router. Then have firewall rules preventing access between dev and prod.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

SQLHeap (2/24/2014)
So, I have this developer that created a job on a dev server that executes a number of ssis packages that I handed over and he/she didn't change any connection information. So he/she updated production (loving it right now :pinch:). How can I deny any updates, inserts, or deletes if the command comes from a particular dev server?

go directly to the source.

disable or delete the job immediately.

make the developerr review the connections in the packages and fix it.

make the developer review the changes in front of you...i've found that when a developer has to SHOW someone the changes, they tend to find one more or think it through better.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLHeap Hall of Fame Points: 3572 More actions · Answer 3

SQLRNNR (2/24/2014)
The best method for that is to have the prod database servers segmented away from the dev servers via firewall/router. Then have firewall rules preventing access between dev and prod.

Unfortunately the dev server still needs to be able to read from the prod server.

There is an exception to every rule, except this one...

SQLHeap Hall of Fame Points: 3572 More actions · Answer 4

Lowell (2/24/2014)
SQLHeap (2/24/2014)
So, I have this developer that created a job on a dev server that executes a number of ssis packages that I handed over and he/she didn't change any connection information. So he/she updated production (loving it right now :pinch:). How can I deny any updates, inserts, or deletes if the command comes from a particular dev server?
go directly to the source.
disable or delete the job immediately.
make the developerr review the connections in the packages and fix it.
make the developer review the changes in front of you...i've found that when a developer has to SHOW someone the changes, they tend to find one more or think it through better.

I always have to double check this developer, didn't quite understand what she was up to on this one. I should've asked.

There is an exception to every rule, except this one...

Lowell SSC Guru Points: 323518 More actions · Answer 5

SQLHeap (2/24/2014)
SQLRNNR (2/24/2014)
The best method for that is to have the prod database servers segmented away from the dev servers via firewall/router. Then have firewall rules preventing access between dev and prod.
Unfortunately the dev server still needs to be able to read from the prod server.

you said the server needs to read from production, that's fine, but a specific erroneous job does NOT need to be doing whatever it's doing.

fix the problem, not try to block the problem via a bunch of extra hoops to jump through. trying to find out what user is used on the dev server, and blocking jsut queries from that user, just form that hostname, is not going to be possible;

you can create database triggers for DDL, but not DML, you'd have to put triggers on each table, i'd think. huge waste of time, when you can simply fix or disable the offending process.

it's TRIVIAL to disable the job, and the first thing you should do until it's fixed. anything else you do is not going to be a best practice.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLHeap Hall of Fame Points: 3572 More actions · Answer 6

Good advice, I will follow it, thanks.

There is an exception to every rule, except this one...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

SQLHeap (2/24/2014)
SQLRNNR (2/24/2014)
The best method for that is to have the prod database servers segmented away from the dev servers via firewall/router. Then have firewall rules preventing access between dev and prod.
Unfortunately the dev server still needs to be able to read from the prod server.

That is still doable with firewalls. Dev can connect and you can set the firewalls to prevent prod from connecting to dev.

When Dev connects, the account it uses should have nothing more than the ability to read data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Lowell (2/24/2014)
SQLHeap (2/24/2014)
SQLRNNR (2/24/2014)
The best method for that is to have the prod database servers segmented away from the dev servers via firewall/router. Then have firewall rules preventing access between dev and prod.
Unfortunately the dev server still needs to be able to read from the prod server.
you said the server needs to read from production, that's fine, but a specific erroneous job does NOT need to be doing whatever it's doing.
fix the problem, not try to block the problem via a bunch of extra hoops to jump through. trying to find out what user is used on the dev server, and blocking jsut queries from that user, just form that hostname, is not going to be possible;
you can create database triggers for DDL, but not DML, you'd have to put triggers on each table, i'd think. huge waste of time, when you can simply fix or disable the offending process.
it's TRIVIAL to disable the job, and the first thing you should do until it's fixed. anything else you do is not going to be a best practice.

Agreed. But if a process needs to be developed and tested, it should be able to execute against dev. This need for testing leaves the door open still for another process to come along, foul up production data and then require time to fix what is broken.

That is one reason why a lot of places have implemented (and more are doing it these days) a firewall between the production databases and the rest of the network. It limits surface area for problems.

If a dev box needs to read from production, that is one thing. A process could be designed to eliminate dev from connecting direct to prod for whatever requirement causes them to read from production.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events