Update-able Views
Go Up to Granting Access to Views
You can assign SELECT
, UPDATE
, INSERT
, and DELETE
privileges to update-able views, just as you can to tables. UPDATES
, INSERTS
, and DELETES
to a view are made to the view’s base tables. You cannot assign REFERENCES
privileges to a view.
The syntax for granting privileges to a view is:
GRANT <privileges>
ON viewname
TO {<object>
| <userlist> | GROUP UNIX_group};
<privileges> = ALL [PRIVILEGES] | <privilege_list>
<privilege_list> = {
SELECT
| DELETE
| INSERT
| UPDATE [(col [, col …])]
}
[, <privilege_list> …]
<object> = {
PROCEDURE procname
| TRIGGER trigname
| VIEW viewname
| PUBLIC
}
[, <object> …]
<userlist> = {
[USER] username
| rolename
| UNIX_user
}
[, <userlist> …]
[WITH GRANT OPTION]
When a view is based on a single table, data changes are made directly to the view’s underlying base table.
For UPDATE
, changes to the view affect only the base table columns selected through the view. Values in other columns are invisible to the view and its users and are never changed. Views created using the WITH CHECK OPTION
integrity constraint can be updated only if the UPDATE
statement fulfills the constraint’s requirements.
For DELETE
, removing a row from the view, and therefore from the base table removes all columns of the row, even those not visible to the view. If SQL integrity constraints or triggers exist for any column in the underlying table and the deletion of the row violates any of those constraints or trigger conditions, the DELETE
statement fails.
For INSERT
, adding a row to the view necessarily adds a row with all columns to the base table, including those not visible to the view. Inserting a row into a view succeeds only when:
- Data being inserted into the columns visible to the view meet all existing integrity constraints and trigger conditions for those columns.
- All other columns of the base table are allowed to contain
NULL
values.
For more information about working with views, see Working with Views.