This document introduces and describes Spanner views.

Overview

Aview is a virtual table defined by a SQL query. When you create a view, youspecify the SQL query it represents. Once you have created a view, you canexecute queries that refer to the view as though it were a table.

When a query that refers to a view is executed, Spanner createsthe virtual table by executing the query defined in the view, and that virtualtable's content is used by the referring query.

Because the query defining a view is executed every time a query referring tothe view is executed, views are sometimes called logical views ordynamic views to distinguish them from SQL materialized views, which storethe results of the query defining the view as an actual table in data storage.

In Spanner, you can create a view as either aninvoker's rightsview or adefiner's rights view. They are the two types of security modelscontrolling access to a view for users.

Benefits of views

Views offer several benefits over including the queries they define in theapplication logic.

• Views can provide logical data-modeling to applications.

  Sometimes the choices that make sense for physical data-modeling onSpanner are not the best abstraction for applications readingthat data. A view can present an alternate table schema that is a moreappropriate abstraction for applications.

• Views centralize query definitions and so simplify maintenance.

  By creating views for widely used or complex queries, you can factor querytext out of applications and centralize it. Doing so makes keeping query textup-to-date across applications much simpler and permits revision and tuning ofqueries without requiring application code to change.

• Views provide stability across schema changes.

  Because the query that defines a view is stored in the database schema insteadof in application logic, Spanner can and does ensure thatschema changes to the objects (tables, columns and so on) the query refers todo not invalidate the query.

Common use cases

Use views when your Spanner database includes highly privilegeddata that shouldn't be exposed to all database users or if you want toencapsulate your data.

If your view doesn't need additional security functionality and all invokersof the view have access to all schema objects that the view references, createan invoker's rights view.

If you want to create a view where not all invokers have access to all schemaobjects that the view references, create a definer's rights view. Definer'srights views are better protected and have more restrictions because thedatabase admin can provide fewer users with privileges on the tables andcolumns referenced in the view. Definer's rights views are useful when a userneeds a way to securely access a relevant subset of a Spannerdatabase. For example, you might want to create a definer's rights view for thefollowing data:

• Personal account data (e.g., application customer).
• Role specific data (e.g., HR personnel, sales associate).
• Location specific data.

Invoker's rights views

If a view has invoker's rights, it means that when a user, the invoker, executesa query against the view, Spanner checks the user's privilegeson the view and on all the schema objects that the view references. The usermust have privileges on all schema objects to query the view.

Definer's rights views

A definer's rights view adds additional security functionality to the view. Itprovides different privileges on the view and the underlying schema objects.Like for invoker's rights views, users must have database-level permissions tocreate definer's rights views. The main difference is that when a database rolequeries a definer's rights view, Spanner verifies thatthe role has access to the view itself (and only the view). Therefore,even if the user who queries the view doesn't have access to all the underlyingschema objects, they can access the view and see its contents. Definer's rightsviews give users access to fresh data, limited to the rows defined in the view.

Spanner Identity and Access Management (IAM) permissions are granted at thedatabase level. Usefine-grained access controlalongside definer's rights view, otherwise the definer's rights view doesn't addany additional access control. This is because if the user has read permissionson the database, they have read permissions on all schema objects in thedatabase. After you configure fine-grained access control on your database,fine-grained access control users with theSELECT privilege on the view andusers with database-level permissions on the database can query the view. Thedifference is that the fine-grained access control user doesn't need privilegeson the underlying objects.

Limitations of views

Views have limitations compared to actual tables that make them inappropriatefor certain use cases.

• Views are read-only. They cannot be used to add, update or delete data.

  You cannot use views in DML statements (INSERT,UPDATE,DELETE).

• The query that defines a view cannot use query parameters.

• Views cannot be indexed.

• References to views cannot usetable hints.

  However, the query that defines a view can include table hints on the tablesit refers to.

• Views are not supported by theReadAPI.

• Definer's rights views are not supported with SpannerData Boost.

  Running a query that contains a definer's rights view in Data Boost results inan error.

• The recommendedquery mode foraccessing a definer's rights view isNORMAL mode.

  Users who don't have access to the underlying schema objects of a definer'srights view receive an error when querying in a query mode other than normal.

• It's possible for a user to create a carefully crafted query that causesSpanner to throw an error that shows or reveals the existenceof data that is not available in the definer's rights view.

  For example, assume there is the following view QualifiedStudentScores whichreturns scores of students who qualify for a course. The criteria forqualifying is based on the level and exam score of the student. If thestudent's level is equal or lower than six, the score matters, and the studenthas to get at least 50 points on the exam to qualify. Otherwise, for levelsequal or greater than six, the student qualifies by default.

  CREATEVIEWQualifiedStudentScoresSQLSECURITYDEFINERASSELECTs.Name,s.Level,sc.ScoreFROMStudentsASsJOINScoresASscONsc.StudentId=s.StudentIdWHERE(CASEWHEN(s.Level <6)OR(s.Level>=6ANDsc.Score>=50)THEN'QUALIFIED';ELSE'FAILED';END)='QUALIFIED';

  A user can run a query in the form ofSELECT * FROM QualifiedStudentScores s WHERE s.Level = 7 AND 1/(s.Score - 20) = 1;.This query might fail with a division by zero error if there is a student inlevel 7 who got a score of 20 points, even though the view limits data to 50points and above for that level.

Query performance when using views

A query that refers to a view performs comparably to that same query with itsview reference replaced by the view's definition.

Quotas and limits that apply to views

• TheQuotas & limits page lists quota and limitinformation specifically for views.

• Using a view in a query can affect that query's conformance toquery limits because the view's definitionbecomes part of the query.

Cost impact

Using views has a very small impact on the cost of an instance:

• Using views has no impact on the compute capacity needs of aninstance, as compared to embedding their defined query text in queries thatrefer to them.

• Using views has very small impact on the database storage of an instancebecause the table generated by executing a view's query definition is notsaved to persistent database storage.

What's next

• Learn how toCreate and manage views.