Well that’s just neat: Row Level Security, new in SQL Server 2016

SQL Server 2016 offers Row Level Security, which sounds awfully cool. It’s apparently been available in Azure for a while, but the on-prem version is getting it, now.

Securing Rows

Okay, so: row level security (or RLS as the MSDN article so groovily puts it). What’s that, then? In short, it’s a method of securing the rows in a table such users only get to see the data they’re entitled to.  More officially:

Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).

I’ve needed this exact thing in multiple shops over the decades, and of course you end up either trying to control access on the front end, or doing some rather fancy (and hard to maintain and scale) footwork on the back end, or – most often – just keeping different databases for different groups of users. Oh yes, it’s just as fun as it sounds.

Now though, we can just create a table valued function – this holds the logic of who gets to see what, and why – and create a security policy on the table, using that function as a filter predicate.  To control who gets to insert what data, create another TVF and use that as a block predicate. (No, Company2, you can’t insert data for Company10. Get real.)

Check out the code examples on MSDN for a nice few scenarios.

Where it helps in real life

This really is great.  This means that the one client* – call them Client X – that keeps 100 separate databases for each of their clients, can now keep just the one database for all of them, if they so choose. Right now, Client X has a ton of common data – product lists and so forth – in a common database, and individual client data – like sales info – in the individual DBs.  What they’ve ended up with is a system of cross-database queries (to combine individual data with common data), cross-database aggregate views (that they have to update each time they add a new client/DB!), and just a big old mess.

There are other examples, but I’ll spare you. If you’ve been in the business long enough, you’ve seen plenty of scenarios like this. But, back to the point.

When Client X upgrades to SQL Server 2016, they can combine everything into one database – one set of objects to maintain, one set of code, no cross database queries! – and implement RLS fairly easily based on their client IDs. It’ll take a bit of rework, but it’s also going to help their future development and troubleshooting efforts 1,000-fold.

 

*I’m combining and fictionalizing this a little – and only a little – to protect client identity, NDA, yadda yadda yadda. It’s still a valid scenario, and very close to the real life situation. 

Finally

There’s a good deal more to RLS, of course.  And MSDN has done pretty well at outlining the behavior, possibilities, and even some really interesting security risks. Go on, read up.

A caveat, after all of this: I tend to get really excited about new** features (well, some of them), and I don’t always see the potential downside. So I’ll be looking forward to reading commentary from some of my more skeptical fellows.

Still though, row level security looks pretty good.

 

**Yes I know it’s not new-new, it’s been in Azure for a while, and other DB products have it. But I’m still mostly an on-prem DBA – when I am a DBA – and 2016 will be my first chance to play around with the feature.

 

Happy days,
Jen
http://www.MidnightDBA.com/Jen

4 thoughts on “Well that’s just neat: Row Level Security, new in SQL Server 2016

  1. Justin King

    Hi Jen, Looks to be a great new feature. Though most customers I’ve dealt with want the separate database due to either ensuring no cross potential to have someone else access their data because of a stuff up and also if they require you to give them a backup of database you would have to export data out of tables into new database etc.

    1. Jen McCown Post author

      Yes, I haven’t fully explored (read: not at all) the downsides yet. Like so many things, some new features just sound bloody COOL on first reading. Still does, too.

  2. Pingback: Dew Drop – February 26, 2016 (#2197) | Morning Dew

Comments are closed.