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 table or database is neither visible nor accessible. Permission to access the subset of data in a view must be granted, denied, or revoked, regardless of the set of permissions in force on the underlying table(s).

For example, the salary column in a table contains confidential employee information, but the rest of the columns contain information that should be available to all users. You can define a view that includes all of the columns in the table with the exception of the sensitive salary column. As long as table and view have the same owner, granting SELECT permissions on the view allows the user to see nonconfidential columns in the view without having any permissions on the table itself.

By defining different views and selectively granting permissions on them, users, groups, or roles 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. For example, you might define a view that contains only rows for business and psychology books and keep information about other types of books hidden from users.
• Access can be restricted to a subset of the columns of a base table. For example, you might define a view that contains all the rows of the titles table but omits the royalty and advance columns because 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 tables to display the names of authors and books they have written. This view hides 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.

Permissions and ALTER VIEW

The ALTER VIEW Transact-SQL statement can be used to change the definition of a view without having to drop the view and reapply permissions. Any permissions applied to a column in the view are based on the column name defined in the view, rather than the underlying column in the table. Therefore, changing the definition of the view with ALTER VIEW, using the same column name, but a different underlying column in a table, results in the same permissions for the new column. For example, assuming the user Fred exists in the pubs database:

USE pubs

GO

CREATE VIEW v1 AS SELECT title_id, title FROM titles

GO

GRANT SELECT(title_id) ON v1 TO Fred

GO

ALTER VIEW v1 AS SELECT qty AS 'title_id' FROM sales

GO

Although the view is altered so that the title_id column name refers to the qty column in the sales table, rather than the title_id column in the titles table, the SELECT permissions granted to Fred on the title_id column name still apply.

See Also