建立及管理檢視畫面

本頁面說明如何為 GoogleSQL 方言資料庫和 PostgreSQL 方言資料庫建立及管理 Spanner 檢視區塊。如要進一步瞭解 Spanner 檢視表，請參閱「檢視表總覽」。

權限

如要建立、授予及撤銷檢視表的存取權，您必須具備 spanner.database.updateDdl 權限。

建立檢視表

如要建立檢視表，請使用 DDL 陳述式 CREATE VIEW 為檢視表命名，並提供定義檢視表的查詢。這項陳述式有兩種形式：

  • CREATE VIEW 會在目前的資料庫中定義新的檢視區塊。如果已有名為「view_name」的檢視區塊，CREATE VIEW 陳述式就會失敗。

  • CREATE OR REPLACE VIEW 會在目前的資料庫中定義新的檢視區塊。如果已有名稱為 view_name 的檢視區塊，系統會取代其定義。

CREATE VIEW 陳述式的語法如下：

{CREATE | CREATE OR REPLACE } VIEW  view_name
SQL SECURITY { INVOKER | DEFINER }
AS query

由於檢視表是虛擬資料表，您指定的 query 必須為該虛擬資料表中的所有資料欄提供名稱。

此外，Spanner 會使用嚴格名稱解析檢查您指定的 query，也就是查詢中使用的所有結構定義物件名稱都必須經過限定，明確識別單一結構定義物件。舉例來說，在下列範例中，Singers 資料表中的 SingerId 資料欄必須符合 Singers.SingerId 資格。

您必須在 CREATE VIEW 或 CREATE OR REPLACE VIEW 陳述式中，將 SQL SECURITY 指定為 INVOKER 或 DEFINER。如要進一步瞭解這兩種安全類型的差異，請參閱「檢視畫面總覽」。

舉例來說，假設 Singers 資料表的定義如下所示：

GoogleSQL 

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId);

PostgreSQL

CREATE TABLE Singers (
  SingerId   BIGINT PRIMARY KEY,
  FirstName  VARCHAR(1024),
  LastName   VARCHAR(1024),
  SingerInfo BYTEA
);

您可以定義具有呼叫端權限的 SingerNames 檢視畫面，如下所示：

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

在查詢中使用 SingerNames 檢視區塊時建立的虛擬資料表有兩個資料欄：SingerId 和 Name。

雖然這個 SingerNames 檢視區塊的定義有效，但並未遵守將資料型別轉換為確保結構定義變更穩定性的最佳做法，詳情請參閱下一節。

建立檢視區塊的最佳做法

為盡量減少更新檢視區塊定義的需求，請在定義檢視區塊的查詢中，明確轉換所有資料表資料欄的資料類型。這樣一來，即使資料欄類型發生結構定義變更，檢視區塊的定義仍可保持有效。

舉例來說，如果變更 Singers 資料表中的資料欄資料類型，下列 SingerNames 檢視區塊的定義可能會失效。

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

如要避免檢視區塊失效，可以明確將資料欄轉換為所需資料類型，如下列範例所示：

GoogleSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS INT64) AS SingerId,
 CAST(Singers.FirstName AS STRING) || " " || CAST(Singers.LastName AS STRING) AS Name
FROM Singers;

PostgreSQL

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS bigint) AS SingerId,
 CAST(Singers.FirstName AS varchar) || ' ' || CAST(Singers.LastName AS varchar) AS Name
FROM Singers;

授予及撤銷檢視區塊的存取權

精細存取權控管使用者必須擁有資料檢視的 SELECT 權限，如要將檢視表的 SELECT 權限授予資料庫角色，請按照下列步驟操作：

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL

GRANT SELECT ON TABLE SingerNames TO Analyst;

如要從資料庫角色撤銷檢視表的 SELECT 權限，請按照下列步驟操作：

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

查詢檢視區塊

查詢呼叫端權限或定義端權限檢視區塊的方式相同。 不過，視檢視區塊的安全類型而定，Spanner 可能需要或不需要根據叫用查詢的主體資料庫角色，檢查檢視區塊中參照的結構定義物件。

查詢呼叫者的權利檢視畫面

如果檢視區具有呼叫端權限，使用者必須對檢視區的所有基礎結構定義物件擁有權限，才能查詢檢視區。

舉例來說，如果資料庫角色有權存取 SingerNames 檢視畫面參照的所有物件，就能查詢 SingerNames 檢視畫面：

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

查詢定義者的權利檢視畫面

如果檢視區塊具有定義者權限，只要您授予必要角色檢視區塊的 SELECT 權限，使用者就能查詢檢視區塊，不必具備基礎物件的權限。

在下列範例中，具有「分析師」資料庫角色的使用者想要查詢 SingerNames 檢視區塊。不過，由於 SingerNames 是呼叫端權限檢視畫面，且分析師角色無法存取所有基礎物件，因此使用者遭到拒絕。在這種情況下，如果您決定授予分析人員檢視畫面的存取權，但不想授予他們 Singers 資料表存取權，可以將檢視畫面的安全性類型取代為定義者的權限。取代檢視區塊的安全類型後，請授予「分析師」角色檢視區塊的存取權。現在，即使使用者沒有 Singers 資料表的存取權，也能查詢 SingerNames 檢視區塊。

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

取代檢視畫面

您可以使用 CREATE OR REPLACE VIEW 陳述式變更檢視定義或檢視的安全類型，藉此取代檢視。

取代檢視區塊與捨棄並重新建立檢視區塊類似。更換檢視區塊後，必須重新授予初始檢視區塊的所有存取權。

如要將呼叫端權限檢視畫面替換為定義者權限檢視畫面，請按照下列步驟操作：

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY DEFINER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

刪除檢視表

檢視區塊捨棄後，具有檢視區塊權限的資料庫角色將無法再存取。如要刪除檢視區塊，請使用 DROP VIEW 陳述式。

DROP VIEW SingerNames;

取得檢視畫面的相關資訊

如要取得資料庫中檢視表的相關資訊，請查詢 INFORMATION_SCHEMA 結構定義中的資料表。

  • INFORMATION_SCHEMA.TABLES 表格會列出所有已定義的檢視區塊名稱。

  • INFORMATION_SCHEMA.VIEWS 表格會列出所有已定義檢視表的名稱、檢視定義、安全性類型和查詢文字。在檢視表上擁有 SELECT 權限的 FGAC 使用者，可以從 INFORMATION_SCHEMA.VIEWS 資料表取得檢視表相關資訊。如果其他 FGAC 使用者沒有檢視畫面 SELECT 權限，則需要 spanner_info_reader 角色。

如要檢查名為 ProductSoldLastWeek 的檢視區塊定義和安全性類型，請執行下列操作：

  SELECT *
  FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_NAME = 'ProductSoldLastWeek';