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

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

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

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

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

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

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

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

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

目標

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

費用

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

MySQL 適用的 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 MySQL API
  - Knowledge Catalog API
  - Data Analytics API with Gemini (geminidataanalytics.googleapis.com)

建立及連線至資料庫

建立執行個體。
連線至執行個體並建立資料庫。
在 MySQL 適用的 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：MySQL 適用的 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 資料庫驗證功能管理使用者」。

準備環境

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

啟用資料庫旗標

確認 MySQL 適用的 Cloud SQL 執行個體，執行的是 MySQL 適用的 Cloud SQL 8.0.43 以上版本。如果 MySQL 8.0 適用的 Cloud SQL 執行個體執行的是較舊的子版本，您可以升級子版本。
為執行個體啟用 cloudsql_parameterized_secure_view 資料庫旗標。這項旗標變更不需重新啟動資料庫。詳情請參閱「設定執行個體的資料庫旗標」。

設定資料庫和角色

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

前往 Cloud SQL
從清單中選取執行個體。
在導覽選單中，按一下「Cloud SQL Studio」。
使用管理員使用者帳戶登入工作室。
按一下「Authenticate」(驗證)。「Explorer」窗格會顯示資料庫中的物件清單。
點選「開啟新的 SQL 編輯器分頁」或「新增分頁」，開啟新分頁。
建立名為 store_db 的資料庫，用於存放應用程式資料和檢視表：
```
CREATE DATABASE IF NOT EXISTS store_db;
USE store_db;
```
建立新的資料庫角色，用於對參數化檢視表執行查詢。應用程式會使用這個角色連線至資料庫，並執行查詢，存取權限嚴格限制為授權檢視表：
```
CREATE ROLE 'psv_user';
```

建立資料表並插入資料

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

前往 Cloud SQL
從清單中選取 MySQL 適用的 Cloud SQL 執行個體。
在導覽選單中，按一下「Cloud SQL Studio」。
使用資料庫驗證登入 Studio。
點選「新增分頁」，開啟新的 SQL 編輯器分頁。

建立資料表並插入資料：

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

INSERT INTO store_db.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，並以您在上一節使用的管理員使用者帳戶 (例如 root 或 ADMIN_USER) 連線至 store_db 資料庫。
按一下「Authenticate」(驗證)。「Explorer」窗格會顯示資料庫中的物件清單。
點選「開啟新的 SQL 編輯器分頁」或「新增分頁」，開啟新分頁。
如要提供檢視表的有限存取權，請建立參數化檢視表：
```
CREATE VIEW store_db.secure_checked_items AS
SELECT bag_id, timestamp, location
FROM store_db.checked_items t
WHERE customer_id = @app_end_userid;
```
注意：建立參數化安全檢視區塊時，請務必使用不重複的參數名稱，例如 @app_end_userid。

將參數化檢視表的 SELECT 存取權授予 psv_user 角色：

GRANT SELECT ON store_db.secure_checked_items TO 'psv_user';

授予內建系統預存程序的執行權限，以便安全執行參數化查詢：
```
GRANT EXECUTE ON PROCEDURE mysql.execute_parameterized_query TO 'psv_user';
```
選用。明確撤銷 psv_user 對基礎資料表的任何直接權限，確保使用者無法規避安全檢視表：
```
REVOKE ALL PRIVILEGES ON store_db.checked_items FROM 'psv_user';
```
注意：如果發生 Error 1147: There is no such grant defined for user，可以放心忽略。如果您嘗試撤銷從未授予的權限，就會看到這則錯誤訊息。新角色預設沒有任何權限。只有在修改已有基礎資料表存取權的現有角色時，才需要執行這項指令。
請確認 IAM_USER_EMAIL 已新增至執行個體，做為 IAM 資料庫使用者，然後將 psv_user 角色授予通過 IAM 驗證的資料庫使用者或應用程式登入帳戶：
```
GRANT 'psv_user' TO 'IAM_USERNAME'@'%';

SET DEFAULT ROLE 'psv_user' TO 'IAM_USERNAME'@'%';
```
將 IAM_USERNAME 替換為 IAM 資料庫使用者名稱或應用程式登入帳戶。如果是 IAM 使用者帳戶，MySQL 適用的 Cloud SQL 會從電子郵件地址中移除 @ 和網域名稱，因此請指定 @ 之前的電子郵件前置字元，例如 user@example.com 的 user。

驗證資料安全性

如要確認您可以查詢參數化安全檢視，請按照下列步驟操作：

使用受限的 IAM 電子郵件地址或應用程式登入資訊，登入 Cloud SQL Studio。
點選「新增分頁」，開啟新的 SQL 編輯器分頁。
查詢基礎資料表，確認存取要求遭拒。
```
SELECT * FROM store_db.checked_items;
```
資料庫傳回 ERROR 1142 (42000): SELECT command denied to user 'IAM_USERNAME'@'%' for table 'checked_items' 錯誤。

使用內建的 mysql.execute_parameterized_query 預存程序，查詢參數化安全檢視。

CALL mysql.execute_parameterized_query(
  'SELECT * FROM store_db.secure_checked_items',
  'app_end_userid=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": "MYSQL",
             "project_id": "PROJECT_ID",
             "region": "REGION",
             "instance_id": "INSTANCE_ID",
             "database_id": "DATABASE_ID",
             "table_ids": [
               "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 只會針對 secure_checked_items 參數化安全檢視區塊生成 SQL。如果省略 table_ids，且資料庫角色對 checked_items 基礎資料表具有 SELECT 存取權，QueryData 可能會直接查詢基礎資料表，略過安全檢視表。

更改下列內容：

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

清除所用資源

刪除執行個體

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

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

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

後續步驟

瞭解參數化安全檢視。

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