Usar visualizações parametrizadas

Neste tutorial, mostramos como usar visualizações parametrizadas no Spanner para restringir o acesso aos dados com base em parâmetros que um aplicativo transmite com segurança.

Para mais informações sobre visualizações parametrizadas, consulte Visão geral das visualizações parametrizadas.

Objetivos

  • Criar uma tabela com dados de amostra.
  • Crie uma visualização parametrizada que filtre dados com base em um parâmetro de contexto seguro.
  • Crie um papel de banco de dados para o controle de acesso refinado que o aplicativo usa para se conectar ao banco de dados e acessar a visualização parametrizada.
  • Conceda à nova função permissões para consultar a visualização parametrizada sem conceder acesso à tabela base.
  • Consulte a visualização parametrizada de um aplicativo transmitindo um parâmetro no contexto seguro.

Antes de começar

Você precisa de uma instância e um banco de dados do Spanner para concluir este tutorial. Se você não tiver um, consulte Criar e consultar um banco de dados usando o console do Google Cloud para criar uma instância e um banco de dados do Spanner.

Preparar dados de amostra

  1. No console Google Cloud , acesse a página Spanner.

    Acessar o Spanner

  2. Clique no nome da instância que você quer usar e selecione o banco de dados para este tutorial.

  3. Na página Visão geral do banco de dados, no menu de navegação, clique em Spanner Studio.

  4. Insira a seguinte instrução DDL para criar a tabela 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. Clique em Executar.

  6. Depois que o Spanner criar a tabela, insira a seguinte instrução para adicionar dados de amostra à tabela:

    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. Clique em Executar.

Criar uma visualização parametrizada

Crie uma visualização com SQL SECURITY DEFINER que filtre as linhas de checked_items com base no valor de app_end_userid fornecido por SECURE_CONTEXT(). Essa função recupera valores de parâmetro que são transmitidos com segurança pelo aplicativo.

  1. No editor de consultas, digite a seguinte instrução para criar a visualização 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. Clique em Executar.

Configurar privilégios de acesso

Para garantir que os usuários só possam acessar dados pela visualização parametrizada, crie um papel de controle de acesso refinado e conceda a ele a permissão SELECT na visualização. Verifique se nem o papel nem o papel do sistema public têm privilégios na tabela base subjacente.

  1. No editor de consultas, insira a seguinte instrução para criar o papel pv_user:

    CREATE ROLE pv_user;

  2. Conceda acesso à visualização:

    GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;

  3. Clique em Executar.

No entanto, se você precisar chamar SECURE_CONTEXT() diretamente para depurar suas consultas, conceda a si mesmo o papel spanner_secure_context_reader. Não conceda esse papel a agentes não confiáveis.

Consultar dados usando a visualização parametrizada

O aplicativo consulta uma visualização parametrizada definindo primeiro os parâmetros no contexto seguro da solicitação de consulta e, em seguida, executando uma consulta SELECT padrão na visualização. A visualização usa a função SECURE_CONTEXT() para filtrar resultados com base em valores de parâmetro.

Os exemplos de código a seguir demonstram como definir o parâmetro seguro app_end_userid como 303 e consultar a visualização 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]}")

Essa consulta retorna apenas linhas de checked_items em que customer_id é 303:

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

Se o aplicativo se conectar ao Spanner usando um principal que recebeu o papel pv_user, qualquer tentativa de consultar a tabela checked_items diretamente vai resultar em um erro de permissão negada.

A seguir