November 3, 2009 at 1:29 pm
Is there a way to deny a right for all tables in a database without explicitly defining each table in the command? My problem is that I have a user who is assigned to the public role, and should only be able to read the tables. But if I look at the table permissions this user has update granted to him as well. I am able to deny update to the tables by typing them out using the command:
DENY UPDATE ON <table_name> TO <username>
What I would like to have is something that would allow me to deny update on all tables, but I don't know if this is possible using the above approach, or if this is scriptable. Any suggestions are appreciated.
Kurt Kapferer
November 3, 2009 at 1:38 pm
USE [DatabaseName]
GO
EXEC sp_addrolemember N'db_denydatawriter', N'UserName'
GO
Should Work
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 1:52 pm
Bru,
Thanks for the quick reply. This does not seem to help me. I feel like the reason for that is because this permission is not granted at the role level. If that were the case this user would only have read access already. (They have public role only, and public only has read in this case).
I think what I need is something that will deny this right at the table level.
November 3, 2009 at 1:58 pm
kkapferer (11/3/2009)
Bru,Thanks for the quick reply. This does not seem to help me. I feel like the reason for that is because this permission is not granted at the role level. If that were the case this user would only have read access already. (They have public role only, and public only has read in this case).
I think what I need is something that will deny this right at the table level.
I'm not sure what you are saying here on why you don't think this will work, but have you tried it? Adding the user name into the the db_denydatawriter group should prevent them from having update capabilities.
Alternatively, you could deny the update permission at the schema level. This would keep you from having to deny explictly for each table.
November 3, 2009 at 2:00 pm
Then If I am in your position, I would create a Cursor that iterates through all the Tables in that Database and run this Command,
DENY UPDATE ON [dbo].[TableName] TO [UserName]
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 2:26 pm
Bru Medishetty (11/3/2009)
Then If I am in your position, I would create a Cursor that iterates through all the Tables in that Database and run this Command,
DENY UPDATE ON [dbo].[TableName] TO [UserName]
What happens when a new table is added?
Permissions at the schema level would cover that scenario.
November 3, 2009 at 2:27 pm
That's True John, I agree
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 6, 2009 at 8:04 am
Thank you all for your suggestions. A more senior DBA here advised me that I was overthinking this problem. He suggested I just delete the user and recreate them giving them the permissions that they should have. It was discovered that a few weeks ago, this person knew they were going to have their dbo privileges revoked, so he went through and granted them to himself at the table level before this happened.
Regards,
Kurt Kapferer
November 6, 2009 at 8:26 am
kkapferer (11/6/2009)
Thank you all for your suggestions. A more senior DBA here advised me that I was overthinking this problem. He suggested I just delete the user and recreate them giving them the permissions that they should have. It was discovered that a few weeks ago, this person knew they were going to have their dbo privileges revoked, so he went through and granted them to himself at the table level before this happened.Regards,
Kurt Kapferer
I have a better suggestion for that user: Fire him!
Don't mess around with the account. If he's done something like that once, you have no idea what other security "back doors" he may have implemented. You already know he's dishonest and bypassing security. You need to terminate him, do a full, thorough security audit of every system he's had any access to at all, including checking for stolen data, and prosecute if you find anything criminal.
- 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
November 6, 2009 at 8:47 am
GSquared (11/6/2009)
kkapferer (11/6/2009)
Thank you all for your suggestions. A more senior DBA here advised me that I was overthinking this problem. He suggested I just delete the user and recreate them giving them the permissions that they should have. It was discovered that a few weeks ago, this person knew they were going to have their dbo privileges revoked, so he went through and granted them to himself at the table level before this happened.Regards,
Kurt Kapferer
I have a better suggestion for that user: Fire him!
Don't mess around with the account. If he's done something like that once, you have no idea what other security "back doors" he may have implemented. You already know he's dishonest and bypassing security. You need to terminate him, do a full, thorough security audit of every system he's had any access to at all, including checking for stolen data, and prosecute if you find anything criminal.
Wow, How rude of you! :w00t: Is it really Gus I'm reading there? 😀
Cheers,
J-F
November 6, 2009 at 8:55 am
Absolutely. I take a very strong stand on questions of ethics and legality. Always.
If I were the manager, and I found out that a DBA had given himself security rights that he wasn't supposed to have, I'd fire, investigate, and prosecute if I found anything even remotely illegal. But the firing wouldn't wait even five minutes. And blocking all known access wouldn't wait any longer.
And if I were the manager, and found out that another employee knew about the security violation and didn't alert me to it, I'd probably fire that person too. Would depend on a couple of other factors in that case, but that would be my first inclination.
Someone who feels it's okay to violate security to grant himself privileges he's not supposed to have, can't be trusted. What else has that person gone "ah, it's okay" about? Stealing proprietary data? Selling SSNs on the black market? Even just recording account information like name and address, which could be taken to a competitor for very good money?
- 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
November 6, 2009 at 9:10 am
I have to admit you're right, in a real big environment with a lot of important data, that could be really important. Where I work, I'm the only DBA, and we have a few developpers, and most of us just have the access to go everywhere. So I don't take the access of the servers too seriously, even though I might be the faulty one in this scenario.
I've never worked at a company where it was really important, one person can do it all, and we've never been a big team, maybe that's why.
Cheers,
J-F
November 6, 2009 at 9:17 am
Strongly support Gsquared on his view, anyone with such serious Security breaches has to be immediately shown the door, no mercy if you spare a guy this time, next time no one else makes such kind of security breach.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 6, 2009 at 9:23 am
I think you both raise very interesting points. My situation is unique in that this person is an R&D engineer, not in IT at all. They prefer to work on their own island, and only come to us when they need us to help out because they "tripped and fell while running with scissors". There were two engineers who oversee the application that connects to the database in question, and have dbo access (the rest of the engineers could only query through the application). The one apparently had repeatedly asked him to stop editing data in Enterpise Manager because he had made typographical errors while doing his quick and dirty edits. Now she came back to me because she discovered he was still doing this after we restricted his access to a read only role. That was how we discovered he had write access on each of the 600 or so tables in this database.
Since I do not work in the same group as this guy, I obviously do not have the power to fire him. I have made it very clear to his superior (the other engineer with dbo access) that what he is doing is very suspect behavior, but that is as far as I go with it. She knows how sensitive her data is, and I don't.
Thanks again for the dialog on this. As a fairly new DBA, (2 years out of college) I always think engaging in conversation about this stuff is fascinating.
November 6, 2009 at 9:46 am
J-F Bergeron (11/6/2009)
I have to admit you're right, in a real big environment with a lot of important data, that could be really important. Where I work, I'm the only DBA, and we have a few developpers, and most of us just have the access to go everywhere. So I don't take the access of the servers too seriously, even though I might be the faulty one in this scenario.I've never worked at a company where it was really important, one person can do it all, and we've never been a big team, maybe that's why.
Right. In that scenario, you have the rights you need to have, and aren't violating any company policy.
In the case we're talking about here, someone knew beforehand that his rights were about to be reduced, and then granted himself a "back door" to access data that he was specifically being denied access to because of a corporate decision.
Regardless of whether the decision to deny was correct or not, it was unethical, and potentially criminal, to do that.
- 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 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply