January 30, 2019 at 12:09 am
Comments posted to this topic are about the item An Introduction to RLS Security Policies: Level 3 of the Stairway to Row-Level Security
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 30, 2019 at 11:22 am
Steve Jones - SSC Editor - Wednesday, January 30, 2019 12:09 AMComments posted to this topic are about the item An Introduction to RLS Security Policies: Level 3 of the Stairway to Row-Level Security
It is great that MS SQL2016 has an out-of-the-box solution for supporting multi-tenancy. But using tenant identification set on the CONTEXT_INFO mechanism has me a little concerned. This onus of setting this information is on the database clients and getting it wrong can result in data leakage. At the higher level, what are the architectural mechanism that can be employed to set this information reliably ? In our case, the clients use Entity Framework to reverse engineer code from the database and use the reverse engineer code for data access. The developers do not tinker with the reverse generated code. In such an environment, how is the CONTEXT_INFO set to a tenant id on the connection ?
Thankd
January 30, 2019 at 11:50 am
Use SESSIONS_CONTEXT() instead. Still some dangers. What you can better used is actual SUSER_SNAME() results. Note that if you use this with groups, like ISMEMBER(), there is a load in checking this. I'd really try to secure read only items in SESSION_CONTEXT() when someone logs in
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 9, 2020 at 9:37 pm
Hello Steve,
Great article, I found it very helpful.
I am implementing RLS on a sql database and I have come across a best practice question that I can't seem to find any opinions on online. This is my understanding:
A Security Policy is essentially a group of Security Predicates
A Security Predicate is a filter on a table thru a function
The security policy appears to have no limit on the number of security predicates or their relevance to one another (different tables, schemas, funcions).
Is it better to keep all security predicates in one security policy or somehow group the predicates into different policies? What are the pros and cons of keeping them all together?
Thanks
April 14, 2020 at 4:37 pm
Sorry, got busy and forgot about this.
I think if things are related, it's good to have them in one policy, so that you see them. If you start to have reasons to separate them, then I think that's fine. There isn't a lot of guidance on this, and as I work with it, I'm not sure myself what makes the most sense.
Keeping them together means they are covered in one place, and there is visibility. However, this also means that if you want separate rights for different groups, it's cumbersome to make changes.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 14, 2020 at 4:57 pm
Thanks for the response!
One comment from your response sparks another question for me. You say "However, this also means that if you want separate rights for different groups, it's cumbersome to make changes.".
Is this in reference to security groups? If it is, can predicates be group specific? For instance, two predicates for the same table but one is for security group A and one is for security group B. Or am I misunderstanding that portion of your response.
Thanks again for the response. It is very helpful to get your opinion.
April 14, 2020 at 6:47 pm
More that you are applying an "all or nothing" for the policy to all objects, meaning that the predicates are one big group. If I needed to have a separate predicates for other tables, I need separate policies. That has it's own complexities v having multiple polices, each being simpler.
It's a thorny mess, and I haven't seen enough people implement at scale over time to get good understanding of what's better.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply