Utilizzare le viste con parametri

Questo tutorial mostra come utilizzare le viste parametrizzate in Spanner per limitare l'accesso ai dati in base ai parametri passati in modo sicuro da un'applicazione.

Per saperne di più sulle viste parametrizzate, consulta la panoramica delle viste parametrizzate.

Obiettivi

  • Crea una tabella con dati di esempio.
  • Crea una vista con parametri che filtra i dati in base a un parametro di contesto sicuro.
  • Crea un ruolo del database per il controllo granulare dell'accesso che l'applicazione utilizza per connettersi al database e accedere alla vista parametrizzata.
  • Concedi al nuovo ruolo le autorizzazioni per eseguire query sulla vista parametrizzata senza concedergli l'accesso alla tabella di base.
  • Esegui una query sulla vista parametrizzata da un'applicazione passando un parametro nel contesto sicuro.

Prima di iniziare

Per completare questo tutorial, devi disporre di un'istanza e di un database Spanner. Se non ne hai uno, consulta Crea ed esegui query su un database utilizzando la console Google Cloud per creare un'istanza e un database Spanner.

Prepara i dati di esempio

  1. Nella console Google Cloud , vai alla pagina Spanner.

    Vai a Spanner

  2. Fai clic sul nome dell'istanza che vuoi utilizzare, quindi seleziona il database da utilizzare per questo tutorial.

  3. Nella pagina Panoramica del database, nel menu di navigazione, fai clic su Spanner Studio.

  4. Inserisci la seguente istruzione DDL per creare la tabella 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. Fai clic su Esegui.

  6. Dopo che Spanner ha creato la tabella, inserisci la seguente istruzione per inserire i dati di esempio nella tabella:

    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. Fai clic su Esegui.

Crea una vista parametrizzata

Crea una vista con SQL SECURITY DEFINER che filtra le righe di checked_items in base al valore di app_end_userid fornito da SECURE_CONTEXT(). Questa funzione recupera i valori parametro che vengono trasmessi in modo sicuro dall'applicazione.

  1. Nell'editor di query, inserisci la seguente istruzione per creare la vista 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. Fai clic su Esegui.

Configura i privilegi di accesso

Per assicurarti che gli utenti possano accedere ai dati solo tramite la vista parametrizzata, crea un ruolo di controllo dell'accesso granulare e concedi a quel ruolo l'autorizzazione SELECT sulla vista. Assicurati che né il ruolo né il ruolo di sistema public dispongano di privilegi sulla tabella di base sottostante.

  1. Nell'editor di query, inserisci la seguente istruzione per creare il ruolo pv_user:

    CREATE ROLE pv_user;

  2. Concedi l'accesso alla visualizzazione:

    GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;

  3. Fai clic su Esegui.

Tuttavia, se devi chiamare direttamente SECURE_CONTEXT() per eseguire il debug delle query, concediti il ruolo spanner_secure_context_reader. Non devi concedere questo ruolo ad agenti non attendibili.

Esegui query sui dati utilizzando la vista con parametri

La tua applicazione esegue query su una vista parametrizzata impostando prima i parametri nel contesto sicuro della richiesta di query, quindi eseguendo una query SELECT standard sulla vista. La vista utilizza la funzione SECURE_CONTEXT() per filtrare i risultati in base ai valori dei parametri.

I seguenti esempi di codice mostrano come impostare il parametro sicuro app_end_userid su 303 ed eseguire query sulla vista 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}");
        }
    }
}

Vai

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]}")

Questa query restituisce solo le righe di checked_items in cui customer_id è 303:

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

Se la tua applicazione si connette a Spanner utilizzando un'entità a cui è stato concesso il ruolo pv_user, qualsiasi tentativo di eseguire query direttamente sulla tabella checked_items genera un errore di autorizzazione negata.

Passaggi successivi