September 1, 2011 at 8:47 pm
Our dev team uses a windows account this has a Sql login with the sysadmin role for connecting to a production Sql Server. They've got the keys to the castle. In 2½ years, they've only burned me twice. No harm was done either time, but I did have to "undo" some of their actions--the potential for real harm is always there.
Without getting too specific, what I'd like is for them to have sysadmin role rights, minus some of the things that make me lose sleep at night. But I know you can't deny privileges to a login with the sysadmin role or a db user with the db_owner role.
How do you handle security for the dev types?
September 5, 2011 at 9:16 am
Every company has its own way of handling security for developers. Some are very strict and other very lax. In our case it is very strict. Developers do not have any access to production DB. Not just that, all the objects they want to create in the production DB has to pass review by the DBAs. All update to to data in production using Ad Hoc query has to be approved by change management group.
In your case it is going to be tough. You will always have a problem as long as the developers have access to production DB. If it is an OLTP server, even a select can cause issues. Only thing you can do is keep talking to the developers not to do certain things. You could talk to the manager and convert them to your side to get the access to production for the developers revoked.
-Roy
September 5, 2011 at 10:49 am
Have you considered purchasing a copy of the DEVELOPER edition of SQL Server. Installing it and restoring to it a copy of the production DB. That way the developers can have a "Sandbox" to test their work and would NOT need to have any access to the production data base. It gives the developer all the capabilities they think they need, and yet protects the production db from their "mistakes"
Edited to add:
By the way go to Amazon.com and see the ridiculously low cost for the various DEVELOPER editions
September 5, 2011 at 10:51 am
Adding to what Roy already pointed out:
What exactly is the reason for allowing a dev team to access the prod system? What permissions would you like to remove? And why do the remaining permissions need to be based on a sysadmin role?
September 6, 2011 at 8:06 pm
Thanks for your replies and input, everyone. I realized after I made the post that it could quickly spiral out of control into a dba's vs developers free-for-all. That was not my intent. Sandbox environments, reasons a dev team member would have sysadmin rights to a production server, change management group approval, et al are all valid points/issues. I don't mean to diminish them in any way. However, they are outside of the scope of my post (or at least the reason for my post). I'd prefer to leave them out of the discussion.
Suffice it to say that I've been dealt a hand of cards, and I have to play them, even if they suck. I think we can all relate to that situation, right?
September 6, 2011 at 8:08 pm
Ok, so now that I've gotten that out of the way, here's some of the details of the path I've started down:
I created a db user [BuiltinAdminUser] in multiple databases. The user has db_datareader, db_datawriter, and db_ddladmin roles assigned. The user is linked to the [Builtin\Administrators] sql login. That login has the [public] server role only. Members of [Builtin\Administrator] can read/write to any table and run any DDL command in the db.
I don't think they can execute sp's in a schema they don't own, however. The dev folks might need that ability. I'm also thinking they can't create/alter udf's from CLR assemblies. If they have these last two rights, that may cover the bases for them.
As for me, if I can make this work, I'll sleep better knowing the dev members won't be able to do any of the following:
•Modify role membership/manage permissions
•Create/Alter/Drop/Restore/Backup databases
•Manage disk files
•Teminate processes
•Change any of the server-wide configuration options
•Stop/Restart/Shut down the server
•Run dbcc commands
•Add/Remove linked servers
Thoughts?
September 6, 2011 at 9:37 pm
Developers should never be a member of the sysadmin role Fixed Server Role in Production.:w00t:
It is not DBA' vs Developers it is just good practice.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 7, 2011 at 7:47 am
Dave Mason (9/6/2011)
Ok, so now that I've gotten that out of the way, here's some of the details of the path I've started down:I created a db user [BuiltinAdminUser] in multiple databases. The user has db_datareader, db_datawriter, and db_ddladmin roles assigned. The user is linked to the [Builtin\Administrators] sql login. That login has the [public] server role only. Members of [Builtin\Administrator] can read/write to any table and run any DDL command in the db.
I don't think they can execute sp's in a schema they don't own, however. The dev folks might need that ability. I'm also thinking they can't create/alter udf's from CLR assemblies. If they have these last two rights, that may cover the bases for them.
As for me, if I can make this work, I'll sleep better knowing the dev members won't be able to do any of the following:
•Add/Remove access to a db
•Modify role membership/manage permissions
•Create/Alter/Drop/Restore/Backup databases
•Manage disk files
•Teminate processes
•Change any of the server-wide configuration options
•Stop/Restart/Shut down the server
•Run dbcc commands
•Add/Remove linked servers
Thoughts?
If you may ever face real auditing, create individual user accounts, preferably tied to AD. Ideally, assign rights to developer roles, and add the users to those roles.
If you don't do this, figure out how you're going to change the password every time a developer leaves the group/company.
Grant them EXECUTE rights to the [dbo] schema, and to whatever other schemas they may need; this works even on brand new stored procedures.
Depending on their skill level and needs, they may have a requirement to be able to kill processes; particularly their own processes. Stored procedure with wrapper.
If they do anything with xp_cmdshell, the best I've found so far is a certificate with a certificate user with rights to xp_cmdshell in Master, and sign each alteration of the SP's which use xp_cmdshell and which others can access with the certificate. Unfortunately, it's a fair bit of overhead if they change often.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply