About fine-grained access control

Stay organized with collections Save and categorize content based on your preferences.

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:

  • 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, and UPDATE at the table and column level, and DELETE at the table level. Privileges correspond to the like-named SQL statements. For example, a role with the INSERT privilege can execute the INSERT SQL statement on the tables and columns that are specified in the GRANT statement.

    The following DDL statements grant SELECT on table employees to the hr_rep database role.

    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.

  • GRANT statements 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.

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.

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

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

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 steps that you take to begin securing data with fine-grained access control.

You must be granted the roles/spanner.admin or roles/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 to other 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 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, revoke the database-level roles so that the principal's access control is managed by only one method.

For details, see Configure fine-grained access control.

Limitations

  • 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.
  • UPDATE and DELETE operations require SELECT on all key columns.

More information

See the following topics for more information: