Implementing row level permissions/security in SQL Server databases: Narayana Vyas Kondreddi's homepage

Implementing row level security in SQL Server databases
Last updated: December 15th '01 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

Database encryption for SQL Server and MSDE:
ActiveCrypt.com

The following questions often get asked in the Microsoft SQL Server newsgroups:

"SQL Server lets you configure permissions at server level, database level, object level and even at column level. But what about row level security?"

"Oracle has a feature called "Virtual Private Databases" (VPD), that enables you to configure permissions at row level. Is there an equivalent feature in SQL Server?"

"I host a database application for different clients. Instead of creating a separate database for every client, can I have a single database, with only one set of tables to store all clients' information and let each client access only his/her data?"

These questions rarely get answered, and the most common solution suggested in the newsgroups is, "Go ahead and create different views for your users". This leads to the obvious question, "What if I have 250 users?"

As you can see, there is lack of information on this topic of "Implementing security at row level". So, I decided to propose a solution for this issue through this article. I hope it gives you a head start, if you are planning to implement row level security.

The solution that I am going to propose, assumes the following:

• Your database is either on SQL Server 7.0 or SQL Server 2000.
  
  
• All your users will have their own logins to connect to SQL Server.
  
  
• All the tables on which you want to implement row level security, must have an additional varchar column, to hold the user's login name (or any other differentiating parameter like host name, user name etc.).
  
  
• Data access is allowed only through stored procedures.

Let's get started with a scenario for implementing row level security. Consider a firm with 50 sales representatives. Primary job of these sales reps is to look for sales leads, talk to potential customers, gather information about customers, and save that information into the central SQL Server database. Each sales rep works with a particular region of the country. Now the restriction imposed by the IT manager is that, each sales rep will work only with his/her own data and should not be able to see, edit or delete the data gathered by other sales reps. He also insists that, all the data gathered by sales reps should be stored in a single table.

This is a perfect scenario for row level security implementation, for which there is no in-built support in SQL Server, at the time of writing this article. So, we need to come up with a home grown solution for this problem. Here are the steps involved in the implementation (Links to SQL scripts, that demonstrate this solution are available at the end of this article):

• Create your database, in this case, it is called 'SalesLeads' database.
  
  
• Create your tables. Make sure you add an additional column to the required tables, to store the sales rep's login name. In this case, the 'Leads' table, with an additional column 'LeadGeneratedBy'.
  
  
• Create a view named 'MyLeads', on the 'Leads' table. Make sure you have a WHERE clause in the view definition, so that the view returns only the relevant data to each sales rep. Here is how the WHERE clause of your view will look like:
  
  WHERE LeadGeneratedBy = SUSER_SNAME()
  
  The SUSER_SNAME() system function returns the currently logged in user's login name. If the first sales rep logs in with the login name 'SalesRep1' and inserts a row, his/her login name is stored along with the row. The SUSER_SNAME() function in the WHERE clause of the view definition makes sure 'Salesep1' see only those rows that have the 'LeadGeneratedBy' column set to 'SalesRep1'.
  
  This trick eliminates the need to create different views for different users. Whatever be the number of users you have, just one view will suffice.
  
  
• Provide data access to sales reps using stored procedures. Go ahead and create different stored procedures to show, insert, update and delete sales leads information. These stored procedures query and manipulate ONLY the view ('MyLeads' in this case), but not the base tables. Since the view is already filtering out the rows using SUSER_SNAME() function, you don't need to worry about security in your stored procedure code. Should your security implementation change, you will only modify one view, and not your stored procedures.
  
  In the INSERT stored procedure, capture the current user's login name using SUSER_SNAME() function and insert the resulting value into the 'LeadGeneratedBy' column.
  
  
• Now it's time to configure permissions. Go ahead and deny all permissions on the tables and views to 'public' role.
  
  Create a database role called 'SalesReps'. Grant execute permissions on show, insert, update and delete stored procedures only to 'SalesReps' database role.
  
  Create individual user logins and grant them access to the 'SalesLeads' database. Add these database users to the 'SalesReps' database role.
  
  As you can gather, these users don't have any permissions on the database tables or views. They can only access the data using the stored procedures provided. These stored procedures in-turn query the views. Views query the base tables and take care of security using SUSER_SNAME() function.
  
  With this setup in place, if 'SalesRep1' logs into the 'SalesLeads' database, he can only see rows inserted by him and can only modify or delete rows that were inserted by him. He will have no clue about the existence of other sales rep's data in the same table.
  
  In this particular example, I used login name to differentiate the sales reps. Apart from login name, you could also use host name for differentiating users. The built-in HOST_NAME() function comes in handy here, but the limitation of this approach is that, a user must always access the database from the same client computer. Merge replication uses this very same technique to implement dynamic filters in SQL Server 7.0 and 2000.
  
  Instead of using built-in system functions, you can create your own functions in SQL Server 2000, to extend this approach.

Click here todownload row_level.zip, that contains the scripts required to setup a working example of this approach. row_level.zip has the following individual SQL script files:

While working with 'SalesRep1.txt', 'SalesRep2.txt' and 'SalesRep3.txt', you will realize how the data is isolated from each other.

Note that, this approach will work with both NT (Integrated security) and SQL server logins or a combination of both (Mixed mode security).

To improve the data retrieval performance, consider adding am index on the login column, in this case 'LeadGeneratedBy'.

As we just saw, row level security can be implemented in SQL Server database, with a little bit of careful planning. I hope you find this article useful. Feel free to leave your comments here, or send an email with your suggestions or comments.