| Author |
Topic |
|
cdpowers
Starting Member
5 Posts |
 Posted - 2003-04-23 : 23:10:29
|
| Greetings all,I'm trying to draw a line between the developers and the dba responsibilities, enforceable by SQL Server security roles and authorities in a development environment (i.e. production and test environment requirements are are to be considered seperately).To start, the dba would be sa or member of the db_owner role, obviously. My goal for the developer role (which I have name APPBUILDER) is to provide db_datareader, db_datawriter, execute on all dbo stored procedures, create user owned procedures and tables. What this restricts the developer from doing is to be able to create dbo objects (tables and stored procedures).However, the rub comes with the stored procedures. I do not want to restrict developers from creating dbo stored procedures, but I do want to restrict developers from creating any other dbo object.I can ratchet down the developers authority by removing them from the db_owner role and associating them with db_ddladmin role. However, this still leaves the gap of allowing them to create any and all dbo objects. But it does remove some of the other db_owner privileges (security, backup, restore, etc.)I have not been able to package any workable solution. I am beginning to think it is not possible.I hope I have stated my case clearly. I would appreciate any response including questions to my current dilemma.Thx-c |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-24 : 00:00:54
|
| Why do you want to stop developers creating dbo owned tables?Will only cause a problem if 2 decide on the same name. Maybe tell them to prefix with their initials.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-04-24 : 01:15:20
|
| If it is a dev environment then let the developers do what they like. Then be in charge of what goes over to your production servers.That will cause you a lot less grief than chasing down lots of object ownership hassles.Damian |
|
|
|
cdpowers
Starting Member
5 Posts |
Posted - 2003-04-24 : 14:32:32
|
| from the originator:I expected these types of questions so....My team consists of the DBA group and the Data Architect group. We are responsible for all data modeling, data standards and db administration activities. Therefore, I need to rely on the developers to consult/collaborate so that we can "solve" the database design issue together. The data architects must keep the models up to date and the dba then construct the db objects based on the model.Now, all of this happens for multiple and distinct development projects. The benefit is that the data architects (and dbas) can develop an "enterprise data view". This can then be applied to the benefit of all projects/products. If I cannot depend on the developers to collaborate at an early stage, then what happens is that the developers design and code for a solution that is then turned over for production without any dba input. My team becomes "order takers" (chee-burger, chee-burger).Naming standards are then up to the whim of each development group. RI and other db basics are left to the whim of each development team. I guess the best way to put it is that we represent a centralization of many data-centric activities.Is that clear? With that, I will ask again whether anyone has any ideas about how to allow the developers to write dbo.* stored procedures but block them from creating dbo.* tables, indexes, etc.Thanks-c |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-24 : 15:09:07
|
quote:
With that, I will ask again whether anyone has any ideas about how to allow the developers to write dbo.* stored procedures but block them from creating dbo.* tables, indexes, etc.
In order for them to create and modify dbo stored procedures, they need to be a member of the db_owner role or the db_ddladmin role. These roles all allow the creation and modification of all other dbo owned objects. To disallow them some of these, then use DENY (see syntax in BOL, also see REVOKE). I would add the developers to the db_ddladmin role and then deny permissions because db_owner permission allows them to undo the DENY/REVOKE commands that you just ran. I would like to add that I do not recommend this approach though based upon the headache that you are going to get trying to keep up with the changes the developers need done.Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-24 : 16:28:36
|
quote:
Therefore, I need to rely on the developers to consult/collaborate so that we can "solve" the database design issue together.
I do like the approach of DBA's and developers working together. I feel a DBA needs to be more involved in development and to really understand what the developers are doing.- Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-24 : 16:44:00
|
| Yes I like that idea too. At my company, the developers keep the DBAs informed of all schema changes by sending e-mails and/or setting up meetings to discuss the changes. Developers make the changes (usually before it is discussed) but the DBAs ultimately get to decide whether or not the change stays or gets deleted. The DBAs are the ones who deploy the changes to the other environments, so we pretty much have control of things here.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-24 : 17:41:03
|
| Easy.If the developers do things without consultation or agreement it doesn't get released.After there manager has explained that to the board a couple of times they'll be a bit more communicative.If you don't have that sort of power then you can't put in place what you're suggesting anyway - they could easily develop a system using user owned objects only.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-24 : 17:43:43
|
quote:
If you don't have that sort of power then you can't put in place what you're suggesting anyway - they could easily develop a system using user owned objects only.
Very good point! But typically developers don't do that though because they don't want to have to type ownername.objectname instead of just objectname.Tara |
|
|
|
cdpowers
Starting Member
5 Posts |
Posted - 2003-05-06 : 12:21:20
|
| Thanks for all the subsequent posts.This confirms my notions of the level of authority that dba group should have in the development environment.It may be an issue of the number of developers at any one shop. The more developers, the more need to control what happens to the database. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-06 : 20:10:02
|
quote:
I'm trying to draw a line between the developers and the dba responsibilities
Interestingly enough microsoft is working on the opposite.quote:
they don't want to have to type ownername.objectname instead of just objectname
It's good practice anyways so tell them to suck it up.Edited by - ValterBorges on 05/06/2003 20:12:41 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-06 : 20:22:15
|
quote:
quote:
I'm trying to draw a line between the developers and the dba responsibilities
Interestingly enough microsoft is working on the opposite.quote:
they don't want to have to type ownername.objectname instead of just objectname
It's good practice anyways so tell them to suck it up.Edited by - ValterBorges on 05/06/2003 20:12:41
Believe me I do tell them to suck it up. But telling them one thing and getting them to do things are two different things. DBAs have a lot of control here, but when it comes down to a deadline, we often lose (not because we didn't try though). We win in the end usually though when things that we lost on bite them in the ass later!Tara |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-06 : 20:30:22
|
Ah yes, the famous "I told you so" technique.   |
|
|
|
Lexta123
Starting Member
5 Posts |
Posted - 2007-08-10 : 00:17:17
|
| I have just found this conversation. I am in exactly the same position at the original post. Was there a solution found? What I can do under 2005 is not permit access to dbo schema but instead grant create procedures, deny create table, deny drop table which is all fine but we also need for the developers to change the schema owner of the stored proc to dbo. Our policy is not too permit developers to create their own tables. The DBA's do that after the Sys Architects approve the model. I have everything except permitting the developers to change ownership of the schema for their stored procedures to dbo. |
|
|
|
craig79
Starting Member
33 Posts |
Posted - 2007-08-10 : 07:23:27
|
| Hi,How about giving that user CREATE PROCEDURE (statment permission) in that particular DB??? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-10 : 10:25:42
|
| I have a different approach.We have published standards for tables, stored procedures, etc. that developers are required to follow.Developers are not allowed to created dbo objects in any database, development or production.Developers create tables and procedures owned by themselves, and ask the DBAs to make them dbo.DBAs review them for adherence to standards, obvious stupidity, bad design, etc. and either promote to dbo or reject them.If they yell, cry, scream, claim we are putting their project behind, we tell them too bad.Obviously, we have to be very responsive to the developers needs to keep the process working smoothly, but it seems to work for us.CODO ERGO SUM |
|
|
|
Lexta123
Starting Member
5 Posts |
Posted - 2007-08-12 : 16:59:16
|
| Thanks guys, I have tried the first one but still because they haven't access to dbo schema they can't rename the stored proc to dbo. instead of their own schema. The second scenario. I did discuss this with my team leader however he said he wants the developers to be able to create Stored Procedures (or at least rename them) only in/to the dbo schema. We look after alot of projects that develop in Sql Server all of which have hundreds of Stored Procs and only 2 Administrators. So I am forced to find another scenario or else they need full DBO (which they currently have) however I am not happy with this full DBO access in dev. I know it is only dev but this shouldn't be in the current way.... You can see the obvious loop hole that opens up here. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-08-13 : 10:01:25
|
| Application developers should not be creating tables, sprocs, or any SQL code. Allowing individual developers to do this will result in a database schema that is a hodgepodge of standards, rife with redundancy, and a collection of individual sub-schemas rather than a complete and coherent database design.You need to have database designers/architects creating the schemas in response to requirements, and writing sprocs in response to developer requests.e4 d5 xd5 Nf6 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-13 : 23:03:19
|
| Developers should be able to do those on dev server but not on prod server. |
|
|
|
Lexta123
Starting Member
5 Posts |
Posted - 2007-08-13 : 23:33:24
|
| I guess it depends solely on the place of work and governents that have to be followed. I have got it almost there now just need to find the scripting way to write grant execute to dbo schema. any suggestions... I sick of reading and hunting for info... I'm ready to accept the easy route and have someone tell me... ;-) |
|
|
|
Lexta123
Starting Member
5 Posts |
Posted - 2007-08-14 : 00:56:21
|
| Got it now... phew... thanks for all your comments |
|
|
|
Next Page
|
Developers vs. DBAs