Sep 09, 2016
Hello again friends, I'm glad you could join me for another foray into the exciting world of SQL Server 2016.
I'd like to have a brief chat with you today about another of the new features in SQL Server 2016. That is, Row-level Security or RLS.
This is a very handy little idea. Have you ever attempted to enable access to different rows within a table to different people? You need to determine the identity of the user searching the data then filter based on that info. Turns out, that's not as easy as you would imagine.
Microsoft have decided to address that very issue and make your life vastly easier in the process. So Adam, I hear you ask, what did they come up with? Well, it's rather ingenious. It involves using a function and the new security policy feature that's being rolled out with SQL Server 2016. The combination of those two means that, with no further interference, you can secure the rows that pertain to each particular individual.
Honestly there's nothing better than an example to show you what I mean.
So I'll start by creating three user accounts that we'll use to demonstrate the capability.
Great, easy so far, yeah? Next step, I want to create a table to access:
What a lovely table. Simple AND elegant. Let's put some stuff in it shall we?
And let's have a look at the newly inserted rows; See! Nothing up my sleeve!:
And, of course, we're going to need to grant some permissions on this new data, so:
Now, here's where the magic begins. Firstly we'll create an inline table valued function which, in our example, will simply return a 1 if a row in the SalesRep column of the Sales table matches the user executing the query, or if the user executing the query is the Manager. Easy, peasy, Campese!
Of course, I'll also need to create one of these funky new security policies as well:
And now, we're finally ready to jump in and test it. Woohoo!
With this result:
With this result:
And:
With this result:
And, really, we should test that it works when we turn the security policy off:
And the result when executed as me, Student:
Brilliant
Alright, I hope you've enjoyed this small demonstration into the utility of the new Row-Level Security (RLS) that was introduced in SQL Server 2016.
Of course, if you come along to a course you'll find out heaps more about not only RLS but many other topics. Have a look at our brilliant website for a list of all the SQL Server 2016 courses on offer, there are 12 of them to pick from, even the fussiest DBA will find something of interest I'm sure.
Thanks for stopping by, I hope you've found this brief blog about Row-Level Security enlightening. See you again soon.
Cheers – Adam
Make sure to have a look at our SQL Server 2016 training courses.
How do your Excel skills stack up?
Test NowNext up:
- How to improve your business writing
- Automate your website publishing process using Github and Azure
- Excelling in Excel
- 3 TED talks on habit
- How-to: Promote a Sub-Site to a Top-Site in SharePoint 2013
- Have you got bubbles?
- Mastering InDesign
- How-to: Add public holidays to a Microsoft Project 2013/2016 calendar
- What they don't tell you about becoming a manager
- The first tool of Power BI
Previously
- How to improve your business writing
- Assertiveness in practice
- Working with Styles – Part 4
- Float workloads into the cloud with Microsoft Azure
- How-to: Install Windows 10 using the media creation tool
- I am merging traditional and digital selling – And it is working!
- What you did not know about the Excel SUMPRODUCT function
- How-to: Validate data based on the value of a different cell in Excel
- Make your content fluid with AngularJS
- Great managers provide great feedback