Granting Access to Views
Go Up to Planning Security
To a user, a view looks—and often acts—just like a table. However, there are significant differences: the contents of a view are not stored anywhere in the database. All that is stored is the query on the underlying base tables. Because of this, any UPDATE
, DELETE
, INSERT
to a view is actually a write to the table on which the view is based.
Any view that is based on a join or an aggregate is considered to be a read-only view, since it is not directly update-able. Views that are based on a single table which have no aggregates or reflexive joins are often update-able. See Types of Views: Read-only and Update-able for more information about this topic.
- Important: It is meaningful to grant
INSERT
,UPDATE
, andDELETE
privileges for a view only if the view is update-able. Although you can grant the privileges to a read-only view without receiving an error message, any actual write operation fails because the view is read-only.SELECT
privileges can be granted on a view just as they are on a table, since reading data from a view does not change anything.
You cannot assign REFERENCES
privileges to views.
- Tip: If you are creating a view for which you plan to grant
INSERT
andUPDATE
privileges, use theWITH CHECK OPTION
constraint so that users can update only base table rows that are accessible through the view.