May 13, 2009 at 9:45 am
Bad habit: we generally make developers "dbo" of dev, test, and production databases.
Pluses: convenience (as dev's create new stored procs they add them to prod and they don't have to be granted EXEC permissions. )
Minuses: "minimal permissions" (what's that?) are out of the question, data access can not be locked down. Data privacy... oy vey. (Of course, if developers break something in production they have to fix it too! I'll provide any data needed from backups.)
We have hundreds of databases on our sql servers,a large datawarehouse, and one Administrative-type DBA (me). It's not practical for me to test every stored proc developers write (including performance testing) or to be the only party updating our production databases. (I'd become the bottleneck).
Lay it on me. Tell me what I doing wrong or offer a better way to deal with these issues.
Barkingdog
May 13, 2009 at 10:00 am
A common way to handle this is to have a QA\Deployment team made up of senior level people that are in charge of making production deployments. Database changes often have to be deployed in conjunction with changes to applications, so it makes sense to have one team of people that handles both. They are usually in a better position to deal with developers and understand the applications.
This limits the people that can make database changes, tends to make the developers a little more careful, and makes sure at least one senior level person looks at the change. It also cuts out testing in production and makes it easier to deal with data security issues.
May 13, 2009 at 10:09 am
Barkingdog (5/13/2009)
Bad habit: we generally make developers "dbo" of dev, test, and production databases.
Oh yes... this is a very bad one.
There is no reason in the world to grant a developer dbo privileges, not even in development version never ever on production.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 13, 2009 at 10:13 am
There is no reason in the world to grant a developer dbo privileges, not even in development version
Depending on the size of your shop. In "my world" developers can do pretty much whatever they want in the development environment (hence the name, development environment); they are not allowed to do anything in production though.
May 13, 2009 at 10:35 am
David (5/13/2009)Depending on the size of your shop.
Agreed.
David (5/13/2009)they are not allowed to do anything in production though.
Couldn't agree more.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 13, 2009 at 10:44 am
I limit their ability in development. The reason has been to ensure that change is controlled and the don't make sweeping changes that cause a halt to development.
I would give them complete control over a dev environment on their own machine, but a shared one would require them to create the object in their own schema/area, and then have a gate (could be DBA, could be specified developer) that can change the owner to dbo.
May 13, 2009 at 3:05 pm
I wouldn't worry much about Dev yet. Sounds like you've got a huge job ahead just limiting Test and Prod.
First you have to find someone that can and will take over moving stuff to Test and Prod. Then you need to develop a process, and give your new Jr DBAs the access they need. Oh, and management will have to buy in to all of this, and agree with your approach and your assistants.
Finally, you need to implement this process one application at a time. No doubt there will be all sorts of code that assumes full rights on the database. And you will be bombarded with all sorts of tasks that the developers run manually now that you or your designee will have to take over or have scheduled. Cleanup jobs, moving data, etc.
Of course, you will want to remove access from Test first, and make sure that a full test cycle has run on the application before tightening down Prod for a given app. Even then, you find some things that were missed.
Lots and lots of work lies ahead.
Best of luck! We're all behind you. 😀
May 13, 2009 at 4:16 pm
More to the point, you need to get management backing on this. Big time. In fact, you need to get a manager to champion it because this is ultimately a management problem, much more than it is a DBA problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 4:32 pm
BarkingDog, this is a good question and you will probably get a number of pedantic, 'best-practice' and impractical answers to it.
For my money you tie down your production DBs first. Clearly this is slighly abridged advice.
First you put your developers in one or more active directory groups.
You give them access though this group (groups) to one DB and you grant minimal access. That means initially no sysadmin role and whatever they REALLY need in DB roles.
This won't fully work .. you then tweak it until it does.
Then you script it to your others DBs and have them managed in the same context.
Next you give them notice you are withdrawing there dbo permissions in a month. They must access through active directory security. They need to report any problems to you.
Then you withdraw dbo permissions.
Then you fix any problems.
There is no quick fix as far as I know, but I would be pleased to learn one.
Tim
.
May 13, 2009 at 5:25 pm
A number of excellent ideas were suggested. I will take the bits and pieces that apply to our situation. Thanks to everyone for your help.
Barkingdog
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply