使用參數化檢視區塊

本教學課程說明如何在 Spanner 中使用參數化檢視表，根據應用程式安全傳遞的參數限制資料存取權。

如要進一步瞭解參數化檢視表，請參閱「參數化檢視表總覽」。

目標

  • 建立含有樣本資料的資料表。
  • 建立參數化檢視表，根據安全情境參數篩選資料。
  • 建立資料庫角色，以便精細的存取控管，讓應用程式用來連線至資料庫及存取參數化檢視表。
  • 授予新角色查詢參數化檢視表的權限，但不授予基礎資料表的存取權。
  • 在安全環境中傳遞參數，從應用程式查詢參數化檢視表。

事前準備

您需要 Spanner 執行個體和資料庫，才能完成本教學課程。如果沒有，請參閱「使用 Google Cloud 控制台建立及查詢資料庫」，建立執行個體和 Spanner 資料庫。

準備範例資料

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

    前往「Spanner」頁面

  2. 按一下要使用的執行個體名稱，然後選取要用於本教學課程的資料庫。

  3. 在「資料庫總覽」頁面的導覽選單中，點選「Spanner Studio」。

  4. 輸入下列 DDL 陳述式，建立 checked_items 資料表：

    CREATE TABLE checked_items(
  bag_id INT64,
  item_timestamp TIMESTAMP,
  loc_code STRING(3),
  scan_type STRING(1),
  location STRING(MAX),
  customer_id INT64
) PRIMARY KEY(bag_id);

  5. 按一下「執行」。

  6. Spanner 建立資料表後，請輸入下列陳述式，將範例資料插入資料表：

    INSERT INTO checked_items (bag_id, item_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);

  7. 按一下「執行」。

建立參數化檢視表

使用 SQL SECURITY DEFINER 建立檢視表，根據 SECURE_CONTEXT() 提供的 app_end_userid 值，篩選 checked_items 中的資料列。這個函式會擷取從應用程式安全傳遞的參數值。

  1. 在查詢編輯器中輸入下列陳述式，建立 secure_checked_items 檢視表：

    CREATE VIEW secure_checked_items
SQL SECURITY DEFINER AS
(SELECT bag_id, item_timestamp, location
FROM checked_items t
WHERE customer_id = CAST(SECURE_CONTEXT('app_end_userid'), INT64));

  2. 按一下「執行」。

設定存取權限

為確保使用者只能透過參數化檢視表存取資料，請建立精細的存取控管角色，並將檢視表的 SELECT 權限授予該角色。請確認該角色和 public 系統角色，都沒有基礎資料表的權限。

  1. 在查詢編輯器中輸入下列陳述式，建立 pv_user 角色：

    CREATE ROLE pv_user;

  2. 授予檢視表的存取權：

    GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;

  3. 按一下「執行」。

不過，如果您需要直接呼叫 SECURE_CONTEXT() 來偵錯查詢，請為自己授予 spanner_secure_context_reader 角色。請勿將這個角色授予不受信任的代理。

使用參數化檢視表查詢資料

應用程式查詢參數化檢視時，會先在查詢要求的安全環境中設定參數，然後對檢視執行標準 SELECT 查詢。檢視表會使用 SECURE_CONTEXT() 函式，根據參數值篩選結果。

下列程式碼範例示範如何將 app_end_userid 安全參數設為 303，並查詢 secure_checked_items 檢視畫面：

C#

static async Task QueryWithUserId(SpannerConnection connection, string userId)
{
    string query = "SELECT * FROM myuserdata";
    using (var command = connection.CreateSelectCommand(query))
    {
        command.ClientContext = new Google.Cloud.Spanner.Data.ClientContext {
            ClientContextModifier = (v1Context) => {
                v1Context.SecureContext["user_id"] = Value.ForString(userId);
            }
        };
        using (var reader = await command.ExecuteReaderAsync())
        {
            int rowCount = 0;
            while (await reader.ReadAsync())
            {
                Console.WriteLine($"UserID: {reader["userid"]}, UserName: {reader["username"]}, SecretData: {reader["secretdata"]}");
                rowCount++;
            }
            if (rowCount != 1) throw new Exception($"Expected 1 row, got {rowCount}");
        }
    }
}

Go

func queryWithUserID(ctx context.Context, client *spanner.Client, userID string) error {
	stmt := spanner.Statement{SQL: "SELECT * FROM myuserdata"}
	iter := client.Single().QueryWithOptions(ctx, stmt, spanner.QueryOptions{
		ClientContext: &spannerpb.RequestOptions_ClientContext{
			SecureContext: map[string]*structpb.Value{"user_id": structpb.NewStringValue(userID)},
		},
	})

	rowCount := 0
	err := iter.Do(func(r *spanner.Row) error {
		var id int64
		var name, secret string
		if err := r.Columns(&id, &name, &secret); err != nil {
			return err
		}
		if fmt.Sprintf("%d", id) != userID {
			return fmt.Errorf("unexpected UserID %d for user %s", id, userID)
		}
		fmt.Printf("UserID: %d, UserName: %s, SecretData: %s\n", id, name, secret)
		rowCount++
		return nil
	})
	if err != nil {
		return err
	}
	if rowCount != 1 {
		return fmt.Errorf("expected 1 row, got %d", rowCount)
	}
	return nil
}

Java

static void queryWithUserId(DatabaseClient dbClient, String userId) {
  RequestOptions.ClientContext clientContext =
      RequestOptions.ClientContext.newBuilder()
          .putSecureContext("user_id", Value.newBuilder().setStringValue(userId).build())
          .build();

  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(
              Statement.of("SELECT * FROM MyUserData"), Options.clientContext(clientContext))) {
    int rowCount = 0;
    while (resultSet.next()) {
      long id = resultSet.getLong("UserID");
      if (!String.valueOf(id).equals(userId)) {
        throw new RuntimeException("Unexpected UserID " + id + " for user " + userId);
      }
      System.out.printf(
          "UserID: %d, UserName: %s, SecretData: %s%n",
          id, resultSet.getString("UserName"), resultSet.getString("SecretData"));
      rowCount++;
    }
    if (rowCount != 1) {
      throw new RuntimeException("Expected 1 row, got " + rowCount);
    }
  }
}

Python

def query_with_user_id(database, user_id: str):
    """Queries a parameterized view using secure context parameters."""
    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            "SELECT * FROM MyUserData",
            request_options={
                "client_context": {
                    "secure_context": {"user_id": user_id}
                }
            },
        )
        for row in results:
            print(f"UserID: {row[0]}, UserName: {row[1]}, SecretData: {row[2]}")

這項查詢只會傳回 checked_items 中 customer_id 為 303 的資料列：

+--------+---------------------------+-------------+
| bag_id | item_timestamp            | location    |
+--------+---------------------------+-------------+
|    106 | 2023-10-26 12:00:00+00:00 | Truck Bay E |
+--------+---------------------------+-------------+
1 row.

如果應用程式使用已授予 pv_user 角色的主體連線至 Spanner，任何直接查詢 checked_items 資料表的嘗試都會導致權限遭拒錯誤。

後續步驟