Using Views as Security Mechanisms
Views can serve as security mechanisms by restricting the data available to users. Some data can be accessible to users for query and modification, while the rest of the table or database is invisible and inaccessible. 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 granting permissions selectively on them, users, groups, or roles can be restricted to different subsets of data. For example:
- Access can be restricted to a subset of the rows of a base table. For example, 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, 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, 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, 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
Use the ALTER VIEW Transact-SQL statement 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 by using the same column name but a different underlying column in a table results in the same permissions for the new column. This example assumes 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.