配置精细的访问权限控制

PostgreSQL 页面说明： PostgreSQL 方言数据库不支持精细的访问权限控制。

本页面介绍如何为 Cloud Spanner 数据库配置精细的访问权限控制。

如需了解精细访问权限控制，请参阅精细访问权限控制简介。

配置精细的访问权限控制涉及以下步骤：

1. 创建数据库角色并授予权限。

2. 可选：创建具有继承的角色层次结构。

3. 向 Identity and Access Management (IAM) 主帐号授予数据库角色的访问权限。

这样，精细的访问权限控制用户就必须指定数据库角色，才能对数据库执行查询、DML 或行操作。

创建数据库角色并授予权限

数据库角色是一组精细的访问权限。您最多可以为每个数据库创建 100 个数据库角色。

确定数据库中的角色和角色层次结构，并以 DDL 对其进行编码。与 Spanner 中的其他架构更改一样，我们强烈建议您批量发出架构更改，而不是单独发出更改。如需了解详情，请参阅限制架构更新频率。

控制台

如需创建数据库角色并向其授予细化的访问权限，请按以下步骤操作：

1. 前往 Google Cloud 控制台中的实例页面。

   实例

2. 选择包含要为其添加角色的数据库的实例。

3. 选择数据库。

4. 在概览页面上，点击编写 DDL。

5. 在编写 DDL 语句页面上，对于您要创建和向其授予权限的每个数据库角色，请按以下步骤操作：

   1. 如需创建角色，请输入以下语句：

      CREATE ROLE ROLE_NAME;
      

      先不要点击 Submit。

   2. 如需向该角色授予权限，请在 CREATE ROLE 语句后的下一行输入 GRANT 语句。

      如需详细了解 GRANT 语句的语法，请参阅 GoogleSQL 数据定义语言。如需了解权限，请参阅精细访问权限控制。

      例如，如需将表 employees 和 contractors 上的 SELECT、INSERT 和 UPDATE 授予数据库角色 hr_manager，请输入以下语句：

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

      注意 ：请与您在创建对象时使用的架构对象名称相同。例如，如果您创建了一个名为 Customers 的表，则必须在 GRANT 语句中使用名称 Customers（大写 C）。

      您可以对 GRANT 语句使用 DDL 模板。在 DDL 模板下拉菜单中，选择数据库角色，然后在授予角色权限下选择一个模板。

6. 点击提交。

   如果 DDL 中存在错误，Google Cloud 控制台会返回错误。

gcloud

如需创建数据库角色并向其授予细化的访问权限，请将 gcloud spanner databases ddl update 命令与 CREATE ROLE 和 GRANT 语句一起使用。

如需详细了解 CREATE ROLE 和 GRANT 语句，请参阅 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;'

• PRIVILEGES 是以英文逗号分隔的精细访问权限控制权限的列表。如需了解权限，请参阅精细访问权限控制权限。

• TABLES 是以英文逗号分隔的表列表。

例如，如需向表 hr 中的数据库 hrdb1 授予表 employees 和 contractors 的 SELECT、INSERT 和 UPDATE 权限，请输入以下语句：

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;'

注意 ：请与您在创建对象时使用的架构对象名称相同。例如，如果您创建了一个名为 Customers 的表，则必须在 GRANT 语句中使用名称 Customers（大写 C）。

客户端库

这些代码示例均可创建和删除数据库角色。

C++

在 GitHub 上查看 反馈

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#

在 GitHub 上查看 反馈

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

在 GitHub 上查看 反馈

import (
	"context"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

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

在 GitHub 上查看 反馈

import com.google.api.gax.longrunning.OperationFuture;
import com.google.cloud.spanner.DatabaseAdminClient;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata;
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 = "my-new-parent-role";
    String childRole = "my-new-child-role";
    addAndDropDatabaseRole(projectId, instanceId, databaseId, parentRole, childRole);
  }

  static void addAndDropDatabaseRole(
      String projectId, String instanceId, String databaseId, String parentRole, String childRole) {
    try (Spanner spanner =
                 SpannerOptions.newBuilder()
                         .setProjectId(projectId)
                         .build()
                         .getService())  {
      final DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
      OperationFuture<Void, UpdateDatabaseDdlMetadata> operation =
          adminClient.updateDatabaseDdl(
              instanceId,
              databaseId,
              ImmutableList.of(
                  "CREATE ROLE " + parentRole,
                  "GRANT SELECT ON TABLE Albums TO ROLE " + parentRole,
                  "CREATE ROLE " + childRole,
                  "GRANT ROLE " + parentRole + " TO ROLE " + childRole),
              null);
      try {
        System.out.println("Waiting for role create operation to complete...");
        operation.get(5, TimeUnit.MINUTES);
        System.out.printf(
            "Created roles %s and %s and granted privileges%n", parentRole, childRole);
        // Delete role and membership.
        operation =
            adminClient.updateDatabaseDdl(
                instanceId,
                databaseId,
                ImmutableList.of(
                    "REVOKE ROLE " + parentRole + " FROM ROLE " + childRole,
                    "DROP ROLE " + childRole),
                null);
        System.out.println("Waiting for role revoke & drop operation to complete...");
        operation.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");
      }
    }
  }
}

Node.js

在 GitHub 上查看 反馈

/**
 * 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 addAndDropNewDatabaseRole() {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  // 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 database.updateSchema(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 database.updateSchema(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 database when finished.
    await database.close();
  }
}
addAndDropNewDatabaseRole();

PHP

在 GitHub 上查看 反馈

use Google\Cloud\Spanner\SpannerClient;

/**
 * Adds and drops roles to the Singers table in the example database.
 * Example:
 * ```
 * add_drop_database_role($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function add_drop_database_role(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $roleParent = 'new_parent';
    $roleChild = 'new_child';

    $operation = $database->updateDdlBatch([
        sprintf('CREATE ROLE %s', $roleParent),
        sprintf('GRANT SELECT ON TABLE Singers TO ROLE %s', $roleParent),
        sprintf('CREATE ROLE %s', $roleChild),
        sprintf('GRANT ROLE %s TO ROLE %s', $roleParent, $roleChild)
    ]);

    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', $roleParent, $roleChild, PHP_EOL);

    $operation = $database->updateDdlBatch([
        sprintf('REVOKE ROLE %s FROM ROLE %s', $roleParent, $roleChild),
        sprintf('DROP ROLE %s', $roleChild)
    ]);

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

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

Python

在 GitHub 上查看 反馈

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
role_parent = "new_parent"
role_child = "new_child"

operation = database.update_ddl(
    [
        "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.result(OPERATION_TIMEOUT_SECONDS)
print(
    "Created roles {} and {} and granted privileges".format(role_parent, role_child)
)

operation = database.update_ddl(
    [
        "REVOKE ROLE {} FROM ROLE {}".format(role_parent, role_child),
        "DROP ROLE {}".format(role_child),
    ]
)
operation.result(OPERATION_TIMEOUT_SECONDS)
print("Revoked privileges and dropped role {}".format(role_child))

Ruby

在 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

创建具有继承的角色层次结构

您可以通过向一个数据库角色授予另一个数据库角色来创建数据库角色层次结构。子角色（称为成员角色）会继承父角色的权限。

如需向另一个数据库角色授予某个数据库角色，请使用以下语句：

GRANT ROLE role1 TO ROLE role2;

如需了解详情，请参阅数据库角色层次结构和继承。

向 IAM 主帐号授予数据库角色的访问权限

您可以使用 IAM 授予对数据库角色的访问权限。

控制台

如需向 IAM 主帐号授予对数据库角色的访问权限，请按以下步骤操作：

1. 在数据库概览页面上，如果信息面板尚未打开，请点击显示信息面板。

2. 点击添加主帐号。

3. 在授予 database_name 的访问权限面板的添加主帐号下，指定一个或多个 IAM 主帐号。

4. 在分配角色下的选择角色菜单中，依次选择 Cloud Spanner > Cloud Spanner Fine-粒度访问权限用户。

   您只需向每个主帐号授予此角色一次。这会使主帐号成为精细的访问权限控制用户。

5. 点击添加其他角色

6. 在选择角色菜单中，选择 Cloud Spanner > Cloud Spanner Database Role User。

   注意 ：请避免选择 Cloud Spanner Database User。

7. 请按照以下步骤创建 IAM 条件，以指定要授予的角色。

   注意 ：如果您不添加条件，主帐号将有权访问所有数据库角色。

   1. 在 Cloud Spanner Database Role User 角色旁边，点击添加 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 支持的任何条件表达式。如需了解详情，请参阅 IAM 条件概览。

   5. 点击保存。

   6. 返回上一面板，验证条件显示在 Role 字段旁边的 IAM Condition 列下。

   7. 点击保存。

      返回信息面板，在角色/主帐号下，请注意对于每个定义的条件，系统会显示 Cloud Spanner Database Role User。

      条件旁边的括号中的数字表示该条件授予数据库角色的主帐号数量。您可以点击展开箭头查看主帐号列表。

   8. 如需更正数据库角色名称或条件中的错误，或者为主帐号添加其他数据库角色，请按以下步骤操作：

      1. 展开 Cloud Spanner Database Role User 条目，该条目会列出所需的条件。

      2. 点击主帐号旁边的修改（铅笔）图标。

      3. 在对“database_name”的修改权限上，执行以下操作之一：

         • 点击添加其他角色

         • 如需修改条件，请点击条件名称旁边的修改（铅笔）图标。然后在修改条件页面上，点击条件编辑器，进行更正，然后点击保存两次。

gcloud

如需向 IAM 主帐号授予对数据库角色的访问权限，请按以下步骤操作：

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 是主帐号的标识符。它必须采用 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 是主帐号的标识符。它必须采用 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 支持的任何条件表达式。如需了解详情，请参阅 IAM 条件概览。

   注意 ：如果您不指定条件，主帐号将有权访问所有数据库角色。

   如果成功，该命令将输出数据库的完整政策。

   以下示例会向主帐号 [e-mail] 授予数据库角色 hr_rep 和 hr_manager。

   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++

在 GitHub 上查看 反馈

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#

在 GitHub 上查看 反馈

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

在 GitHub 上查看 反馈

import (
	"context"
	"fmt"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	iampb "google.golang.org/genproto/googleapis/iam/v1"
	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

在 GitHub 上查看 反馈

import com.google.cloud.Binding;
import com.google.cloud.Condition;
import com.google.cloud.Policy;
import com.google.cloud.spanner.DatabaseAdminClient;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.common.collect.ImmutableList;

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()) {
      final DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
      Policy policy = adminClient.getDatabaseIAMPolicy(instanceId, databaseId, 3);
      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")
              .setMembers(ImmutableList.of(iamMember))
              .build();

      Binding binding2 =
          Binding.newBuilder()
              .setRole("roles/spanner.databaseRoleUser")
              .setCondition(
                  Condition.newBuilder()
                      .setDescription(title)
                      .setExpression(
                          String.format("resource.name.endsWith(\"/databaseRoles/%s\")", role))
                      .setTitle(title)
                      .build())
              .setMembers(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())
              .setBindings(bindings)
              .build();
      Policy response =
          adminClient.setDatabaseIAMPolicy(instanceId, databaseId, policyWithConditions);
      System.out.printf(
          "Enabled fine-grained access in IAM with version %d%n", response.getVersion());
    }
  }
}

Node.js

在 GitHub 上查看 反馈

/**
 * 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} = require('@google-cloud/spanner');

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

async function enableFineGrainedAccess() {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  const [policy] = await database.getIamPolicy({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 database.setIamPolicy({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 database.getIamPolicy({requestedPolicyVersion: 3});
  console.log(newPolicy);
}
enableFineGrainedAccess();

PHP

在 GitHub 上查看 反馈

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

/**
 * 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 = sprintf('projects/%s/instances/%s/databases/%s', $projectId, $instanceId, $databaseId);
    $policy = $adminClient->getIamPolicy($resource);

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

    $binding = new Binding([
        'role' => 'roles/spanner.fineGrainedAccessUser',
        'members' => [$iamMember],
        'condition' => new Expr([
            'title' => $title,
            'expression' => sprintf("resource.name.endsWith('/databaseRoles/%s')", $databaseRole)
        ])
    ]);
    $policy->setBindings([$binding]);
    $adminClient->setIamPolicy($resource, $policy);

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

Python

在 GitHub 上查看 反馈

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
# iam_member = "user:[e-mail]"
# database_role = "new_parent"
# title = "condition title"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

# 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.
policy = database.get_iam_policy(3)
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)
database.set_iam_policy(policy)

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

Ruby

在 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

将主帐号转换为精细的访问权限控制机制

如需将 IAM 主帐号从数据库级访问权限控制转换为精细访问权限控制，请按以下步骤操作：

1. 按照向 IAM 主帐号授予数据库角色的访问权限中所述，为主帐号启用精细的访问权限控制，并授予对所有必需的数据库角色的访问权限。

2. 更新以主帐号身份运行的所有应用。在调用客户端库方法时指定适当的数据库角色。

3. 撤消主帐号的所有 IAM 数据库级角色。这样，主帐号的访问权限便只受一种方法控制。

   如需撤消 IAM 数据库级角色，请按照移除数据库级权限中的说明操作。

列出数据库角色

您可以列出与数据库关联的数据库角色。

控制台

如需列出数据库角色，请在数据库的查询页面上输入以下查询：

SELECT * FROM INFORMATION_SCHEMA.ROLES;

响应包括当前角色及其当前角色通过继承可以使用的角色。如需提取所有角色，请使用 gcloud 命令。

gcloud

如需获取未经过滤的数据库角色列表，请输入以下命令。 它需要 spanner.databaseRoles.list 权限。

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

客户端库

C++

在 GitHub 上查看 反馈

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#

在 GitHub 上查看 反馈

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

在 GitHub 上查看 反馈

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

	"google.golang.org/api/iterator"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

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

在 GitHub 上查看 反馈

import com.google.cloud.spanner.DatabaseAdminClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.DatabaseRole;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import java.util.concurrent.ExecutionException;

public class ListDatabaseRoles {

  static void listDatabaseRoles() throws InterruptedException, ExecutionException {
    // 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()) {
      final DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
      String databasePath = DatabaseId.of(projectId, instanceId, databaseId).getName();
      System.out.println("List of Database roles");
      for (DatabaseRole role : adminClient.listDatabaseRoles(instanceId, databaseId).iterateAll()) {
        System.out.printf("%s%n", role.getName());
      }
    }
  }
}

Node.js

在 GitHub 上查看 反馈

/**
 * 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 getDatabaseRoles() {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  // Fetching database roles
  const [databaseRoles] = await database.getDatabaseRoles();
  console.log(`Roles for Database: ${database.formattedName_}`);
  databaseRoles.forEach(role => {
    console.log(`Role: ${role.name}`);
  });
}
getDatabaseRoles();

PHP

在 GitHub 上查看 反馈

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

/**
 * 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 = sprintf('projects/%s/instances/%s/databases/%s', $projectId, $instanceId, $databaseId);

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

Python

在 GitHub 上查看 反馈

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

# List database roles.
print("Database Roles are:")
for role in database.list_database_roles():
    print(role.name.split("/")[-1])

Ruby

在 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

查看授予数据库角色的权限

如需查看授予某角色的权限，请运行以下查询：

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。

查看有权访问数据库角色的 IAM 主帐号

如需查看已被授予特定数据库角色的主帐号列表，请运行以下命令。如需运行该命令，您必须在项目中启用 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 Database Role User 角色时指定的 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. 在数据库概览页面上，点击编写 DDL。

2. 如需撤消此角色的权限，请输入 REVOKE 语句。

   如需详细了解 REVOKE 语句的语法，请参阅 GoogleSQL 数据定义语言。如需了解权限，请参阅精细访问权限控制。

   例如，如需从数据库角色 hr_manager 上撤消表 employees 和 contractors 的 SELECT、INSERT 和 UPDATE，请输入以下语句：

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

   您可以对 REVOKE 语句使用 DDL 模板。在 DDL 模板下拉菜单中，选择数据库角色，然后在撤消角色权限下选择一个模板。

3. 删除与角色关联的所有 IAM 条件。

   1. 在信息面板上的角色列表中，找到具有相关条件标题的 Cloud Spanner Database Role User 角色，然后展开该角色以查看有权访问该角色的主帐号。

   2. 对于其中一个主帐号，请点击修改主帐号（铅笔）图标。

   3. 在修改访问权限页面上，点击与 Cloud Spanner Database Role User 角色旁边的删除角色（垃圾桶）图标。

   4. 点击保存。

   5. 针对满足该条件的其他主帐号重复执行上述三个步骤。

4. 如需撤消此角色，请转到编写 DDL 页面，然后输入以下语句：

   DROP ROLE ROLE_NAME;
   

5. 点击提交。

gcloud

1. 如需撤消某个角色的所有权限，然后删除该角色，请使用 gcloud spanner databases ddl update 命令，如下所示：

   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;'
   

   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 是主帐号的标识符。它必须采用 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++

在 GitHub 上查看 反馈

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#

在 GitHub 上查看 反馈

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

在 GitHub 上查看 反馈

import (
	"context"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

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

在 GitHub 上查看 反馈

import com.google.api.gax.longrunning.OperationFuture;
import com.google.cloud.spanner.DatabaseAdminClient;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata;
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 = "my-new-parent-role";
    String childRole = "my-new-child-role";
    addAndDropDatabaseRole(projectId, instanceId, databaseId, parentRole, childRole);
  }

  static void addAndDropDatabaseRole(
      String projectId, String instanceId, String databaseId, String parentRole, String childRole) {
    try (Spanner spanner =
                 SpannerOptions.newBuilder()
                         .setProjectId(projectId)
                         .build()
                         .getService())  {
      final DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
      OperationFuture<Void, UpdateDatabaseDdlMetadata> operation =
          adminClient.updateDatabaseDdl(
              instanceId,
              databaseId,
              ImmutableList.of(
                  "CREATE ROLE " + parentRole,
                  "GRANT SELECT ON TABLE Albums TO ROLE " + parentRole,
                  "CREATE ROLE " + childRole,
                  "GRANT ROLE " + parentRole + " TO ROLE " + childRole),
              null);
      try {
        System.out.println("Waiting for role create operation to complete...");
        operation.get(5, TimeUnit.MINUTES);
        System.out.printf(
            "Created roles %s and %s and granted privileges%n", parentRole, childRole);
        // Delete role and membership.
        operation =
            adminClient.updateDatabaseDdl(
                instanceId,
                databaseId,
                ImmutableList.of(
                    "REVOKE ROLE " + parentRole + " FROM ROLE " + childRole,
                    "DROP ROLE " + childRole),
                null);
        System.out.println("Waiting for role revoke & drop operation to complete...");
        operation.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");
      }
    }
  }
}

Node.js

在 GitHub 上查看 反馈

/**
 * 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 addAndDropNewDatabaseRole() {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  // 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 database.updateSchema(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 database.updateSchema(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 database when finished.
    await database.close();
  }
}
addAndDropNewDatabaseRole();

PHP

在 GitHub 上查看 反馈

use Google\Cloud\Spanner\SpannerClient;

/**
 * Adds and drops roles to the Singers table in the example database.
 * Example:
 * ```
 * add_drop_database_role($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function add_drop_database_role(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $roleParent = 'new_parent';
    $roleChild = 'new_child';

    $operation = $database->updateDdlBatch([
        sprintf('CREATE ROLE %s', $roleParent),
        sprintf('GRANT SELECT ON TABLE Singers TO ROLE %s', $roleParent),
        sprintf('CREATE ROLE %s', $roleChild),
        sprintf('GRANT ROLE %s TO ROLE %s', $roleParent, $roleChild)
    ]);

    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', $roleParent, $roleChild, PHP_EOL);

    $operation = $database->updateDdlBatch([
        sprintf('REVOKE ROLE %s FROM ROLE %s', $roleParent, $roleChild),
        sprintf('DROP ROLE %s', $roleChild)
    ]);

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

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

Python

在 GitHub 上查看 反馈

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
role_parent = "new_parent"
role_child = "new_child"

operation = database.update_ddl(
    [
        "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.result(OPERATION_TIMEOUT_SECONDS)
print(
    "Created roles {} and {} and granted privileges".format(role_parent, role_child)
)

operation = database.update_ddl(
    [
        "REVOKE ROLE {} FROM ROLE {}".format(role_parent, role_child),
        "DROP ROLE {}".format(role_child),
    ]
)
operation.result(OPERATION_TIMEOUT_SECONDS)
print("Revoked privileges and dropped role {}".format(role_child))

Ruby

在 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 参考文档
• 更新架构