Fine-grained access control overview

Spanner fine-grained access control combines the benefits of Identity and Access Management (IAM) with 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. You can use it to access individual database objects. To control access to the entire database, use IAM roles.

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:

CREATE and DROP statements for creating and dropping database roles. Database roles are collections of privileges. You can create up to 100 roles for a database.
GRANT and REVOKE statements to grant and revoke privileges to and from database roles. Privileges include SELECT, INSERT, UPDATE, DELETE, and EXECUTE. Privilege names correspond to the like-named SQL statements. For example, a role with the INSERT privilege can execute the INSERT SQL statement on the tables that are specified in the GRANT statement.

The following DDL statements grant SELECT on table employees to the hr_rep database role.
GoogleSQL
```
CREATE ROLE hr_rep;
GRANT SELECT ON TABLE employees TO ROLE hr_rep;
```
PostgreSQL
```
CREATE ROLE hr_rep;
GRANT SELECT ON TABLE employees TO hr_rep;
```
For more information on privileges, see Fine-grained access control privileges reference.
GRANT statements for granting roles to other roles to create hierarchies of roles, with privilege inheritance.

Use cases

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 SELECT and INSERT operations but not UPDATE and DELETE operations.

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.

Schemas: You can grant the USAGE privilege on schemas to specific database roles. For a non-default schema, database roles must have the USAGE privilege to access the database objects. The privilege check looks like the following:

Do you have USAGE on the schema?

No: Reject access.

Yes: Do you also have the appropriate rights on the table?

No: Reject access.

Yes: You can access the table.

Tables: You can grant the SELECT, INSERT, UPDATE, and DELETE privileges on tables to database roles. For interleaved tables, a privilege granted on the parent table doesn't propagate to the child table.
Columns: You can grant SELECT, INSERT, and UPDATE on a subset of columns in a table. The privilege is then valid only for those columns. DELETE is not permitted at the column level.
Views: You can grant SELECT privilege on a view. Only SELECT is supported for views. Spanner supports both invoker's rights views and definer's rights views. If you create a view with invoker's rights, to query the view, the database role or user needs the SELECT privilege on the view, and also the SELECT privilege on the underlying objects referenced in the view. If you create a view with definer's rights, to query the view, the database role or user only needs the SELECT privilege on the view. For more information, see Views overview.
Change streams: You can grant SELECT on change streams. You must also grant EXECUTE on the read function associated with a change stream. For information, see Fine-grained access control for change streams.
Sequences: You can grant SELECT and UPDATE on sequences. For information, see Fine-grained access control for sequences.
Models: You can grant EXECUTE on models. For information, see Fine-grained access control for models.

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 spanner_sys_reader system role to access Key Visualizer, and needs the spanner_info_reader system role to be able to see unfiltered results when querying the INFORMATION_SCHEMA tables.

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 statements:

GoogleSQL

GRANT SELECT ON TABLE employees TO ROLE pii_access;
GRANT ROLE pii_access TO ROLE hr_manager, hr_director;

PostgreSQL

GRANT SELECT ON TABLE employees TO pii_access;
GRANT pii_access TO hr_manager, hr_director;

hr_manager and hr_director are members of role pii_access, and inherit the SELECT privilege on table employees.

Privilege inheritance

hr_manager and hr_director can also have members, and those members would inherit the SELECT privilege on employees.

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.admin or 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:
1. Enable fine-grained access control for the principal. The principal is then automatically granted the public database role, which has no privileges by default. This is a one-time operation for each principal.
2. Grant IAM permissions on one or more database roles to the principal.
3. 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.

Limitations

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.

What's next

See the following topics for more information: