August 21, 2009 at 3:32 pm
Is there a way to apply row level security to a table to where if a particular user selects from the table it will not display certain rows?
This would be based on a column value, so say if a user could not see certain rows based on column FilterNumber = '34' none of the rows would display with that user.
I'm sure you could do something like use a function to pass in the user_id and have some conditional logic to match that user to certain values that you want to filter on. However, I was wondering if this kind of security was built in so that adhoc queries would do this?
Or what are the best practice to accomplish this?
August 21, 2009 at 4:11 pm
Sure, it's easy. Here is an article from my blog that demonstrates how to do it.
The article is actually on Multi-Tenant databases, but the basic idea is the same: put a security/owner column on each table that should have row-level security implemented. Then implement a layer of "Security Views" that prevent access to any rows other than those authorized for that user. Finally, the actual tables must be protected from direct access by the users, who should only be able to get to the security views.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 21, 2009 at 4:50 pm
I agree with Barry that it's pretty easy to implement, but it can be complex to understand and keep it straight in your head which rows are visible to which people and easily matching that up with the data in tables.
Definitely views and strict security is the way to go, as mentioned in Barry's blog.
August 24, 2009 at 7:35 am
Ok, thanks a lot, very helpful. 🙂
August 24, 2009 at 10:47 am
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply