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 can set up fine-grained access policies at the table and column level. To manage table-level and column-level policies, you use the following DDL statements and IAM features:
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
UPDATEat the table and column level, and
DELETEat the table level. Privileges correspond to the like-named SQL statements. For example, a role with the
INSERTprivilege can execute the
INSERTSQL statement on the tables and columns 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 use of existing IAM methods to grant access to database roles to IAM principals. You grant fine-grained access control privileges to IAM principals by granting them access to database roles.
Cloud Spanner users (IAM principals) are not fine-grained access control users by default. As an administrator, you must enable fine-grained access control for each user. Database 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.
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
Database access for fine-grained access control users
Users who are enabled for fine-grained access control must specify a database role when starting a session with the database.
For details, see Access a database with fine-grained access control.
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 ROLE pii_access TO ROLE hr_manager, hr_director; GRANT SELECT ON TABLE employees TO ROLE pii_access;
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 steps that you take to begin securing data with 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, revoke 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
For details, see Configure fine-grained access control.
- Fine-grained access control is not supported for PostgreSQL-dialect databases.
- Export operations don't export roles and privileges.
- The Data tab on the TABLE page in the Google Cloud console is not available for fine-grained access control users.
- Change streams, views, and key visualizers are not supported for fine-grained access control users.
SELECTon all key columns.
See the following topics for more information:
- Configure fine-grained access control
- Fine-grained access control privileges reference
- Fine-grained access control system roles
- Google Standard SQL data definition language