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

選取說明文件版本：

本教學課程說明如何在 AlloyDB Omni 中使用參數化安全檢視區塊，透過 AlloyDB Omni Studio 或 psql 限制使用者對參數化檢視區塊的存取權。

我們提供範例，說明參數化安全檢視區塊的功能。這些範例僅供示範。

目標

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

安裝及連線至資料庫

  1. 安裝 AlloyDB Omni(適用於 Kubernetes、適用於容器)。
  2. 建立叢集及其主要執行個體。
  3. 連線至執行個體 (適用於 Kubernetes、適用於容器)

準備環境

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

使用參數化安全檢視區塊前，您必須在每個新的 Postgres 容器中執行下列操作一次。您可以使用 ALTER SYSTEM 或直接編輯 postgresql.conf，套用各項設定：

  1. 將「parameterized_views」新增至 shared_preload_libraries。
  2. 設定 parameterized_views.enabled=on 即可啟用這項功能。
  3. 重新啟動 postgres，變更就會生效。

設定資料庫

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

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

  1. 使用 psql 以 postgres 使用者身分，或以具備 AlloyDB Omni superuser 權限的使用者身分，連線至資料庫。

    psql database -U postgres

    詳情請參閱「About database user management in AlloyDB Omni」(關於 AlloyDB Omni 的資料庫使用者管理)。

  2. 在資料庫中建立 parameterized_views 擴充功能。

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

    建立擴充功能時，系統也會建立名為 parameterized_views 的結構定義，確保 API 位於該結構定義的命名空間中，且不會與現有 API 發生衝突。

  3. 建立 AlloyDB Omni 管理角色，擁有及管理資料庫。

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

    詳情請參閱「CREATE USER」。

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

    詳情請參閱「CREATE USER」。

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

    SET role TO admin_user;

  6. 建立包含資料表的結構定義。

    CREATE SCHEMA schema;

  7. 建立資料表並插入資料。

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

INSERT INTO schema.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. 使用 psql 以 admin_user 身分連線至資料庫。

    psql database -U admin_user

  2. 如要提供檢視表的有限存取權，請建立參數化安全檢視表。

    CREATE VIEW schema.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM schema.checked_items t
WHERE customer_id = $@app_end_userid;

  3. 授予檢視權限。

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. 如要存取檢視畫面，請授予結構定義的存取權。

    GRANT USAGE ON SCHEMA schema TO psv_user;

  5. 撤銷對基礎資料表的直接存取權。

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

驗證資料安全性

如要確認參數化安全檢視畫面是否限制對指定檢視畫面的存取權，請以 psv_user身分執行下列指令。這是 AlloyDB Omni 資料庫角色，應用程式會使用這個角色連線及登入資料庫，以執行查詢。

  1. 以參數化安全檢視使用者身分連線。

    psql database -U psv_user

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

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

  3. 使用 execute_parameterized_query 函式存取參數化安全檢視畫面。

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

  4. 如要提升自然語言生成查詢的安全性，請使用自然語言整合參數化安全檢視區塊。

清除所用資源

如要清理，您可以解除安裝 AlloyDB Omni 執行個體，或保留執行個體並刪除個別物件。

後續步驟