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

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

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

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

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

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

使用參數化安全檢視有兩大好處:

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

目標

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

費用

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

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

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

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

事前準備

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

啟用計費功能和必要 API

  1. 在 Google Cloud 控制台選取專案。

    前往專案選取器

  2. 請確認您已為 Google Cloud 專案啟用計費功能。

  3. 啟用必要的 Cloud API,以便建立及連線至 AlloyDB for PostgreSQL。

    1. 啟用 API

    2. 在「Confirm project」(確認專案) 步驟中,按一下「Next」(下一步),確認要變更的專案名稱。

    3. 在「啟用 API」步驟中,點選「啟用」來啟用下列項目:

      • AlloyDB for PostgreSQL API
      • Knowledge Catalog API
      • Data Analytics API with Gemini (geminidataanalytics.googleapis.com)

建立及連線至資料庫

  1. 建立叢集及其主要執行個體。
  2. 連線至執行個體並建立資料庫。

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

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

  1. 將下列 IAM 角色授予 Google Cloud 專案的IAM_USER_EMAIL:

    • roles/geminidataanalytics.queryDataUser
    • roles/serviceusage.serviceUsageConsumer
    • roles/alloydb.databaseUser

    如要進一步瞭解如何授予 IAM 角色,請參閱「新增專案的 IAM 政策繫結」。

  2. 在 AlloyDB 執行個體上啟用 IAM 驗證,並將 IAM_USER_EMAIL 新增為叢集上以 IAM 為基礎的資料庫使用者。您必須先建立這個 IAM 資料庫使用者,才能將 psv_user 資料庫角色授予該使用者。如要進一步瞭解如何設定 IAM 資料庫使用者,請參閱「管理 IAM 驗證」。

準備環境

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

啟用資料庫旗標

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

設定資料庫

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

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

  1. 前往 Google Cloud 控制台的「AlloyDB」頁面。

    前往 AlloyDB

  2. 從清單中選取叢集。

  3. 在導覽選單中,點選「AlloyDB Studio」。

  4. 使用管理員帳戶驗證登入 Studio。

  5. 按一下「Authenticate」(驗證)。「Explorer」窗格會顯示資料庫中的物件清單。

  6. 點選「開啟新的 SQL 編輯器分頁」或「新增分頁」,開啟新分頁。

  7. 如要使用參數化檢視表,請在資料庫中建立 parameterized_views 擴充功能:

    -- Requires parameterized_views.enabled set to true
    CREATE EXTENSION parameterized_views;
    

    建立擴充功能時,資料庫也會建立名為 parameterized_views 的結構定義,以便將 API 納入該結構定義的命名空間,避免與現有 API 衝突。

  8. 以具備超級使用者權限的使用者身分登入,例如內建的 postgres 使用者。如要建立新的超級使用者 (例如 admin_user),請執行下列指令:

    CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
    GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;
    

    詳情請參閱 CREATE USER。

  9. 建立新的資料庫角色,用於對參數化安全檢視執行查詢,並授予 admin_user 管理這個角色的權限。應用程式會使用這個 AlloyDB 角色連線及登入資料庫,並以最低限度的必要權限執行查詢,存取公開函式或物件。

    CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
    GRANT psv_user TO admin_user WITH ADMIN OPTION;
    

    詳情請參閱 CREATE USER。

  10. 以管理員使用者身分連線。

    SET role TO admin_user;
    
  11. 建立包含資料表的結構定義。

    CREATE SCHEMA store;
    
  12. 建立資料表並插入資料。

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

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

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

  1. 前往 Google Cloud 控制台的「AlloyDB」頁面。

    前往 AlloyDB

  2. 從清單中選取叢集。

  3. 在導覽選單中,點選「AlloyDB Studio」。

  4. 登入 Studio,並以 admin_user 使用者身分連線至 database 資料庫。

  5. 按一下「Authenticate」(驗證)。「Explorer」窗格會顯示資料庫中的物件清單。

  6. 點選「開啟新的 SQL 編輯器分頁」或「新增分頁」,開啟新分頁。

  7. 如要提供檢視表的有限存取權,請建立參數化檢視表:

    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;
    
  8. 授予檢視表的存取權。

    GRANT SELECT ON store.secure_checked_items TO psv_user;
    
  9. 如要存取檢視表,請授予結構定義存取權。

    GRANT USAGE ON SCHEMA store TO psv_user;
    
  10. 撤銷基礎資料表的直接存取權。

    REVOKE ALL PRIVILEGES ON store.checked_items FROM psv_user;
    

    注意:參數化檢視表的擁有者必須具備基礎資料表的 SELECT 權限。此外,參數化檢視表的使用者必須具備檢視表結構定義的 USAGE 權限,以及檢視表的 SELECT 權限。舉例來說,admin_user 使用者擁有參數化檢視表,因此對基礎資料表具有 SELECT 權限,而 psv_user 必須對 store 結構定義具有 USAGE 權限,並對檢視表具有 SELECT 權限。

  11. 確認 IAM_USER_EMAIL 已新增至叢集,做為 IAM 資料庫使用者,然後以 postgres 使用者或 admin_user 身分登入,並將 psv_user 角色授予通過 IAM 驗證的使用者:

    GRANT psv_user TO "IAM_USER_EMAIL";
    

    將 IAM_USER_EMAIL 替換為您的 IAM 使用者電子郵件地址。

驗證資料安全性

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

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

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

    SELECT * FROM store.checked_items;
    ERROR:  permission denied for table checked_items
    
  3. 使用 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']
    );
    
  4. 執行 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": {
              "alloydb": {
                "database_reference": {
                  "project_id": "PROJECT_ID",
                  "region": "REGION",
                  "cluster_id": "CLUSTER_ID",
                  "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:AlloyDB for PostgreSQL 執行個體所在的區域。
    • CLUSTER_ID:AlloyDB for PostgreSQL 叢集的 ID。
    • INSTANCE_ID:AlloyDB for PostgreSQL 執行個體的 ID。
    • DATABASE_ID:AlloyDB for PostgreSQL 資料庫的 ID,在本教學課程中為 database。

    清除所用資源

    刪除叢集

    刪除在「事前準備」一節中建立的叢集時,您建立的所有物件也會一併刪除。

    1. 前往 Google Cloud 控制台的「AlloyDB」頁面。

      前往 AlloyDB

    2. 從清單中選取叢集。

    3. 按一下「Delete cluster」(刪除叢集)。

    4. 在「Delete cluster」(刪除叢集) 中,輸入叢集名稱,確認要刪除叢集。

    後續步驟