none
data driven security

    Question

  • hi.  Does the 2012 engine offer some sort of security scheme based on data values in one or more columns by table by user?  One in which the same rules for including or excluding certain rows from certain tables for a user would act the same whether the resultset was being built by a view or direct select on the table?
    Friday, January 10, 2014 4:05 AM

Answers

  • thx all.  I dont believe where I was going is considered filtered views.

    What I'm asking has to do with forcing my user to use procs and views to access dbo tables.  And preventing him from doing selects directly on the dbo tables.

    I'm proposing that the proc and view schemas be named something different than dbo, say "abc".  But I dont know if this is what the experts would do.

    My understanding is that if i grant execute and select on "abc", sql's chaining feature will ignore the fact that my user doesnt have select permissions (direct) on the dbo tables themselves.

    The views and procs would join to a security table and the where clauses in the joins would cause only allowable data for this user to be returned.

    Yes.  That will work.  Using a separate schema is handy here as you can grant permissions at the schema level, so when you add a proc or view to the schema it will automatically have the correct permissions.  You can also set the default schema for users and groups so they can query objects in their schema without including the schema name.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by DB042189 Wednesday, January 15, 2014 4:28 PM
    Wednesday, January 15, 2014 4:17 PM
  • You mean ownership chaining for permissions? Yes, that works. Grant select permission on the view for the user, but not on the table. You user then can select from the view, even if he don't have permission for the base table(s).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by DB042189 Wednesday, January 15, 2014 4:29 PM
    Friday, January 10, 2014 4:12 PM
    Moderator
  • i dont think the view approach is viable.  You cant select this WINDOWS userid from sql, at least as far as i know.  That means you cant join it on a custom security scheme.  Very disappointing.   

    Sure it is. 

    Use suser_name() to return the user's login, which works for both a Windows Login or SQL Login.

    Use user_name() to return the name of the database user the login is mapped to.

    And, best of all, use is_member() to check if the user is a member of a role.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by DB042189 Wednesday, January 15, 2014 4:28 PM
    Monday, January 13, 2014 9:43 PM

All replies

  • Hello,

    There is no such function as a build-in solution available, you have to create your own solution.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 10, 2014 7:46 AM
    Moderator
  • thx.  Can a trigger control select behavior so my rules can be centralized?
    Friday, January 10, 2014 2:36 PM
  • No triggers cannot control SELECT behaviour.  You can use views or stored procedures to have fine grain control on this.  The way Dynamics CRM does this for example is using a function to determine current user and their permissions in the Filtered views, eg pseudo-code:

    select *
    from [table]
    where UserHasAccess = 1

    Have a look at this article which covers a similar topic:

    http://technet.microsoft.com/en-us/library/cc966395.aspx

    • Edited by wBob Friday, January 10, 2014 2:49 PM link provided
    Friday, January 10, 2014 2:47 PM
  • thx.  Hopefully one more question.  Can sql server 2012 enterprise lock a particular fact table down from being queried by a particular user but allow that same user to run a view that uses that table in a join ?  Perhaps using chaining or some other feature of sql?
    Friday, January 10, 2014 4:00 PM
  • There is nothing like "Row level" security in the database engine.   This is generally done via the application layer, using WHERE criteria, not at the database layer.

    Friday, January 10, 2014 4:09 PM
    Moderator
  • You mean ownership chaining for permissions? Yes, that works. Grant select permission on the view for the user, but not on the table. You user then can select from the view, even if he don't have permission for the base table(s).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by DB042189 Wednesday, January 15, 2014 4:29 PM
    Friday, January 10, 2014 4:12 PM
    Moderator
  • I read the article wBob referred to.  Interesting but not really column value (at the granularity I seek) oriented. 

    Olaf, is the approach you suggest considered ownership chaining?  Or just (thankfully) a useful feature?  I thought chaining involved different schemas which in turn allow control at different object levels.


    • Edited by DB042189 Friday, January 10, 2014 5:47 PM encrypt/decrypt wouldnt work anyway
    Friday, January 10, 2014 5:42 PM
  • i dont think the view approach is viable.  You cant select this WINDOWS userid from sql, at least as far as i know.  That means you cant join it on a custom security scheme.  Very disappointing.   
    Monday, January 13, 2014 6:36 PM
  • i dont think the view approach is viable.  You cant select this WINDOWS userid from sql, at least as far as i know.  That means you cant join it on a custom security scheme.  Very disappointing.   

    Sure it is. 

    Use suser_name() to return the user's login, which works for both a Windows Login or SQL Login.

    Use user_name() to return the name of the database user the login is mapped to.

    And, best of all, use is_member() to check if the user is a member of a role.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by DB042189 Wednesday, January 15, 2014 4:28 PM
    Monday, January 13, 2014 9:43 PM
  • excellent!  thx.  Any idea if giving different permissions to same user at table and view level really requires/involves chaining?  If yes, does that require different schemas for table and view?
    Tuesday, January 14, 2014 2:14 AM
  • I'm not sure I understand the question.   If the table and the view are in the same database and have the same owner then the table permissions will never be checked because of ownership chaining.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, January 14, 2014 3:38 AM
  • my goal is to block the dbo tables (from direct query by certain users) but not the views and procs that use them cuz the latter items now incorporates my home grown row level security.

    So I'm trying to picture an effective way of drawing this line in the sand and am wondering if using different schemas on the views and procs from the one used on the tables i could save a lot of time when granting permissions and manage things pretty efficiently.  And bank on sql's chaining effect (if chaining is even relevant here) to do the rest for me.  Just not comfortable right now with the best strategy or what all the choices are.  Also, not sure if using different schemas is really a way of saving lots of time.  But I think it is from what i remember.

    Also, i have to look back but my recollection is that dynamic sql interferes in some way.



    • Edited by DB042189 Tuesday, January 14, 2014 10:04 PM clarity
    Tuesday, January 14, 2014 2:46 PM
  • ...

    Also, i have to look back but my recollection is that dynamic sql interferes in some way.



    Yes, dynamic SQL always breaks the ownership-chain

    Also I would be very careful in implementing security by the means of filtered views, as it is not that hard to craft statements which discloses contents of the underlying table data even with a filter in place.


    Andreas Wolter
    Microsoft Certified Master SQL Server 2008
    Microsoft Certified Solutions Master SQL Data Platform, SQL Server 2012
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com


    Wednesday, January 15, 2014 12:31 PM
  • >Also I would be very careful in implementing security by the means of filtered views, as it is not that hard to craft statements which discloses contents of the underlying table data even with a filter in place.

    Could you elaborate on this?  I am not aware of any way to do that.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, January 15, 2014 1:22 PM
  • thx all.  I dont believe where I was going is considered filtered views.

    What I'm asking has to do with forcing my user to use procs and views to access dbo tables.  And preventing him from doing selects directly on the dbo tables.

    I'm proposing that the proc and view schemas be named something different than dbo, say "abc".  But I dont know if this is what the experts would do.

    My understanding is that if i grant execute and select on "abc", sql's chaining feature will ignore the fact that my user doesnt have select permissions (direct) on the dbo tables themselves.

    The views and procs would join to a security table and the where clauses in the joins would cause only allowable data for this user to be returned.

    Wednesday, January 15, 2014 4:03 PM
  • thx all.  I dont believe where I was going is considered filtered views.

    What I'm asking has to do with forcing my user to use procs and views to access dbo tables.  And preventing him from doing selects directly on the dbo tables.

    I'm proposing that the proc and view schemas be named something different than dbo, say "abc".  But I dont know if this is what the experts would do.

    My understanding is that if i grant execute and select on "abc", sql's chaining feature will ignore the fact that my user doesnt have select permissions (direct) on the dbo tables themselves.

    The views and procs would join to a security table and the where clauses in the joins would cause only allowable data for this user to be returned.

    Yes.  That will work.  Using a separate schema is handy here as you can grant permissions at the schema level, so when you add a proc or view to the schema it will automatically have the correct permissions.  You can also set the default schema for users and groups so they can query objects in their schema without including the schema name.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by DB042189 Wednesday, January 15, 2014 4:28 PM
    Wednesday, January 15, 2014 4:17 PM
  • >Also I would be very careful in implementing security by the means of filtered views, as it is not that hard to craft statements which discloses contents of the underlying table data even with a filter in place.

    Could you elaborate on this?  I am not aware of any way to do that.

    ..

    sure

    I put it into a blog post: SQL Server Row- and Cell-Level Security – Disclosure vulnerability

    (so I have to thank you for the idea ;-) )


    Andreas Wolter
    Microsoft Certified Master SQL Server 2008
    Microsoft Certified Solutions Master SQL Data Platform, SQL Server 2012
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Thursday, January 16, 2014 12:45 PM
  • Excellent.  Thanks.

    Can you think of any attack against a view of the form

    create view readonly.personal_data
    select id, case(when is_member(role)=1 then personal_data else null end) personal_data,...
    where is_member(role)=1

    ?  So wrapping the columns in an expression to intercept any predicate reordering?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, January 16, 2014 7:12 PM
  • ...

    Can you think of any attack against a view of the form

    create view readonly.personal_data
    select id, case(when is_member(role)=1 then personal_data else null end) personal_data,...
    where is_member(role)=1

    ?  So wrapping the columns in an expression to intercept any predicate reordering?

    ...

    Thanks for your input, David.

    Your proposal seems good at first glance, definitely a good idea. I cannot think of a straightforward way to defeat it right now. But that may not be enough. I can at least imagine that one could trick the query processor under certain conditions with different index sets and query plans/force hints, to disclose something.. I'd need more time to craft something though.

    Another problem is, and why this cannot be used in all cases: This view cannot be used for updating the data then any more. My example only showed Select, but that is just for brevity. In real life those kind of views will also be used for CRUD operations very often (not necessarily of course, but not everybody embraces the concept of using procedures for data changes!).

    So even if I exclude the Update part, I still don’t feel well, saying “there is no way to break it”. After all I just spend 10 minutes over the problem :-) – If I’d get payed / the data is very valuable, people get much more creative than I might imagine. And I do have a problem with entrusting the query engine with Security-matters. It’s not designed for it. Bugs are not rare, the query processor gets updated almost with every cumulative update.. so this is why I won’t fully recommend this solution. Hope it makes sense to you.



    Andreas Wolter
    Microsoft Certified Master SQL Server 2008
    Microsoft Certified Solutions Master SQL Data Platform, SQL Server 2012
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Thursday, January 16, 2014 8:13 PM