透過精細的存取權控管機制存取資料庫

本頁面說明精細存取權控管使用者如何存取 Spanner 資料庫。

如要瞭解精細的存取權控管機制，請參閱「關於精細的存取權控管機制」。

精細存取權控管使用者必須選取資料庫角色，才能執行 SQL 陳述式和查詢，以及對資料庫執行資料列作業。在變更角色之前，系統會在整個工作階段中保留您選取的角色。

提交查詢、DML 或資料列作業時，Spanner 會使用下列規則檢查授權：

Google Cloud 控制台

Spanner 會先檢查您是否具備資料庫層級的 IAM 權限。如果是，Google Cloud 控制台就不會顯示資料庫角色選取器，工作階段會繼續使用資料庫層級的權限。

  • 如果您只有精細的存取權控管權限，沒有 IAM 資料庫層級權限，則必須已獲授與 spanner_sys_reader 系統角色或其中一個成員角色。在資料庫「總覽」頁面選取角色，讓 Google Cloud 控制台工作階段以必要權限繼續執行。

Google Cloud SDK

提交查詢、DML 或列作業時，如果指定資料庫角色，Spanner 會檢查精細的存取控管權限。如果檢查失敗，Spanner 就不會檢查資料庫層級的 IAM 權限，作業也會失敗。

如果您未指定資料庫角色，Spanner 會檢查資料庫層級的 IAM 權限，如果檢查成功，您的工作階段就會繼續使用資料庫層級的權限。

存取 Spanner 資料庫時，請使用下列方法指定資料庫角色：

控制台

  1. 選取資料庫，然後在資料庫的「總覽」頁面中，按一下「目前角色」欄位旁的「變更資料庫角色」 (鉛筆) 圖示。

    在預設情況下，精細存取權控管使用者登入時，這個欄位的值為 public。如要瞭解 public 系統角色，請參閱精細的存取權控管系統角色。

  2. 在「變更資料庫角色」對話方塊中，從可用角色清單中選取其他角色。

  3. 按一下「更新」。

    「目前角色」欄位會顯示新角色。

gcloud

  • 將 --database-role 選項新增至 gcloud spanner databases execute-sql 指令，如下所示：

    gcloud spanner databases execute-sql DATABASE_NAME \
--instance=INSTANCE_NAME \
--sql="SELECT * from TABLE_NAME;" \
--database-role=ROLE_NAME

用戶端程式庫

C++

void ReadDataWithDatabaseRole(std::string const& project_id,
                              std::string const& instance_id,
                              std::string const& database_id,
                              std::string const& role) {
  namespace spanner = ::google::cloud::spanner;
  auto client = spanner::Client(spanner::MakeConnection(
      spanner::Database(project_id, instance_id, database_id),
      google::cloud::Options{}.set<spanner::SessionCreatorRoleOption>(role)));
  spanner::SqlStatement select_star("SELECT * FROM Singers");
  auto rows = client.ExecuteQuery(std::move(select_star));
  using RowType =
      std::tuple<std::int64_t, std::string, std::string, spanner::Bytes>;
  for (auto& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << "SingerId: " << std::get<0>(*row) << ", "
              << "FirstName: " << std::get<1>(*row) << ", "
              << "LastName: " << std::get<2>(*row) << "\n";
  }
}

C#


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class ReadDataWithDatabaseRoleAsyncSample
{
    public class Singer
    {
        public int SingerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public async Task<List<Singer>> ReadDataWithDatabaseRoleAsync(string projectId, string instanceId, string databaseId, string databaseRole)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string tableName = "Singers";

        var spannerConnectionStringBuilder = new SpannerConnectionStringBuilder
        {
            ConnectionString = connectionString,
            DatabaseRole = databaseRole
        };
        using var connection = new SpannerConnection(spannerConnectionStringBuilder);
        var createSelectCmd = connection.CreateSelectCommand($"SELECT * FROM {tableName}");
        using var reader = await createSelectCmd.ExecuteReaderAsync();
        var singers = new List<Singer>();
        while (await reader.ReadAsync())
        {
            singers.Add(new Singer
            {
                SingerId = reader.GetFieldValue<int>("SingerId"),
                FirstName = reader.GetFieldValue<string>("FirstName"),
                LastName = reader.GetFieldValue<string>("LastName"),
            });
        }
        return singers;
    }
}

Go


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

func readDataWithDatabaseRole(w io.Writer, db string, databaseRole string) error {
	// databaseRole = "parent"
	ctx := context.Background()
	cfg := spanner.ClientConfig{
		DatabaseRole: databaseRole,
	}
	client, err := spanner.NewClientWithConfig(ctx, db, cfg)
	if err != nil {
		return err
	}
	defer client.Close()

	// Read all albums.
	iter := client.Single().Read(ctx, "Albums", spanner.AllKeys(),
		[]string{"SingerId", "AlbumId", "AlbumTitle"})
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var singerID, albumID int64
		var albumTitle string
		if err := row.Columns(&singerID, &albumID, &albumTitle); err != nil {
			return err
		}
		fmt.Fprintf(w, "%d %d %s\n", singerID, albumID, albumTitle)
	}
}

Java

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.KeySet;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import java.util.Arrays;

public class ReadDataWithDatabaseRole {

  static void readDataWithDatabaseRole() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    String role = "my-role";
    readDataWithDatabaseRole(projectId, instanceId, databaseId, role);
  }

  static void readDataWithDatabaseRole(
      String projectId, String instanceId, String databaseId, String role) {
    try (Spanner spannerWithRole =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .setDatabaseRole(role)
            .build()
            .getService()) {
      DatabaseClient dbClient =
          spannerWithRole.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      ResultSet resultSet =
          dbClient
              .singleUse()
              .read(
                  "Singers",
                  KeySet.all(),
                  Arrays.asList("SingerId", "FirstName", "LastName"));
      while (resultSet.next()) {
        System.out.printf("SingerId: %d\n", resultSet.getLong(0));
        System.out.printf("FirstName: %s\n", resultSet.getString(1));
        System.out.printf("LastName: %s\n", resultSet.getString(2));
      }
    }
  }
}

Node.js

/**
 * TODO(developer): Uncomment these variables before running the sample.
 */
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// const projectId = 'my-project-id';
// Imports the Google Cloud Spanner client library
const {Spanner} = require('@google-cloud/spanner');

// Instantiates a client
const spanner = new Spanner({
  projectId: projectId,
});

async function readDataWithDatabaseRole() {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  // Connect to a database using the 'parent' database role. This means that the connection will only have the permissions that have explicitly been granted to the 'parent' role.
  const options = {
    databaseRole: 'parent',
  };
  const database = instance.database(databaseId, options);

  try {
    const query = {
      sql: 'SELECT SingerId, FirstName, LastName FROM Singers',
    };
    const [rows] = await database.run(query);

    for (const row of rows) {
      const json = row.toJSON();

      console.log(
        `SingerId: ${json.SingerId}, FirstName: ${json.FirstName}, LastName: ${json.LastName}`,
      );
    }
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    await database.close();
  }
}
readDataWithDatabaseRole();

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Read database with a database role.
 * Example:
 * ```
 * read_data_with_database_role($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function read_data_with_database_role(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $databaseRole = 'new_parent';
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId, ['databaseRole' => $databaseRole]);
    $results = $database->execute('SELECT * FROM Singers');

    foreach ($results as $row) {
        printf('SingerId: %s, Firstname: %s, LastName: %s' . PHP_EOL, $row['SingerId'], $row['FirstName'], $row['LastName']);
    }
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
role = "new_parent"
database = instance.database(database_id, database_role=role)

with database.snapshot() as snapshot:
    results = snapshot.execute_sql("SELECT * FROM Singers")
    for row in results:
        print("SingerId: {}, FirstName: {}, LastName: {}".format(*row))

Ruby

require "google/cloud/spanner"

def spanner_read_data_with_database_role project_id:, instance_id:, database_id:
  # project_id  = "Your Google Cloud project ID"
  # instance_id = "Your Spanner instance ID"
  # database_id = "Your Spanner database ID"

  role = "new_parent"
  spanner = Google::Cloud::Spanner.new project: project_id
  client = spanner.client instance_id, database_id, database_role: role

  result = client.execute_sql "SELECT * FROM Singers"

  result.rows.each do |row|
    puts "SingerId: #{row[:SingerId]}"
    puts "FirstName: #{row[:FirstName]}"
    puts "LastName: #{row[:LastName]}"
  end
end