Update-able Views

From InterBase
Jump to: navigation, search

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.

Advance To: