Cloud Spanner fine-grained access control combines the benefits of Identity and Access Management (IAM) with traditional SQL role-based access control. With fine-grained access control, you define database roles, grant privileges to the roles, and create IAM policies to grant permissions on database roles to IAM principals.
As an administrator, you must enable fine-grained access control for individual IAM principals. Principals for whom fine-grained access control is enabled ("fine-grained access control users") must assume a database role to access Spanner resources.
Resource access for users who are are not fine-grained access control users is governed by IAM database-level roles. Fine-grained access control is fully compatible and can co-exist with existing IAM database-level access control.
With fine-grained access control, you can control access to tables, columns, views, and change streams.
To manage fine-grained access control, you use the following DDL statements:
DROPstatements for creating and dropping database roles. Database roles are collections of privileges. You can create up to 100 roles for a database.
REVOKEstatements to grant and revoke privileges to and from database roles. Privileges include
EXECUTE. Privilege names correspond to the like-named SQL statements. For example, a role with the
INSERTprivilege can execute the
INSERTSQL statement on the tables that are specified in the
The following DDL statements grant
CREATE ROLE hr_rep; GRANT SELECT ON TABLE employees TO ROLE hr_rep;
For more information on privileges, see Fine-grained access control privileges reference.
GRANTstatements for granting roles to other roles to create hierarchies of roles, with privilege inheritance.
The following are sample use cases for fine-grained access control:
- An HR information system that has roles for sales compensation analyst, sales management, and HR analyst, each with different access levels on the data. For example, compensation analysts and sales management shouldn't see social security numbers.
- A ride-sharing application with different service accounts and privileges for riders and drivers.
- A ledger that permits
INSERToperations but not
Spanner resources and their privileges
The following is a list of Spanner resources and the fine-grained access control privileges that you can grant for them.
- You can grant the
DELETEprivileges on tables to database roles. For interleaved tables, a privilege granted on the parent table doesn't propagate to the child table.
- You can grant
UPDATEon a subset of columns in a table. The privilege is then valid only for those columns.
DELETEis not permitted at the column level.
- You can grant
SELECTon a view. Only
SELECTis supported for views. Because Cloud Spanner supports only invoker rights views, a database role needs the
SELECTprivilege on the view, and also the
SELECTprivilege on the underlying objects that the view references.
- Change streams
- You can grant
SELECTon change streams. You must also grant
EXECUTEon the table-valued function (TVF) associated with a change stream. For information, see Fine-grained access control for change streams.
Fine-grained access control system roles
Fine-grained access control has predefined system roles for each database. Like user-defined database roles, system roles can control access to Spanner resources.
For example, a fine-grained access control user needs to be granted the
system role to access Key Visualizer, and needs the
role to be able to see unfiltered results when querying the
For more information, see Fine-grained access control system roles.
Database role hierarchies and inheritance
You can create hierarchies of database roles, where child roles inherit the privileges of parent roles. Child roles are known as members of the parent role.
For example, consider the following
GRANT SELECT ON TABLE employees TO ROLE pii_access; GRANT ROLE pii_access TO ROLE hr_manager, hr_director;
hr_director are members of role
pii_access, and inherit the
SELECT privilege on table
hr_director can also have members, and those members would
SELECT privilege on
There are no limits on the depth of role hierarchies, but query performance might degrade with deep and wide role hierarchy structures.
Backup and restore
Spanner backups include database role definitions. When a database is restored from backup, database roles are re-created with their granted privileges. However, IAM policies are not a part of database backups, so you must re-grant access to database roles to principals in the restored database.
Overview of setting up fine-grained access control
The following are the high-level steps that you take to begin securing data with fine-grained access control. For details, see Configure fine-grained access control.
You must be granted the
roles/spanner.databaseAdmin IAM roles to perform these tasks.
- Create database roles and grant privileges to the roles.
- Optional: Create role hierarchies with inheritance by granting roles to other roles.
- Perform these steps for each principal who is to be a fine-grained access control user:
- Enable fine-grained access control for the principal.
The principal is then automatically granted the
publicdatabase role, which has no privileges by default. This is a one-time operation for each principal.
- Grant IAM permissions on one or more database roles to the principal.
- After the principal is granted all required database roles, if the principal has database-level IAM roles, consider revoking the database-level roles so that the principal's access control is managed by only one method.
- Enable fine-grained access control for the principal. The principal is then automatically granted the
- Fine-grained access control is not supported for PostgreSQL-dialect databases.
- Export operations don't export database roles and privileges, and import operations can't import them. You must manually set up roles and privileges after your import is complete.
- The Data tab on the TABLE page in the Google Cloud console is not available for fine-grained access control users.
SELECTon all key columns.
See the following topics for more information:
- Fine-grained access control for change streams
- Configure fine-grained access control
- Fine-grained access control privileges reference
- Fine-grained access control system roles
REVOKEstatements in the GoogleSQL data definition language topic.