Google は AI 技術を使用して、コンテンツをご希望の言語に翻訳しています。AI 翻訳には誤りが含まれる場合があります。

パラメータ化されたビューを使用する

このチュートリアルでは、Spanner でパラメータ化されたビューを使用して、アプリケーションが安全に渡すパラメータに基づいてデータへのアクセスを制限する方法について説明します。

パラメータ化されたビューの詳細については、パラメータ化されたビューの概要をご覧ください。

目標

サンプルデータを含むテーブルを作成します。
セキュアなコンテキストパラメータに基づいてデータをフィルタするパラメータ化されたビューを作成します。
アプリケーションがデータベースに接続し、パラメータ化されたビューにアクセスするために使用する、きめ細かいアクセス制御用のデータベースロールを作成します。
新しいロールに、ベーステーブルへのアクセス権を付与せずに、パラメータ化されたビューをクエリする権限を付与します。
セキュアなコンテキストでパラメータを渡して、アプリケーションからパラメータ化されたビューにクエリを実行します。

始める前に

このチュートリアルを完了するには、Spanner インスタンスとデータベースが必要です。まだ作成していない場合は、 Google Cloud コンソールを使用してデータベースを作成してクエリを実行するの手順に沿って、インスタンスと Spanner データベースを作成します。

サンプルデータを準備する

Google Cloud コンソールで、[Spanner] ページに移動します。

Spanner に移動
使用するインスタンスの名前をクリックし、このチュートリアルで使用するデータベースを選択します。
[データベースの概要] ページのナビゲーションメニューで、[Spanner Studio] をクリックします。

次の 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);

[実行] をクリックします。

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);

[実行] をクリックします。

パラメータ化されたビューを作成する

SECURE_CONTEXT() によって提供される app_end_userid の値に基づいて checked_items から行をフィルタする SQL SECURITY DEFINER を含むビューを作成します。この関数は、アプリケーションから安全に渡されたパラメータ値を取得します。

クエリエディタで次のステートメントを入力して、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));

[実行] をクリックします。

アクセス権限を設定する

ユーザーがパラメータ化されたビューを介してのみデータにアクセスできるようにするには、きめ細かいアクセス制御ロールを作成し、そのロールにビューに対する SELECT 権限を付与します。ロールと public システムロールのいずれにも、基盤となるベーステーブルに対する権限がないことを確認します。

クエリエディタで次のステートメントを入力して、pv_user ロールを作成します。
```
CREATE ROLE pv_user;
```

ビューへのアクセス権を付与します。

GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;

[実行] をクリックします。

ただし、クエリをデバッグするために 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]}")

このクエリは、customer_id が 303 である checked_items の行のみを返します。

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

アプリケーションが pv_user ロールを付与されたプリンシパルを使用して Spanner に接続する場合、checked_items テーブルを直接クエリしようとすると、権限拒否エラーが発生します。

次のステップ

パラメータ化されたビューの概要について学習する。
ビューの作成と管理の方法を確認する。
きめ細かいアクセス制御に関する詳細を確認する。