August 6, 2009 at 11:38 am
Hello,
We have different groups of people put in AD groups. These groups have been granted sql server access through windows authentication. These groups are part of specific database roles to access objects within specific databases through application.
However, many of them have sql server EM on their desktops/laptops and they connect directly to database and make all the changes in the database through queries as they still have access through te roles. This causes huge audit headache.
What is the quick and safe way to prevent this type of direct access through enterprise manager and query analyser for specific people?
Thanks,
Nikki
August 6, 2009 at 12:16 pm
Put them in a role that doesn't have access to anything they shouldn't have access to.
Edit: That's the most effective way to do it that I know.
- 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 6, 2009 at 2:25 pm
I guess I did not phrase the question clearly.
Users do need access within database to perform their duties but they should be connecting to database only through the application they are given and not SQL management studio and query analyser.
I want to prevent them from accessing database information through sql management studio and query analyser.
Nikki
August 6, 2009 at 3:03 pm
Nikki (8/6/2009)
I want to prevent them from accessing database information through sql management studio and query analyser.
Wrong. Problem is not the tool, problem is sloppy security.
No reason for an "user" to have dbo privileges, that's what has to be fixed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 6, 2009 at 3:15 pm
Nikki (8/6/2009)
I guess I did not phrase the question clearly.Users do need access within database to perform their duties but they should be connecting to database only through the application they are given and not SQL management studio and query analyser.
I want to prevent them from accessing database information through sql management studio and query analyser.
Nikki
The usual way to secure a database like this is to create stored procedures that do all the data access and manipulation, then create a login that has execution rights to the procs, and make the application use that login.
That way, if they use QA to access the database, they still can't do anything except what they could do through the application anyway.
- 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply