Utiliser des vues paramétrées

Ce tutoriel explique comment utiliser des vues paramétrées dans Spanner pour restreindre l'accès aux données en fonction des paramètres qu'une application transmet de manière sécurisée.

Pour en savoir plus sur les vues paramétrées, consultez Présentation des vues paramétrées.

Objectifs

  • Créez une table avec des exemples de données.
  • Créez une vue paramétrée qui filtre les données en fonction d'un paramètre de contexte sécurisé.
  • Créez un rôle de base de données pour le contrôle ultraprécis des accès que l'application utilise pour se connecter à la base de données et accéder à la vue paramétrée.
  • Accordez au nouveau rôle les autorisations nécessaires pour interroger la vue paramétrée sans lui accorder l'accès à la table de base.
  • Interrogez la vue paramétrée à partir d'une application en transmettant un paramètre dans le contexte sécurisé.

Avant de commencer

Pour suivre ce tutoriel, vous avez besoin d'une instance et d'une base de données Spanner. Si vous n'en avez pas, consultez Créer et interroger une base de données à l'aide de la console Google Cloud pour créer une instance et une base de données Spanner.

Préparer des exemples de données

  1. Dans la console Google Cloud , accédez à la page Spanner.

    Accéder à Spanner

  2. Cliquez sur le nom de l'instance que vous souhaitez utiliser, puis sélectionnez la base de données à utiliser pour ce tutoriel.

  3. Sur la page Présentation de la base de données, dans le menu de navigation, cliquez sur Spanner Studio.

  4. Saisissez l'instruction LDD suivante pour créer la table 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. Cliquez sur Exécuter.

  6. Une fois que Spanner a créé la table, saisissez l'instruction suivante pour y insérer des exemples de données :

    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. Cliquez sur Exécuter.

Créer une vue paramétrée

Créez une vue avec SQL SECURITY DEFINER qui filtre les lignes de checked_items en fonction de la valeur de app_end_userid fournie par SECURE_CONTEXT(). Cette fonction récupère les valeurs de paramètres transmises de manière sécurisée par l'application.

  1. Dans l'éditeur de requête, saisissez l'instruction suivante pour créer la vue 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. Cliquez sur Exécuter.

Configurer les droits d'accès

Pour vous assurer que les utilisateurs ne peuvent accéder aux données que via la vue paramétrée, créez un rôle de contrôle des accès ultraprécis des accès et accordez-lui l'autorisation SELECT sur la vue. Assurez-vous que ni le rôle, ni le rôle système public ne disposent de droits sur la table de base sous-jacente.

  1. Dans l'éditeur de requête, saisissez l'instruction suivante pour créer le rôle pv_user :

    CREATE ROLE pv_user;

  2. Accordez l'accès à la vue :

    GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;

  3. Cliquez sur Exécuter.

Toutefois, si vous devez appeler SECURE_CONTEXT() directement pour déboguer vos requêtes, attribuez-vous le rôle spanner_secure_context_reader. Vous ne devez pas accorder ce rôle à des agents non approuvés.

Interroger des données à l'aide de la vue paramétrée

Votre application interroge une vue paramétrée en définissant d'abord des paramètres dans le contexte sécurisé de la requête, puis en exécutant une requête SELECT standard sur la vue. La vue utilise la fonction SECURE_CONTEXT() pour filtrer les résultats en fonction des valeurs de paramètre.

Les exemples de code suivants montrent comment définir le paramètre sécurisé app_end_userid sur 303 et interroger la vue 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]}")

Cette requête ne renvoie que les lignes de checked_items où customer_id est 303 :

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

Si votre application se connecte à Spanner à l'aide d'un compte principal auquel le rôle pv_user a été attribué, toute tentative d'interrogation directe de la table checked_items entraîne une erreur d'autorisation refusée.

Étapes suivantes