Usa vistas con parámetros

En este instructivo, se muestra cómo usar vistas parametrizadas en Spanner para restringir el acceso a los datos según los parámetros que una aplicación pasa de forma segura.

Para obtener más información sobre las vistas parametrizadas, consulta Descripción general de las vistas parametrizadas.

Objetivos

  • Crear una tabla con datos de muestra
  • Crea una vista parametrizada que filtre los datos según un parámetro de contexto seguro.
  • Crea un rol de base de datos para el control de acceso detallado que la aplicación usa para conectarse a la base de datos y acceder a la vista parametrizada.
  • Otorga permisos al rol nuevo para consultar la vista parametrizada sin otorgarle acceso a la tabla base.
  • Consulta la vista parametrizada desde una aplicación pasando un parámetro en el contexto seguro.

Antes de comenzar

Necesitas una instancia y una base de datos de Spanner para completar este instructivo. Si no tienes una, consulta Crea y consulta una base de datos con la consola de Google Cloud para crear una instancia y una base de datos de Spanner.

Prepara datos de muestra

  1. En la consola de Google Cloud , ve a la página Spanner.

    Ir a Spanner

  2. Haz clic en el nombre de la instancia que deseas usar y, luego, selecciona la base de datos que usarás en este instructivo.

  3. En la página Descripción general de la base de datos, en el menú de navegación, haz clic en Spanner Studio.

  4. Ingresa la siguiente declaración DDL para crear la tabla 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. Haz clic en Ejecutar.

  6. Después de que Spanner cree la tabla, ingresa la siguiente sentencia para insertar datos de muestra en la tabla:

    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. Haz clic en Ejecutar.

Crea una vista parametrizada

Crea una vista con SQL SECURITY DEFINER que filtre las filas de checked_items según el valor de app_end_userid que proporciona SECURE_CONTEXT(). Esta función recupera los valores de los parámetros que se pasan de forma segura desde la aplicación.

  1. En el editor de consultas, ingresa la siguiente sentencia para crear 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. Haz clic en Ejecutar.

Configura los privilegios de acceso

Para asegurarte de que los usuarios solo puedan acceder a los datos a través de la vista parametrizada, crea un rol de control de acceso detallado y otórgale el permiso SELECT en la vista. Asegúrate de que ni el rol ni el rol del sistema public tengan privilegios en la tabla base subyacente.

  1. En el editor de consultas, ingresa la siguiente sentencia para crear el rol pv_user:

    CREATE ROLE pv_user;

  2. Otorga acceso a la vista:

    GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;

  3. Haz clic en Ejecutar.

Sin embargo, si necesitas llamar a SECURE_CONTEXT() directamente para depurar tus consultas, otórgate el rol spanner_secure_context_reader. No debes otorgar este rol a agentes que no sean de confianza.

Consulta datos con la vista parametrizada

Tu aplicación consulta una vista parametrizada. Para ello, primero establece parámetros en el contexto seguro de la solicitud de consulta y, luego, ejecuta una consulta SELECT estándar en la vista. La vista usa la función SECURE_CONTEXT() para filtrar los resultados según los valores de los parámetros.

En las siguientes muestras de código, se indica cómo establecer el parámetro seguro app_end_userid en 303 y consultar la 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}");
        }
    }
}

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

Esta consulta solo devuelve filas de checked_items en las que customer_id es 303:

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

Si tu aplicación se conecta a Spanner con una principal a la que se le otorgó el rol pv_user, cualquier intento de consultar la tabla checked_items directamente generará un error de permiso denegado.

¿Qué sigue?