April 4, 2012 at 3:43 am
There is this server where a user is having only db_reader access and execute permissions only for specific procedures in a database. However, he has been able to delete data from 4 tables.
On checking this persons windows level permissions, we could see he is member of number of other windows based groups.
Could it be possible that this user has had permissions inherited from one of those groups due to which he was able to delete data. If so, how do we find out or confirm? Please share your inputs.
M&M
April 4, 2012 at 3:57 am
yes it is possible as permissions are cumulative.
as you already know the groups to which the user has access too, check that these groups cannot delete data. Also they have permissions to execute procedures, if the procedure issues a delete then they can execute the proc to delete the data so you will need to check the procs as well.
you will want to look through sys.database_permissions, sys.database_principals, sys.objects and a few others where needed to get to the information you need
April 4, 2012 at 4:55 am
Thank you Anthony
M&M
April 4, 2012 at 7:26 am
mohammed moinudheen (4/4/2012)
There is this server where a user is having only db_reader access and execute permissions only for specific procedures in a database. However, he has been able to delete data from 4 tables.On checking this persons windows level permissions, we could see he is member of number of other windows based groups.
Could it be possible that this user has had permissions inherited from one of those groups due to which he was able to delete data. If so, how do we find out or confirm? Please share your inputs.
one more reason to use SQL Server auth.
(for any one who comes across this post. and to the OP if he had not considered SQL Server authentication.)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 4, 2012 at 11:22 pm
and to the OP if he had not considered SQL Server authentication.)
No, not yet.
M&M
April 5, 2012 at 8:06 am
mohammed moinudheen (4/4/2012)
and to the OP if he had not considered SQL Server authentication.)
No, not yet.
with sql authentication you dont have the nasty problem of elevated access levels inherited from your domain group levels. we use only sql auth on our servers. it increases security by having to manually go in and add each user (which can be scripted to make it easier). if you want to increase simplicty you could use the same password for the domain login and sql login but seperate them and you gain security.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 5, 2012 at 8:11 am
I've been using WINDOWS Auth with ease and multiple group swith EASE for years..
More than likely the user is in a group for which is granted ELEVATED access.. Visit the ROLES section for that user an for the groups and decrease the access.
If possible create a new user; Add it to the group; Add the user to the sql server and perform simple sql deletes/inserts etc..
YoungHog
April 5, 2012 at 8:12 am
its 6 of one and half a dozen of the other with that as with windows auth, the auth is done via tokens with the domain controllers which reduces the load on SQL, where as with SQL auth its SQL handling the authentication which can result in more resource going through SQL, so if you've already got a loaded system, would you want to add more load?
personally Windows is my prefered method and never had an issues with it, just make sure your on top of your security
April 5, 2012 at 11:55 am
We found it. This account was a member of two other groups which had DBO on the database.
M&M
April 5, 2012 at 12:13 pm
mohammed moinudheen (4/5/2012)
We found it. This account was a member of two other groups which had DBO on the database.
glad you found the user.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 9, 2012 at 9:29 am
A login can inherit membership in the SYSADMIN role by membership in a windows domain group. You can confirm if a login account has been explicitly granted (or has inherited by some other means) admin role privillages by using the xp_logininfo procedure. For example:
xp_logininfo 'corpdomain\username'
Also, I wrote a script that returns this information for all login accounts:
http://www.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 9, 2012 at 9:46 am
capn.hector (4/4/2012)
mohammed moinudheen (4/4/2012)
There is this server where a user is having only db_reader access and execute permissions only for specific procedures in a database. However, he has been able to delete data from 4 tables.On checking this persons windows level permissions, we could see he is member of number of other windows based groups.
Could it be possible that this user has had permissions inherited from one of those groups due to which he was able to delete data. If so, how do we find out or confirm? Please share your inputs.
one more reason to use SQL Server auth.
(for any one who comes across this post. and to the OP if he had not considered SQL Server authentication.)
Or maybe to use DENY for this user to eliminate undesirable permissions that this user has obtained through membership of other groups by preventing their inheritance. Or remove this user from the windows based gfroups which give him permissions he should not have.
Tom
April 9, 2012 at 9:53 am
capn.hector (4/5/2012)
mohammed moinudheen (4/4/2012)
and to the OP if he had not considered SQL Server authentication.)
No, not yet.
with sql authentication you dont have the nasty problem of elevated access levels inherited from your domain group levels. we use only sql auth on our servers. it increases security by having to manually go in and add each user (which can be scripted to make it easier). if you want to increase simplicty you could use the same password for the domain login and sql login but seperate them and you gain security.
But you may still have the inherited access problem from database roles and server roles.
There is often a need for a coherent security policy which takes into account database access and OS level access together, and giving one user two logins (one for SQL acess and another for everything else) is usually not the best way to go about creating such a policy.
Tom
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply