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

參數化安全檢視區塊會根據應用程式使用者身分篩選查詢結果，提供資料列層級的資料安全性與存取控管。

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

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

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

如果應用程式需要的安全性比標準資料庫層級存取權限更精細，您可以使用參數化安全檢視畫面，確保使用者只能查看授權資料。

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

  • 動態資料列層級安全性：使用使用者 ID 篩選查詢，確保使用者只能存取授權資料，不受查詢措辭影響。
  • 簡化角色管理：為所有應用程式使用者使用單一共用資料庫角色，不必為每位使用者管理個別的資料庫角色。

目標

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

費用

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

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

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

如要避免繼續計費，請在完成本文中的工作後，刪除您建立的資源。詳情請參閱「清理」一節。

事前準備

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

啟用帳單和必要的 API

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

    前往專案選取器

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

  3. 啟用建立及連線至 AlloyDB for PostgreSQL 時所需的 Cloud API。

    1. 啟用 API

    2. 在「確認專案」步驟中，按一下「下一步」，確認要變更的專案名稱。

    3. 在「啟用 API」步驟中，按一下「啟用」，啟用下列項目：

      • AlloyDB for PostgreSQL API
      • Knowledge Catalog API

建立及連線至資料庫

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

準備環境

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

啟用資料庫旗標

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

設定資料庫

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

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

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

    前往 AlloyDB

  2. 從清單中選取叢集。

  3. 在導覽選單中，按一下「AlloyDB Studio」。

  4. 使用 postgres 驗證登入 Studio。

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

  6. 按一下「開啟新的 SQL 編輯器分頁」或「開啟新分頁」，開啟新分頁。

  7. 如要使用參數化檢視區塊，請在資料庫中建立 parameterized_views 擴充功能：

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

    建立擴充功能時，資料庫也會建立名為 parameterized_views 的結構定義，以便將 API 納入該結構定義的命名空間，並避免這些 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. 建立新的資料庫角色，以便對參數化安全檢視區塊執行查詢。這是 AlloyDB 角色，應用程式會使用這個角色連線及登入資料庫，並執行查詢，但對公開函式或物件的存取權有限，僅限於最低必要組合。

    CREATE ROLE psv_user WITH LOGIN PASSWORD '...';

    詳情請參閱 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. 按一下「驗證」。「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. 以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']
);

    1. 使用 SQL 語法和 QueryData 要求 (含 PSV 參數)，查詢參數化安全檢視區塊。

        curl -X POST \
    "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
    -H "Authorization: Bearer $(gcloud auth print-access-token)" \
    -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"
            }
          }
        },
        "parameterized_secure_view_parameters": {
          "parameters": {
            "app_end_userid": "303"
          }
        }
      },
      "generation_options": {
        "generate_query_result": true,
        "generate_natural_language_answer": true,
        "generate_explanation": true
      }
    }'

      替換下列值：

      • 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。

    清除所用資源

    刪除叢集

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

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

      前往 AlloyDB

    2. 從清單中選取叢集。

    3. 按一下「刪除叢集」。

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

    後續步驟