Setting up "View Only" Access
-
Last week, one of our developers accidentally deleted some data, thinking that he was connected to his local machine. The supervisor of our developers asked me to lock down accounts, something that I have wanted to do for a long time.
My first pass was to change all of developers to have a db_datareader role for all databases. While that has definitely worked to prevent deletions, it is too restrictive. My developers can't review a sproc, get to views, functions, etc.
So, while I have met the original request, there has got to be a way to set up a middle ground that allows them better access to the databases.
I am leaning toward creating a role for each database that grants additional rights to the developers, but I am wondering if someone else has been through this and how you addressed the problem.
Regards,
Scott
-
Sorry for my english...
A developper must work on developpement database. that's the best security.
A script developped by a developper must be passed on a pre-production database by the dba. if no probleme, the dba run the script on production database.
the application developped by the developper have only the right to do what they must do.
the developper never acceded to the database production like dba. only dba access to production database.
The production database must be back up. the dba can restore if a scrash began.
with all this rules, you have a good security...
French Geek.
http://blog.developpez.com/ylarvor
I work my english 🙂 -
I agree with yann... they shouldn't be in there in the first place. And should you get audited, you'll have to show a good reason why devs are in your prod system deleting records. It's just not good practice.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
I appreciate your responses, but they don't really offer me any help me with my problem. These guys are responsible for supporting the production system, I don't need to worry about audits, and as a group we have decided what the requirements are. Keep in mind that prior to the change stated, all of the developers had full access to all databases, so this is a step in the right direction.
Having said that, I was hoping that someone had been through a similar migration and had some input on how they had solved the problem.
Scott
-
Fair enough... I've been through such a thing many times and yeah, the best way to handle it is to get a list of rights/objects the devs think they'll need, get the levels of dev there'll be (will they all have the same access?), and then start creating groups. This makes it much easier for when you start replacing members of the team, or just adding them cause you're so successful.
If they're not sure what rights they'll need, then there'll be an adjustment period while you discover it together. As they discover the need for objects, you'll just add that access to the groups. Or you can try to be proactive and give them full rights for a week while you profile their usage and then pick through it and narrow down the results that way... but that's a awful lot of work. I've done it once and sitting on the other side of it I'd rather just add rights as the need comes up.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Maybe that's the way to go, put it back on them to tell me what they need ....
I'll ponder that one for awhile.
Scott
-
Y, I mean, afterall you're there to provide a service, and you can't provide it unless they tell you what they need. They're your customers, and name any other business where you go in as a customer and they just have to sit around and guess at what you need until you're happy. It doesn't work like that.
This also protects you a little bit as well. When it takes 3wks to snipe the rights, and they go to your boss and say, hey, the DBA's taking to long to get us worked out, you can say, hey, I asked them what they needed and they gave me an incomplete list. But every time they come to me I add the new one right away. So it's their fault for not knowing the rights they need to begin with. And y, I've been in environments that hostile before. More than several times.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Not an easy question. One thing that occurs to me is a change control procedure...Maybe something requiring a second sign off before modifying records in the production system. It's very easy to be in a pressure situation and modify records unintentionally...simply having a second set of eyes quickly review things will avoid a lot of problems. Most system downtime situations are caused by changes like this than hardware failures.
Of course management has to create some real consequences for a rule like this to make sure it is enforced.
As an admin I don't like change procedures. As a manager, I sleep much better at night. 🙂
-
Scott,
Honestly I think that this can be handled with allowing them read only access.
They shouldn't "review" a sproc from production, they should get it from source control and review it on a test system. We've implemented "suppport" copies of production specifically for people to examine things, look for issues, etc. they can always get the sproc from a dev/test/support system, pull out the queries and run them against production, as db_datareaders, to try and find problems.
Supporting a production system doesn't necessarily mean being able to make changes on the fly. Having a delay, especially requiring a hand-off to someone else, provides for more stability by giving a 2nd person the chance to look over the code.
-
You cite the developers needing access to production to troubleshoot issues as the reason they need access... key question, do they need access the rest of the time? If not, why not create a user/group with the appropriate permissions that can be turned on/off at will by the DBA - trouble? Turn it on... fixed? Turn it off.
Joe
-
How large is this group? What I've seen is the larger the group, the bigger need for change control, read only permissions, etc. You may be in between or growing to the point that the change control starts making sense. This is as much a work culture change as it is a system permissions change.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply