設定精細的存取權控管機制

本頁面說明如何為 GoogleSQL 方言資料庫和 PostgreSQL 方言資料庫設定 Spanner 的精細存取控管。

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

如要設定精細的存取權控管，請按照下列步驟操作：

1. 建立資料庫角色並授予權限。

2. 選用：建立具有角色階層的繼承關係。

3. 將資料庫角色授予主體。

4. 告知使用者和開發人員使用資料庫角色。

精細存取控管使用者接著必須指定資料庫角色，才能對資料庫執行查詢、DML 或資料列作業。

事前準備

請確保要成為精細存取權控管使用者的每個主體，都已獲授 Cloud Spanner Viewer Identity and Access Management (IAM) 角色 (roles/spanner.viewer)。如果使用者必須在 Google Cloud 控制台中與 Spanner 資源互動，建議在專案層級指派這個角色。

如需操作說明，請參閱「授予主體權限」。

建立資料庫角色並授予權限

資料庫角色是一組精細的存取權限，每個資料庫最多可建立 100 個資料庫角色。

決定資料庫中的角色和角色階層，並以 DDL 編碼。 與 Spanner 中的其他結構定義變更一樣，我們強烈建議您以批次形式發布結構定義變更，而非個別發布。詳情請參閱限制結構定義更新頻率。

控制台

如要建立資料庫角色並授予精細的存取權限，請按照下列步驟操作：

1. 前往 Google Cloud 控制台的「Instances」(執行個體) 頁面。

   執行個體

2. 選取包含要新增角色的資料庫的執行個體。

3. 選取資料庫。

4. 在「總覽」頁面中，按一下「Spanner Studio」。

5. 在「Spanner Studio」頁面中，針對要建立及授予權限的每個資料庫角色，執行下列步驟：

   1. 如要建立角色，請輸入下列陳述式：

      CREATE ROLE ROLE_NAME;

      但先不要點選「提交」。

   2. 如要授予角色權限，請在 CREATE ROLE 陳述式後方的下一行輸入 GRANT 陳述式。

      如要瞭解 GRANT 陳述式的語法詳細資料，請參閱「GoogleSQL 資料定義語言」。如要瞭解權限，請參閱「精細的存取控管權限」。

      舉例來說，如要將資料表 employees 和 contractors 的 SELECT、INSERT 和 UPDATE 授予資料庫角色 hr_manager，請輸入下列陳述式：

      GoogleSQL

      GRANT SELECT, INSERT, UPDATE ON TABLE employees, contractors TO ROLE hr_manager;
      

      PostgreSQL

      GRANT SELECT, INSERT, UPDATE ON TABLE employees, contractors TO hr_manager;
      

      注意： 請使用與建立物件時相同的大小寫格式，為結構定義物件命名。舉例來說，如果您建立名為 Customers 的資料表，則必須在 GRANT 陳述式中使用名稱 Customers (C 為大寫)。

      您可以為 GRANT 陳述式使用 DDL 範本。在「Explorer」窗格中，前往要授予權限的角色。按一下 「查看動作」，然後選取要授予這個角色存取權的權限類型。GRANT 範本陳述式會在新編輯器分頁中填入。

6. 按一下「提交」。

   如果 DDL 有錯誤， Google Cloud 控制台會傳回錯誤。

gcloud

如要建立資料庫角色並授予精細的存取權限，請使用 gcloud spanner databases ddl update 指令搭配 CREATE ROLE 和 GRANT 陳述式。

如要瞭解 CREATE ROLE 和 GRANT 陳述式的語法詳細資料，請參閱 GoogleSQL 資料定義語言。

舉例來說，您可以使用下列指令建立資料庫角色，並授予一或多個資料表的權限。

GoogleSQL

gcloud spanner databases ddl update DATABASE_NAME --instance=INSTANCE_NAME \
--ddl='CREATE ROLE ROLE_NAME; GRANT PRIVILEGES ON TABLE TABLES TO ROLE ROLE_NAME;'

PostgreSQL

gcloud spanner databases ddl update DATABASE_NAME --instance=INSTANCE_NAME \
--ddl='CREATE ROLE ROLE_NAME; GRANT PRIVILEGES ON TABLE TABLES TO ROLE_NAME;'

更改下列內容：

• PRIVILEGES 是以逗號分隔的精細存取控管權限清單。如要瞭解權限，請參閱「精細的存取控管權限」。

• TABLES 是以半形逗號分隔的資料表清單。

舉例來說，如要將 SELECT、INSERT 和 UPDATE 授予執行個體 hr 中資料庫 hrdb1 的資料庫角色 hr_analyst，請輸入下列陳述式：employeescontractors

GoogleSQL

gcloud spanner databases ddl update hrdb1 --instance=hr \
--ddl='CREATE ROLE hr_analyst; GRANT SELECT, INSERT, UPDATE ON TABLE employees, contractors TO ROLE hr_analyst;'

PostgreSQL

gcloud spanner databases ddl update hrdb1 --instance=hr \
--ddl='CREATE ROLE hr_analyst; GRANT SELECT, INSERT, UPDATE ON TABLE employees, contractors TO hr_analyst;'

注意： 請使用建立物件時的相同大小寫格式，舉例來說，如果您建立的資料表名為 Customers，則必須在 GRANT 陳述式中使用名稱 Customers (含大寫 C)。

用戶端程式庫

這兩個程式碼範例都會建立及捨棄資料庫角色。

C++

void AddAndDropDatabaseRole(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id, std::string const& role_parent,
    std::string const& role_child) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::vector<std::string> grant_statements = {
      "CREATE ROLE " + role_parent,
      "GRANT SELECT ON TABLE Singers TO ROLE " + role_parent,
      "CREATE ROLE " + role_child,
      "GRANT ROLE " + role_parent + " TO ROLE " + role_child,
  };
  auto metadata =
      client.UpdateDatabaseDdl(database.FullName(), grant_statements).get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Created roles " << role_parent << " and " << role_child
            << " and granted privileges\n";

  std::vector<std::string> revoke_statements = {
      "REVOKE ROLE " + role_parent + " FROM ROLE " + role_child,
      "DROP ROLE " + role_child,
  };
  metadata =
      client.UpdateDatabaseDdl(database.FullName(), revoke_statements).get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Revoked privileges and dropped role " << role_child << "\n";
}

C#

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

public class AddAndDropDatabaseRoleAsyncSample
{
    public async Task AddDatabaseRoleAsync(string projectId, string instanceId, string databaseId, string databaseRole)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string createRoleStatement = $"CREATE ROLE {databaseRole}";

        // Creates the given database role.
        using var connection = new SpannerConnection(connectionString);
        using var updateCmd = connection.CreateDdlCommand(createRoleStatement);
        await updateCmd.ExecuteNonQueryAsync();
    }

    public async Task DropDatabaseRoleAsync(string projectId, string instanceId, string databaseId, string databaseRole)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string deleteRoleStatement = $"DROP ROLE {databaseRole}";

        // Drops the given database role.
        using var connection = new SpannerConnection(connectionString);
        using var updateCmd = connection.CreateDdlCommand(deleteRoleStatement);
        await updateCmd.ExecuteNonQueryAsync();
    }
}

Go

import (
	"context"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
)

func addAndDropDatabaseRole(w io.Writer, db string) error {
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	// Set up database roles and membership. After database roles are created,
	// users can be granted roles by setting IAM policies.
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: db,
		Statements: []string{
			"CREATE ROLE parent",
			"GRANT SELECT ON TABLE Albums TO ROLE parent",
			"CREATE ROLE child",
			"GRANT ROLE parent TO ROLE child",
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}

	// Delete role and membership.
	op, err = adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: db,
		Statements: []string{
			"REVOKE ROLE parent FROM ROLE child",
			"DROP ROLE child",
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}
	return nil
}

Java

import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class AddAndDropDatabaseRole {

  static void addAndDropDatabaseRole() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    String parentRole = "parent_role";
    String childRole = "child_role";
    addAndDropDatabaseRole(projectId, instanceId, databaseId, parentRole, childRole, "Albums");
  }

  static void addAndDropDatabaseRole(
      String projectId, String instanceId, String databaseId,
      String parentRole, String childRole, String... tables) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      System.out.println("Waiting for role create operation to complete...");
      List<String> roleStatements = new ArrayList<>(ImmutableList.of(
          String.format("CREATE ROLE %s", parentRole),
          String.format("CREATE ROLE %s", childRole),
          String.format("GRANT ROLE %s TO ROLE %s", parentRole, childRole)));
      for (String table : tables) {
        roleStatements.add(String.format("GRANT SELECT ON TABLE %s TO ROLE %s", table, parentRole));
      }
      databaseAdminClient.updateDatabaseDdlAsync(
              DatabaseName.of(projectId, instanceId, databaseId), roleStatements)
          .get(5, TimeUnit.MINUTES);
      System.out.printf(
          "Created roles %s and %s and granted privileges%n", parentRole, childRole);
      // Delete role and membership.
      System.out.println("Waiting for role revoke & drop operation to complete...");
      databaseAdminClient.updateDatabaseDdlAsync(
          DatabaseName.of(projectId, instanceId, databaseId),
          ImmutableList.of(
              String.format("REVOKE ROLE %s FROM ROLE %s", parentRole, childRole),
              String.format("DROP ROLE %s", childRole))).get(5, TimeUnit.MINUTES);
      System.out.printf("Revoked privileges and dropped role %s%n", childRole);
    } catch (ExecutionException | TimeoutException e) {
      System.out.printf(
          "Error: AddAndDropDatabaseRole failed with error message %s\n", e.getMessage());
      e.printStackTrace();
    } catch (InterruptedException e) {
      System.out.println(
          "Error: Waiting for AddAndDropDatabaseRole operation to finish was interrupted");
    }
  }
}

注意：舊版 Java 用戶端程式庫介面的程式碼範例已封存於 GitHub。

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 client library
const {Spanner} = require('@google-cloud/spanner');

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

const databaseAdminClient = spanner.getDatabaseAdminClient();

async function addAndDropNewDatabaseRole() {
  // Creates a new user defined role and grant permissions
  try {
    const request = [
      'CREATE ROLE parent',
      'GRANT SELECT ON TABLE Singers TO ROLE parent',
      'CREATE ROLE child',
      'GRANT ROLE parent TO ROLE child',
    ];
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId,
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log('Created roles child and parent and granted privileges');
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Revoke permissions and drop child role.
  // A role can't be dropped until all its permissions are revoked.
  try {
    const request = ['REVOKE ROLE parent FROM ROLE child', 'DROP ROLE child'];
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId,
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log('Revoked privileges and dropped role child');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the spanner client when finished.
    // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.
    spanner.close();
  }
}
addAndDropNewDatabaseRole();

注意：Node.js 的舊版用戶端程式庫介面程式碼範例已封存至 GitHub。

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;

/**
 * Adds and drops roles to the Singers table in the example database.
 * Example:
 * ```
 * add_drop_database_role($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function add_drop_database_role(string $projectId, string $instanceId, string $databaseId): void
{
    $databaseAdminClient = new DatabaseAdminClient();
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => [
            'CREATE ROLE new_parent',
            'GRANT SELECT ON TABLE Singers TO ROLE new_parent',
            'CREATE ROLE new_child',
            'GRANT ROLE new_parent TO ROLE new_child'
        ]
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

    printf('Waiting for create role and grant operation to complete...%s', PHP_EOL);
    $operation->pollUntilComplete();

    printf('Created roles %s and %s and granted privileges%s', 'new_parent', 'new_child', PHP_EOL);

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => [
            'REVOKE ROLE new_parent FROM ROLE new_child',
            'DROP ROLE new_child'
        ]
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

    printf('Waiting for revoke role and drop role operation to complete...%s', PHP_EOL);
    $operation->pollUntilComplete();

    printf('Revoked privileges and dropped role %s%s', 'new_child', PHP_EOL);
}

注意：舊版 PHP 用戶端程式庫介面程式碼範例已封存於 GitHub。

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()
database_admin_api = spanner_client.database_admin_api

role_parent = "new_parent"
role_child = "new_child"

request = spanner_database_admin.UpdateDatabaseDdlRequest(
    database=database_admin_api.database_path(
        spanner_client.project, instance_id, database_id
    ),
    statements=[
        "CREATE ROLE {}".format(role_parent),
        "GRANT SELECT ON TABLE Singers TO ROLE {}".format(role_parent),
        "CREATE ROLE {}".format(role_child),
        "GRANT ROLE {} TO ROLE {}".format(role_parent, role_child),
    ],
)
operation = database_admin_api.update_database_ddl(request)

operation.result(OPERATION_TIMEOUT_SECONDS)
print(
    "Created roles {} and {} and granted privileges".format(role_parent, role_child)
)

request = spanner_database_admin.UpdateDatabaseDdlRequest(
    database=database_admin_api.database_path(
        spanner_client.project, instance_id, database_id
    ),
    statements=[
        "REVOKE ROLE {} FROM ROLE {}".format(role_parent, role_child),
        "DROP ROLE {}".format(role_child),
    ],
)
operation = database_admin_api.update_database_ddl(request)

operation.result(OPERATION_TIMEOUT_SECONDS)
print("Revoked privileges and dropped role {}".format(role_child))

注意：舊版 Python 用戶端程式庫介面的程式碼範例已封存至 GitHub。

小茹

require "google/cloud/spanner"

def spanner_add_and_drop_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"

  admin_client = Google::Cloud::Spanner::Admin::Database::V1::DatabaseAdmin::Client.new
  role_parent = "new_parent"
  role_child = "new_child"

  db_path = admin_client.database_path project: project_id, instance: instance_id, database: database_id

  job = admin_client.update_database_ddl database: db_path, statements: [
    "CREATE ROLE #{role_parent}",
    "GRANT SELECT ON TABLE Singers TO ROLE #{role_parent}",
    "CREATE ROLE #{role_child}",
    "GRANT ROLE #{role_parent} TO ROLE #{role_child}"
  ]

  job.wait_until_done!
  puts "Created roles #{role_parent} and #{role_child} and granted privileges"


  job = admin_client.update_database_ddl database: db_path, statements: [
    "REVOKE ROLE #{role_parent} FROM ROLE #{role_child}",
    "DROP ROLE #{role_child}"
  ]

  job.wait_until_done!
  puts "Revoked privileges and dropped role #{role_child}"
end

建立具有繼承權的角色階層

您可以將一個資料庫角色授予另一個角色，藉此建立資料庫角色階層。子角色 (又稱「成員角色」) 會沿用父項的權限。

如要將資料庫角色授予其他資料庫角色，請使用下列陳述式：

GoogleSQL

GRANT ROLE role1 TO ROLE role2;

PostgreSQL

GRANT role1 TO role2;

詳情請參閱「資料庫角色階層和繼承」。

將資料庫角色授予主體

如要存取 Spanner 資源，必須使用下列其中一種方法，授予主體必要的資料庫角色：

控制台

1. 在資料庫的「總覽」頁面中，如果「資訊面板」尚未開啟，請點選「顯示資訊面板」。

2. 按一下「新增主體」。

3. 在「Add principals」(新增主體) 下方的「New principals」(新增主體) 中，輸入一或多個主體。

4. 在「指派角色」下方，依序選取「選取角色」>「Cloud Spanner」>「Cloud Spanner 細部存取權使用者」。

   您只需要為每個主體授予一次這個角色。這會將主體設為精細存取控管使用者。

5. 點選「新增其他角色」。

6. 在「請選擇角色」中，依序選取「Cloud Spanner」>「Cloud Spanner 資料庫角色使用者」。

   注意： 請避免授予 Cloud Spanner 資料庫使用者角色。角色。授予過多權限。詳情請參閱「Cloud Spanner Database User」。

7. 請按照下列步驟建立 IAM 條件，指定要授予的角色。

   注意： 如果未新增條件，主體就能存取所有資料庫角色。

   1. 在「Cloud Spanner 資料庫角色使用者」角色旁邊，按一下「新增 IAM 條件」。

   2. 在「新增條件」面板中，輸入條件的標題和說明 (選填)。

      如果您要授予單一資料庫角色，通常會在條件標題中加入角色名稱。如果授予多個角色，可以指出角色組合的相關事項。

   3. 按一下「條件編輯器」。

   4. 在「運算式」欄位中輸入下列程式碼：

      resource.type == "spanner.googleapis.com/DatabaseRole" &&
      resource.name.endsWith("/ROLE")

      將 ROLE 替換為您的角色名稱。

      如要將多個角色授予主體，請使用 or (||) 運算子新增更多條件，如下列範例所示：

      resource.type == "spanner.googleapis.com/DatabaseRole" &&
      (resource.name.endsWith("/ROLE1") || resource.name.endsWith("/ROLE2"))

      這組代碼會授予兩個角色。將 ROLE1 和 ROLE2 替換為您的角色名稱。如要授予超過兩個角色，請新增更多「或」條件。

      您可以使用 IAM 支援的任何條件運算式。詳情請參閱「身分與存取權管理條件總覽」。

   5. 按一下 [儲存]。

   6. 確認條件顯示在「角色」欄位旁的「IAM 條件」欄中。

   7. 按一下 [儲存]。

      返回「Info panel」(資訊面板)，在「Role/Principal」(角色/主體) 下，您會看到每個已定義的條件都顯示「Cloud Spanner Database Role User」(Cloud Spanner 資料庫角色使用者)。

      條件旁邊括號中的數字，表示該條件授予資料庫角色的主體數量。您可以點選展開箭頭，查看主體清單。

   8. 如要修正資料庫角色名稱或條件的錯誤，或是為主體新增其他資料庫角色，請按照下列步驟操作：

      1. 展開列出所需條件的「Cloud Spanner Database Role User」(Cloud Spanner 資料庫角色使用者) 項目。

      2. 按一下主體旁的「編輯」 (鉛筆) 圖示。

      3. 在「Edit access to database_name」(編輯「database_name」的存取權) 面板中，執行 下列任一操作：

         • 點選「新增其他角色」。

         • 如要編輯條件，請按一下條件名稱旁的「編輯」 (鉛筆) 圖示。接著在「編輯條件」頁面中，按一下「條件編輯器」，進行修正，然後按兩下「儲存」。

gcloud

1. 使用 gcloud spanner databases add-iam-policy-binding 指令，為主體啟用精細的存取控管機制，如下所示：

   gcloud spanner databases add-iam-policy-binding DATABASE_NAME \
   --instance=INSTANCE_NAME \
   --role=roles/spanner.fineGrainedAccessUser \
   --member=MEMBER_NAME \
   --condition=None

   • MEMBER_NAME 是主體的 ID。必須使用下列其中一種語法： user|group|serviceAccount:email 或 domain:domain。

   • 這項指令會將主體設為精細存取控管使用者。每個主體只能提交一次這個指令。

   • 如果成功，指令會輸出資料庫的完整政策。

2. 使用 gcloud spanner databases add-iam-policy-binding 指令授予使用一或多個資料庫角色的權限，如下所示：

   gcloud spanner databases add-iam-policy-binding DATABASE_NAME \
   --instance=INSTANCE_NAME \
   --role=roles/spanner.databaseRoleUser \
   --member=MEMBER_NAME \
   --condition=CONDITION

   • MEMBER_NAME 是主體的 ID。必須使用下列其中一種語法： user|group|serviceAccount:email 或 domain:domain。

   • CONDITION 是 IAM 條件運算式，用於指定要授予主體的角色。

     CONDITION 的格式如下：

     --condition='expression=(resource.type == "spanner.googleapis.com/DatabaseRole" && resource.name.endsWith("/ROLE1")),title=TITLE,description=DESCRIPTION'

     如要授予主體多個角色的存取權，請使用 or (||) 運算子新增更多條件，如下列範例所示：

     --condition='expression=(resource.type == "spanner.googleapis.com/DatabaseRole" && (resource.name.endsWith("/ROLE1") || resource.name.endsWith("/ROLE2"))),title=TITLE,description=DESCRIPTION'

     這組代碼會授予兩個角色。將 ROLE1 和 ROLE2 替換為您的角色名稱。如要授予兩個以上的角色，請使用 || 運算子新增更多 或條件。

     您可以使用 IAM 支援的任何條件運算式。詳情請參閱「身分與存取權管理條件總覽」。

   注意： 如果未指定條件，主體可以存取所有資料庫角色。

   如果成功，指令會輸出資料庫的完整政策。

   以下範例會將資料庫角色 hr_rep 和 hr_manager 授予給主體 [e-mail]。

   gcloud spanner databases add-iam-policy-binding myDatabase \
     --instance=myInstance \
     --role=roles/spanner.databaseRoleUser \
     --member=user:[e-mail] \
     --condition='expression=(resource.type == "spanner.googleapis.com/DatabaseRole" && (resource.name.endsWith("/hr_rep") || resource.name.endsWith("/hr_manager"))),title=HR roles,description=Grant permissions on HR roles'
   

用戶端程式庫

C++

void EnableFineGrainedAccess(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id, std::string const& iam_member,
    std::string const& database_role, std::string const& title) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);

  google::iam::v1::GetIamPolicyRequest request;
  request.set_resource(database.FullName());
  request.mutable_options()->set_requested_policy_version(3);
  auto policy = client.GetIamPolicy(request);
  if (!policy) throw std::move(policy).status();
  if (policy->version() < 3) policy->set_version(3);

  auto& binding = *policy->add_bindings();
  binding.set_role("roles/spanner.fineGrainedAccessUser");
  binding.add_members(iam_member);
  auto& condition = *binding.mutable_condition();
  condition.set_expression("resource.name.endsWith(\"/databaseRoles/" +
                           database_role + "\")");
  condition.set_title(title);

  auto new_policy =
      client.SetIamPolicy(database.FullName(), *std::move(policy));
  if (!new_policy) throw std::move(new_policy).status();
  std::cout << "Enabled fine-grained access in IAM. New policy has version "
            << new_policy->version() << "\n";
}

C#

using Google.Api.Gax;
using Google.Cloud.Iam.V1;
using Google.Cloud.Spanner.Admin.Database.V1;

public class EnableFineGrainedAccessSample
{
    public Policy EnableFineGrainedAccess(
        string projectId, string instanceId, string databaseId, 
        string databaseRole, string iamMember)
    {
        var resourceName = new UnparsedResourceName($"projects/{projectId}/instances/{instanceId}/databases/{databaseId}");

        var client = new DatabaseAdminClientBuilder().Build();

        // Request policy version 3 as earlier versions do not support condition field in role binding.
        // For more information see https://cloud.google.com/iam/docs/policies#versions.

        GetIamPolicyRequest getIamPolicyRequest = new GetIamPolicyRequest
        {
            ResourceAsResourceName = resourceName,
            Options = new GetPolicyOptions
            {
                RequestedPolicyVersion = 3
            }
        };

        var policy = client.GetIamPolicy(getIamPolicyRequest);

        // Gives the given IAM member access to the all the database roles
        // with resource name ending in ../databaseRoles/{databaseRole}.
        // For more information see https://cloud.google.com/iam/docs/conditions-overview.
        Binding newBinding = new Binding
        {
            Role = "roles/spanner.fineGrainedAccessUser",
            Members = { iamMember },
            Condition = new Google.Type.Expr
            {
                Title = "DatabaseRoleBindingTitle",
                Expression = $"resource.name.endsWith('/databaseRoles/{databaseRole}')"
            }
        };

        policy.Bindings.Add(newBinding);
        if (policy.Version < 3)
        {
            policy.Version = 3;
        }
        SetIamPolicyRequest setIamPolicyRequest = new SetIamPolicyRequest
        {
            Policy = policy,
            ResourceAsResourceName = resourceName,
        };
        var updatedPolicy = client.SetIamPolicy(setIamPolicyRequest);
        return updatedPolicy;
    }
}

Go

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/iam/apiv1/iampb"
	database "cloud.google.com/go/spanner/admin/database/apiv1"
	expr "google.golang.org/genproto/googleapis/type/expr"
)

func enableFineGrainedAccess(w io.Writer, db string, iamMember string, databaseRole string, title string) error {
	// iamMember = "user:[e-mail]"
	// databaseRole = "parent"
	// title = "condition title"
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	policy, err := adminClient.GetIamPolicy(ctx, &iampb.GetIamPolicyRequest{
		Resource: db,
		Options: &iampb.GetPolicyOptions{
			// IAM conditions need at least version 3
			RequestedPolicyVersion: 3,
		},
	})
	if err != nil {
		return err
	}

	// IAM conditions need at least version 3
	if policy.Version < 3 {
		policy.Version = 3
	}
	policy.Bindings = append(policy.Bindings, []*iampb.Binding{
		{
			Role:    "roles/spanner.fineGrainedAccessUser",
			Members: []string{iamMember},
		},
		{
			Role:    "roles/spanner.databaseRoleUser",
			Members: []string{iamMember},
			Condition: &expr.Expr{
				Expression: fmt.Sprintf(`resource.name.endsWith("/databaseRoles/%s")`, databaseRole),
				Title:      title,
			},
		},
	}...)
	_, err = adminClient.SetIamPolicy(ctx, &iampb.SetIamPolicyRequest{
		Resource: db,
		Policy:   policy,
	})
	if err != nil {
		return err
	}

	fmt.Fprintf(w, "Enabled fine-grained access in IAM.\n")
	return nil
}

Java

import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.iam.v1.Binding;
import com.google.iam.v1.GetIamPolicyRequest;
import com.google.iam.v1.GetPolicyOptions;
import com.google.iam.v1.Policy;
import com.google.iam.v1.SetIamPolicyRequest;
import com.google.spanner.admin.database.v1.DatabaseName;
import com.google.type.Expr;

public class EnableFineGrainedAccess {

  static void enableFineGrainedAccess() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    String iamMember = "user:[e-mail]";
    String role = "my-role";
    String title = "my-condition-title";
    enableFineGrainedAccess(projectId, instanceId, databaseId, iamMember, title, role);
  }

  static void enableFineGrainedAccess(
      String projectId,
      String instanceId,
      String databaseId,
      String iamMember,
      String title,
      String role) {
    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      final GetPolicyOptions options =
          GetPolicyOptions.newBuilder().setRequestedPolicyVersion(3).build();
      final GetIamPolicyRequest getRequest =
          GetIamPolicyRequest.newBuilder()
              .setResource(DatabaseName.of(projectId, instanceId, databaseId).toString())
              .setOptions(options).build();
      final Policy policy = databaseAdminClient.getIamPolicy(getRequest);
      int policyVersion = policy.getVersion();
      // The policy in the response from getDatabaseIAMPolicy might use the policy version
      // that you specified, or it might use a lower policy version. For example, if you
      // specify version 3, but the policy has no conditional role bindings, the response
      // uses version 1. Valid values are 0, 1, and 3.
      if (policy.getVersion() < 3) {
        // conditional role bindings work with policy version 3
        policyVersion = 3;
      }

      Binding binding1 =
          Binding.newBuilder()
              .setRole("roles/spanner.fineGrainedAccessUser")
              .addAllMembers(ImmutableList.of(iamMember))
              .build();

      Binding binding2 =
          Binding.newBuilder()
              .setRole("roles/spanner.databaseRoleUser")
              .setCondition(
                  Expr.newBuilder().setDescription(title).setExpression(
                      String.format("resource.name.endsWith(\"/databaseRoles/%s\")", role)
                  ).setTitle(title).build())
              .addAllMembers(ImmutableList.of(iamMember))
              .build();
      ImmutableList<Binding> bindings =
          ImmutableList.<Binding>builder()
              .addAll(policy.getBindingsList())
              .add(binding1)
              .add(binding2)
              .build();
      Policy policyWithConditions =
          Policy.newBuilder()
              .setVersion(policyVersion)
              .setEtag(policy.getEtag())
              .addAllBindings(bindings)
              .build();
      final SetIamPolicyRequest setRequest =
          SetIamPolicyRequest.newBuilder()
              .setResource(DatabaseName.of(projectId, instanceId, databaseId).toString())
              .setPolicy(policyWithConditions).build();
      final Policy response = databaseAdminClient.setIamPolicy(setRequest);
      System.out.printf(
          "Enabled fine-grained access in IAM with version %d%n", response.getVersion());
    }
  }
}

注意：舊版 Java 用戶端程式庫介面的程式碼範例已封存於 GitHub。

Node.js

/**
 * TODO(developer): Uncomment these variables before running the sample.
 */
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// const projectId = 'my-project-id';
// iamMember = 'user:[e-mail]';
// databaseRole = 'parent';
// title = 'condition title';
// Imports the Google Cloud Spanner client library
const {Spanner, protos} = require('@google-cloud/spanner');

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

async function enableFineGrainedAccess() {
  // Gets a reference to a Cloud Spanner Database Admin Client object
  const databaseAdminClient = spanner.getDatabaseAdminClient();

  const [policy] = await databaseAdminClient.getIamPolicy({
    resource: databaseAdminClient.databasePath(
      projectId,
      instanceId,
      databaseId,
    ),
    options: (protos.google.iam.v1.GetPolicyOptions = {
      requestedPolicyVersion: 3,
    }),
  });
  if (policy.version < 3) {
    policy.version = 3;
  }

  const newBinding = {
    role: 'roles/spanner.fineGrainedAccessUser',
    members: [`user:${iamMember}`],
    condition: {
      title: title,
      expression: `resource.name.endsWith("/databaseRoles/${databaseRole}")`,
    },
  };
  policy.bindings.push(newBinding);
  await databaseAdminClient.setIamPolicy({
    resource: databaseAdminClient.databasePath(
      projectId,
      instanceId,
      databaseId,
    ),
    policy: policy,
  });
  // Requested Policy Version is Optional. The maximum policy version that will be used to format the policy.
  // Valid values are 0, 1, and 3. Requests specifying an invalid value will be rejected.
  const newPolicy = await databaseAdminClient.getIamPolicy({
    resource: databaseAdminClient.databasePath(
      projectId,
      instanceId,
      databaseId,
    ),
    options: (protos.google.iam.v1.GetPolicyOptions = {
      requestedPolicyVersion: 3,
    }),
  });
  console.log(newPolicy);
}
enableFineGrainedAccess();

注意：Node.js 的舊版用戶端程式庫介面程式碼範例已封存至 GitHub。

PHP

use \Google\Cloud\Iam\V1\Binding;
use \Google\Type\Expr;
use Google\Cloud\Iam\V1\GetIamPolicyRequest;
use Google\Cloud\Iam\V1\SetIamPolicyRequest;
use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;

/**
 * Enable Fine Grained Access.
 * Example:
 * ```
 * enable_fine_grained_access($projectId, $instanceId, $databaseId, $iamMember, $databaseRole, $title);
 * ```
 *
 * @param string $projectId The Google cloud project ID
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $iamMember The IAM member. Eg: `user:{emailid}`,
 *        `serviceAccount:{emailid}`, `group:{emailid}`, `domain:{domain}`
 * @param string $databaseRole The database role bound to
 *        the IAM member.
 * @param string $title Condition title.
 */
function enable_fine_grained_access(
    string $projectId,
    string $instanceId,
    string $databaseId,
    string $iamMember,
    string $databaseRole,
    string $title
): void {
    $adminClient = new DatabaseAdminClient();
    $resource = $adminC>lient-databaseName($projectId, $instanceId, $databaseId);
    $getIamPolicyRequest = (new GetIamPolicyRequest())
   >     -setResource($resource);
    $policy = $adminC>lient-getIamPolicy($getIamPolicyRequest);

    // IAM conditions need at least version 3
    if ($p>olicy-getVersion() != 3) {
        $p>olicy-setVersion(3);
    }

    $binding = new Binding([
        'r>ole' = 'roles/spanner.fineGrainedAccessUser',
        'memb>ers' = [$iamMember],
        'condit>ion' = new Expr([
            'ti>tle' = $title,
            'express>ion' = sprintf("resource.name.endsWith('/databaseRoles/%s')", $databaseRole)
        ])
    ]);
    $p>olicy-setBindings([$binding]);
    $setIamPolicyRequest = (new SetIamPolicyRequest())
   >     -setResource($resource)
   >     -setPolicy($policy);
    $adminC>lient-setIamPolicy($setIamPolicyRequest);

    printf('Enabled fine-grained access in IAM' . PHP_EOL);
}

注意：舊版 PHP 用戶端程式庫介面程式碼範例已封存於 GitHub。

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
# iam_member = "user:[e-mail]"
# database_role = "new_parent"
# title = "condition title"

from google.iam.v1 import iam_policy_pb2, options_pb2, policy_pb2
from google.type import expr_pb2

spanner_client = spanner.Client()
database_admin_api = spanner_client.database_admin_api

# The policy in the response from getDatabaseIAMPolicy might use the policy version
# that you specified, or it might use a lower policy version. For example, if you
# specify version 3, but the policy has no conditional role bindings, the response
# uses version 1. Valid values are 0, 1, and 3.
request = iam_policy_pb2.GetIamPolicyRequest(
    resource=database_admin_api.database_path(
        spanner_client.project, instance_id, database_id
    ),
    options=options_pb2.GetPolicyOptions(requested_policy_version=3),
)
policy = database_admin_api.get_iam_policy(request=request)
if policy.version < 3:
    policy.version = 3

new_binding = policy_pb2.Binding(
    role="roles/spanner.fineGrainedAccessUser",
    members=[iam_member],
    condition=expr_pb2.Expr(
        title=title,
        expression=f'resource.name.endsWith("/databaseRoles/{database_role}")',
    ),
)

policy.version = 3
policy.bindings.append(new_binding)
set_request = iam_policy_pb2.SetIamPolicyRequest(
    resource=database_admin_api.database_path(
        spanner_client.project, instance_id, database_id
    ),
    policy=policy,
)
database_admin_api.set_iam_policy(set_request)

new_policy = database_admin_api.get_iam_policy(request=request)
print(
    f"Enabled fine-grained access in IAM. New policy has version {new_policy.version}"
)

注意：舊版 Python 用戶端程式庫介面的程式碼範例已封存至 GitHub。

小茹

require "google/cloud/spanner"

def spanner_enable_fine_grained_access project_id:, instance_id:, database_id:, iam_member:, database_role:, title:
  # project_id  = "Your Google Cloud project ID"
  # instance_id = "Your Spanner instance ID"
  # database_id = "Your Spanner database ID"
  # iam_member = "user:[e-mail]"
  # database_role = "new_parent"
  # title = "condition title"

  admin_client = Google::Cloud::Spanner::Admin::Database::V1::DatabaseAdmin::Client.new
  db_path = admin_client.database_path project: project_id, instance: instance_id, database: database_id

  policy = admin_client.get_iam_policy resource: db_path, options: { requested_policy_version: 3 }

  policy.version = 3 if policy.version < 3

  binding = Google::Iam::V1::Binding.new(
    role: "roles/spanner.fineGrainedAccessUser",
    members: [iam_member],
    condition: Google::Type::Expr.new(
      title: title,
      expression: "resource.name.endsWith('/databaseRoles/#{database_role}')"
    )
  )

  policy.bindings << binding
  result = admin_client.set_iam_policy resource: db_path, policy: policy

  puts "Enabled fine-grained access in IAM."
end

通知使用者和開發人員開始使用資料庫角色

完成初始的精細存取控管設定後，請通知使用者和應用程式開發人員，他們必須開始使用資料庫角色。

• 精細存取權控管使用者透過 Google Cloud 控制台或 Google Cloud CLI 存取 Spanner 資料庫時，必須開始指定資料庫角色。

• 使用精細存取控管機制的應用程式存取資料庫時，必須指定資料庫角色。

詳情請參閱「使用精細的存取控管機制存取資料庫」。

將主體轉換為精細的存取權控管機制

如要將主體從資料庫層級存取控管機制轉換為精細存取控管機制，請按照下列步驟操作：

1. 為主體啟用精細的存取控管機制，並授予所有必要資料庫角色的存取權，詳情請參閱「授予主體資料庫角色」。

2. 更新以這個主體身分執行的所有應用程式。在呼叫用戶端程式庫方法時，指定適當的資料庫角色。

3. 撤銷主體的所有 IAM 資料庫層級角色。 這樣一來，主體的存取權只會由一種方法控管。

   例外狀況：如要在Google Cloud 控制台中與 Spanner 資源互動，所有使用者都必須具備 roles/spanner.viewer IAM 角色。

   如要撤銷 IAM 資料庫層級角色，請按照「移除資料庫層級權限」一文中的操作說明進行。

列出資料庫角色

注意： Spanner Studio (在 Google Cloud 控制台中標示為「查詢」) 支援在單一編輯器中執行 SQL、DML 和 DDL 作業。詳情請參閱「使用 Google Cloud 控制台管理資料」。

您可以列出與資料庫相關聯的資料庫角色。

控制台

如要列出資料庫角色，請在資料庫的 Spanner Studio 頁面中輸入下列查詢：

GoogleSQL

SELECT * FROM INFORMATION_SCHEMA.ROLES;

PostgreSQL

SELECT * FROM information_schema.enabled_roles;

回應會包含目前角色，以及目前角色可透過繼承使用的角色權限。如要擷取所有角色，請使用 gcloud CLI 指令。

gcloud

如要取得未經過濾的資料庫角色清單，請輸入下列指令。 這項功能需要 spanner.databaseRoles.list 權限。

gcloud spanner databases roles list --database=DATABASE_NAME --instance=INSTANCE_NAME

用戶端程式庫

C++

void ListDatabaseRoles(google::cloud::spanner_admin::DatabaseAdminClient client,
                       std::string const& project_id,
                       std::string const& instance_id,
                       std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::cout << "Database Roles are:\n";
  for (auto& role : client.ListDatabaseRoles(database.FullName())) {
    if (!role) throw std::move(role).status();
    std::cout << role->name() << "\n";
  }
}

C#

using Google.Api.Gax;
using Google.Cloud.Spanner.Admin.Database.V1;
using System;

public class ListDatabaseRolesSample
{
    public PagedEnumerable<ListDatabaseRolesResponse, DatabaseRole> ListDatabaseRoles(string projectId, string instanceId, string databaseId)
    {
        string parent = $"projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        var client = DatabaseAdminClient.Create();
        PagedEnumerable<ListDatabaseRolesResponse, DatabaseRole> databaseRoles = client.ListDatabaseRoles(parent);
        foreach (var dbRole in databaseRoles)
        {
            Console.WriteLine($"Database Role: {dbRole.DatabaseRoleName}");
        }
        return databaseRoles;
    }
}

Go

import (
	"context"
	"fmt"
	"io"
	"strings"

	"google.golang.org/api/iterator"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
)

func listDatabaseRoles(w io.Writer, db string) error {
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	iter := adminClient.ListDatabaseRoles(ctx, &adminpb.ListDatabaseRolesRequest{
		Parent: db,
	})
	rolePrefix := db + "/databaseRoles/"
	for {
		role, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		if !strings.HasPrefix(role.Name, rolePrefix) {
			return fmt.Errorf("Role %v does not have prefix %v", role.Name, rolePrefix)
		}
		fmt.Fprintf(w, "%s\n", strings.TrimPrefix(role.Name, rolePrefix))
	}
	return nil
}

Java

import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient.ListDatabaseRolesPage;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient.ListDatabaseRolesPagedResponse;
import com.google.spanner.admin.database.v1.DatabaseName;
import com.google.spanner.admin.database.v1.DatabaseRole;

public class ListDatabaseRoles {

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

  static void listDatabaseRoles(String projectId, String instanceId, String databaseId) {
    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      DatabaseName databaseName = DatabaseName.of(projectId, instanceId, databaseId);
      ListDatabaseRolesPagedResponse response
          = databaseAdminClient.listDatabaseRoles(databaseName);
      System.out.println("List of Database roles");
      for (ListDatabaseRolesPage page : response.iteratePages()) {
        for (DatabaseRole role : page.iterateAll()) {
          System.out.printf("Obtained role %s%n", role.getName());
        }
      }
    }
  }
}

注意：舊版 Java 用戶端程式庫介面的程式碼範例已封存於 GitHub。

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 client library
const {Spanner} = require('@google-cloud/spanner');

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

const databaseAdminClient = spanner.getDatabaseAdminClient();

async function getDatabaseRoles() {
  // Fetching database roles
  const [databaseRoles] = await databaseAdminClient.listDatabaseRoles({
    parent: databaseAdminClient.databasePath(
      projectId,
      instanceId,
      databaseId,
    ),
  });
  console.log(
    `Roles for Database: ${databaseAdminClient.databasePath(
      projectId,
      instanceId,
      databaseId,
    )}`,
  );
  databaseRoles.forEach(role => {
    console.log(`Role: ${role.name}`);
  });
}
getDatabaseRoles();

注意：Node.js 的舊版用戶端程式庫介面程式碼範例已封存至 GitHub。

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\ListDatabaseRolesRequest;

/**
 * List Database roles in the given database.
 * Example:
 * ```
 * list_database_roles($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google cloud project ID
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function list_database_roles(
    string $projectId,
    string $instanceId,
    string $databaseId
): void {
    $adminClient = new DatabaseAdminClient();
    $resource = $adminClient->databaseName($projectId, $instanceId, $databaseId);
    $listDatabaseRolesRequest = (new ListDatabaseRolesRequest())
        ->setParent($resource);

    $roles = $adminClient->listDatabaseRoles($listDatabaseRolesRequest);
    printf('List of Database roles:' . PHP_EOL);
    foreach ($roles as $role) {
        printf($role->getName() . PHP_EOL);
    }
}

注意：舊版 PHP 用戶端程式庫介面程式碼範例已封存於 GitHub。

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()
database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.ListDatabaseRolesRequest(
    parent=database_admin_api.database_path(
        spanner_client.project, instance_id, database_id
    )
)
# List database roles.
print("Database Roles are:")
for role in database_admin_api.list_database_roles(request):
    print(role.name.split("/")[-1])

注意：舊版 Python 用戶端程式庫介面的程式碼範例已封存至 GitHub。

小茹

require "google/cloud/spanner"

def spanner_list_database_roles 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"

  admin_client = Google::Cloud::Spanner::Admin::Database::V1::DatabaseAdmin::Client.new

  db_path = admin_client.database_path project: project_id, instance: instance_id, database: database_id

  result = admin_client.list_database_roles parent: db_path

  puts "List of Database roles:"
  result.each do |role|
    puts role.name
  end
end

查看授予資料庫角色的權限

如要查看授予角色的權限，請執行下列查詢：

GoogleSQL

SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE grantee = 'ROLE_NAME';
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE grantee = 'ROLE_NAME';
SELECT * FROM INFORMATION_SCHEMA.CHANGE_STREAM_PRIVILEGES WHERE grantee = 'ROLE_NAME';

INFORMATION_SCHEMA.TABLE_PRIVILEGES 會傳回資料表和檢視表的權限。TABLE_PRIVILEGES 中的 SELECT、INSERT 和 UPDATE 權限也會顯示在 COLUMN_PRIVILEGES 中。

注意： 如果資料表的所有權限都僅限於部分資料欄，則該資料表不會納入 TABLE_PRIVILEGES 查詢的結果集。

PostgreSQL

SELECT * FROM information_schema.table_privileges WHERE grantee = 'ROLE_NAME';
SELECT * FROM information_schema.column_privileges WHERE grantee = 'ROLE_NAME';
SELECT * FROM information_schema.change_stream_privileges WHERE grantee = 'ROLE_NAME';

information_schema.table_privileges 會傳回資料表和檢視表的權限。table_privileges 中的「SELECT」、「INSERT」和「UPDATE」權限也會顯示在 column_privileges 中。

注意： 如果資料表的所有權限都僅限於部分資料欄，則該資料表不會納入 table_privileges 查詢的結果集。

查看精細存取控管機制使用者

如要查看細部存取權控管使用者主體的清單，請執行下列指令。如要執行指令，您必須在專案中啟用 Cloud Asset API，並具備 cloudasset.assets.searchAllIamPolicies IAM 權限。

gcloud asset search-all-iam-policies \
--scope=projects/PROJECT_NAME \
--query='roles=roles/spanner.fineGrainedAccessUser AND resource=//spanner.googleapis.com/projects/PROJECT_NAME/instances/INSTANCE_NAME/databases/DATABASE_NAME' \
--flatten=policy.bindings[].members[] \
--format='table(policy.bindings.members)'

輸出結果會與下列內容相似：

MEMBERS
user:[e-mail]
user:[e-mail]
serviceAccount:[e-mail]
serviceAccount:[e-mail]

詳情請參閱「在專案中 Google Cloud 啟用 API」一文。

查看具有資料庫存取權的主體

如要查看已獲授權存取特定資料庫的主體清單，請執行下列指令。如要執行這些指令，您必須在專案中啟用 Cloud Asset API，並具備 cloudasset.assets.searchAllIamPolicies IAM 權限。

gcloud asset search-all-iam-policies \
--scope=projects/PROJECT_NAME \
--query='roles=roles/spanner.databaseRoleUser AND policy:"resource.name" AND policy:/ROLE_NAME AND resource=//spanner.googleapis.com/projects/PROJECT_NAME/instances/INSTANCE_NAME/databases/DATABASE_NAME' \
--flatten=policy.bindings[].members[] \
--format='table(policy.bindings.members)'

輸出結果會與下列內容相似：

MEMBERS
[e-mail]

查看主體的 IAM 條件

如要查看將「Cloud Spanner 資料庫角色使用者」角色授予主體時指定的 IAM 條件清單，請執行下列指令：

gcloud asset search-all-iam-policies \
--scope=projects/PROJECT_NAME \
--query='roles=roles/spanner.databaseRoleUser AND policy:resource.name AND policy:"PRINCIPAL_IDENTIFIER" AND resource=//spanner.googleapis.com/projects/PROJECT_NAME/instances/INSTANCE_NAME/databases/DATABASE_NAME' \
--flatten=policy.bindings[] \
--format='table(policy.bindings.condition.expression)'

其中 PRINCIPAL_IDENTIFIER 為：

  { user:user-account-name | serviceAccount:service-account-name }

PRINCIPAL_IDENTIFIER 範例：

user:[e-mail]
serviceAccount:[e-mail]

下列範例輸出內容顯示兩個條件運算式。

EXPRESSION
resource.type == "spanner.googleapis.com/DatabaseRole" &&
resource.name.endsWith("/hr_analyst")
resource.type == "spanner.googleapis.com/DatabaseRole" &&
resource.name.endsWith("/hr_manager")

檢查 IAM 政策是否缺少資料庫角色條件

將資料庫角色存取權授予主體後，建議您確保每個 IAM 繫結都指定了條件。

如要執行這項檢查，請執行下列指令：

gcloud asset search-all-iam-policies \
--scope=projects/PROJECT_NAME \
--query='roles:roles/spanner.databaseRoleUser AND resource=//spanner.googleapis.com/projects/PROJECT_NAME/instances/INSTANCE_NAME/databases/DATABASE_NAME'
--flatten=policy.bindings[].members[]

輸出結果會與下列內容相似：

ROLE                              MEMBERS                         EXPRESSION
roles/spanner.databaseRoleUser    serviceAccount:cs-fgac-sa-1@...
roles/spanner.databaseRoleUser    serviceAccount:cs-fgac-sa-2@... resource.type == "spanner…"

請注意，第一個結果缺少條件，因此這個繫結中的主體可以存取所有資料庫角色。

捨棄資料庫角色

捨棄資料庫角色會自動撤銷其他角色的成員資格，以及該角色在其他角色中的成員資格。

如要捨棄資料庫角色，請先執行下列操作：

1. 從角色撤銷所有精細存取控管權限。
2. 移除參照該角色的所有 IAM 政策繫結，這樣稍後建立的同名資料庫角色就不會繼承這些繫結。

控制台

如要捨棄資料庫角色，請按照下列步驟操作：

1. 在資料庫的「總覽」頁面，點選「Spanner Studio」。

2. 如要撤銷角色的權限，請輸入 REVOKE 陳述式。

   GoogleSQL

   如要瞭解 REVOKE 陳述式的語法詳細資料，請參閱「GoogleSQL 資料定義語言」。如要瞭解權限，請參閱「精細的存取控管權限」。

   舉例來說，如要撤銷資料庫角色 hr_manager 對資料表 employees 和 contractors 的 SELECT、INSERT 和 UPDATE 權限，請輸入下列陳述式：

   REVOKE SELECT, INSERT, UPDATE ON TABLE employees, contractors FROM ROLE hr_manager;
   

   PostgreSQL

   如要瞭解 REVOKE 陳述式的語法詳細資料，請參閱 PostgreSQL 資料定義語言。如要瞭解權限，請參閱「精細的存取控管權限」。

   舉例來說，如要從資料庫角色 hr_manager 撤銷 employees 和 contractors 資料表的 SELECT、INSERT 和 UPDATE 權限，請輸入下列陳述式：

   REVOKE SELECT, INSERT, UPDATE ON TABLE employees, contractors FROM hr_manager;
   

   您可以為 REVOKE 陳述式使用 DDL 範本。在「Explorer」窗格中，前往要撤銷權限的角色。按一下「查看動作」，然後選取要撤銷此角色存取權的權限類型。REVOKE 範本陳述式會在新編輯器分頁中填入。

3. 刪除與角色相關聯的所有 IAM 條件。

   1. 在「資訊面板」的角色清單中，找出旁邊有感興趣條件標題的「Cloud Spanner 資料庫角色使用者」角色，然後展開該角色，查看有權存取該角色的主體。

   2. 按一下其中一個主體的「編輯主體」 (鉛筆) 圖示。

   3. 在「編輯權限」頁面中，點選「Cloud Spanner 資料庫角色使用者」角色旁的「刪除角色」 (垃圾桶) 圖示。

   4. 按一下 [儲存]。

   5. 針對條件下列出的其他主體，重複前三個步驟。

4. 如要捨棄角色，請前往「Spanner Studio」頁面，然後輸入下列陳述式：

   DROP ROLE ROLE_NAME;

5. 按一下「提交」。

gcloud

1. 如要撤銷角色的所有權限，然後捨棄角色，請使用 gcloud spanner databases ddl update 指令，如下所示：

   GoogleSQL

   gcloud spanner databases ddl update DATABASE_NAME \
   --instance=INSTANCE_NAME \
   --ddl='REVOKE PERMISSIONS ON TABLE TABLE_NAME FROM ROLE ROLE_NAME; DROP ROLE ROLE_NAME;'

   PostgreSQL

   gcloud spanner databases ddl update DATABASE_NAME \
   --instance=INSTANCE_NAME \
   --ddl='REVOKE PERMISSIONS ON TABLE TABLE_NAME FROM ROLE_NAME; DROP ROLE ROLE_NAME;'

   PERMISSIONS 的有效值為 SELECT、INSERT、UPDATE 和 DELETE。

2. 如要刪除任何相關的 IAM 條件，請使用 gcloud spanner databases remove-iam-policy-binding 指令，如下所示：

   gcloud spanner databases remove-iam-policy-binding DATABASE_NAME \
   --instance=INSTANCE_NAME \
   --role=ROLE_NAME \
   --member=MEMBER_NAME \
   --condition=CONDITION

   • MEMBER_NAME 是主體的 ID。格式必須為 user|group|serviceAccount:email 或 domain:domain。

   • CONDITION 是 IAM 條件運算式，用於指定要授予主體的角色。

     CONDITION 的格式如下：

     --condition='expression=(resource.type == "spanner.googleapis.com/DatabaseRole" && (resource.name.endsWith("/ROLE1") || resource.name.endsWith("/ROLE2"))),title=TITLE,description=DESCRIPTION'

     整個條件規格必須與授權指令中使用的條件規格完全相符，包括標題和說明。

用戶端程式庫

這兩個程式碼範例都會建立及捨棄資料庫角色。

C++

void AddAndDropDatabaseRole(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id, std::string const& role_parent,
    std::string const& role_child) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::vector<std::string> grant_statements = {
      "CREATE ROLE " + role_parent,
      "GRANT SELECT ON TABLE Singers TO ROLE " + role_parent,
      "CREATE ROLE " + role_child,
      "GRANT ROLE " + role_parent + " TO ROLE " + role_child,
  };
  auto metadata =
      client.UpdateDatabaseDdl(database.FullName(), grant_statements).get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Created roles " << role_parent << " and " << role_child
            << " and granted privileges\n";

  std::vector<std::string> revoke_statements = {
      "REVOKE ROLE " + role_parent + " FROM ROLE " + role_child,
      "DROP ROLE " + role_child,
  };
  metadata =
      client.UpdateDatabaseDdl(database.FullName(), revoke_statements).get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Revoked privileges and dropped role " << role_child << "\n";
}

C#

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

public class AddAndDropDatabaseRoleAsyncSample
{
    public async Task AddDatabaseRoleAsync(string projectId, string instanceId, string databaseId, string databaseRole)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string createRoleStatement = $"CREATE ROLE {databaseRole}";

        // Creates the given database role.
        using var connection = new SpannerConnection(connectionString);
        using var updateCmd = connection.CreateDdlCommand(createRoleStatement);
        await updateCmd.ExecuteNonQueryAsync();
    }

    public async Task DropDatabaseRoleAsync(string projectId, string instanceId, string databaseId, string databaseRole)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string deleteRoleStatement = $"DROP ROLE {databaseRole}";

        // Drops the given database role.
        using var connection = new SpannerConnection(connectionString);
        using var updateCmd = connection.CreateDdlCommand(deleteRoleStatement);
        await updateCmd.ExecuteNonQueryAsync();
    }
}

Go

import (
	"context"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
)

func addAndDropDatabaseRole(w io.Writer, db string) error {
	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	// Set up database roles and membership. After database roles are created,
	// users can be granted roles by setting IAM policies.
	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: db,
		Statements: []string{
			"CREATE ROLE parent",
			"GRANT SELECT ON TABLE Albums TO ROLE parent",
			"CREATE ROLE child",
			"GRANT ROLE parent TO ROLE child",
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}

	// Delete role and membership.
	op, err = adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: db,
		Statements: []string{
			"REVOKE ROLE parent FROM ROLE child",
			"DROP ROLE child",
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}
	return nil
}

Java

import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class AddAndDropDatabaseRole {

  static void addAndDropDatabaseRole() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    String parentRole = "parent_role";
    String childRole = "child_role";
    addAndDropDatabaseRole(projectId, instanceId, databaseId, parentRole, childRole, "Albums");
  }

  static void addAndDropDatabaseRole(
      String projectId, String instanceId, String databaseId,
      String parentRole, String childRole, String... tables) {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      System.out.println("Waiting for role create operation to complete...");
      List<String> roleStatements = new ArrayList<>(ImmutableList.of(
          String.format("CREATE ROLE %s", parentRole),
          String.format("CREATE ROLE %s", childRole),
          String.format("GRANT ROLE %s TO ROLE %s", parentRole, childRole)));
      for (String table : tables) {
        roleStatements.add(String.format("GRANT SELECT ON TABLE %s TO ROLE %s", table, parentRole));
      }
      databaseAdminClient.updateDatabaseDdlAsync(
              DatabaseName.of(projectId, instanceId, databaseId), roleStatements)
          .get(5, TimeUnit.MINUTES);
      System.out.printf(
          "Created roles %s and %s and granted privileges%n", parentRole, childRole);
      // Delete role and membership.
      System.out.println("Waiting for role revoke & drop operation to complete...");
      databaseAdminClient.updateDatabaseDdlAsync(
          DatabaseName.of(projectId, instanceId, databaseId),
          ImmutableList.of(
              String.format("REVOKE ROLE %s FROM ROLE %s", parentRole, childRole),
              String.format("DROP ROLE %s", childRole))).get(5, TimeUnit.MINUTES);
      System.out.printf("Revoked privileges and dropped role %s%n", childRole);
    } catch (ExecutionException | TimeoutException e) {
      System.out.printf(
          "Error: AddAndDropDatabaseRole failed with error message %s\n", e.getMessage());
      e.printStackTrace();
    } catch (InterruptedException e) {
      System.out.println(
          "Error: Waiting for AddAndDropDatabaseRole operation to finish was interrupted");
    }
  }
}

注意：舊版 Java 用戶端程式庫介面的程式碼範例已封存於 GitHub。

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 client library
const {Spanner} = require('@google-cloud/spanner');

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

const databaseAdminClient = spanner.getDatabaseAdminClient();

async function addAndDropNewDatabaseRole() {
  // Creates a new user defined role and grant permissions
  try {
    const request = [
      'CREATE ROLE parent',
      'GRANT SELECT ON TABLE Singers TO ROLE parent',
      'CREATE ROLE child',
      'GRANT ROLE parent TO ROLE child',
    ];
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId,
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log('Created roles child and parent and granted privileges');
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Revoke permissions and drop child role.
  // A role can't be dropped until all its permissions are revoked.
  try {
    const request = ['REVOKE ROLE parent FROM ROLE child', 'DROP ROLE child'];
    const [operation] = await databaseAdminClient.updateDatabaseDdl({
      database: databaseAdminClient.databasePath(
        projectId,
        instanceId,
        databaseId,
      ),
      statements: request,
    });

    console.log('Waiting for operation to complete...');
    await operation.promise();

    console.log('Revoked privileges and dropped role child');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the spanner client when finished.
    // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.
    spanner.close();
  }
}
addAndDropNewDatabaseRole();

注意：Node.js 的舊版用戶端程式庫介面程式碼範例已封存至 GitHub。

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;

/**
 * Adds and drops roles to the Singers table in the example database.
 * Example:
 * ```
 * add_drop_database_role($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function add_drop_database_role(string $projectId, string $instanceId, string $databaseId): void
{
    $databaseAdminClient = new DatabaseAdminClient();
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => [
            'CREATE ROLE new_parent',
            'GRANT SELECT ON TABLE Singers TO ROLE new_parent',
            'CREATE ROLE new_child',
            'GRANT ROLE new_parent TO ROLE new_child'
        ]
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

    printf('Waiting for create role and grant operation to complete...%s', PHP_EOL);
    $operation->pollUntilComplete();

    printf('Created roles %s and %s and granted privileges%s', 'new_parent', 'new_child', PHP_EOL);

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => [
            'REVOKE ROLE new_parent FROM ROLE new_child',
            'DROP ROLE new_child'
        ]
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

    printf('Waiting for revoke role and drop role operation to complete...%s', PHP_EOL);
    $operation->pollUntilComplete();

    printf('Revoked privileges and dropped role %s%s', 'new_child', PHP_EOL);
}

注意：舊版 PHP 用戶端程式庫介面程式碼範例已封存於 GitHub。

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()
database_admin_api = spanner_client.database_admin_api

role_parent = "new_parent"
role_child = "new_child"

request = spanner_database_admin.UpdateDatabaseDdlRequest(
    database=database_admin_api.database_path(
        spanner_client.project, instance_id, database_id
    ),
    statements=[
        "CREATE ROLE {}".format(role_parent),
        "GRANT SELECT ON TABLE Singers TO ROLE {}".format(role_parent),
        "CREATE ROLE {}".format(role_child),
        "GRANT ROLE {} TO ROLE {}".format(role_parent, role_child),
    ],
)
operation = database_admin_api.update_database_ddl(request)

operation.result(OPERATION_TIMEOUT_SECONDS)
print(
    "Created roles {} and {} and granted privileges".format(role_parent, role_child)
)

request = spanner_database_admin.UpdateDatabaseDdlRequest(
    database=database_admin_api.database_path(
        spanner_client.project, instance_id, database_id
    ),
    statements=[
        "REVOKE ROLE {} FROM ROLE {}".format(role_parent, role_child),
        "DROP ROLE {}".format(role_child),
    ],
)
operation = database_admin_api.update_database_ddl(request)

operation.result(OPERATION_TIMEOUT_SECONDS)
print("Revoked privileges and dropped role {}".format(role_child))

注意：舊版 Python 用戶端程式庫介面的程式碼範例已封存至 GitHub。

小茹

require "google/cloud/spanner"

def spanner_add_and_drop_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"

  admin_client = Google::Cloud::Spanner::Admin::Database::V1::DatabaseAdmin::Client.new
  role_parent = "new_parent"
  role_child = "new_child"

  db_path = admin_client.database_path project: project_id, instance: instance_id, database: database_id

  job = admin_client.update_database_ddl database: db_path, statements: [
    "CREATE ROLE #{role_parent}",
    "GRANT SELECT ON TABLE Singers TO ROLE #{role_parent}",
    "CREATE ROLE #{role_child}",
    "GRANT ROLE #{role_parent} TO ROLE #{role_child}"
  ]

  job.wait_until_done!
  puts "Created roles #{role_parent} and #{role_child} and granted privileges"


  job = admin_client.update_database_ddl database: db_path, statements: [
    "REVOKE ROLE #{role_parent} FROM ROLE #{role_child}",
    "DROP ROLE #{role_child}"
  ]

  job.wait_until_done!
  puts "Revoked privileges and dropped role #{role_child}"
end

更多資訊

• 關於精細的存取控管
• 變更串流的精細存取權控管機制
• 精細的存取控管權限
• GoogleSQL DDL 參考資料
• 更新結構定義