November 24, 2008 at 12:44 pm
I'm pretty sure I know the answer, but I'm looking for a little confirmation. Basically, I'm wondering if it is legit to use views to hide columns from users? I know you can set column-level permissions, but that sounds like a real pain. So, what I did was:
a. Create a group (NTDomain group in my case) named, "BasicRO"
b. Create a view named, "TableA_Basic" that is really just a SELECT on TableA, but only includes columns I want the members of the, "BasicRO" group to see.
c. Set explicit DENY permissions for the, "BasicRO" group for all activities on ALL DBO's in the DB.
d. Set explilcit GRANT SELECT permissions for the "BasicRO" group on the, "TableA_Basic" view.
Questions:
1. Will this work?
2. Did I go too far with step "c" where I set explicit DENY permissions? Seems like overkill....but I know permissions can get a bit tricky when it comes to Explicit vs. Implicit settings and I didn't want to take chances.
3. Is there a better way?
November 24, 2008 at 2:02 pm
Using views or stored procedures to control access to a data is certainly a valid, and I would argue a necessary step for security.
The Explicit Deny is probably overkill because, I believe, the DENY will override any GRANT's you may do later to another group to which a user may belong.
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
November 24, 2008 at 7:56 pm
:w00t:
That's a good point about the DENY overriding any other group's GRANT permissions...the "most restrictive" rule always "wins" if I recall my old 2000 Domain ACL/DACL courses correctly. But, if I leave the permissions on the underlying tables alone (left at implicit), will the members of, "BasicRO" group have the ability to execute SELECTs against the tables directly? I want them to ONLY be able to select from the view, not the tables directly.
...hold on....Just read (ok, skimmed) this article:
http://www.sqlservercentral.com/articles/Security/sqlserversecurityfixeddatabaseroles/1231/
I guess that answers my question - I think the public role has permissions to all objects in the DB I'm working with. Seems like that would explain why any login can see the underlying tables with implicit permissions...which is probably why it seemed, to me, that by NOT explicitly setting DENY to my DBO's, everyone would gain access. I think I need to talk to my server team about their use of the public role. According to the article, it'd be safer to NOT give the public role explicit GRANT permissions to access anything (although definitely DO NOT set explicit DENY to public or else everyone would be locked out of that/those DBO/s).
Am I on the right track here?
November 25, 2008 at 4:56 am
Yeah it seems you are on the right track. You should definitely remove permissions to your user objects from the public role.
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
November 26, 2008 at 11:46 am
Great...thanks for the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply