| Author |
Topic |
|
RapmasterB
Starting Member
3 Posts |
 Posted - 2009-10-29 : 19:09:50
|
| I apologize for the long post but please - somebody help me! I am a manager of a small web development group within a company of several thousand people. The "main" website for our company is controlled by another dev group within the IS group but we are separate - kind of a "stepchild" group that was essentially formed to create small websites for event registrations and things like that because when we asked the 'real' web group to do it they would tell us that they would do it but it would take 4 months and we needed it in one. So anyway, this relationship has worked for many years and we all share the same production SQL server although the IS group controls it. We have had priveleges within our own databases to do whatever we want, ie., change data, add/delete tables etc. So last week we migrated to SQL 2008 and come to find out that the IS group has decided that we will no longer have any admin access to the production sql server. The admin is telling me that we have to do all development work on another server and then use scripts to create/edit any table on the production server. We can create views on the production server but cannot save them. We also have to run every change to any table through their daily change control group. He tells me that every company runs this way and that I am an idiot for not knowing this. He refuses to give us access to our own databases. My developers of course are upset and are telling me that this is bull and that the sql admin is a lying sack of something. They say that for the small/quick sites that we develop this type of thing is ridiculous and they are threatening to quit. Can somebody please tell me if I am an idiot or my sql admin is a nazi? Has anybody else gone through this process?Bruce |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2009-10-29 : 20:33:03
|
| Unfortunately - I think both of you are right. As a DBA myself, I would definitely prefer that developers do not have access to the production systems. And, if they do have access - it should be read_only access. The model that the SQL Admin you are working with is generally the accepted method of change control.Normally, the change control process would be to:1) Develop code in a development environment. Developers have open access (not necessarily full sysadmin, but possibly could have that level.2) Move code to QA/UAT/Test environment. This move would be managed by the DBA's and the changes applied to the test environment. The changes can then be tested and validated by all interested parties and signed off.3) Move validated/tested code to productionAgain, this is a general scenario and is usually tailored to the environment you find yourself in. With that said, I find that this is the ideal solution and one that everyone *wants* to move to, but there are always issues that prevent this from happening.You need to realize that this is really in your best interests. Making changes in the production environment has the potential for taking down sites that have become important to your users. If the site is unavailable, they are not able to do their work. So, working through a change control process to make sure tested and validated changes are moved to production will reduce the outages due to bad code.Finally, I think the IS group forcing this issue without working with you is not the correct way to approach this problem. What should have happened is that you were given notice of this change and the time to train on the new process and validate the process works. You should have been given the time to identify issues with the process that won't work for your team, and input into changing the process. Doesn't sound like that happened...I would recommend that you review your own change management policies and see how you can change those processes to fall more in line with what the DBA's are asking. If that is not possible, document what doesn't work and meet with them to discuss ways of handling those specific issues.You might even try working with them to give you the level of access you need now, while working with them to develop the change management process that will work best for both of you.And yes, I have gone through this before and still have not been able to 'revoke' full access to developers from the live system. However, the developers now understand the importance of the process and follow it - mostly because it saves them in the long run.Good Luck. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-29 : 21:32:38
|
Ditto. Seriously, I've been on both sides: headstrong developer who though "change control" was just for paper-pushing bureaucrats trying to protect their jobs; I mean, there's NOTHING WRONG with MY code.And I've been called at 3:00 AM as the DBA who needs to fix something that they "tested thoroughly" but managed to drop a database or truncate a table or melt a hard drive.Before you and your devs start sharpening pitchforks, do yourselves a favor and read this:http://www.fastcompany.com/magazine/06/writestuff.htmlAfterwards, consider 2 things: 1) is your change control REALLY that arduous, compared to NASA's? 2) If you were an astronaut, how safe would you feel if one of the devs "tweaked some code" just before liftoff? |
|
|
|
RapmasterB
Starting Member
3 Posts |
Posted - 2009-10-29 : 23:03:51
|
| Thanks for the quick replies! Please understand that I am not against the concept of following "the process" and recognize the postives that such a system can bring - although in our case the change control process consists of a bunch of documentation and then presenting your changes to a room full of people who couldn't care less what you are doing and just want you to shut up so they can get on with their day so the entire exercise seems a bit absurd to me. There is no "analysis" of any changes - its just communication to the community that somebody has changed something. Is this the norm? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2009-10-30 : 00:14:22
|
| Sometimes having communication that something has changed can make all the difference in the world. When users start to report that there are issues with the web site, if there is no notification of changes - you have people pointing fingers at every group.If everybody is aware that the DBA's have made changes, and a general idea of what those changes are - they can usually identify whether or not the problems could be related to those changes. Even if they are not related, they have a starting point to troubleshoot.Same goes for development - and honestly, 9 times out of 10 when we hit an issue in live it's one of those moments where we look at each other and go: oops, we forgot to ... that's easy to fix. The reason is because we've done this process before and had those issues before.At least that's all you have to do. I've been in positions where changes didn't move forward until they had been code reviewed (peer review), then panel review (team of developers), then it goes through unit testing, requirements testing, integration testing and finally user authorization testing. If it fails at any point, we don't get the opportunity to fix it and move forward - we stop the whole thing and start all over.I've also seen what happens when you have multiple teams making changes to the same code base at the same time, and they are not talking to each other. It's very interesting to watch what happens when it's that last team that implements their changes in live that wins. Ever watch a group squirm in front of the CIO and multiple VP's as they were yelling about how everybody should be talking to each about these changes? Ever been the one being yelled at? If you haven't - great, but I'll tell you right now it is not a good experience. |
|
|
|
RapmasterB
Starting Member
3 Posts |
Posted - 2009-11-05 : 14:03:29
|
| OK - the saga continues. Thanks for your replies so far. I am still trying to figure out who is telling me the truth with all my questions so I will pose one of them to you:Q: The SQL admin tells me that even if we only have access to our own databases, we are still a threat to other databases - can someone give me examples of why we are a threat? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-05 : 14:20:40
|
| SureYou can eat up all the resources on the box, blow out temp db, incur massive amounts of transaction logging (filling or blowing out the logs), perhaps permanently use up and not free memory until the next reboot, the list goes onWhat type of access were you looking for?And what's wrong with (besides secure/semsitive data...which is another SOX issue) getting a copy of the actual prod db and working off of thatEven if you are worried about data security (and you ARE, right) you can always sanitize/scramble the dataBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2009-11-06 : 00:14:18
|
| Other than causing performance issues - which is not really an access level issue, I really can't see how a user of a database can be a threat to another database on the same server.If all you have is db_owner access rights to your databases - you cannot access other databases and harm them. Yes, you can cause performance issues, but you don't need to be db_owner to do that.As Brent outlined above, you really should be concerned about data security. You might not be required to restrict access to the level of SOX or HIPAA, but it really is something that you should be following anyways. Basically, it comes down to only granting the level of accessed required to do the job - at the time it is required.Also, you should be doing all development on a development system anyways. So, changes to the system could all be scripted and validated prior to applying to live. Additionally, those change scripts could be generated from source control and managed that way. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2009-11-06 : 02:05:54
|
| Yeah - I am agreeing with you :) |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-11-09 : 07:37:45
|
| Always worked that way myself where no developer can change anything.Developers at my company work on a seperate developer instance, which is just a restore backup of the live database. They have full control of this database to alter amend etc.Then from there they save all scripts which are then when released onto the live system. |
|
|
|
|
Tell me if my sql admin is whacked