Views as Security Mechanisms

Views can serve as security mechanisms. Through a view, users can query and modify only the data they can see. The rest of the database is neither visible nor accessible.

Permission to access the subset of data in a view must be granted or revoked, regardless of the set of permissions in force on the view's underlying tables. Data in an underlying table that is not included in the view is hidden from users who are authorized to access the view but not the underlying table.

By defining different views and selectively granting permissions on them, a user (or any combination of users) can be restricted to different subsets of data. The following examples illustrate the use of views for security purposes:

• Access can be restricted to a subset of the rows of a base table (a value-dependent subset). For example, you might define a view that contains only the rows for business and psychology books and keep information about other types of books hidden from some users.
• Access can be restricted to a subset of the columns of a base table (a value-
  independent subset). For example, you might define a view that contains all the rows of the titles table but omits the royalty and advance columns, since this information is sensitive.
• Access can be restricted to a row-and-column subset of a base table.
• Access can be restricted to the rows that qualify for a join of more than one base table. For example, you might define a view that joins the titles, authors, and titleauthor table to display the names of the authors and the books they have written. This view would hide personal data about the authors and financial information about the books.
• Access can be restricted to a statistical summary of data in a base table. For example, you might define a view that contains only the average price of each type of book.
• Access can be restricted to a subset of another view or of some combination of views and base tables.

For information about creating views, see the Microsoft SQL Server Transact-SQL Reference.

As an example, say you want to prevent some users from accessing the columns in the titles table that have to do with money and sales, while allowing all users to see the other columns. You could:

1. Create a view – named bookview – of the titles table that omits the price, advance, royalty, and ytd_sales columns.
2. Grant all object permissions on bookview to the public group.
3. Grant all object permissions on titles to the sales group.

To set up equivalent permission conditions without using a view, you could:

1. Grant all object permissions on titles to public.
2. Revoke select and update permissions on titles from the public group for the price, advance, royalty, and ytd_sales columns
3. Grant select and update permissions on titles to the sales group for the price, advance, royalty, and ytd_sales columns.

One possible problem with the second scheme is that users not in the sales group who enter the statement "SELECT * FROM titles" might be surprised to see a "permission denied" message. SQL Server expands the asterisk into a list of all the columns in the titles table and, since permission on some of these columns has been revoked from nonsales users, refuses access to them. The error message lists the columns for which the user does not have access.

To see all the columns for which they do have permission, the nonsales users would have to name the columns specifically. For this reason, creating a view and granting the appropriate permissions on it is a better solution.

In addition to protecting data based on a selection of rows and/or columns, views can be used for context-sensitive permission. For example, you can create a view that gives a data entry clerk permission to access only those rows that he or she has added or updated. You could:

1. Add a user_-name column to a table in which the login ID of the user entering each row will be automatically recorded with a default.
2. Define a view that includes all the rows of the table where user_name is the current user, which can be checked with the SUSER_NAME() function. For example:

   where user_name = SUSER_NAME() 
   

   The rows retrievable through the view now depend on the identity of the person who executes the SELECT statement against the view.

For information on managing and using tables, view, columns, and defaults and on using the user_id system function, see the Microsoft SQL Server Transact-SQL Reference. For information on managing permissions, see Chapter 9, Managing Security.