Create and manage views

This page describes how to create and manage Spanner views for GoogleSQL-dialect databases and PostgreSQL-dialect databases. For more information about Spanner views, see Views overview.

Permissions

To create, grant, and revoke access to a view, you must have the spanner.database.updateDdl permission.

Create a view

To create a view, use the DDL statement CREATE VIEW to name the view and provide the query that defines it. This statement has two forms:

• CREATE VIEW defines a new view in the current database. If a view named view_name already exists, the CREATE VIEW statement fails.

• CREATE OR REPLACE VIEW defines a new view in the current database. If a view named view_name already exists, its definition is replaced.

The syntax for the CREATE VIEW statement is:

{CREATE|CREATEORREPLACE}VIEWview_name
SQLSECURITY{INVOKER|DEFINER}
ASquery

Because a view is a virtual table, the query that you specify must provide names for all the columns in that virtual table.

Additionally, Spanner checks the query you specify using strict name resolution, meaning that all schema object names used in the query must be qualified such that they unambiguously identify a single schema object. For example, in the examples that follow the SingerId column in the Singers table must be qualified as Singers.SingerId.

You must specify the SQL SECURITY as either INVOKER or DEFINER in the CREATE VIEW or CREATE OR REPLACE VIEW statement. For more information about the difference between the two security types, see Views overview.

For example, assume the Singers table is defined as shown in the following:

CREATETABLESingers(
SingerIdINT64NOTNULL,
FirstNameSTRING(1024),
LastNameSTRING(1024),
SingerInfoBYTES(MAX)
)PRIMARYKEY(SingerId);

CREATETABLESingers(
SingerIdBIGINTPRIMARYKEY,
FirstNameVARCHAR(1024),
LastNameVARCHAR(1024),
SingerInfoBYTEA
);

You can define the SingerNames view with invoker's rights as shown in the following:

CREATEVIEWSingerNames
SQLSECURITYINVOKER
ASSELECT
Singers.SingerIdASSingerId,
Singers.FirstName||' '||Singers.LastNameASName
FROMSingers;

The virtual table created when the SingerNames view is used in a query has two columns, SingerId and Name.

While this definition of the SingerNames view is valid, it does not abide by the best practice of casting data types to ensure stability across schema changes, as described in the next section.

Best practices when creating views

To minimize the need to update a view's definition, explicitly cast the data type of all table columns in the query that defines the view. When you do so, the view's definition can remain valid across schema changes to a column's type.

For example, the following definition of the SingerNames view might become invalid as the result of changing a column's data type in the Singers table.

CREATEVIEWSingerNames
SQLSECURITYINVOKER
ASSELECT
Singers.SingerIdASSingerId,
Singers.FirstName||' '||Singers.LastNameASName
FROMSingers;

You can avoid the view becoming invalid by explicitly casting the columns to the needed data types, as shown in the following example:

CREATEORREPLACEVIEWSingerNames
SQLSECURITYINVOKER
ASSELECT
CAST(Singers.SingerIdASINT64)ASSingerId,
CAST(Singers.FirstNameASSTRING)||" "||CAST(Singers.LastNameASSTRING)ASName
FROMSingers;

CREATEORREPLACEVIEWSingerNames
SQLSECURITYINVOKER
ASSELECT
CAST(Singers.SingerIdASbigint)ASSingerId,
CAST(Singers.FirstNameASvarchar)||' '||CAST(Singers.LastNameASvarchar)ASName
FROMSingers;

Grant and revoke access to a view

As a fine-grained access control user, you must have the SELECT privilege on a view. To grant SELECT privilege on a view to a database role:

GRANTSELECTONVIEWSingerNamesTOROLEAnalyst;

GRANTSELECTONTABLESingerNamesTOAnalyst;

To revoke SELECT privilege on a view from a database role:

REVOKESELECTONVIEWSingerNamesFROMROLEAnalyst;

REVOKESELECTONTABLESingerNamesFROMAnalyst;

Query a view

The way to query an invoker's rights or a definer's rights view is the same. However, depending on the security type of the view, Spanner may or may not need to check the schema objects referenced in the view against the database role of the principal who invoked the query.

Query an invoker's rights view

If a view has invoker's rights, the user must have privileges on all underlying schema objects of the view in order to query it.

For example, if a database role has access to all objects referenced by the SingerNames view, they can query the SingerNames view:

SELECTCOUNT(SingerID)asSingerCount
FROMSingerNames;

Query a definer's rights view

If a view has definer's rights, a user can query the view without needing privileges on the underlying objects as long as you grant the required role the SELECT privilege on the view.

In the following example, a user with the Analyst database role wants to query the SingerNames view. However, the user is denied access because SingerNames is an invoker's rights view and the Analyst role does not have access to all the underlying objects. In this case, if you decide to provide the Analyst with access to the view, but don't want to provide them access to the Singers table, you can replace the security type of the view to definer's rights. After you replace the security type of the view, grant the Analyst role access to the view. The user can now query the SingerNames view even though they don't have access to the Singers table.

SELECTCOUNT(SingerID)asSingerCount
FROMSingerNames;

Replace a view

You can replace a view by using the CREATE OR REPLACE VIEW statement to change the view definition or the security type of the view.

Replacing a view is similar to dropping and recreating the view. Any access grants given to the initial view has to be granted again after replacing the view.

To replace an invoker's rights view with a definer's rights view:

CREATEORREPLACEVIEWSingerNames
SQLSECURITYDEFINER
ASSELECT
Singers.SingerIdASSingerId,
Singers.FirstName||' '||Singers.LastNameASName
FROMSingers;

Delete a view

After a view is dropped, database roles with privileges on it no longer have access. To delete a view, use the DROP VIEW statement.

DROPVIEWSingerNames;

Get information about a view

You can get information about views in a database by querying tables in its INFORMATION_SCHEMA schema.

• The INFORMATION_SCHEMA.TABLES table provides the names of all defined views.

• The INFORMATION_SCHEMA.VIEWS table provides the names, view definition, security type, and query text of all defined views. FGAC users who have SELECT privilege on the view can get information about the view from the INFORMATION_SCHEMA.VIEWS table. Other FGAC users need the spanner_info_reader role if they don't have SELECT privilege for the view.

To check the view definition and security type of a view called ProductSoldLastWeek:

SELECT*
FROMINFORMATION_SCHEMA.VIEWS
WHERETABLE_NAME='ProductSoldLastWeek';