September 9, 2008 at 11:26 am
Greetings all. I have one user who is able to update a field, despite my having denied her permissions to do so. On the public role, I only have select on all pertinent objects. The user is a member of two roles, for both of which I have explicitly denied updating the column in question. Our server is hosted by someone else, so I am not well schooled in these types of issues, but does anyone have any ideas where I can start to look? The users connect with an Access project, using Windows authentication.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 9, 2008 at 1:48 pm
Do you have the ability to put a trigger on the table and log the user name of the person doing the update (using suser_name())? Or can you run a trace on the server to verify that the logins are working the way you think?
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
September 9, 2008 at 2:27 pm
I have set up a trigger on the table a couple of months ago, and that is how I noticed the problem. I capture the table name, pk, column, old val, new val, date, and user name, so I know she is doing it, plus I have called her to ask, and she admitted it. Do you have any ideas where the problem could be?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 9, 2008 at 9:31 pm
Are you capturing suser_sname() as well?
If you have permissions denied, there shouldn't be a way they can update the column. I bet you have some strange permissions mismatch.
Can you check what's talked about here: http://www.sqlservercentral.com/articles/Administering/dumpsqlpermissions/1314/
September 10, 2008 at 6:05 am
In the trigger I am using system_user to capture the user name. Is there a difference between system_user and suser_sname?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 10, 2008 at 8:40 am
Ok, I looked at the audit table again, and found there is another guy who is able to update the field in question. The post about restricting access to the database based on IP got me thinking. Both of these people are in the same city, so my question is this: is there any way for SQL Server to know where a connection is coming from? Does it care? My update trigger is correctly capturing their user names, so is there something else about a user name that is involved with permissions?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 11, 2008 at 7:01 am
I tried SELECT-ing those two items from an SSMS query window, and regardless of which server I connected that query to, both items were the same as the ID used to establish the connection. This suggests that there may be a difference if you have multiple connections from an application. Each connection can have it's own ID to connect with, and I'm not exactly sure how it all works, but I'd be looking to see exactly how that application connects.... pehaps SQL Server Profiler?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2008 at 7:35 am
Just a couple of thoughts.
Could these people be a member of SysAdmin or db_owner? No matter what permissions are disallowed, they would still be able to update.
Is the field being updated by a stored proceedure that they have permission to run?
Steve
September 11, 2008 at 7:48 am
The only ways I can find that a user can update a column when Update on that column has been explicitly denied, is if they have execute rights on a stored procedure that does the update or they own the object or schema the object is in (2005).
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
September 11, 2008 at 11:15 am
sblock (9/11/2008)
Could these people be a member of SysAdmin or db_owner?
Bingo! I think. Indeed the main offender was checked as an administrator. I did not even think to check because I would not have done it. The other rogue updater was not an administrator, but it has been many months since he has made an update, so maybe he was checked as admin at one time, and the other guy who works in our server saw it and fixed it. Thanks for the hint.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply