locked
SQL Server > Record-level security > using application users > how to implement? RRS feed

  • Question

  • Hello,

    I need someone to guide me how to implement Record-Level security in SQL Server or what is the best practice to implement it.

    Please note that I want to get this record level security implemented for Application users and not the windows users. By Application users I mean users which would be created within my application.

    My data could be accessed from - WPF application, web application and SSRS Report Manager (via reports).

    I have explored using SUSER_NAME(), SETUSER, EXECUTE AS and Application Roles in SQL Server but could not co-relate them & build a solution.

    Need someone to help me in coming up with a most secured approach.

    Please feel free to query.

    Thanks.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Friday, April 3, 2015 7:14 AM

Answers

  • Besides Erland's suggestion, I would also like to make a note that we have recently introduced a public preview for Row-Level Security (RLS) in Azure SQL Database (Available on the latest version of SQL Database, V12).

    I would recommend giving it a try as it may simplify your scenario. We would like to encourage you to try it and send us feedback on this new feature.

    I am including a few links below for anyone who may be interested in trying this feature:

    * Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security (http://azure.microsoft.com/blog/2015/03/02/building-more-secure-middle-tier-applications-with-azure-sql-database-using-row-level-security/)

    * Row Level Security in BOL (Preview) (https://msdn.microsoft.com/library/dn765131.aspx?f=255&MSPPError=-2147217396)

    * SQL Server Security Blog (http://blogs.msdn.com/b/sqlsecurity/)

    * Azure is the Ideal Cloud for Your Database Workloads with Increased SQL Server Compatibility, Security, Automation, and Power (http://blogs.technet.com/b/dataplatforminsider/archive/2015/01/29/update-to-azure-sql.aspx?WT.mc_id=Social_SQL_General_DI&linkId=12041901)

    Thanks,

    -Raul Garcia

     SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.


    Friday, April 3, 2015 5:28 PM
  • I understand the administrative burden and yes, it is there for sure.

    However, from your line - "but if you had planned to add them to your own table," I feel there is still some gap between what I want to explain & what you have perceived. I may be totally wrong in saying this but still I want to ensure we both are on the same page.

    Below is the flow I am planning to implement with an example of permitting users to see only Departments which they are supposed to - 

    1. I will create Tables - Users, Departments & UserDepartments. UserDepartments will have the mapping of which User is permitted to view which Department.
    2. From the front end application, when users are created ("Applicaiton Users"), a new row will be added to the Users table + a new SQL Login will be created with the SAME name. I will also add this SQL Login to a Database Role which will give restricted access to this user. This will manage the SQL object level security.
    3. Now, whenever any user logs in into the application - Desktop/Web/SSRS, the connection  to SQL will be impersonated to the related SQL Login.
    4. I will create View for Department where I will use SUSER_NAME() function to get the contextual user & will return the permitted records by making use of the UserDepartments table. This will ensure the Record-Level security.

    Cons

    There will be as many Logins in SQL Server as there are users in the Users table.

    Hope, I have made my implementation clear.

    Would appreciate your views for the same.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia


    • Edited by vinaypugalia Sunday, April 5, 2015 8:57 AM
    • Marked as answer by vinaypugalia Wednesday, April 8, 2015 4:13 AM
    Sunday, April 5, 2015 8:57 AM
  • I guess that will work. I would still prefer to use Window authentication, since membership in different groups then can be administered through the AD. But there may be circumstances in your environment that precludes this choice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 5, 2015 2:00 PM

All replies

  • I'm not sure what you mean with "application users". Do you mean SQL logins? Or are you maintaining your own user structure, and the application logs in with a proxy login?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 3, 2015 8:57 AM
  • Hello Erland,

    By "Application Users" I mean users maintained by my application in it's own database table say "Users" and yes, the connection to SQL Server would be made by a common SQL login.

    Hope, this clarifies your concern.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Friday, April 3, 2015 9:51 AM
  • In that case, you need a means to identify this user. The best way to do this is to use the command SET CONTEXT_INFO. This sets a 128-byte binary value which you can retrieve with the context_info() function.

    See this passage in one of my articles for more information and an example:
    http://www.sommarskog.se/grantperm.html#context_info

    I may be pointing out the obvious, but if you maintain your own users like this, don't store their passwords in the database, but only store irreversiable hash values.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 3, 2015 11:42 AM
  • Thanks for pointing me to SET CONTEXT_INFO.

    I have seen it's usage in MS Dynamics CRM "[dbo.].[fn_FindUserGuid] ()" function.

    I have had a look @ it and it would solve the purpose for the WPF & Web application but not for the SSRS report as per my understanding.

    From within the application, we can ensure to set the CONTEXT_INFO once the connection is made with the back-end as this could be coded by the developer but how to ensure this happens correctly when a SSRS reports fetches the data from the SQL database.

    As the end user can set the info of a different user to the see the data which he is not supposed to.

    Could you please share your views on this?

    Also, I have a different approach to suggest & would appreciate your inputs on this too.

    I am planning to create SQL Users from the application as and when the application admin creates one for its application. And then impersonate the user after connection is made. This would even help me with SSRS as it has got an option - "Impersonate the authenticated user after a connection has been made to the data source".

    Please guide.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Friday, April 3, 2015 2:35 PM
  • I am planning to create SQL Users from the application as and when the application admin creates one for its application. And then impersonate the user after connection is made. This would even help me with SSRS as it has got an option - "Impersonate the authenticated user after a connection has been made to the data source".

    Yes, I think I like this better than having your own application-specfic users.

    I can't comment specifically on SSRS because that is a very white spot on my map.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 3, 2015 3:20 PM
  • Besides Erland's suggestion, I would also like to make a note that we have recently introduced a public preview for Row-Level Security (RLS) in Azure SQL Database (Available on the latest version of SQL Database, V12).

    I would recommend giving it a try as it may simplify your scenario. We would like to encourage you to try it and send us feedback on this new feature.

    I am including a few links below for anyone who may be interested in trying this feature:

    * Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security (http://azure.microsoft.com/blog/2015/03/02/building-more-secure-middle-tier-applications-with-azure-sql-database-using-row-level-security/)

    * Row Level Security in BOL (Preview) (https://msdn.microsoft.com/library/dn765131.aspx?f=255&MSPPError=-2147217396)

    * SQL Server Security Blog (http://blogs.msdn.com/b/sqlsecurity/)

    * Azure is the Ideal Cloud for Your Database Workloads with Increased SQL Server Compatibility, Security, Automation, and Power (http://blogs.technet.com/b/dataplatforminsider/archive/2015/01/29/update-to-azure-sql.aspx?WT.mc_id=Social_SQL_General_DI&linkId=12041901)

    Thanks,

    -Raul Garcia

     SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.


    Friday, April 3, 2015 5:28 PM
  • Hello Raul,

    Thank you very much for the links.

    I certainly plan to get my hands dirty with this new feature & check the feasibility of using it in my situation.

    If you could comment something on how RLS could help with SSRS, that would prove to be very helpful at the moment.

    I would appreciate if you could also share you views on implementing record-level security using some currently used techniques. This would really help in-case we could not pick the new RLS feature.

    Looking forward for your response.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Saturday, April 4, 2015 4:50 AM
  • If you could comment something on how RLS could help with SSRS, that would prove to be very helpful at the moment.

    I would appreciate if you could also share you views on implementing record-level security using some currently used techniques. This would really help in-case we could not pick the new RLS feature.

    So there are two things to solve to do row-level security. One is to identify the current user, and that is what our discussion so far has focused on. The other thing is how to do the filtering as such, and this is something RLS help you with, but you can do this fairly easily in older versions of SQL Server as well. My assumption was that you had already done some thinking in this area, why I did not stray into that area.

    The common solution is to have a table which is keyed by user id and entity id and then you build views or similar around the entity tables where you filter out rows for which there is no mapping to the user.

    As for SSRS, in worst case, you could pass the user name as a parameter and do SET CONTEXT_INFO first thing in the procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 4, 2015 9:19 AM
  • My assumption was that you had already done some thinking in this area, why I did not stray into that area.

    Yes, your assumption was correct and I have already done the thinking in this area. In fact, very much similar to what you have proposed.

    The main issue is handling the security @ SSRS. If we allow the users to pass their @UserName/@UserId in the StoredProcedure or Table Valued Function, there is a possibility that they pass @UserName/Id of another user & fetch the data they are not supposed to. And we land up again in the same situation.. :(

    Why I asked #Raul to share his views is, in case he can show some way to handle this SSRS situation either with some existing techniques or through RLS.

    I am still looking forward for some possibility here...


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Saturday, April 4, 2015 10:58 AM
  • The main issue is handling the security @ SSRS. If we allow the users to pass their @UserName/@UserId in the StoredProcedure or Table Valued Function, there is a possibility that they pass @UserName/Id of another user & fetch the data they are not supposed to. And we land up again in the same situation.. :(

    Obviously that would only work if the reports are encapsulated by application code. When you say it, I recall that you mentioned something called "Report builder". Did I say that I am clueless about SSRS?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 4, 2015 4:07 PM
  • Additionally, do you see any side effect of creating a lot of SQL Users?

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Sunday, April 5, 2015 4:26 AM
  • Additionally, do you see any side effect of creating a lot of SQL Users?

    There certainly is an administrative burden, but if you had planned to add them to your own table, there is not much difference.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 5, 2015 8:39 AM
  • I understand the administrative burden and yes, it is there for sure.

    However, from your line - "but if you had planned to add them to your own table," I feel there is still some gap between what I want to explain & what you have perceived. I may be totally wrong in saying this but still I want to ensure we both are on the same page.

    Below is the flow I am planning to implement with an example of permitting users to see only Departments which they are supposed to - 

    1. I will create Tables - Users, Departments & UserDepartments. UserDepartments will have the mapping of which User is permitted to view which Department.
    2. From the front end application, when users are created ("Applicaiton Users"), a new row will be added to the Users table + a new SQL Login will be created with the SAME name. I will also add this SQL Login to a Database Role which will give restricted access to this user. This will manage the SQL object level security.
    3. Now, whenever any user logs in into the application - Desktop/Web/SSRS, the connection  to SQL will be impersonated to the related SQL Login.
    4. I will create View for Department where I will use SUSER_NAME() function to get the contextual user & will return the permitted records by making use of the UserDepartments table. This will ensure the Record-Level security.

    Cons

    There will be as many Logins in SQL Server as there are users in the Users table.

    Hope, I have made my implementation clear.

    Would appreciate your views for the same.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia


    • Edited by vinaypugalia Sunday, April 5, 2015 8:57 AM
    • Marked as answer by vinaypugalia Wednesday, April 8, 2015 4:13 AM
    Sunday, April 5, 2015 8:57 AM
  • I guess that will work. I would still prefer to use Window authentication, since membership in different groups then can be administered through the AD. But there may be circumstances in your environment that precludes this choice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 5, 2015 2:00 PM
  • Thanks for sharing your views and help for narrowing down to a solution which would work in my case.

    Thanks again!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Monday, April 6, 2015 4:06 AM
  • ...

    The main issue is handling the security @ SSRS. If we allow the users to pass their @UserName/@UserId in the StoredProcedure or Table Valued Function, there is a possibility that they pass @UserName/Id of another user & fetch the data they are not supposed to. And we land up again in the same situation.. :(

    ...

    Never rely on parameters in Reporting Services! They can be spoofed as you guessed.

    But you have the very handy builtin variable User!UserID

    That of course means to stick to windows-authentication - with all the nifty Kerberos and Extended Protection Features added ;-)

    Using that + stored procedures (preferably without any dynamic sql) should make your concept pretty hard to crack. (For the readers: Why not ad hoc?: SQL Server Row- and Cell-Level Security – Disclosure vulnerability )



    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Tuesday, April 7, 2015 9:04 PM
  • Hello Andreas,

    I have even explored passing User!UserID with implementation of Custom Authentication (IAuthenticationExtension) with SSRS.

    But again, if user passes some other value instead of User!UserID, we land up in same situation, as it's again left to SSRS to pass the value as a parameter to the Stored Procedure.

    Hope, this helps other readers as well.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Wednesday, April 8, 2015 11:16 AM
  • Hello Erland,

    Moving further, I am stuck defining the Secured Role for the SQL Users we have been talking about. Mainly with the object level security.

    I have created a new role - say "SecuredRole". Now, what permissions should I grant to this role so that the members of this role can access/SELECT the secured views/Table valued functions but not the underlying tables directly.

    I am sure you have an answer for this.. :)

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Wednesday, April 8, 2015 11:20 AM
  • Hello Andreas,

    I have even explored passing User!UserID with implementation of Custom Authentication (IAuthenticationExtension) with SSRS.

    But again, if user passes some other value instead of User!UserID, we land up in same situation, as it's again left to SSRS to pass the value as a parameter to the Stored Procedure.

    ...
    I can only imagine that you left the parameter writable. It has to be an internal. That type cannot be changed by any Report user.

    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Wednesday, April 8, 2015 12:06 PM
  • Actually, our application users are allowed to create custom SSRS reports.

    Hence, in order to get the correct results, they will have to pass @UserId to the StoredProcedure or the Table Valued Function.

    This is what creating the security threat.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Wednesday, April 8, 2015 1:58 PM
  • Actually, our application users are allowed to create custom SSRS reports.

    Hence, in order to get the correct results, they will have to pass @UserId to the StoredProcedure or the Table Valued Function.

    This is what creating the security threat.


    You name it.

    Now you really need to stick to the rule of only giving them access to prepared stored procedures. If they even can write ad-hoc code you security concept will be in risk.

    At best you can prepare those procs with filter on System_User, internally join to some table containing allowed logins and let them only use integrated authentication for the respective DataSource. That of course has some other implications like no scheduled subscriptions but you gotta die one way or the other if security is crucial.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com


    Wednesday, April 8, 2015 2:41 PM
  • I think the best for manageability is to put the tables in a separate schema, and have the secured views/functions/procedure in the dbo schema.

    Now you can grant SecuredRole SELECT and EXECUTE on the dbo schema, but you do not grant any rights on the table schema. Or DENY them permission on that schema, if you afraid that someone incorrectly adds them to db_datareader or similar. But DENY can cause other problems further along the road, if you want to grant an occasional user access to the table schema.

    Note that the table schema should be owned by dbo, so that ownership chaining from the views to the tables apply.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, April 8, 2015 9:17 PM
  • Hello Erland,

    I have tried the other way round. I have the underlying tables with default dbo schema & have added the secured views & functions under the "secured" schema.

    I have granted SELECT & EXECUTE to the "SecuredRole" on the "secured" schema.

    But a user who belongs to the SecuredRole could not SELECT from the secured view as it has got underlying table with "dbo" schema and the error says - 

    Msg 229, Level 14, State 5, Line 2
    The SELECT permission was denied on the object '{UnderlyingTableName}', database '{DBName}', schema 'dbo'.

    So, if this is not working, could you please help me in understanding how what you have suggested would work.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Thursday, April 9, 2015 8:10 AM
  • Hello Erland,

    I have tried the other way round. I have the underlying tables with default dbo schema & have added the secured views & functions under the "secured" schema.

    I have granted SELECT & EXECUTE to the "SecuredRole" on the "secured" schema.

    But a user who belongs to the SecuredRole could not SELECT from the secured view as it has got underlying table with "dbo" schema and the error says - 

    Msg 229, Level 14, State 5, Line 2
    The SELECT permission was denied on the object '{UnderlyingTableName}', database '{DBName}', schema 'dbo'.

    So, if this is not working, could you please help me in understanding how what you have suggested would work.

    ...

    That happens when the ownership-chain is broken.

    For your scenario make sure the owner of your Schema "secured" has the same owner as the "dbo-schema" as Erland stated above - just the other way around.

    check the owners via sys.schemas or sys.tables and adjust them wit ALTER AUTHORIZATION


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Thursday, April 9, 2015 8:30 AM
  • Hello Andreas,

    Thanks for pointing out this - "making the owner of the schema same"

    It was initially not.

    But even after making them same, I am getting the same error.

    The moment I GRANT the SELECT permission on the "dbo" schema to "SecuredRole"....things works as expected but this is not what I want.

    Could you please help me on this further?


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Thursday, April 9, 2015 10:13 AM
  • Hello Andreas,

    Thanks for pointing out this - "making the owner of the schema same"

    It was initially not.

    But even after making them same, I am getting the same error.

    The moment I GRANT the SELECT permission on the "dbo" schema to "SecuredRole"....things works as expected but this is not what I want.

    ...

    If the owner of the schema is the same, then I can only imagine 2 things right now:

    • dynamic SQL
    • or the owner of the table itself being different from the schema owner (rarely the case). You can check the latter via sys.tables.

    Maybe you can show us the output of

    SELECT principal_id from sys.schemas
        where name IN ('dbo', 'secured')
    
    SELECT principal_id, schema_id from sys.tables
        WHERE name = 'YourTable'
    
    SELECT principal_id, schema_id from sys.procedures
            WHERE name = 'YourProc'


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Thursday, April 9, 2015 10:36 AM
  • Hey, I got it working.

    I missed as ";" after the EXECUTE AS USER = '' statement

    The moment I added it, things have started working as expected.

    Thanks again!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Thursday, April 9, 2015 10:49 AM
  • Hey, I got it working.

    I missed as ";" after the EXECUTE AS USER = '' statement

    The moment I added it, things have started working as expected.

    ...

    A missing semicolon after execute as led to that problem?

    Wow! I'd be curious to see that part of the code to understand why that can happen.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Thursday, April 9, 2015 10:55 AM
  • EXECUTE AS USER = 'UserName';
    SELECT * FROM [Secured].[vw_Departments];
    REVERT;

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Thursday, April 9, 2015 11:05 AM