保護及控管應用程式資料的存取權

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

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

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

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

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

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

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

目標

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

費用

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

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

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

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

事前準備

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

啟用帳單和必要的 API

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

    前往專案選取器

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

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

    1. 啟用 API

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

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

      • PostgreSQL 適用的 Cloud SQL API
      • Knowledge Catalog API

建立及連線至資料庫

  1. 建立執行個體。
  2. 連線至執行個體並建立資料庫。

準備環境

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

啟用資料庫旗標

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

設定資料庫

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

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

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

    前往 Cloud SQL

  2. 從清單中選取執行個體。

  3. 按一下導覽選單中的「Cloud SQL 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 使用者。

  9. 建立新的資料庫角色，針對參數化安全檢視區塊執行查詢。這是應用程式用來連線及登入資料庫的 Cloud SQL 角色，可執行查詢，並將公開函式或物件的存取權限制在最低必要範圍內。

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

    詳情請參閱 CREATE USER。

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

    SET role TO postgres;

  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 控制台的 Cloud SQL 頁面。

    前往 Cloud SQL

  2. 從清單中選取執行個體。

  3. 按一下導覽選單中的「Cloud SQL Studio」。

  4. 登入 Studio，然後以postgres身分連線至 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 權限。舉例來說，postgres 使用者擁有參數化檢視表，因此具備基礎資料表的 SELECT 權限，而 psv_user 必須具備 store 結構定義的 USAGE 權限和檢視表的 SELECT 權限。

  11. 以postgres管理員身分登入，然後將 psv_user 角色授予已通過 IAM 驗證的使用者：

    GRANT psv_user TO "IAM_USER_EMAIL";

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

驗證資料安全性

如要確認參數化安全檢視畫面是否限制對指定檢視畫面的存取權，請以 IAM 驗證使用者身分登入資料庫。在 Cloud SQL 中，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": {
          "cloud_sql_reference": {
            "database_reference": {
              "engine": "POSTGRESQL",
              "project_id": "PROJECT_ID",
              "region": "REGION",
              "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：PostgreSQL 適用的 Cloud SQL 執行個體所在區域。
      • INSTANCE_ID：PostgreSQL 適用的 Cloud SQL 執行個體 ID。
      • DATABASE_ID：PostgreSQL 適用的 Cloud SQL 資料庫 ID。

    清除所用資源

    刪除叢集

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

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

      前往 Cloud SQL

    2. 從清單中選取執行個體。

    3. 點選「刪除」。

    4. 輸入執行個體名稱，然後按一下「刪除」，確認要刪除執行個體。