Google 會運用 AI 技術將內容翻譯成你偏好的語言，但可能會出錯。

使用參數化安全檢視畫面，安全地控管應用程式資料存取權

參數化安全檢視表可為應用程式提供資料安全性，以及資料列存取控管機制。根據應用程式使用者的身分篩選查詢結果。

本教學課程說明如何在 Cloud SQL 中設定參數化安全檢視表、設定資料庫角色和權限來限制基礎資料表的存取權，以及驗證資料安全性。本文件提供的範例僅供示範。

什麼是參數化安全檢視區塊？

一般最佳做法是，應用程式應使用具備資料庫最低必要存取權的服務帳戶執行。舉例來說，如果應用程式不應寫入資料庫，就應使用具備唯讀存取權的角色。您應在資料庫層級設定這些存取控管機制。

如果應用程式需要比標準資料庫層級存取權更精細的安全防護，您可以使用參數化安全檢視表，確保使用者只能查看有權存取的資料。參數化安全檢視表提供動態資料安全性，以及適用於多租戶 SaaS 應用程式的資料列存取控管功能，不必為每個應用程式使用者建立個別資料庫角色。

使用參數化安全檢視區塊有三大優點：

動態資料安全性與資料列存取控管：使用使用者 ID 篩選查詢，無論查詢語句為何，使用者都只能存取獲授權的資料。
更輕鬆地管理角色：所有應用程式使用者都使用單一共用資料庫角色，不必為每位使用者管理個別資料庫角色。

目標

使用具名檢視參數建立參數化安全檢視。
建立資料庫角色，供應用程式連線至資料庫，並存取參數化安全檢視表。
將新角色的權限授予參數化安全檢視，並撤銷基礎資料表的存取權。
使用新角色連線，並確認無法存取受限制的資料表。
使用 execute_parameterized_query() 函式或 QueryData API，對參數化安全檢視表執行查詢。

費用

在本文件中，您會使用下列 Google Cloud計費元件：

PostgreSQL 適用的 Cloud SQL

您可以使用 Pricing Calculator，根據預測用量估算費用。

初次使用 Google Cloud 的使用者可能符合免費試用期資格。

完成本文所述工作後，請刪除建立的資源，避免繼續計費。詳情請參閱「清除所用資源」一節。

事前準備

建立參數化安全檢視表前，請先完成下列必要條件。

啟用計費功能和必要 API

在 Google Cloud 控制台選取專案。

前往專案選取器
請確認您已為 Google Cloud 專案啟用計費功能。
啟用必要的 Cloud API，以便建立及連線至 Cloud SQL。
1. 啟用 API
2. 在「Confirm project」(確認專案) 步驟中，按一下「Next」(下一步)，確認要變更的專案名稱。
3. 在「啟用 API」步驟中，點選「啟用」來啟用下列項目：
  - Cloud SQL for PostgreSQL API
  - Knowledge Catalog API
  - Data Analytics API with Gemini (geminidataanalytics.googleapis.com)

建立及連線至資料庫

建立執行個體。
連線至執行個體並建立資料庫。
在 PostgreSQL 適用的 Cloud SQL 執行個體上啟用 Cloud SQL Data API，讓 QueryData API 執行查詢：
```
gcloud sql instances patch INSTANCE_ID \
    --project=PROJECT_ID \
    --data-api-access=ALLOW_DATA_API
```
更改下列內容：
- INSTANCE_ID：PostgreSQL 適用的 Cloud SQL 執行個體 ID。
- PROJECT_ID：您的 Google Cloud 專案 ID。

授予必要角色並建立 IAM 資料庫使用者

如要透過 Identity and Access Management (IAM) 進行驗證，並對參數化安全檢視表執行 queryData 要求，請設定 IAM 使用者或服務帳戶：

將下列 IAM 角色授予 Google Cloud 專案的IAM_USER_EMAIL：
- roles/geminidataanalytics.queryDataUser
- roles/serviceusage.serviceUsageConsumer
- roles/cloudsql.instanceUser
如要進一步瞭解如何授予 IAM 角色，請參閱「新增專案的 IAM 政策繫結」。
在 Cloud SQL 執行個體啟用 IAM 資料庫驗證機制，並將 IAM_USER_EMAIL 新增為執行個體的 IAM 資料庫使用者。您必須先建立這個 IAM 資料庫使用者，才能將 psv_user 資料庫角色授予該使用者。如要進一步瞭解如何設定 IAM 資料庫使用者，請參閱「使用 IAM 資料庫驗證功能管理使用者」。

準備環境

如要準備對參數化安全檢視表執行查詢，請設定資料庫、資料庫角色、parameterized_views 擴充功能和應用程式結構定義。

啟用資料庫旗標

啟用 cloudsql.enable_parameterized_views 資料庫旗標，載入必要的擴充功能程式庫。詳情請參閱「設定執行個體的資料庫旗標」。

設定資料庫

為應用程式資料和參數化安全檢視表建立名為 database 的資料庫。詳情請參閱「建立資料庫」。

建立資料庫角色、擴充功能和應用程式結構定義

前往 Google Cloud 控制台的「Cloud SQL」頁面。

前往 Cloud SQL
從清單中選取執行個體。
在導覽選單中，按一下「Cloud SQL Studio」。
使用管理員帳戶驗證登入 Studio。
按一下「Authenticate」(驗證)。「Explorer」窗格會顯示資料庫中的物件清單。
點選「開啟新的 SQL 編輯器分頁」或「新增分頁」，開啟新分頁。
如要使用參數化檢視表，請在資料庫中建立 parameterized_views 擴充功能：
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
建立擴充功能時，資料庫也會建立名為 parameterized_views 的結構定義，以便將 API 納入該結構定義的命名空間，避免與現有 API 衝突。

注意：您必須在每個建立參數化檢視表的資料庫中建立擴充功能。
以具備超級使用者權限的使用者身分登入，例如內建的 postgres 使用者。
建立新的資料庫角色，對參數化安全檢視執行查詢。這是 Cloud SQL 角色，應用程式會使用這個角色連線並登入資料庫，以執行查詢，並將公開函式或物件的存取權限限制在最低必要範圍內。
```
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
```
詳情請參閱 CREATE USER。
以管理員使用者身分連線。
```
SET role TO postgres;
```
建立包含資料表的結構定義。
```
CREATE SCHEMA store;
```

建立資料表並插入資料。

CREATE TABLE store.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO store.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

建立參數化安全檢視，並設定存取權限

如要建立參數化安全檢視表，並為基礎資料表和檢視表設定適當的存取權限，請按照下列步驟操作：

前往 Google Cloud 控制台的「Cloud SQL」頁面。

前往 Cloud SQL
從清單中選取執行個體。
在導覽選單中，按一下「Cloud SQL Studio」。
登入 Studio，並以 postgres 使用者身分連線至 database 資料庫。
按一下「Authenticate」(驗證)。「Explorer」窗格會顯示資料庫中的物件清單。
點選「開啟新的 SQL 編輯器分頁」或「新增分頁」，開啟新分頁。
如要提供檢視表的有限存取權，請建立參數化檢視表：
```
CREATE VIEW store.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM store.checked_items t
WHERE customer_id = $@app_end_userid;
```
注意：建立參數化安全檢視表時，請務必使用不重複的參數名稱，例如 $@app_end_userid。

授予檢視表的存取權。

GRANT SELECT ON store.secure_checked_items TO psv_user;

如要存取檢視表，請授予結構定義存取權。
```
GRANT USAGE ON SCHEMA store TO psv_user;
```
撤銷基礎資料表的直接存取權。
```
REVOKE ALL PRIVILEGES ON store.checked_items FROM psv_user;
```
注意：參數化檢視表的擁有者必須具備基礎資料表的 SELECT 權限。此外，參數化檢視表的使用者必須具備檢視表結構定義的 USAGE 權限，以及檢視表的 SELECT 權限。舉例來說，postgres 使用者擁有參數化檢視表，因此對基礎資料表具有 SELECT 權限，而 psv_user 必須對 store 結構定義具有 USAGE 權限，並對檢視表具有 SELECT 權限。
確認 IAM_USER_EMAIL 已新增至執行個體，做為 IAM 資料庫使用者，然後以 postgres 使用者身分登入，並將 psv_user 角色授予通過 IAM 驗證的使用者：
```
GRANT psv_user TO "IAM_USER_EMAIL";
```
將 IAM_USER_EMAIL 替換為您的 IAM 使用者電子郵件地址。

驗證資料安全性

如要確認參數化安全檢視是否限制存取指定檢視，請以 IAM 驗證使用者的身分登入資料庫。在 Cloud SQL 中，IAM 使用者會繼承指派給他們的資料庫角色權限。

以通過 IAM 驗證的使用者身分登入資料庫。

確認無法存取基礎資料表。

SELECT * FROM store.checked_items;
ERROR:  permission denied for table checked_items

使用 execute_parameterized_query 函式存取參數化安全檢視：

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

執行 gcloud auth application-default login，以 IAM_USER_EMAIL 身分登入 Google Cloud CLI，讓 gcloud auth application-default print-access-token 為受限的 IAM 資料庫使用者產生權杖，然後使用 queryData 方法和 PSV 參數查詢參數化安全檢視表：

  curl -X POST \
    "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
    -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
    -H "x-goog-user-project: PROJECT_ID" \
    -H "Content-Type: application/json; charset=utf-8" \
    -d '{
      "prompt": "Show me the checked items.",
      "context": {
        "datasource_references": {
          "cloud_sql_reference": {
            "database_reference": {
              "engine": "POSTGRESQL",
              "project_id": "PROJECT_ID",
              "region": "REGION",
              "instance_id": "INSTANCE_ID",
              "database_id": "DATABASE_ID",
              "table_ids": [
                "store.secure_checked_items"
              ]
            }
          }
        },
        "parameterized_secure_view_parameters": {
          "parameters": [
            {
              "key": "app_end_userid",
              "value": "303"
            }
          ]
        }
      },
      "generation_options": {
        "generate_query_result": true,
        "generate_natural_language_answer": true,
        "generate_explanation": true
      }
    }'

使用 database_reference 中的 table_ids 欄位，確保 QueryData 只會針對 store.secure_checked_items 參數化安全檢視產生 SQL。如果省略 table_ids，且資料庫角色對 store.checked_items 基礎資料表具有 SELECT 存取權，QueryData 可能會直接查詢基礎資料表，略過安全檢視表。請指定符合結構定義的檢視表名稱，因為 table_ids 中不符合規定的名稱會預設為 public 結構定義。

更改下列內容：

PROJECT_ID：您的 Google Cloud 專案 ID。
REGION：PostgreSQL 適用的 Cloud SQL 執行個體所在區域。
INSTANCE_ID：PostgreSQL 適用的 Cloud SQL 執行個體 ID。
DATABASE_ID：PostgreSQL 適用的 Cloud SQL 資料庫 ID，在本教學課程中為 database。

清除所用資源

刪除執行個體

刪除在「事前準備」一節中建立的執行個體時，您建立的所有物件也會一併刪除。

前往 Google Cloud 控制台的「Cloud SQL」頁面。

前往 Cloud SQL
從清單中選取執行個體。
點選「刪除」。
輸入執行個體名稱，然後按一下「刪除」，確認要刪除執行個體。

後續步驟

瞭解參數化安全檢視。

瞭解如何使用參數化安全檢視表。