how do you secure SQL against everyone?
-
we have a problem. our developers love to write code, not test it and implement it and have applications access sql servers they didn't access previously for new features. problem is that things like blocking comes up along with other issues and it takes weeks to troubleshoot apps and no one says that any changes were made
we use a mix of sql aithentication for apps and windows authentication for some users who need r/w access to the db's. problem is everyone shares passwords and there is no way to stop it.
we were thinking of firewalling off all the servers and giving access by IP along with sql/windows authentication to make sure all application changes go through IT.
has anyone done anything like this or is there a better way to do this?
-
Yes... don't allow developers to have access to write to production machines. Works for us. Create a new server with snapshots of the production box and use that as the developer's sandbox. Then, institute a change control system where only the DBA promotes code files and takes the time to review them before promoting them.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
yeah this is a common scenario in smaller shops as they get bigger....Jeff's suggestions are right on; you'll upset the apple cart so to speak , as the developers must be cut off from production no matter what.
my shop started as a small two man developer gorup, and has grown substantially since then. change control and testing were added only after we got a clietn base big enough to pay for it.
code must be tested before promotion, and the developer who wrote it can't be the ones who test it.
Your developers, like ours, used to do everything, from installing software, networking, ad hoc changes to code, and they have to be limited to their own playground now, instead. They'll resent it, but it's gotta be done.
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! -
What they said!
Even in small shops developer access to Production is risky, however you can usually deal with it. The only think you might want to grant the developers is access to the schema, so they can verify changes before code is promoted.
GRANT VIEW ANY DEFINITION TO ...
FYI, I have 5 developers, and all are VERY skilled. Two of them have full access, but they also support the application. The other 3 do not have access to production.
-
As said in previous posts, using staging servers can be a great first step; but I would add formal policies in the mix so that developers understand how serious the issue is.
Regarding firewalling, blocking by IP is usually not enough. Developers can easily go to other servers remotely and execute code from an IP that is allowed.
Your firewall needs to work by IP and by user name so that users can only access SQL from known IPs. This would provide another lock to your databases. Depending on the firewall there are additional options available.
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing -
for now we just changed the password on a user account and the developer changed a few things.
problem is we have a few sql accounts with the same username/password across most servers and after a while a lot of people know them. in this case it was a sql user id that the developer who uses that server gave someone else.
on the firewall thing we would have blocked everything and allowed only a few IP's.
-
A blanket block by IP would work if this meets your needs. If you need more granular control send me an email; I have something in mind.
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing -
stock market has been a lot more intersting than SQL lately
this thing died down some, like they always do. next months we'll probably change passwords, but the lockdown plan is dead for now
-
We used to have this problem with all our Dev guys having change access on our Production server and what we change there access to read only & view any definition. The only other thing that you maybe able to setup would be a DDL Trigger on all databases to Audit any changes to you databases then that way you have some way of finding out who made the change to the sysstem the created the problem with your app.
-
I was discussing development vs. production database access with one of our developers today. Currently, the developers have full access to their development database and zero access to production. All access to production databases is supposed to be done via an application. Not being able to see the data and schema makes it tough to troubleshoot production problems, so the developers have built backdoor utilities into their applications that allow them to run any query.
I would like to give the developers read access to the production data and schema. They should have the ability to verify data, views, stored procedures, etc. via Management Studio. Does anyone see an issue with this? How would I give them this level of access (our previous DBA said it was not possible)?
-
db_datareader would be a good start - would at least give your developers read only access to all the data. In sql 2005 you can also allow the user the ability to view but not alter stored procedures, etc.
Joe
-
Re-read what Jeff M. said: Make a snapshot of production and turn that over to the developers. This should give them a good platform for debugging / seeing how things work / whatever they're doing now/ etc. Yes, you will have to update this on a regular basis. (weekly?) It might also be worthwhile to have multiple copies of your production database. One as a 'developer's playground' where they can develop new code and try stuff out, and one clean copy of production for debugging client problems and testing new installs.
Steve G.
-
yep use a snapshot: a developer doing SELECT * FROM THE800MILLIONROWTABLE will kill usability, and he might have done it just to remind himself of column names.
keep developers off production.
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! -
Someone didn't really do that
I liked the calls... I just ran an update on production, but forgot, or had the where clause commented out. Can you get the old data back?
-
I don't recommend a snapshot, actually. If the Developers are going to be constantly developing against a DB, a better practice would be to do a nightly restore of production down to a sandbox / dev server. Call it ProdCopy and let them see on a day to day (or week to week) basis how it changes.
Snapshots are okay, but if something happens to production anyway and you need to restore it, you'll need to drop the snapshot which means the developers can't work while you're fixing the datbase. On the other hand, a Dev database won't mysteriously disappear if a snapshot is deleted. Also, eventually the snapshot is going to be the same size as the production DB anyway once everything in the DB changes, so you might as well bite the bullet up front.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply