Privileges on a View

You must have at least the Select privilege on a table or columns to create a view on that table. For views that reference only tables in the current database, if the owner of a view loses the Select privilege on any base table underlying the view, the view is dropped.

You have the same privileges for the view that you have for the table or tables contributing data to the view. For example, if you create a view from a table to which you have only Select privileges, you can select data from your view but you cannot delete or update data. For information on how to create a view, see CREATE VIEW statement.

When you create a view, PUBLIC does not automatically receive any privileges for a view that you create. Only you have access to table data through that view. Even users who have privileges on the base table of the view do not automatically receive privileges for the view.

You can grant (or revoke) privileges on a view only if you are the owner of the underlying base tables, or if you received these privileges on the base table with the right to grant them (specified by the WITH GRANT OPTION keywords). You must explicitly grant those privileges within your authority, because PUBLIC does not automatically receive any privileges on a view when it is created.

The creator of a view can explicitly grant Select, Insert, Delete, and Update privileges for the view to other users or to a role. You cannot grant Index, Alter, Under, or References privileges on a view (nor can you specify the ALL keyword for views, because ALL confers Index, References, and Alter privileges).

When a GRANT or REVOKE statement changes the discretionary access privileges on any table referenced in the definition of an existing view, the database server does not automatically apply those privilege modifications to the view. To apply the new table access privileges to a view that depends on that table, you can use the DROP VIEW and CREATE VIEW statements to drop and recreate the view.

In this case, if the definitions of other views reference the view that you drop, or if INSTEAD OF triggers are defined on that view, you can also use CREATE VIEW and CREATE TRIGGER statements to recreate, respectively, the dependent views and the INSTEAD OF triggers that the DROP VIEW statement destroyed.