January 17, 2005 at 11:10 am
My apologies if this has been discussed before--it can't be new--but I can't find reference to the subject anywhere. Links on this subject would be appreciated!
We're trying to set up an environment where develoeprs can develop stored procedures (and maybe functions) but not have any "system" rights--no dbo, db_owner, db_ddlAdmin, or db_securityAdmin. They need to be able to create stored procedures owned by dbo, and they need to be albe to grant EXECUTE privilieges to the database roles used by applications.
How has anyone else done this? Has anyone else done this?
In a previous position, I gave the developers dbo rights, and exacted from them oaths that they would never do anything but create stored procedures. This was a very small shop, and so this strategy was easy to maintain.
Here, there are far, far too many and too disparate groups and developers (and turnover--gotta factor in time!) and "the personal touch" just won't work. Some form of supportable and enforcable policy must be devised, implemented, and distributed.
I've messed around with GRANT CREATE PROCEDURE to <user> and the like, only to determine that either the developers have dbo rights, db_ddlAdmin and db_securityAdmin rights, DENY CREATE TABLE, DENY CREATE VIEW... it turns into a massive headache, and they can still mess around with indexes (oops) and triggers (OW!)
There's code/SQL Agent jobs that could be written to check for all procs not owned by dbo and make them dbo (in all databases...), but what if it isn't yet ready for "promotion to dbo"?
...and round and round it goes. What has anyone else done to address this situation? [And have they addressed the issue in SQL 2005?]
Philip
January 17, 2005 at 12:14 pm
I have no experience in this kind of situation but how about this??
Can you set up a developpement environement, give 'em dbo rights there. Then create a way to push the new procs, views... to production that only YOU could plan and execute. That way you'd have absolute control of the server.
January 17, 2005 at 12:34 pm
That's the trick--identifying what I need to push beyond Development. If the developers can do any old thing on their box (and, having been a developer, I know they will), then how do I identify what needs to go out? Restricting all (non data) database modifications to DBAs only controls both this and architectural integrity. [Yep, the old "DBAs know more about database design and architecture than developers" thread.]
Simple for tables and the like, as there are few and (properly designed) don't change at all often. Stored procedures (and perhaps functions) can end up in the hundreds or thousands, swamping the DBA team, and in any case their purposes is best understood by the developers. Leastways, that's how it's set up here.
Lastly, lots of this is has to do with security/responsability/culpability issues (with Sarbanes-Oxley overtones). Mgmt wants only a small handful of folks to be responsible for the boxes and databases thereon, and that means restricted access rights all around.
So, paraphrasin: How do you let them do anything they want with stored procedures, and nothing else?
Philip
January 17, 2005 at 1:06 pm
I hear one thing here.
It seens that the developers are working on their own little system with no code centralisation. That could be a problem, or the solution. If the code was centralised into a single box, or program like sourcesafe. Maybe you could simply ask the team what they need to have implemented and when (there's a good article on the production cycle on this forum here, maybe that could provide a solution or a good start).
That way you could review the changes made to the code since you'd have old copies from it, you'd also have a way to trace what's been changed and when and lastly by whom. This could also give you a way to have a total "developer lock out" on the production server giving then even less chance to break stuff.
All that being said I'm a developper myself and I have never had to deal with anything as complexe as what you are trying to do. So I just hope some of the gurus here can help you with that.
Good luck.
January 18, 2005 at 12:25 am
This is the way I would do it:
I would use my source control and allow developers access to the functions and stored procedure projects only. Then I would build the contents of the checked in source code daily and check it against a copy of my production database that I would be releasing to in the future. Once I had identified all the changes and had done my quality assurance on them I would relabel the source code and build a copy of the database from that label. I would then test the ability to deploy.
Impossible?
This is database change management using tools that were built for the task, your source control and DB Ghost http://www.dbghost.com .
January 18, 2005 at 8:13 am
Both Remi and Mark suggest a course of action similar to the one I implemented at my last position: tight control over code produced by the development team. [Note that the trick is not creating a new database every time, but rather updating an existing one. Saves wear and tear on reloading your data with every mod.]
The problem here (doing a few months contract work) is size and scale. This place is big. I'm working with the SQL DBA group, which is responsible for managing over 200 servers supporting over 40 applications of varrying complexity. There are I don't know how many distinct development teams on this campus, and they all go through us for (SQL Server) database modifications. To keep our heads above water, we need to offload as much as possible, and making the development teams ultimately and solely responsible for their stored procedures [a.k.a. source code] is one of our goals. To date the DBAs have reluctantly doled out SysAdmin and db_owner rights, but under the shadow of SOX [as interpreted by the local auditors] that's no longer an option.
Thus, how to let developers do anything they want to stored procedures (and functions) and nothing else. Personally, I don't think it can be done natively in SQL Server 7.0 or 2000, not without a few special DBA management jobs and procedures that work against the system table. I'm hoping someone can prove me wrong, or point me to an established solution.
Philip
January 18, 2005 at 9:51 am
Philip,
I suggest you stick with the GRANT CREATE PROCEDURE so that users can create sp's that are owned by themselves. The hard part comes in when they need to integrate into the rest of an application.
My suggestion (and something we've been working on for about a year and a half, but haven't finished because of priorities) is to set up a table that developers can store database name, user and stored procedure name. You can then run a batch process (SQL Agent) that can parse through the table and change the object owners to dbo (sp_changeobjectowner). The part we haven't worked out was how to carry permissions across (sp_helpprotect).
If you get it finished before we do, please share - I'd love to put that to bed.
Larry
January 18, 2005 at 2:39 pm
I spent the last 4 years thinking about database change management and DB Ghost is what I've come up with. It works for both large and small environments and because you can automate the details of it - it saves you loads!
Having your source code in your source control and grant permissions on these projects IE: the stored procedures project under the database; and read only access to all the other projects means you have a centralized point of access and auditability - it doesn't matter what is actually on the developers desk top, it's up to him to get it into source control. Now that you have the source code in source control you can build using that source, DB Ghost builds approx 1000 scripts per minute so this is an extremely fast process, this built database can now be used to compare and create a delta script for deployment to your end database. Generally our customers do scheduled builds and test deploys of their database code daily so they know when a developer has broken the build much like any other procedural code builds.
Check out this document on the very subject of automated database change management: http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf
regards,
Mark Baekdal
+44 (0)208 241 1762
Database change management for SQL Server
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply