本頁面由 Cloud Translation API 翻譯而成。

使用 AlloyDB 參數化安全檢視區塊管理應用程式資料安全

本文說明如何在 PostgreSQL 適用的 AlloyDB 中使用參數化安全檢視區塊，根據應用程式專屬的具名參數 (例如應用程式使用者憑證) 限制資料存取權。參數化安全檢視區塊可擴充 PostgreSQL 檢視區塊的功能，進而提升安全性和存取權控管。此外，這些檢視畫面也會對執行的任何查詢自動強制執行多項限制，降低從應用程式執行不受信任查詢的風險。

詳情請參閱參數化安全檢視區塊總覽和參數化安全檢視區塊教學課程。

事前準備

本文假設您已建立 AlloyDB 叢集和執行個體。詳情請參閱「建立資料庫」。

如要使用參數化安全檢視區塊，請先完成下列操作：

要求存取參數化安全檢視區塊，並等待啟用確認訊息。
等待 AlloyDB 團隊啟用 parameterized_views.enabled 資料庫標記，載入必要的擴充功能程式庫。您必須先啟用這個資料庫標記，才能開始作業。

AlloyDB 團隊啟用parameterized_views.enabled資料庫標記後，資料庫會重新啟動，這些變更才會生效。
使用 AlloyDB Studio 或 psql，在建立參數化檢視區塊的任何資料庫中建立 parameterized_views 擴充功能：
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
建立擴充功能時，系統也會建立名為 parameterized_views 的結構定義，以便將 API 納入該結構定義的命名空間，並避免 API 與現有 API 發生衝突。

建立參數化安全檢視區塊

如要建立含參數的安全檢視區塊，請按照下列步驟操作：

執行 CREATE VIEW DDL 指令，如下列範例所示：
```
CREATE VIEW secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM checked_items t
WHERE customer_id = $@app_end_userid;
```
在上述範例中，參數化安全檢視畫面可存取名為 /users/checked_items/ 的資料表中的三個資料欄。這個檢視畫面會將結果限制為 /users.id/checked_items.customer_id/ 符合必要參數的資料列。

請使用下列屬性：
- 使用 security_barrier 選項建立檢視區塊。
- 如要限制應用程式使用者，讓他們只能查看允許存取的資料列，請在 WHERE 子句中使用 $@PARAMETER_NAME 語法新增必要參數。常見用途是使用 WHERE COLUMN = $@PARAMETER_NAME 檢查資料欄的值。
- $@PARAMETER_NAME 表示具名檢視區塊參數。使用 execute_parameterized_query API 時，系統會提供這個值。具名檢視區塊參數須符合下列規定：
  - 具名檢視區塊參數開頭必須為英文字母 (a-z)。
  - 您可以使用帶有變音符號的字母和非拉丁字母，也可以使用底線 (_)。
  - 後續字元可以是字母、底線或數字 (0 至 9)。
  - 具名檢視區塊參數不得包含 $。
  - 具名檢視區塊參數會區分大小寫。舉例來說，$@PARAMETER_NAME 的解讀方式與 $@parameter_name 不同。
將資料檢視的 SELECT 授予允許查詢資料檢視的任何資料庫使用者。
對允許查詢檢視區塊的任何資料庫使用者，授予包含檢視區塊中定義資料表的結構定義的 USAGE 權限。

詳情請參閱「使用參數化安全檢視畫面，確保應用程式資料安全並控管存取權」。

設定應用程式的安全性

如要使用參數化安全檢視區塊設定應用程式的安全性，請按照下列步驟操作：

以管理員身分建立安全參數化檢視區塊。這個使用者是 AlloyDB 資料庫使用者，負責執行應用程式的管理作業，包括資料庫設定和安全性管理。
建立新的資料庫角色，以便對參數化安全檢視區塊執行查詢。這是 AlloyDB 資料庫角色，應用程式會使用這個角色連線及登入資料庫，並針對參數化檢視區塊執行查詢。
1. 將新角色權限授予安全檢視區塊，通常包括檢視區塊的 SELECT 權限和結構定義的 USAGE 權限。
2. 將這個角色可存取的物件，限制為應用程式所需的最低必要公開函式和物件集。避免提供非公開結構定義和資料表的存取權。
查詢檢視區塊時，應用程式會提供與應用程式使用者身分相關的必要檢視區塊參數值。

詳情請參閱「建立資料庫使用者」。

查詢參數化安全檢視區塊

如要查詢參數化安全檢視區塊，請使用下列其中一個最符合您用途的選項：

以 JSON 為基礎：使用這個 API 一次執行查詢，並傳回 JSON 資料列。
以 CURSOR 為準：如果查詢執行時間較長，或是查詢量較大，且您想分批擷取結果，請使用這個 API。parameterized_views 擴充功能提供的 execute_parameterized_query 函式會接受游標名稱。
PREPARE EXECUTE 陳述式：適用於可使用不同參數值多次執行的預先準備陳述式。

如要查詢參數化安全檢視區塊，請使用 parameterized_views 擴充功能提供的 execute_parameterized_query() 函式。

JSON API

這個 API 會為指定查詢宣告游標，因此有相關限制。因此，查詢必須與 PostgreSQL 指標相容。舉例來說，CURSOR API 不支援 DO 或 SHOW 陳述式。

此外，這個 API 不會依大小或傳回的資料列數限制結果。

執行 execute_parameterized_query() 函式，語法如下：

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

更改下列內容：

SQL_QUERY：SQL 查詢，其中的 FROM 子句參照一或多個參數化安全檢視區塊。
PARAMETER_NAMES：要以字串形式傳遞的參數名稱清單。
PARAMETER_VALUES：要傳遞的參數值清單。
- 這個清單的大小必須與 param_names 清單相同，且值的順序必須與名稱的順序相符。
- 值的確切類型是從查詢和參數化檢視定義推斷而來。系統會在需要時，以及在給定參數值允許的情況下，執行型別轉換。如果類型不符，系統會擲回錯誤。

此函式會傳回 JSON 物件表格。表格中的每個資料列都等同於原始查詢結果資料列的 ROW_TO_JSON() 值。

請使用下列範例查詢參數化安全檢視區塊：

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

使用這個 API 時，結果集的大小會受到限制，限制條件包括結果的大小 (以 KB 為單位) 和列數。您可以使用 parameterized_views.json_results_max_size 和 parameterized_views.json_results_max_rows 設定這些限制。

CURSOR API

執行 execute_parameterized_query() 函式，建立並傳回交易範圍的 CURSOR，用於擷取查詢結果：

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

更改下列內容：

SQL_QUERY：SQL 查詢，其中的 FROM 子句參照一或多個參數化安全檢視區塊。
CURSOR_NAME：要宣告的游標名稱。
PARAMETER_NAMES：要以字串形式傳遞的參數名稱清單。
PARAMETER_VALUES：要傳遞的參數值清單。這個清單的大小必須與 param_names 清單相同，且值的順序必須與名稱的順序相符。值的確切類型是從查詢和參數化檢視定義推斷而來。系統會在必要時，盡可能為指定參數值執行類型轉換。如果類型不符，系統會擲回錯誤。

請使用下列範例查詢參數化安全檢視區塊：

  -- start a transaction as the that is the default lifetime of a CURSOR
  BEGIN;
  -- create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor'
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- then, to actually fetch the results
  FETCH ALL FROM mycursor;
  -- end the transaction, which will clean up the cursor
  END;

傳回的游標是 NO SCROLL 游標 WITHOUT HOLD。您無法使用游標以非循序方式 (例如反向) 擷取資料列。您無法在建立游標的交易外使用游標。

PREPARE 陳述式

使用 PREPARE .. AS RESTRICTED 指令建立參照參數化檢視區塊的預先準備陳述式。這些預先準備好的陳述式支援位置參數，並在您執行時強制執行各種限制。詳情請參閱「安全機制」。

這項功能會擴充 PREPARE 和 EXECUTE commands，以支援具名檢視區塊參數。使用預先準備好的陳述式，避免每次執行陳述式時剖析、分析及重寫的額外負擔，這可大幅提升效能，尤其對於經常執行的查詢或複雜查詢而言更是如此。預先準備的陳述式是伺服器端物件，可預先編譯及儲存參數化 SQL 陳述式，供日後執行，藉此提升效能。

這項 API 有限制，因為陳述式必須允許出現在PREPARE陳述式中，也就是說，系統只支援 SELECT 和 VALUES 陳述式。

這個 API 也不會依大小或傳回的資料列數限制結果。

如要建立參照參數化檢視區塊的預先處理陳述式，請執行 PREPARE .. AS RESTRICTED 指令：

PREPARE pquery (/POSITIONAL_PARAM_TYPES/)
        AS RESTRICTED query % a query that may refer to parameterized views
EXECUTE pquery (/POSITIONAL_PARAM_VALUES/)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

更改下列內容：

POSITIONAL_PARAM_TYPES：在 RESTRICTED 查詢中使用的一或多個位置參數。
POSITIONAL_PARAM_VALUES：實際值，會取代 PREPARE 陳述式中定義的位置參數。
VIEW_PARAM_NAME：RESTRICTED 查詢中參照的參數化檢視區塊預期的參數名稱。
VIEW_PARAM_VALUE：傳遞至參數化檢視區塊對應 viewParamName 參數的實際值。

如要在預先準備好的陳述式中加入參數，請在 PREPARE 陳述式中提供資料類型清單。在準備的陳述式中，您可以使用 $1 和 $2 等方式，依位置參照參數。

使用 EXECUTE .. WITH VIEW PARAMETERS 指令執行先前準備好的陳述式，該陳述式是使用 PREPARE .. AS RESTRICTED 指令建立。如果建立陳述式的 PREPARE 陳述式指定了位置參數，則您必須將相容的參數集傳遞至 EXECUTE 陳述式。您必須在 WITH VIEW PARAMETERS 子句中，傳遞參數化檢視區塊所需的任何具名檢視區塊參數。

請使用下列範例查詢參數化安全檢視區塊：

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

強制執行的查詢限制

下列清單列出使用「查詢參數化安全檢視區塊」一節所述選項執行的查詢，可執行的受限作業：

禁止以遞迴方式呼叫任何 API (execute_parameterized_query 或使用 EXECUTE .. WITH VIEW PARAMETERS)，確保系統只會使用應用程式指定的值。這項限制也能防止查詢用於規避特定參數值組合的安全範圍。
系統不允許部分會啟動新背景工作階段的擴充功能，包括 dblink、pg_cron 和 pg_background 擴充功能。
以下列出允許的查詢結構，但受到限制：
- 允許使用唯讀 SELECT 陳述式。
- 允許使用唯讀 SHOW 陳述式、CALL 陳述式和 DO 陳述式。
- 不允許使用 INSERT、UPDATE 和 DELETE 等 DML 陳述式。
- 不允許使用 DDL 陳述式，例如 CREATE TABLE 和 ALTER TABLE。
- 不允許使用其他陳述式類型，例如 LOAD、SET、CLUSTER、LOCK、CHECKPOINT 和 EXPLAIN。
為避免使用查詢計畫發動隱蔽管道攻擊，系統不允許使用 EXPLAIN 陳述式。詳情請參閱「隱蔽管道」。

列出所有參數化檢視區塊

使用 parameterized_views 擴充功能，透過 all_parameterized_views 檢視畫面列出資料庫中的所有參數化檢視畫面。這個檢視區塊的輸出內容與 pg_views 目錄檢視區塊相同，但 all_parameterized_views 只會列出含有具名檢視區塊參數的檢視區塊。

如要列出參數化檢視區塊，請使用下列範例：

postgres=# select * from parameterized_views.all_parameterized_views ;
schemaname |      viewname      | viewowner |                       definition
-----------+--------------------+-----------+---------------------------------------------------------
public     | checked_items_view | postgres  |  SELECT checked_items.bag_id,                          +
           |                    |           |     checked_items."timestamp",                         +
           |                    |           |     checked_items.location                             +
           |                    |           |    FROM checked_items                                  +
           |                    |           |   WHERE (checked_items.customer_id = $@app_end_userid);

如要在 all_parameterized_views 中列出參數化檢視區塊，請確認參數化檢視區塊的定義中至少包含一個具名檢視區塊參數。

後續步驟

瞭解參數化安全檢視畫面。
瞭解如何使用參數化安全檢視畫面，保護及控管應用程式資料的存取權。

使用 AlloyDB 參數化安全檢視區塊管理應用程式資料安全 透過集合功能整理內容 你可以依據偏好儲存及分類內容。

事前準備

建立參數化安全檢視區塊

設定應用程式的安全性

查詢參數化安全檢視區塊

JSON API

CURSOR API

PREPARE 陳述式

強制執行的查詢限制

列出所有參數化檢視區塊

後續步驟

使用 AlloyDB 參數化安全檢視區塊管理應用程式資料安全