使用参数化视图

本教程介绍了如何在 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 表的尝试都会导致权限遭拒错误。

后续步骤