Introduction to Row Level Security in SQL 2016

Question

Introduction to Row Level Security in SQL 2016

kapil_kk

SSC-Insane

Points: 21316
More actions
July 13, 2016 at 9:50 pm

#309047

Comments posted to this topic are about the item Introduction to Row Level Security in SQL 2016
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Upsert SSC Veteran Points: 296 More actions · Answer 1

Is it just me, or does the RLS implementation by Microsoft seem too complex? Why not just set your RLS in a view like the ssis team did with the ssisdb in SQL 2012? It seems like it would be a lot easier to manage and way more transparent than a security policy and predicate that get buried in internal MS tables.

Am I missing something here?

unladenswallow SSC Enthusiast Points: 134 More actions · Answer 2

I think the "internal" version of the query in the article. was wrong. It was:

SELECT * FROM dbo.Person

WHERE User_Name() = 'User_CS'

That would return all rows if executed by User_CS

I think you meant:

SELECT * FROM dbo.Person

WHERE User_Access = 'User_CS'

Or, maybe:

SELECT * FROM dbo.Person

WHERE User_Access = User_Name()

which amounts to the same thing in this case.

c.johnson SSC Enthusiast Points: 184 More actions · Answer 3

Maybe I'm missing something, but can you control access on a hierarchy basis?

This looks to me like you either only see your dept's data or everything. I'm thinking about everyday applications where you might have a manager requiring access to more than a single team's data.

Upsert SSC Veteran Points: 296 More actions · Answer 4

Your access function could have logic built in to check hierarchies. The function can do a whole lot more than just compare user names.

c.johnson SSC Enthusiast Points: 184 More actions · Answer 5

It would be good to see an example of this as I think this would be a more practical example.

thomasstricker Valued Member Points: 69 More actions · Answer 6

Not sure I understand how this works. When you are running these SELECT statements which login are you using (signed in as USER1, etc.). Is the security level ("SELECT * FROM dbo.Person

WHERE User_Name() = 'User_CS'"), based on the fact that other users ("USER_CS", "USER_IT", "USER_EC") do not know the other user names? Does USER_CS Login as USER_CS; how are these USER_NAMES attached to User Login's (the USER_Names are created WITHOUT LOGIN).:unsure:http://www.sqlservercentral.com/Forums/Skins/Classic/Images/EmotIcons/Unsure.gif