建立及管理檢視畫面

本頁面說明如何建立及管理 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 檢視表的這個定義有效，但並未遵循轉換資料類型的最佳做法，無法確保結構定義變更時的穩定性，詳情請參閱下一節。

建立參數化檢視表

如要建立參數化檢視表，請在 SELECT 陳述式的 WHERE 子句中加入 SECURE_CONTEXT() 函式。

在以下範例中，參數化檢視表會授予應用程式使用者 Singers 資料表中 SingerId、FirstName 和 LastName 欄的存取權。檢視表會將結果限制在 UserID 與必要參數 app_end_userid 相符的資料列。

  CREATE VIEW SingerNames
  SQL SECURITY DEFINER
  AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
  FROM Singers
  WHERE UserID = CAST(SECURE_CONTEXT('app_end_userid'), INT64);

如要使用參數化檢視表，您必須具備檢視表的 SELECT 權限。詳情請參閱「授予及撤銷檢視表的存取權」。

建立檢視表的最佳做法

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

舉例來說，如果變更 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;

查詢參數化檢視表

查詢參數化檢視表的方式與其他檢視表相同。檢視畫面會根據安全參數 (例如使用者 ID) 篩選資料，安全地傳遞應用程式層級情境。

取代檢視表

您可以使用 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';