Security

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:

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.