Granting Row-Level Permissions in SQL Server (ADO.NET)
In some scenarios, there is a requirement to control access at a more granular level than that allowed by simply granting, revoking, or denying permissions on the data. For example, a hospital database application may store patient information in a single table. Doctors may need to be restricted to viewing information related to their own patients. Similar scenarios exist in many environments, including finance, law, government, and military applications. However, SQL Server does not have support for implementing row-level security. You must create additional columns in your tables that define row filtering mechanisms.
Implementing Row-level Permissions
Row-level permissions are used for applications that store information in a single table. Each row has a column that defines a differentiating parameter, such as a user name, label or other identifier. You then create parameterized stored procedures, passing in the appropriate value. Users can see only rows that match the supplied value.
The following steps describe how to configure row-level permissions based on a user or login name.
Create the table, adding an additional column to store the name.
Create a view that has a WHERE clause based on the user name column. This will restrict the rows returned to those with the specified value. Use one of the built-in functions to specify a database user or login name. This eliminates the need to create different views for different users.
' Returns the login identification name of the user.
WHERE UserName = SUSER_SNAME()
' USER_NAME or CURRENT_USER Return the database user name.
WHERE UserName = CURRENT_USER()
Create stored procedures to select, insert, update, and delete data based on the view, not the base tables. The view provides a filter that restricts the rows returned or modified.
For stored procedures that insert data, capture the user name using the same function specified in the WHERE clause of the view and insert that value into the UserName column.
Deny all permissions on the tables and views to the public role. Users will not be able to inherit permissions from other database roles, because the WHERE clause is based on user or login names, not on roles.
Grant EXECUTE on the stored procedures to database roles. Users can only access data through the stored procedures provided.
External Resources
For more information, see the following resource.
Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005 on the SQL Server TechCenter site. |
Describes how to use row- and cell-level security to meet classified database security requirements. |
See Also
Concepts
Application Security Scenarios in SQL Server (ADO.NET)
Managing Permissions with Stored Procedures in SQL Server (ADO.NET)
Writing Secure Dynamic SQL in SQL Server (ADO.NET)