Stairway to SQL Server Security Level 10: Row-Level Security

Question

Stairway to SQL Server Security Level 10: Row-Level Security

Don Kiely

SSC Eights!

Points: 959
More actions
July 13, 2015 at 4:38 am

#309808

Comments posted to this topic are about the item Stairway to SQL Server Security Level 10: Row-Level Security

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

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

williamn SSC-Addicted Points: 435 More actions · Answer 1

In the first Tip, "...generally a Windows of SQL Server login" should be "generally a Windows or SQL Server login."

And in the paragraph immediately after Listing 10.2, I can not make sense of the opening statement, "That is all the code needed to set up the hold the data and provide access to it." The phrase before "and" is confusing. Please clarify.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

July 29, 2015 at 7:13 am

#1815694

Another great security article. Thanks.

sknox SSChampion Points: 12388 More actions · Answer 3

Interesting. I recently had to implement row-level security on a reporting where access could be granted in three distinct ways.

Since all access to the reporting system is via stored procedures, I ended up writing an inline TVF similar to the security predicate example and CROSS APPLYing it in each of the stored procedures' SELECT statements.

Don Kiely SSC Eights! Points: 959 More actions · Answer 4

williamn (7/29/2015)
In the first Tip, "...generally a Windows of SQL Server login" should be "generally a Windows or SQL Server login."

Thanks! I'll let the editor know.

williamn (7/29/2015)
And in the paragraph immediately after Listing 10.2, I can not make sense of the opening statement, "That is all the code needed to set up the hold the data and provide access to it." The phrase before "and" is confusing. Please clarify.

Sorry another typo that got by three of us! It should be, "That is all the code needed to set up to hold the data...." Although that is a bit awkward, so let's make it , "That is all the code needed to set up a table to hold the data...."

Thanks!

Don

davidsatz Mr or Mrs. 500 Points: 579 More actions · Answer 5

We use views for row level security (2008 R2) and found that you need to add "WITH CHECK OPTION" to the view or else users can insert data into the view for a "UserAccess" that is not theirs.

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 6

I'd be interested in knowing if anyone is able to get this working via some front-end interface that sits between the database and somewhere else. Something that could interpret a custom user login and pass details from that through to the security function, but otherwise act normally once that authentication hits. That would be a great thing for SaaS businesses that want to enable MS tools at their place of business.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 7

Great Article!!

Would you happen to know how I could build a script to determine if RLS is ON/OFF and to provide details?

Thank you in advance for all your help 🙂

Rudy