Transact-SQL User's Guide

Home

Chapter 9 Views: Limiting Access to
Data

Transact-SQL User's Guide

Chapter 9 Views: Limiting Access to Data

How views work

A view is an alternative way of looking at the data in one or more tables.

For example, suppose you are working on a project that is specific to the state of Utah. You can create a view that lists only the authors who live in Utah:

create view authors_ut
as select * from authors
where state = "UT"

To display the authors_ut view, enter:

select * from authors_ut

When the authors who live in Utah are added to or removed from the authors table, the authors_ut view reflects the updated authors table.

A view is derived from one or more real tables whose data is physically stored in the database. The tables from which a view is derived are called its base tables or underlying tables. A view can also be derived from another view.

The definition of a view, in terms of the base tables from which it is derived, is stored in the database. No separate copies of data are associated with this stored definition. The data that you view is stored in the underlying tables.

A view looks exactly like any other database table. You can display it and operate on it almost exactly as you can any other table. There are no restrictions at all on querying through views and fewer than usual on modifying them. The exceptions are explained later in this chapter.

When you modify the data in a view, you are actually changing the data in the underlying base tables. Conversely, changes to data in the underlying base tables are automatically reflected in the views that are derived from them.

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.

Security

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.

Logical data independence

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.

View examples

The first example is a view derived from the titles table. Suppose you are interested only in books priced higher than $15 and for which an advance of more than $5000 was paid. This straightforward select statement finds the rows that qualify:

select * 
from titles 
where price > $15 
  and advance > $5000

Now, suppose you have a lot of retrieval and update operations to do on this collection of data. You could, of course, combine the conditions shown in the previous query with any command that you issue. However, for convenience, you can create a view that displays only the records of interest:

create view hiprice 
as select * 
from titles 
where price > $15 
  and advance > $5000

When Adaptive Server receives this command, it does not actually execute the select statement that follows the keyword as. Instead, it stores the select statement, which is the definition of the view hiprice, in the system table syscomments. Entries are also made in sysobjects and in syscolumns for each column included in the view.

Now, when you display or operate on hiprice, Adaptive Server combines your statement with the stored definition of hiprice. For example, you can change all the prices in hiprice just as you can change any other table:

update hiprice 
set price = price * 2

Adaptive Server finds the view definition in the system tables and converts the update command into the statement:

update titles 
set price = price * 2 
where price > $15 
  and advance > $5000

In other words, Adaptive Server knows from the view definition that the data to be updated is in titles. It also knows that it should increase the prices only in the rows that meet the conditions on the price and advance columns given in the view definition and those in the update statement.

Having issued the update to hiprice, you can see its effect either in the view or in the titles table. Conversely, if you had created the view and then issued the second update statement, which operates directly on the base table, the changed prices would also be visible through the view.

Updating a view's underlying table in such a way that different rows qualify for the view affects the view. For example, suppose you increase the price of the book You Can Combat Computer Stress to $25.95. Since this book now meets the qualifying conditions in the view definition statement, it is considered part of the view.

However, if you alter the structure of a view's underlying table by adding columns, the new columns will not appear in a view that is defined with a select * clause unless the view is dropped and redefined. This is because the asterisk in the original view definition considers only the original columns.


	Expand Search