Views

A method of restricting user access is by use of Views. These create what appears to the user to be a standard table which can be accessed normally with the use of SQL statements, and data added, modified or deleted. However, these views are actually the result of a query on the database and as such show the user what he is permitted to see.

The syntax of the statement to create a view is:

CREATE VIEW viewname [(column list)] AS SELECT statement [WITH CHECK OPTION];

Notes

1. If no column-list is given, the names of the columns within the SELECT statement will be used in the view. However, a column-list must be used if expressions or functions appear in the SELECT statement.

2. The CHECK OPTION clause will be used by the system whenever data is changed through the view (rather than through the underlying base table). Entries are checked to ensure that they conform with the SELECT statement and are rejected if they do not.

3. A view cannot be altered, once it has been created. However, it can be dropped, using the statement:

   DROP VIEW viewname;

There are many restrictions on what can be done with views. In particular, some views do not allow the user to insert, delete or update the data presented by the view. For example:

CREATE VIEW categ (category, no_films)
AS SELECT category, COUNT(*)
FROM film
GROUP BY category;

The data presented to the user cannot be associated with any one row in the base table so the user cannot make any changes to the data in this view simply by editing the view. The only way is by editing the base table.

CREATE VIEW outstanding (firstname, surname, title, date_rented)
AS SELECT firstname, surname, film.title, date_rented
FROM customer, film, rental
WHERE customer.number = customer_no
AND film.number = film_no
AND returned = 'N';

This view presents the customer's name, film title and date rented as if it were a single table. This view cannot be updated because the columns are drawn from more than one base table.

A view which restricts the user to a subset of the rows in a table could still allow a user to enter data into the base table which will not be part of the view. For example, the view below restricts the user to a list of films with a rating of 'U' or '12'. It does not contain the columns of rating, category or duration, but this does not matter as all three columns permit NULL values. Therefore, the base table of film may be updated through the view:

CREATE VIEW childrens AS
SELECT number, title, rent price, quantity
FROM film
WHERE rating IN ('U', '12');

Consider the following variation of the view. It contains the rating column in the SELECT clause and has the WITH CHECK OPTION:

CREATE VIEW childrens AS
SELECT number, title, rating, rent_price, quantity
FROM film
WHERE rating IN ('U', '12')
WITH CHECK OPTION;

The CHECK OPTION instructs the system to ensure that any modification made to the underlying table using the view will also satisfy the constraints defined by the view. Hence, although a row may be inserted or modified through the above view with a certificate rating of 'U' or '12', the insertion or modification will fail if the rating stated was any other value.

There are a number of restrictions on the definition of a view, most notably that the SELECT statement may not contain an ORDER BY clause, nor may it use UNION. [The UNION restriction has been removed on the later engines.