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 Chapter 17, “Managing User Permissions,” in the System Administration Guide: Volume 1.
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, 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. However, this view hides 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.
