Home

	Expand Search

Transact-SQL User's Guide

Chapter 9 Views: Limiting Access to Data

How views work

Advantages of views

Advantages of views

You can use views to focus, simplify, and customize each user's perception of the database; they also provide an easy-to-use security measure. Views can also be helpful when changes have been made to the structure of a database, but users prefer to work with the structure of the database they are accustomed to.

You can use views to:

• Focus on the data that interests them and on the tasks for which they are responsible. Data that is not of interest to a user can be left out of the view.

• Define frequently used joins, projections, and selections as views so that users do not have to specify all the conditions and qualifications each time an operation is performed on that data.

• Display different data for different users, even when they are using the same data at the same time. This advantage is particularly important when users of many different interests and skill levels share the same database.

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

With the grant and revoke commands, each user's access to the database can be restricted to specified database objects--including views. If a view and all the tables and other views from which it is derived are owned by the same user, that user can grant permission to others to use the view while denying permission to use its underlying tables and views. This is a simple but effective security mechanism. See the System Administration Guide for details on the grant and revoke commands.

By defining different views and selectively granting permissions on them, users can be restricted to different subsets of data. For example:

• Access can be restricted to a subset of the rows of a base table, that is, a value-dependent subset. For example, you might define a view that contains only the rows for business and psychology books, in order to 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, that is, a value-independent subset. For example, you might define a view that contains all the rows of the titles table, except the royalty and advance columns.

• 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 to display the names of the authors and the books they have written. This view would hide personal data about authors and financial information about the books.

• Access can be restricted to a statistical summary of data in a base table. For example, through the view category_price a user can access only the average price of each type of book.

• Access can be restricted to a subset of another view or a combination of views and base tables. For example, through the view hiprice_computer a user can access the title and price of computer books that meet the qualifications in the view definition of hiprice.

To create a view, a user must be granted create view permission by the Database Owner, and must have appropriate permissions on any tables or views referenced in the view definition.

If a view references objects in different databases, users of the view must be valid users or guests in each of the databases.

If you own an object on which other users have created views, you must be aware of who can see what data through what views. For example: the Database Owner has granted "harold" create view permission, and "maude" has granted "harold" permission to select from a table she owns. Given these permissions, "harold" can create a view that selects all columns and rows from the table owned by "maude." If "maude" revokes permission for "harold" to select from her table, he can still look at her data through the view he has created.

Views can shield users from changes in the structure of the real tables if such changes become necessary.

For example, suppose you restructure the database by using select into to split the titles table into these two new base tables and then dropping the titles table:

titletext (title_id, title, type, notes)

titlenumbers (title_id, pub_id, price, advance, royalty, total_sales, pub_date)

The old titles table can be "regenerated" by joining on the title_id columns of the two new tables. You can create a view that is a join of the two new tables. You can even name it titles.

Any query or stored procedure that previously referred to the base table titles now refers to the view titles. As far as the users are concerned, select operations work exactly as before. Users who retrieve only from the new view need not even know that the restructuring has occurred.

Unfortunately, views provide only partial logical independence. Some data modification statements on the new titles are not allowed because of certain restrictions.