Menggunakan tampilan berparameter

Tutorial ini menunjukkan cara menggunakan tampilan berparameter di Spanner untuk membatasi akses ke data berdasarkan parameter yang diteruskan aplikasi dengan aman.

Untuk mengetahui informasi selengkapnya tentang tampilan berparameter, lihat Ringkasan tampilan berparameter.

Tujuan

  • Membuat tabel dengan data sampel.
  • Buat tampilan berparameter yang memfilter data berdasarkan parameter konteks yang aman.
  • Buat peran database untuk kontrol akses terperinci yang digunakan aplikasi untuk terhubung ke database dan mengakses tampilan berparameter.
  • Beri peran baru izin untuk mengkueri tampilan berparameter tanpa memberinya akses ke tabel dasar.
  • Buat kueri tampilan berparameter dari aplikasi dengan meneruskan parameter dalam konteks yang aman.

Sebelum memulai

Anda memerlukan instance dan database Spanner untuk menyelesaikan tutorial ini. Jika Anda belum memilikinya, lihat Membuat dan mengkueri database menggunakan konsol Google Cloud untuk membuat instance dan database Spanner.

Menyiapkan data sampel

  1. Di konsol Google Cloud , buka halaman Spanner.

    Buka Spanner

  2. Klik nama instance yang ingin Anda gunakan, lalu pilih database yang akan digunakan untuk tutorial ini.

  3. Di halaman Database overview, di menu navigasi, klik Spanner Studio.

  4. Masukkan pernyataan DDL berikut untuk membuat tabel 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. Klik Run.

  6. Setelah Spanner membuat tabel, masukkan pernyataan berikut untuk menyisipkan data sampel ke dalam tabel:

    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. Klik Run.

Membuat tampilan berparameter

Buat tampilan dengan SQL SECURITY DEFINER yang memfilter baris dari checked_items berdasarkan nilai app_end_userid yang disediakan oleh SECURE_CONTEXT(). Fungsi ini mengambil nilai parameter yang diteruskan dengan aman dari aplikasi.

  1. Di editor kueri, masukkan pernyataan berikut untuk membuat tampilan 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. Klik Run.

Menyiapkan hak istimewa akses

Untuk memastikan bahwa pengguna hanya dapat mengakses data melalui tampilan berparameter, buat peran kontrol akses terperinci dan berikan izin SELECT kepada peran tersebut pada tampilan. Pastikan peran maupun peran sistem public tidak memiliki hak istimewa pada tabel dasar yang mendasarinya.

  1. Di editor kueri, masukkan pernyataan berikut untuk membuat peran pv_user:

    CREATE ROLE pv_user;

  2. Berikan akses ke tampilan:

    GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;

  3. Klik Run.

Namun, jika Anda perlu memanggil SECURE_CONTEXT() secara langsung untuk men-debug kueri, berikan peran spanner_secure_context_reader kepada diri Anda sendiri. Anda tidak boleh memberikan peran ini kepada agen yang tidak tepercaya.

Membuat kueri data menggunakan tampilan berparameter

Aplikasi Anda mengkueri tampilan berparameter dengan terlebih dahulu menetapkan parameter dalam konteks aman permintaan kueri, lalu menjalankan kueri SELECT standar terhadap tampilan. Tampilan menggunakan fungsi SECURE_CONTEXT() untuk memfilter hasil berdasarkan nilai parameter.

Contoh kode berikut menunjukkan cara menetapkan parameter aman app_end_userid ke 303 dan membuat kueri tabel virtual 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]}")

Kueri ini hanya menampilkan baris dari checked_items dengan customer_id adalah 303:

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

Jika aplikasi Anda terhubung ke Spanner menggunakan akun utama yang telah diberi peran pv_user, setiap upaya untuk mengkueri tabel checked_items secara langsung akan menghasilkan error izin ditolak.

Langkah berikutnya