Parametrisierte Ansichten verwenden

In dieser Anleitung wird gezeigt, wie Sie parametrisierte Ansichten in Spanner verwenden, um den Zugriff auf Daten basierend auf Parametern einzuschränken, die eine Anwendung sicher übergibt.

Weitere Informationen zu parametrisierten Ansichten finden Sie unter Übersicht über parametrisierte Ansichten.

Ziele

• Tabelle mit Beispieldaten erstellen
• Erstellen Sie eine parametrisierte Ansicht, die Daten basierend auf einem sicheren Kontextparameter filtert.
• Erstellen Sie eine Datenbankrolle für die detaillierte Zugriffssteuerung, die die Anwendung verwendet, um eine Verbindung zur Datenbank herzustellen und auf die parametrisierte Ansicht zuzugreifen.
• Der neuen Rolle Berechtigungen zum Abfragen der parametrisierten Ansicht gewähren, ohne ihr Zugriff auf die Basistabelle zu gewähren
• Fragen Sie die parametrisierte Ansicht über eine Anwendung ab, indem Sie einen Parameter im sicheren Kontext übergeben.

Hinweis

Sie benötigen eine Cloud Spanner-Instanz und eine Datenbank, um diese Anleitung abzuschließen. Wenn Sie keine haben, finden Sie unter Datenbank mit der Google Cloud Console erstellen und abfragen Informationen zum Erstellen einer Instanz und einer Spanner-Datenbank.

Beispieldaten vorbereiten

1. Rufen Sie in der Google Cloud Console die Seite Spanner auf.

   Spanner aufrufen

2. Klicken Sie auf den Namen der Instanz, die Sie verwenden möchten, und wählen Sie dann die Datenbank aus, die Sie für diese Anleitung verwenden möchten.

3. Klicken Sie auf der Seite Datenbankübersicht im Navigationsmenü auf Spanner Studio.

4. Geben Sie die folgende DDL-Anweisung ein, um die Tabelle checked_items zu erstellen:

   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. Klicken Sie auf Ausführen.

6. Nachdem Spanner die Tabelle erstellt hat, geben Sie die folgende Anweisung ein, um Beispieldaten in die Tabelle einzufügen:

   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. Klicken Sie auf Ausführen.

Parametrisierte Ansicht erstellen

Erstellen Sie eine Ansicht mit SQL SECURITY DEFINER, die Zeilen aus checked_items anhand des von SECURE_CONTEXT() bereitgestellten Werts von app_end_userid filtert. Diese Funktion ruft Parameterwerte ab, die sicher von der Anwendung übergeben werden.

1. Geben Sie im Abfrageeditor die folgende Anweisung ein, um die Ansicht secure_checked_items zu erstellen:

   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. Klicken Sie auf Ausführen.

Zugriffsberechtigungen einrichten

Damit Nutzer nur über die parametrisierte Ansicht auf Daten zugreifen können, erstellen Sie eine Rolle für die detaillierte Zugriffssteuerung und weisen Sie dieser Rolle die Berechtigung SELECT für die Ansicht zu. Achten Sie darauf, dass weder die Rolle noch die Systemrolle public Berechtigungen für die zugrunde liegende Basistabelle haben.

1. Geben Sie im Abfrageeditor die folgende Anweisung ein, um die Rolle pv_user zu erstellen:

   CREATE ROLE pv_user;
   

2. Gewähren Sie Zugriff auf die Ansicht:

   GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;
   

3. Klicken Sie auf Ausführen.

Hinweis: Anwendungsnutzer können Ansichten mit den Rechten des Erstellers abfragen, die die Funktion SECURE_CONTEXT() aufrufen, ohne explizite Berechtigungen für die Funktion selbst zu benötigen.

Wenn Sie jedoch SECURE_CONTEXT() direkt aufrufen müssen, um Ihre Abfragen zu debuggen, weisen Sie sich die Rolle spanner_secure_context_reader zu. Sie dürfen diese Rolle nicht an nicht vertrauenswürdige Agents vergeben.

Daten mit der parametrisierten Ansicht abfragen

Ihre Anwendung fragt eine parametrisierte Ansicht ab, indem sie zuerst Parameter im sicheren Kontext der Abfrageanfrage festlegt und dann eine Standard-SELECT-Abfrage für die Ansicht ausführt. In der Ansicht wird die Funktion SECURE_CONTEXT() verwendet, um Ergebnisse anhand von Parameterwerten zu filtern.

Die folgenden Codebeispiele zeigen, wie Sie den sicheren Parameter app_end_userid auf 303 setzen und die Ansicht secure_checked_items abfragen:

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

Diese Abfrage gibt nur Zeilen aus checked_items zurück, in denen customer_id den Wert 303 hat:

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

Wenn Ihre Anwendung eine Verbindung zu Spanner über ein Hauptkonto herstellt, dem die Rolle pv_user zugewiesen wurde, führt jeder Versuch, die Tabelle checked_items direkt abzufragen, zu einem Fehler „Berechtigung verweigert“.

Nächste Schritte

• Weitere Informationen zu parametrisierten Ansichten
• Ansichten erstellen und verwalten
• Weitere Informationen zur detaillierten Zugriffssteuerung