August 19, 2009 at 11:37 am
In my organization, a larger number of people, across a number of departments, are granted various rights to access our sql servers across our development, testing and production environments. In general, most people have only read-only access to databases on the various servers. With the growth of our organization, and the growth in the number of databases and servers, I get many requests for access to databases via SSMS. I am trying to come up with some method for requiring a minimum competency level before granting access and/or an acceptable use policy to govern usage and set expectations and criteria for possible access denials. Do any other admins have such policies in place? I'd like to hear how other organizations handle db access requests and police users once access has been granted.
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
August 20, 2009 at 7:28 am
I tend to be on the "limit access" side of this equation. I believe that users should rarely be granted direct access to tables, but access should be controlled through stored procedures and views. You have to remember that once you grant access they can use Excel, Access, etc... to do whatever they want with the data. If they have base table access and you have a 0 to many relationship and they use an INNER JOIN instead of OUTER they get inaccurate data. That's why I limit access.
For auditing purposes you should have a policy in place so there is a "paper" trail that documents why they need access and that it has been approved by supervisors.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2009 at 7:56 am
Jack,
I absolutely believe in the "limit access" mindset. Unfortunately, the business users in my company do not, which is why I have the dilemma. The business side of the house calls the shots and if a manager says a user needs access, they get access. What I am looking for is a good way to ensure that the user has the proper competency and to establish rules and policies to be followed once access has been granted.
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
August 20, 2009 at 8:10 am
The problem is that once you grant access how do your enforce or even monitor those policies? In reality you can't. You can run server-side traces, but when are you going to look at them. Unless you require everyone who gets database access to attend a T-SQL class and what manager will pay for that or give the time for you to do it in-house?
Sorry to say that, if you can't make a case that users do not really understand T-SQL and you need to help them with views and stored procedures, report models using SSRS and report builder, then you are fighting a losing battle. You'll just need to make sure you have good auditing of permissions granted and to whom so you can remove them as needed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2009 at 8:36 am
Set up a VM with a copy of the databases that they need access to. Set the databases there in read-only mode. Give them full access.
Refresh the databases nightly through backup-restore jobs.
If they need fresher data than that, set the databases up for log shipping to that server.
That should give them what they need, without putting your production OLTP databases at risk.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2009 at 12:26 pm
Good luck. Your problem is more political than technical. Plus you are the victim of SQL Server being easy enough to use that just about anyone feels they are a "programmer".
This is a never ending battle unless you have the authority to say "no more".
I have been trying to port some of our users queries to the mainframe so they would have enough resources. 😀
August 22, 2009 at 10:02 am
You are in a seriously difficult situation. As the DBA, you're held accountable for the condition of the company's databases and yet any employee can do anything to the data. In short, you have responsibility but no authority to enforce. That is a very bad situation for you and the company.
I liked the suggestion about making the databases read only on a VM. That at least protects the data from modifications, and maybe your job. But it doesn't protect the company from the data being mal-appropriated by a dishonest employee.
I hate to suggest this in these difficult economic times but maybe it would be best to quietly look for another DBA position with another company where sanity is the norm.
LC
August 23, 2009 at 12:00 am
Unfortunately, as Jack said, it's best to take the approach of limiting access.
Having said that, the approach that I would take is to draft up a formal document noting the reasons for why you want to move toward that approach, and emphasize ways that users having access to production can harm the organization (blocking, deadlocks, etc) so that you protect yourself.
I would then send that to your manager and the other development teams' managers in the organization to show that you have warned them.
I am in the same situation to a point where I manage environments for critical apps, low-level apps, and everything in-between. We tend to not allow access to the critical apps, but grant read-only when necessary and forced to by other managers; but we let them know that they cannot get into the habit of paging us if someone has a rogue query.
The question of competency is valid, but it may be good for you to set up a sort of "intro to t-sql" for those that query SQL directly. Getting together for a half hour or show to inform them of some of the do's and don'ts to querying can do two things: 1) help them gain a better understanding of SQL, and 2) allow you to inform them of ways to not hang SQL Server and make them more competent. You'd be surprised how inviting people are to learn things, and if their manager insists that they query using SSMS, you might as well show them how to use it in a better way.
Hope that helps,
Steve
August 23, 2009 at 9:21 am
What access do they need? Just viewing table data in grid form?
I might start there and look to grant them access and let them use Excel or Access to query for data. My concerns around SSMS would be that they'd "click around", try things, and create more headaches around support than anything else.
There are some nice SSMS replacements that are web based where you could remove functionality as well if you wanted to do that.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply