locked
SQL Server Row Level Security RRS feed

  • Question

  • Hi,

    We have around 200 tables in our database. The tables will have information for different companies.The tables are maintaining referential integrity using Primary and Foreign-Key relationships.

    If any person from one company tries to query the tables from our database, they should see the data related to their company only and not data for other companies.

    Is there anyway with the new Row Level Security feature in SQL 2016 that i can achieve this?I am thinking that, all the tables should have UserName field (USER_NAME() so that we can identify the person who is querying and then filter the results based on his Username.

    But,We don't have UserName column in any of our tables to identify who is querying the tables and there is no chance we can add this field in future too.

    Thanks!!


    • Edited by Leo00 Friday, October 21, 2016 1:59 PM
    Friday, October 21, 2016 1:42 PM

All replies

  • If there is no field in the table that allows you to determine the record owner, then you must be establishing that through a join with another table. (Yeah, I'm making some assumptions here.) Presenting the customer with a view that joins the tables and that has a where clause restricting the records to the customer's data, sounds to me like what you want.

    And you can place a security policy on a view if the view uses schema binding.

    (But of course, I don't know what kind of changes you can make in your environment.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, October 21, 2016 4:15 PM
  • In your 200 tables, how do you identify your data right now?

    A Fan of SSIS, SSRS and SSAS

    Friday, October 21, 2016 5:12 PM
  • There is a field in a table which has company names.

    This table ties back to the primary key table (Claim) and that's how we identify which company's data it is.

    And, ID from Claim table is referenced by few child tables.

    Friday, October 21, 2016 5:46 PM
  • Create a table SQLUses which contains the columns of CompanyID and UserName.

    A Fan of SSIS, SSRS and SSAS

    Friday, October 21, 2016 5:52 PM
  • Ok.

    Few Questions:

    1. There will be many people from one company who wants to query the tables. In this case what will the SQLUsers table look like? Will that look like below:

    SQLUserID	  CompanyID   UserName
    1			 1001    User1, User2, User3
    2			 1002    User5

    2. What if some users queries other than SQLUser, Claim and its referenced tables? Will that still restricts the data based on user trying to query?

    3. There is no one particular stored procedure, view or table they would like to query. They will connect to that database and can query anything they want Adhoc. How will this work?


    • Edited by Leo00 Friday, October 21, 2016 6:04 PM
    Friday, October 21, 2016 6:03 PM
  • You mean that the user is able to access to the tables via management studio?

    A Fan of SSIS, SSRS and SSAS

    Friday, October 21, 2016 6:34 PM
  • Yes, they have access now. We want to restrict them to see data only related to their company and not other company's data using Row level Security.
    Friday, October 21, 2016 6:37 PM
  • Take a look at the article Row-Level Security and follow the example "A. Scenario for users who authenticate to the database". It should help you with your situation.


    A Fan of SSIS, SSRS and SSAS

    Friday, October 21, 2016 7:32 PM
  • 1. There will be many people from one company who wants to query the tables. In this case what will the SQLUsers table look like? Will that look like below:


    SQLUserID       CompanyID   UserName
    1                1001    User1, User2, User3
    2                1002    User5

    No. In a relational database, you have one value per cell. You don't have comma-separated lists in table columns. I repeat that: You don't have comma-separated lists in table columns.
    I guess that you will have a users table which holds information about that users. Key is the username, and one of the attributes is the company the users belong to. (Or the only attribute, if you don't need to store any other information about them.)

    I would also recommend that you make sure that users cannot access the database through SSMS. If users can compose queries themselves, a skilled user can still wrestle out information they are not supposed to see even if you have row-level security in place.

    • Proposed as answer by Teige Gao Monday, October 24, 2016 12:14 PM
    Friday, October 21, 2016 9:31 PM
  • I have face same kind of issue, Try like below :-

    1) Create Table with 2 column (UserName, Company)

    2) Create View to select database from table which will filter data on basis of company assigned to user. Also user will be picked as the login user. So user has no choice of getting other companies data.

    Create PROC sp_data

    as

    Select * from TBL_CMP

    where cmpname (select Company from Table_in_Step_1 where UserName = CURRENT_USER )


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, October 24, 2016 5:46 AM