SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 317

Using SQL Views for Isolation, Multiple Permissions,
and Multiple Triggers
SQL Views have three other important uses. First, they can isolate source data tables from
application code. To see how, suppose we define the view:

/* *** SQL-CREATE-VIEW-CH07-08 *** */
CREATE VIEW CustomerTableBasicDataView AS
SELECT *
FROM CUSTOMER;

This view assigns the alias CustomerTableBasicDataView to the CUSTOMER table, and
when we query this view, the result, as expected, is the data in the CUSTOMER table itself. If
all application code uses the CustomerTableBasicDataView as the data source in SQL state-
ments, then the true source of the data is hidden from application programmers.

/* *** SQL-Query-View-CH07-08 *** */
SELECT *

FROM CustomerTableBasicDataView;

Such table isolation provides flexibility to the database administration staff. For example,
suppose that at some future date the source of customer data is changed to a different table
(perhaps one that is imported from a different database) named NEW_CUSTOMER. In this
situation, all the database administrator needs to do is redefine CustomerTableBasicDataView
using the SQL ALTER VIEW statement, as follows:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-ALTER-VIEW-CH07-08 *** */
ALTER VIEW CustomerTableBasicDataView AS
SELECT *
FROM NEW_CUSTOMER;

All of the application code that uses CustomerTableBasicDataView will now run on the new
data source without any problem.
The second important use for SQL views is to give different sets of processing permissions
to the same table. We will discuss security in more detail in Chapters 9, 10, 10A, 10B, and 10C,
but for now understand that it is possible to limit insert, update, delete, and read permissions
on tables and views.
For example, an organization might define a view of CUSTOMER called
CustomerTableReadView with read-only permissions on CUSTOMER and a second view of
CUSTOMER called CustomerTableUpdateView with both read and update permissions.
Applications that need not update the customer data would work with CustomerTableReadView,
whereas those that need to update these data would work with CustomerTableUpdateView.
The final use of SQL views is to enable the definition of multiple sets of triggers on the
same data source. This technique is commonly used for enforcing O-M and M-M relationships.