What Is a View? - Firebird

At its simplest, a view is a table specification that stores no data. It acts as a filter on both the columns and the rows of the underlying tables referenced in the view—a “window” through which actual data is exposed. The query that defines the view can be from one or more tables or other views in the current database. It behaves in many ways like a persistent table and encapsulates some special extensions to link it with the underlying tables.

You query a view as if it were an ordinary table and perform joins, order and group output, specify search conditions, subquery it, derive runtime columns from its virtual data, process a named or unnamed cursor selected from it, and so on.

Many views can be “updated,” thereby modifying the state of the underlying persistent tables, or they can be made updatable through triggers. When changes to the tables’ data are committed, the data content of the view changes with them. When a view’s data changes are committed, underlying tables’ data changes accordingly.

Keys and Indexes

Views cannot have keys or indexes. The underlying tables, known as base tables, will be used as the sources of indexes when the optimizer constructs query plans. The topic of query plans for queries involving views is quite complicated. It is discussed later in this chapter, in the section “Using Query Plans for Views.”

Row Ordering and Grouping

A view definition cannot be ordered. An exception is thrown if an ORDER BY clause is included. Consequently, it does not make sense to use the FIRST and/or SKIP quantifiers for SELECT, since they operate on ordered sets. A grouped query specification (using a legal GROUP BY clause) is fine.

Some Simple View Specifications

A view can be created from virtually any SELECT query specification. Examples are presented in the following sections.

A Vertical Subset of Columns froma Single Table

The JOB table in the employee.fdb database has eight columns: JOB _CODE, JOB _GRADE, JOB _COUNTRY, JOB _TITLE, MIN _SALARY, MAX _SALARY, JOB _REQUIREMENT, and LANGUAGE_REQ.

The following view returns a list of salary ranges (subset of columns) for all jobs (all rows) in the JOB table:

A Horizontal Subset of Rows from a Single Table

The next view returns all of the columns in the JOB table, but only the subset of rows where the MAX_SALARY is less than $15,000:

A Combined Vertical and Horizontal Subset

This view returns only the JOB_CODE and JOB_TITLE columns and only those jobs where MAX_SALARY is less than $15,000:

A Subset of Rows and Columns from Multiple Tables

The next example shows a view that joins the JOB and EMPLOYEE tables. EMPLOYEE contains 11 columns: EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY, and FULL_NAME. It returns two columns from the JOB table and two columns from EMPLOYEE, filtering so that records for workers whose salary is $15,000 or more are suppressed:

Why Views Can Be Useful

The data requirements of an individual user or user group are often quite consistent. Views provide the means to create custom versions of the underlying tables to target clusters of data that are pertinent to specific users and their tasks. The following list summarizes the benefits of views:

• Simplified, reuseable data access paths: Views enable you to encapsulate a subset of data from one or more tables to use as a foundation for future queries.
• Customized access to the data: Views provide a way to tailor the database output so that it is task oriented, suits the specific skills and requirements of users, and reduces the volume of data moving across networks.
• Data independence: Views can shield user applications from the effects of changes to database structure. For example, if the DBA decides to split one table into two, a view can be created that joins the two new tables. Applications can continue to query the view as if it were still a single, persistent table.
• Data security: Views enable access to sensitive or irrelevant portions of tables to be restricted. For example, a user might be able to look up job information through a view over an Employee table, without seeing associated salary information.

Privileges

Because a view is a database object, it requires specific user privileges in order to be accessed. Through granting privileges to a view, it is possible to provide users with very fine-grained access to certain columns and rows from tables, while denying them access to other, more sensitive data stored in the underlying tables. In that case, the view is granted privileges to the tables and the users are granted privileges to the view.

Owner Privileges

The user that creates a view will be its owner. In order to create the view, the user must have the appropriate privileges on the base tables:

• Some views are read-only by nature (see the section “Read-Only and Updatable Views”). To create a read-only view, the creator needs SELECT privileges for any underlying tables.
• For views that are updatable, the creator needs ALL privileges to the underlying tables.

Additionally, the owners of the base tables and other views accessed by the view must grant all required privileges for accessing those objects and for modifying them through the view if required, to the view itself. That is, privileges on those base objects must be granted to the view.

The owner of the view has all privileges for it, including the ability to grant privileges on it to other users, to triggers, and to stored procedures.

User Privileges

The creator of the view must grant the appropriate privileges to users, stored procedures, and other views that need to access the view. A user can be granted privileges to a view without having access to its base tables.

In the case of updatable views, INSERT, UPDATE, and DELETE privileges must be assigned to any users who need to perform DML on underlying tables through the view. Conversely, grant the users only SELECT privileges if your intention is to provide a read-only view.

If a user already has the required rights on the view ’s base objects, it will automatically have the same rights on the view.