שימוש בתצוגות עם פרמטרים

במדריך הזה מוסבר איך להשתמש בתצוגות עם פרמטרים ב-Spanner כדי להגביל את הגישה לנתונים על סמך פרמטרים שהאפליקציה מעבירה באופן מאובטח.

למידע נוסף על תצוגות עם פרמטרים, קראו את הסקירה הכללית על תצוגות עם פרמטרים.

מטרות

• יוצרים טבלה עם נתונים לדוגמה.
• יוצרים תצוגה עם פרמטרים שמסננת נתונים על סמך פרמטר הקשר מאובטח.
• יוצרים תפקיד במסד הנתונים לבקרת גישה מפורטת, שבו האפליקציה משתמשת כדי להתחבר למסד הנתונים ולגשת לתצוגה עם הפרמטרים.
• נותנים לתפקיד החדש הרשאות לשלוח שאילתות לתצוגה עם הפרמטרים, בלי לתת לו גישה למסד הנתונים הטבלאי.
• שולחים שאילתה לתצוגה שמכילה פרמטרים מאפליקציה, על ידי העברת פרמטר בהקשר המאובטח.

לפני שמתחילים

כדי להשלים את המדריך הזה, צריך מכונה ומסד נתונים של Spanner. אם אין לכם מסד נתונים, תוכלו לקרוא את המאמר יצירה של מסד נתונים ושליחת שאילתות אליו באמצעות מסוף Google Cloud כדי ליצור מכונה ומסד נתונים של Spanner.

הכנת נתונים לדוגמה

1. נכנסים לדף Spanner במסוף Google Cloud .

   כניסה ל-Spanner

2. לוחצים על שם המכונה שבה רוצים להשתמש, ובוחרים את מסד הנתונים שבו רוצים להשתמש במדריך הזה.

3. בדף Database overview, לוחצים על Spanner Studio בתפריט הניווט.

4. מזינים את הצהרת ה-DDL הבאה כדי ליצור את הטבלה 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. לוחצים על Run.

6. אחרי ש-Spanner יוצר את הטבלה, מזינים את ההצהרה הבאה כדי להוסיף נתונים לדוגמה לטבלה:

   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. לוחצים על Run.

יצירת תצוגה עם פרמטרים

יוצרים תצוגה עם SQL SECURITY DEFINER שמסננת שורות מ-checked_items על סמך הערך של app_end_userid שסופק על ידי SECURE_CONTEXT(). הפונקציה הזו מאחזרת ערכי פרמטרים שמועברים באופן מאובטח מהאפליקציה.

1. בעורך השאילתות, מזינים את ההצהרה הבאה כדי ליצור את התצוגה 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. לוחצים על Run.

הגדרת הרשאות גישה

כדי לוודא שהמשתמשים יוכלו לגשת לנתונים רק דרך התצוגה עם הפרמטרים, יוצרים תפקיד של בקרת גישה מפורטת (FGAC) ומקצים לתפקיד הזה את ההרשאה SELECT בתצוגה. חשוב לוודא שלתפקיד ולתפקיד המערכת public אין הרשאות במסד הנתונים הטבלאי הבסיסי.

1. בעורך השאילתות, מזינים את ההצהרה הבאה כדי ליצור את התפקיד pv_user:

   CREATE ROLE pv_user;
   

2. נותנים גישה לתצוגה:

   GRANT SELECT ON VIEW secure_checked_items TO ROLE pv_user;
   

3. לוחצים על Run.

הערה: משתמשי האפליקציה יכולים לשלוח שאילתות לתצוגות של זכויות המגדיר שקוראות לפונקציה SECURE_CONTEXT(), בלי צורך בהרשאות מפורשות לפונקציה עצמה.

עם זאת, אם אתם צריכים לקרוא ישירות ל-SECURE_CONTEXT() כדי לנפות באגים בשאילתות, תנו לעצמכם את התפקיד spanner_secure_context_reader. אל תקצו את התפקיד הזה לסוכנים לא מהימנים.

שליחת שאילתה על נתונים באמצעות התצוגה שמכילה פרמטרים

האפליקציה שולחת שאילתה לתצוגה עם פרמטרים. קודם היא מגדירה פרמטרים בהקשר המאובטח של בקשת השאילתה, ואז מריצה שאילתת SELECT רגילה בתצוגה. התצוגה משתמשת בפונקציה SECURE_CONTEXT() כדי לסנן את התוצאות על סמך ערכי הפרמטרים.

בדוגמאות הקוד הבאות אפשר לראות איך מגדירים את הפרמטר המאובטח app_end_userid לערך 303 ושולחים שאילתה לתצוגה 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]}")

השאילתה הזו מחזירה רק שורות מ-checked_items שבהן customer_id הוא 303:

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

אם האפליקציה מתחברת ל-Spanner באמצעות חשבון משתמש שקיבל את התפקיד pv_user, כל ניסיון לשלוח שאילתה ישירות לטבלה checked_items יגרום לשגיאה של דחיית הרשאה.

המאמרים הבאים

• סקירה כללית בנושא תצוגות עם פרמטרים
• איך יוצרים ומנהלים תצוגות
• מידע נוסף על בקרת גישה פרטנית