Through a view, users can query and modify only the data they can see. The rest of the database is invisable and inaccessible.
Use the grant and revoke commands to restrict each user’s access to the database 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, you can restrict access 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.
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.
A row-and-column subset of a base table.
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.
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.
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.
