14.7. Privileges Required for Views

To create a view, you must have the CREATE VIEW privilege for it, and you must have sufficient privileges for accessing the tables to which the view definition refers:

• For each table column used in the view column list (that is, selected by the view SELECT statement), you must have some privilege for accessing the column, such as SELECT, INSERT, or UPDATE.

• For columns accessed elsewhere in the statement, such as in a WHERE or GROUP BY clause, you must have the SELECT privilege.

For example, to create the following view on the Country table, you must have some privilege for its Code and Name columns, and the SELECT privilege for its Continent column:

 CREATE VIEW SACountry AS SELECT Code, Name FROM Country WHERE Continent = 'South America'; 

To use the OR REPLACE clause in a CREATE VIEW statement or to alter a view with ALTER VIEW, you must have the DROP privilege for the view in addition to the privileges required to create the view.

The DROP VIEW statement requires the DROP privilege for the view.

To access existing views, the privileges required are much like those for tables. To select from a view, you must have the SELECT privilege for it. For an updatable view, to use INSERT, DELETE, or UPDATE, you must have the respective INSERT, DELETE, or UPDATE privilege.

Privileges for a view apply to the view, not to the underlying tables. Suppose that you have the UPDATE privilege for an updatable view. That enables you to update the underlying table by using the view, but not to update the table directly. For that, you must have the UPDATE privilege for the table itself.

The SHOW CREATE VIEW statement requires the SELECT privilege for the view. If the view definition refers to tables for which you have no privileges, you must also have the SHOW VIEW privilege in addition to SELECT.