Spanner fine-grained access control combines the benefits ofIdentity and Access Management (IAM) withSQL role-based access control. With fine-grained access control, you definedatabase roles, grant privileges to the roles, and createIAM policies to grant permissions on database roles toIAM principals. This page describes how to use fine-grained access controlwith Spanner for GoogleSQL-dialect databases and PostgreSQL-dialect databases.

As an administrator, you must enable fine-grained access control for individual IAMprincipals. Principalsfor whom fine-grained access control is enabled ("fine-grained access control users") must assume a databaserole to access Spanner resources.

Resource access for users who are are not fine-grained access control users is governedby IAM database-level roles.Fine-grained access control is fully compatible and can co-exist with existingIAM database-level access control. You can use it to accessindividual database objects. To control access to the entire database, useIAM roles.

• CREATE andDROP statements for creating and dropping database roles.Database roles are collections of privileges. You can createup to 100 roles for a database.

• GRANT andREVOKE statements to grant and revoke privileges to and fromdatabase roles. Privileges includeSELECT,INSERT,UPDATE,DELETE, andEXECUTE. Privilege namescorrespond to the like-named SQL statements. For example, a role with theINSERT privilege can execute theINSERT SQL statement on the tables thatare specified in theGRANT statement.

  The following DDL statements grantSELECT on tableemployeesto thehr_rep database role.

  GoogleSQL

  CREATEROLEhr_rep;GRANTSELECTONTABLEemployeesTOROLEhr_rep;

  PostgreSQL

  CREATEROLEhr_rep;GRANTSELECTONTABLEemployeesTOhr_rep;

  For more information on privileges, seeFine-grained access control privileges reference.

• GRANT statements for granting roles to other roles to create hierarchies ofroles, 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 socialsecurity numbers.
• A ride-sharing application with different service accounts and privilegesfor riders and drivers.
• A ledger that permitsSELECT andINSERT operations but notUPDATE andDELETE operations.

Spanner resources and their privileges

The following is a list of Spanner resources and thefine-grained access controlprivileges that you can grant for them.

Schemas

You can grant theUSAGE privilege on schemas to specific database roles. Fora non-default schema, database roles must have theUSAGE privilege to accessthe database objects. The privilege check looks like the following:

Do you haveUSAGE 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 theSELECT,INSERT,UPDATE, andDELETE privileges ontables to database roles. For interleaved tables, a privilege granted on theparent table doesn't propagate to the child table.

Columns

You can grantSELECT,INSERT, andUPDATE on a subset of columns in atable. The privilege is then valid only for those columns.DELETE is notpermitted at the column level.

Views

You can grantSELECT privilege on a view. OnlySELECT is supported forviews. Spanner supports both invoker's rights views and definer'srights views. If you create a view with invoker's rights, to query the view, thedatabase role or user needs theSELECT privilege on the view, and also theSELECT privilege on the underlying objects referenced in the view. If youcreate a view with definer's rights, to query the view, the database role oruser only needs theSELECT privilege on the view. For more information, seeViews overview.

Change streams

You can grantSELECT on change streams. You must also grantEXECUTE on theread function associated with a change stream. For information,seeFine-grained access control for change streams.

Sequences

You can grantSELECT andUPDATE on sequences. For information,seeFine-grained access control for sequences.

Models

You can grantEXECUTE on models. For information,seeFine-grained access control for models.

Fine-grained access control system roles

Fine-grained access control has predefinedsystem roles for each database.Like user-defined database roles, system roles can control access toSpanner resources.

For example, a fine-grained access control user needs to be granted thespanner_sys_readersystem role to access Key Visualizer, and needs thespanner_info_reader systemrole to be able to see unfiltered results when querying theINFORMATION_SCHEMA tables.

For more information, seeFine-grained access control system roles.

Database role hierarchies and inheritance

You can create hierarchies of database roles, where child rolesinherit the privileges of parent roles. Child roles are known asmembers ofthe parent role.

For example, consider the followingGRANT statements:

GRANTSELECTONTABLEemployeesTOROLEpii_access;GRANTROLEpii_accessTOROLEhr_manager,hr_director;

GRANTSELECTONTABLEemployeesTOpii_access;GRANTpii_accessTOhr_manager,hr_director;

hr_manager andhr_director are members of rolepii_access, and inherit theSELECT privilege on tableemployees.

hr_manager andhr_director can also have members, and those members wouldinherit theSELECT privilege onemployees.

There are no limits on the depth of role hierarchies, but query performancemight degrade with deep and wide role hierarchy structures.

Backup and restore

Spannerbackups include database roledefinitions. When a database is restored from backup, database rolesare re-created with their granted privileges. However, IAMpolicies are not a part of database backups, so you must re-grant access todatabase 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 beginsecuring data with fine-grained access control. For details, seeConfigure fine-grained access control.

You must be granted theroles/spanner.admin orroles/spanner.databaseAdmin IAM roles to perform these tasks.

1. Create database roles and grant privileges to the roles.
2. Optional: Create role hierarchies with inheritance by granting roles toother roles.
3. 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 thepublic database role,which has no privileges by default. This is a one-time operation for eachprincipal.
   2. Grant IAM permissions on one or moredatabase 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 rolesso that the principal's access control is managed by only one method.

Limitations

• Export operations don't export database roles and privileges, and importoperations can't import them. You must manually set up roles and privilegesafter your import is complete.
• TheData tab on theTABLE page in the Google Cloud console is not available forfine-grained access control users.

What's next

• Access a database with fine-grained access control
• Fine-grained access control for change streams
• Configure fine-grained access control
• Fine-grained access control privileges reference
• Fine-grained access control system roles
• GoogleSQLGRANT andREVOKE statements
• PostgreSQLGRANT andREVOKE statements
• Fine-grained access control for sequences
• Fine-grained access control for models