本页面介绍作为精细访问权限控制用户如何访问 Spanner 数据库。
如需了解精细访问权限控制,请参阅精细访问权限控制简介。
作为精细的访问权限控制用户,您必须选择一个数据库角色来执行 SQL 语句和查询,以及对数据库执行行操作。角色选择将在整个会话过程中持续,直到您更改角色。
当您提交查询、DML 或行操作时,Spanner 会使用以下规则检查授权:
- Google Cloud 控制台
Spanner 首先检查您是否具有数据库级 IAM 权限。如果是这样,Google Cloud 控制台不会显示数据库角色选择器,您的会话会继续使用数据库级权限。
如果您只有精细的访问权限控制权限,没有 IAM 数据库级权限,则必须已被授予
spanner_sys_reader
系统角色或其某个成员角色的访问权限。请在数据库概览页面上选择一个角色,以便您的 Google Cloud 控制台会话继续拥有所需的权限。
- Google Cloud SDK
如果您在提交查询、DML 或行操作时指定了数据库角色,则 Spanner 会检查精细的访问权限控制特权。如果检查失败,则 Spanner 不会检查数据库级 IAM 权限,并且操作会失败。
如果您未指定数据库角色,Spanner 会检查数据库级 IAM 权限,如果检查成功,您的会话会继续使用数据库级权限。
在访问 Spanner 数据库时,请使用以下方法来指定数据库角色:
控制台
选择一个数据库,然后在数据库概览页面上,点击当前角色字段旁边的更改数据库角色(铅笔)图标。
默认情况下,当精细访问权限控制用户登录时,此字段的值为
public
。如需了解public
系统角色,请参阅精细访问权限控制系统角色。在更改数据库角色对话框中,从可用角色列表中选择其他角色。
点击更新。
当前角色字段会显示新角色。
gcloud
将
--database-role
选项添加到gcloud spanner databases execute-sql
命令中,如下所示:gcloud spanner databases execute-sql DATABASE_NAME \ --instance=INSTANCE_NAME \ --sql="SELECT * from TABLE_NAME;" \ --database-role=ROLE_NAME
客户端库
C++
void ReadDataWithDatabaseRole(std::string const& project_id,
std::string const& instance_id,
std::string const& database_id,
std::string const& role) {
namespace spanner = ::google::cloud::spanner;
auto client = spanner::Client(spanner::MakeConnection(
spanner::Database(project_id, instance_id, database_id),
google::cloud::Options{}.set<spanner::SessionCreatorRoleOption>(role)));
spanner::SqlStatement select_star("SELECT * FROM Singers");
auto rows = client.ExecuteQuery(std::move(select_star));
using RowType =
std::tuple<std::int64_t, std::string, std::string, spanner::Bytes>;
for (auto& row : spanner::StreamOf<RowType>(rows)) {
if (!row) throw std::move(row).status();
std::cout << "SingerId: " << std::get<0>(*row) << ", "
<< "FirstName: " << std::get<1>(*row) << ", "
<< "LastName: " << std::get<2>(*row) << "\n";
}
}
C#
using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;
public class ReadDataWithDatabaseRoleAsyncSample
{
public class Singer
{
public int SingerId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public async Task<List<Singer>> ReadDataWithDatabaseRoleAsync(string projectId, string instanceId, string databaseId, string databaseRole)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
string tableName = "Singers";
var spannerConnectionStringBuilder = new SpannerConnectionStringBuilder
{
ConnectionString = connectionString,
DatabaseRole = databaseRole
};
using var connection = new SpannerConnection(spannerConnectionStringBuilder);
var createSelectCmd = connection.CreateSelectCommand($"SELECT * FROM {tableName}");
using var reader = await createSelectCmd.ExecuteReaderAsync();
var singers = new List<Singer>();
while (await reader.ReadAsync())
{
singers.Add(new Singer
{
SingerId = reader.GetFieldValue<int>("SingerId"),
FirstName = reader.GetFieldValue<string>("FirstName"),
LastName = reader.GetFieldValue<string>("LastName"),
});
}
return singers;
}
}
Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func readDataWithDatabaseRole(w io.Writer, db string, databaseRole string) error {
// databaseRole = "parent"
ctx := context.Background()
cfg := spanner.ClientConfig{
DatabaseRole: databaseRole,
}
client, err := spanner.NewClientWithConfig(ctx, db, cfg)
if err != nil {
return err
}
defer client.Close()
// Read all albums.
iter := client.Single().Read(ctx, "Albums", spanner.AllKeys(),
[]string{"SingerId", "AlbumId", "AlbumTitle"})
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var singerID, albumID int64
var albumTitle string
if err := row.Columns(&singerID, &albumID, &albumTitle); err != nil {
return err
}
fmt.Fprintf(w, "%d %d %s\n", singerID, albumID, albumTitle)
}
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.KeySet;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import java.util.Arrays;
public class ReadDataWithDatabaseRole {
static void readDataWithDatabaseRole() {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
String role = "my-role";
readDataWithDatabaseRole(projectId, instanceId, databaseId, role);
}
static void readDataWithDatabaseRole(
String projectId, String instanceId, String databaseId, String role) {
try (Spanner spannerWithRole =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.setDatabaseRole(role)
.build()
.getService()) {
DatabaseClient dbClient =
spannerWithRole.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
ResultSet resultSet =
dbClient
.singleUse()
.read(
"Singers",
KeySet.all(),
Arrays.asList("SingerId", "FirstName", "LastName"));
while (resultSet.next()) {
System.out.printf("SingerId: %d\n", resultSet.getLong(0));
System.out.printf("FirstName: %s\n", resultSet.getString(1));
System.out.printf("LastName: %s\n", resultSet.getString(2));
}
}
}
}
Node.js
/**
* TODO(developer): Uncomment these variables before running the sample.
*/
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// const projectId = 'my-project-id';
// Imports the Google Cloud Spanner client library
const {Spanner} = require('@google-cloud/spanner');
// Instantiates a client
const spanner = new Spanner({
projectId: projectId,
});
async function readDataWithDatabaseRole() {
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
// Connect to a database using the 'parent' database role. This means that the connection will only have the permissions that have explicitly been granted to the 'parent' role.
const options = {
databaseRole: 'parent',
};
const database = instance.database(databaseId, options);
try {
const query = {
sql: 'SELECT SingerId, FirstName, LastName FROM Singers',
};
const [rows] = await database.run(query);
for (const row of rows) {
const json = row.toJSON();
console.log(
`SingerId: ${json.SingerId}, FirstName: ${json.FirstName}, LastName: ${json.LastName}`
);
}
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
await database.close();
}
}
readDataWithDatabaseRole();
PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Read database with a database role.
* Example:
* ```
* read_data_with_database_role($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function read_data_with_database_role(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$databaseRole = 'new_parent';
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId, ['databaseRole' => $databaseRole]);
$results = $database->execute('SELECT * FROM Singers');
foreach ($results as $row) {
printf('SingerId: %s, Firstname: %s, LastName: %s' . PHP_EOL, $row['SingerId'], $row['FirstName'], $row['LastName']);
}
}
Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
role = "new_parent"
database = instance.database(database_id, database_role=role)
with database.snapshot() as snapshot:
results = snapshot.execute_sql("SELECT * FROM Singers")
for row in results:
print("SingerId: {}, FirstName: {}, LastName: {}".format(*row))
Ruby
require "google/cloud/spanner"
def spanner_read_data_with_database_role project_id:, instance_id:, database_id:
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
role = "new_parent"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id, database_role: role
result = client.execute_sql "SELECT * FROM Singers"
result.rows.each do |row|
puts "SingerId: #{row[:SingerId]}"
puts "FirstName: #{row[:FirstName]}"
puts "LastName: #{row[:LastName]}"
end
end