SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

INSERT, and ALL privileges provide the user with access to all columns in the table. To protect
your tables from access by unauthorized users, provide GRANT privileges to table views.
• Alterations to tables generally have a greater impact on application code that uses the tables
directly. For example, an application should not use SELECT * to retrieve values from a table,
because adding a column to the table would mean that the application must change.
Using Views
A view is a logical table derived from one or more tables. A view can include any SQL query from
one or more tables or other views. You can create more than one view on a table or combination
of tables.
Use views to externalize some or all of the data to the user.
A view can have the same structure as an underlying table, or the view can be different. Views
do not store data physically on the disk.
In general, views have these advantages and uses:
• To enable the apparent structure of retrieved data to be different from the actual stored data.
• To enable logical renaming of columns.
• To redefine headings for columns.
• To present only the columns and rows a user needs to work with instead of all the columns
and rows in underlying tables or views.
NOTE: A view does not insulate the programs that use it from being invalidated by changes to
the table definition. Even changes that do not directly affect the view require that the programs be
explicitly SQL compiled to be revalidated. The requirement for explicit compilation is the same as
if the programs used the table directly.
ANSI Views
SQL/MX views conform to the SQL:1999 standard and have these attributes:
• You can change the data in the underlying table through a view if the view is updatable. A
view that references more than one table is not updatable.
• A view can be secured separately from the underlying tables or views. Users of the view do
not need to have access privileges to these underlying objects to access the view.
Use updatable views to:
• Provide validity checks on the underlying table for inserts and updates.
• Provide restrictions so that only certain information can be presented to a user by masking
rows and columns of the underlying table from displays or updating. If an updatable view is
defined with the WITH CHECK option, through the view you can only insert or update rows
that the WHERE clause of the view query evaluates to true.
Using Only Views to Externalize Data
Experienced database administrators have observed that having applications use only views as
the external interface for the database scheme has certain advantages for both programs and ad
hoc queries.
Advantages include:
• The physical database structure of the tables is not externalized to programmers or other users.
• The database file layout can be normalized independent of a program or of a user’s
interpretation.
• New tables and views can be easily created and integrated with the existing scheme.
Determining a Database Layout 35