February 21, 2014 at 6:59 am
Is there anything in 2012 that allows for row level security out of the box? If not, what are some ways to build this in?
Thanks in advance
John
February 21, 2014 at 8:36 am
No.
You can build this by implementing RBAC (google). Usually someone does a user/group/row implementation where you have a table of users. A table of groups that map rows, and linkages in there.
So if I have something like salaries, with a PK of SalaryID, what I'd do is build table that maps SalaryID to some group. The group is a logical construct that I'd probably implement as integers, so group 1 has a value of 1, group 2, has 2, etc. In the mapping table I have Group=1 and a SalaryID = 1 that show group 1 has access to that row. In this way multiple groups can access a row.
Then I have a table that links user/logins/application IDs to the Groups. Queries accessing the Salary table need to join back to the user and group tables to determine access. This is essentially what SQL Server and Windows do, though in a more complex way.
Managing and displaying the access that a user has or a group has can be tricky because this will be a lot of data.
I had one company that wanted to do this at multiple levels. We had clients, accounts, portfolios, and transactions. We included those IDs at each level, so that client had ClientID, but accounts had ClientID and AccountID. Queries against any level joined to a group table that had the particular client/account/portfolio/transaction ids in it. This would easily control access as long as people used the application.
February 21, 2014 at 9:55 am
yeah, the hardest part about row level security is that all access must go through a common path in order for it to work. As soon as you open up ad hoc queries from disparate sources all that hard work goes out the window.
The choices are simple. In some way you mark the row that allows you to filter based on that mark. As Steve says, a particular group id is one mechanism. Another choice is to break out storage by group, having a different schema for each set of data. that does make aggregating the data back together a bit more difficult, but it actually makes security management easier and allows for ad hoc querying since, presumably, one schema won't be allowed to see other schemas.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2014 at 10:20 am
Here is a tool that should be worth your while.
http://sqlserverlst.codeplex.com/
Lara built the Enterprise Policy Management Framework and has this product out there to do RLS and CLS.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 21, 2014 at 11:17 am
You can still allow ad-hoc querying, if access is restricted to views that incorporate your row level access framework.
February 21, 2014 at 11:30 am
You could also take a look at the patient encryption demo script provided in this download
http://www.sqlskills.com/resources/conferences/201203devweek_security.zip
That is a session put on by Bob Beauchemin.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 21, 2014 at 11:48 am
Thanks for all the feedback.
John
February 21, 2014 at 11:58 am
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply