So what is developer security best practice?
-
Hi all,
Once when our development team had <5 people, we made a decision that all developers should be sysadmins on the dev and prod sql servers. It was pure convenience, we all played developer and DBA.
Now we have merged, grown and merged again, having all 30 developers over 3 sites as sys admin is scary. So we've decided we need a dedicated DBA.
I see a lot on forums here that developers should not have change access to prod, which makes sense and I can manage that.
But what rights should developers have to the dev environment. Should an assigned DBA be responsible for creating databases even in the dev environment.
I'll happily listen to your stories...
Cheers,
Steve
-
I don't think there is any real "best practice" as such.
A setup that has worked is giving Developers/Project Teams their own SQL Servers and sa rights to those servers. If resources allow, you can designate a "Dev Support DBA".
For migration from the Development Environment you have a seperate "migration" server which the Developers don't have access to. This is where Production DBA's ensure that site standards have been followed, stored procedure use, security, etc... Anything that fails at this point is kicked back to Development. Having the "migration" server in the middle alleviates a number of concerns that things won't be developed to standard.
Giving Developers sysadmin access allows much better use of development time. Here's an example of how things can be held up. We were developing a whole raft of DTS packages and logging the execution of these packages to msdb via the standard DTS package logging. Unfortunately we didn't have access to the necessary tables in msdb to see the resulting logs and extract meaningful error messages to present to the users. Then we couldn't schedule the DTS package because only sysadmins were allowed to execute Command Exec jobs.
These were just a few of the many little issues we had when sysadmin access wasn't provided. If hardware is limited you can do the same setup by running multiple instances.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
We have a dev server where they have sysadmin access, some leverage it more than others, but it does keep me from being the bottleneck. Once code is ready, they send scripts to me to apply (or I generate using SQL Compare), I check to see if they look reasonably sound, then apply to production. SQL Compare has been a life saver for me working this way, before they would send me alters that while the right idea, just didn't work. For simple changes they send scripts, for more complex or just a lot of changes, we do the diff and they pick what gets applied.
Andy
-
I've worked in small shops where I didn't allow sysadmin access. Helped me to control things and ensure they met standards or we a good idea from the get go. For testing that needed higher level access, I say use your personal edition on your workstation, script it out when it works and I'll drop it on the dev server.
Course, I had to be sure not to be the bottleneck.
Steve Jones
-
Much like Andy, they have sadmin rights on development and I use SQL Compare to make find out what they have been upto. One rule we have in place tho is that any new tables must be agreed to by the DBA.
Clive Strong
-
The only issue is if (or when) the dev server is out of sync with production. As the DBA I ask that all changes to table structure, including new databases, come through me. I don't mind them playing in their sandboxes. Every developer has access to their SQL Server. I've locked away SA (we had several developers who wrote all their code with SA rights - gee, I wonder why it doesn't work in production...) and I set up a very loose naming convention (just don't use sp_ s'il vous plait).
When it comes time to move to production I check the scripts and the stored procedures, and any DTS or SQL specific jobs, and go from there.
Patrick
Quand on parle du loup, on en voit la queue
-
Production box. Only operation staff.
Development box, all developers are sa. Nightly, automated Sourcesafe update of complete database.
Formal process of upgrades every 3 months to 3 months.
ERP / MRP development.
-
I never give production server's sa or assign sysadmin role to other developer in normal situation. They all access separate development server as their login with different role based on the jobs they perform. Keep development server sync with production based on need. This keeps production server away from problem such as data was gone which developer told me that 'I did a test on my stuff'.
When I work on development, I also do it in development server.
If new script needs to put in production server, always test it in development server before to do it.
Jie Ma
Jie Ma
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply