建立及管理檢視畫面

本頁面說明如何為 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 資料表，可以取代檢視畫面的安全性類型，以便定義者設定權限。取代檢視畫面的安全性類型後，請授予分析師角色存取檢視畫面的權限。使用者現在可以查詢 SingerNames 檢視畫面，即使他們沒有 Singers 資料表的存取權也一樣。

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';