Configure fine-grained access control

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

This page explains how to configure fine-grained access control for Cloud Spanner databases.

To learn about fine-grained access control, see About fine-grained access control.

Configuring fine-grained access control involves the following steps:

  1. Create database roles and grant privileges.

  2. Optional: Create a hierarchy of roles with inheritance.

  3. Grant access to database roles to IAM principals.

After you configure the database roles, a fine-grained access control user must select a role to perform queries, DML, or row operations against the database.

Create database roles and grant privileges

A database role is a collection of fine-grained access privileges. You can create up to 100 database roles for each database.

Decide on roles and role hierarchies in your database and encode them in DDL. As with other schema changes in Spanner, we strongly recommend issuing schema changes in a batch rather than separately. For more information, see Limit the frequency of schema updates.

Console

To create a database role and grant fine-grained access privileges to it, follow these steps:

  1. Go to the Instances page in the Google Cloud console.

    Instances

  2. Select the instance containing the database for which you want to add the role.

  3. Select the database.

  4. On the Overview page, click Write DDL.

  5. On the Write DDL statements page, for each database role that you want to create and grant privileges to, follow these steps:

    1. To create the role, enter the following statement:

      CREATE ROLE ROLE_NAME;
      

    2. To grant privileges to the role, enter the following statement:

      GRANT PRIVILEGE_LIST ON TABLE TABLE_LIST TO ROLE ROLE_NAME;
      

      • PRIVILEGE_LIST is a comma-delimited list of privileges. Allowed privileges are SELECT, INSERT, UPDATE, and DELETE. DELETE is allowed only at the table level.

      • TABLE_LIST is a comma-delimited list of tables.

      For example, to grant SELECT, INSERT, and UPDATE on tables employees and contractors to the database role hr_manager, enter the following statement:

      GRANT SELECT, INSERT, UPDATE ON TABLE employees, contractors TO ROLE hr_manager;
      

      You can grant privileges to a list of roles instead of just one role.

      You can grant each privilege on a subset of table columns. For examples, see Fine-grained access control privileges. For detailed syntax for the GRANT statement, see Google Standard SQL data definition language.

      You can use a DDL template for the GRANT statement. In the DDL TEMPLATES drop-down menu, select Database roles, and then under Grant role privileges, select a template.

  6. Click Submit.

    If there are errors in your DDL, the Google Cloud console returns an error.

gcloud

To create a database role and grant fine-grained access privileges to it, use the gcloud spanner databases ddl update command as follows:

gcloud spanner databases ddl update DATABASE_NAME \
--ddl='CREATE ROLE ROLE_NAME; GRANT PRIVILEGES ON TABLE TABLES TO ROLE ROLE_NAME;'
  • PRIVILEGES is a comma-delimited list of fine-grained access control privileges. Allowed privileges are SELECT, INSERT, UPDATE, and DELETE.

  • TABLES is a comma-delimited list of tables.

For example, to grant SELECT, INSERT, and UPDATE on tables employees and contractors to the database role hr_manager, enter the following statement:

GRANT SELECT, INSERT, UPDATE ON TABLE employees, contractors TO ROLE hr_manager;

You can grant privileges to a list of roles instead of just one role.

You can grant each privilege on a subset of table columns. For examples, see Fine-grained access control privileges. For detailed syntax for the GRANT statement, see Google Standard SQL data definition language.

Create a hierarchy of roles with inheritance

You can create a hierarchy of database roles by granting one database role to another. Child roles (known as member roles) inherit privileges from the parent.

To grant a database role to another database role, use the following statement:

GRANT ROLE role1 TO ROLE role2;

For more information, see Database role hierarchies and inheritance.

Grant access to database roles to IAM principals

You use IAM to grant access to database roles.

Console

To grant access to database roles to a principal, follow these steps:

  1. On the database Overview page, click SHOW INFO PANEL if the Info panel is not already open.

  2. Click ADD PRINCIPAL.

  3. In the Add principals and roles panel, in New principals, specify one or more IAM principals.

  4. In the Role menu, select Cloud Spanner > Cloud Spanner Fine-grained Access User.

    You need to grant this role only once to each principal. It makes the principal a fine-grained access control user.

  5. Click ADD ANOTHER ROLE.

  6. In the Select a role menu, select Cloud Spanner > Cloud Spanner Database Role User.

  7. Follow these steps to create the IAM condition that specifies the roles to grant.

    1. Click ADD CONDITION.

    2. In the Edit condition panel, enter a title and description for the condition.

      If you're granting a single database role, you would typically include the role name in the condition title. If you're granting multiple roles, you could indicate something about the set of roles.

    3. Click CONDITION EDITOR.

    4. In the Expression field, enter the following code:

      resource.type == "spanner.googleapis.com/DatabaseRole" &&
      resource.name.endsWith("/ROLE")
      

      Replace ROLE with your role name.

      Or, to grant access to more than one role, add more conditions with the or (||) operator, as shown in the following example:

      resource.type == "spanner.googleapis.com/DatabaseRole" &&
      (resource.name.endsWith("/ROLE1") || resource.name.endsWith("/ROLE2"))
      

      This code grants two roles. Replace ROLE1 and ROLE2 with your role names. To grant more than two roles, add more or conditions.

      You can use any condition expression that is supported by IAM. For more information, see Overview of IAM conditions.

    5. Click Save.

    6. Back in the Add principals panel, verify that the condition appears under the Condition column next to the Role field.

    7. Click Save.

      Back on the Info panel, under Role/Principal, notice that Cloud Spanner Database Role User appears for each defined condition. The number in parentheses next to the condition indicates the number of principals that are granted the database role by that condition.

    8. To correct errors in database role names or conditions, or to add additional database roles for a principal, follow these steps:

      1. Expand the Cloud Spanner Database Role User entry that corresponds to the condition that you want.

      2. On the Edit permissions page, do one of the following:

        • Click ADD ANOTHER ROLE.

        • To edit the condition, click the pencil icon adjacent to the condition name. Then on the Edit condition page, click CONDITION EDITOR, and make corrections.

gcloud

To grant access to database roles to an IAM principal, follow these steps:

  1. Enable fine-grained access control for the principal by using the gcloud spanner databases add-iam-policy-binding command as follows:

    gcloud spanner databases add-iam-policy-binding DATABASE_NAME \
    --instance=INSTANCE_NAME \
    --role=roles/spanner.fineGrainedAccessUser \
    --member=MEMBER_NAME
    
    • MEMBER_NAME is the identifier for the principal. It must be of the form user|group|serviceAccount:email or domain:domain.

    • This command makes the principal a fine-grained access control user. Submit this command only once for each principal.

  2. Grant permission to use one or more database roles by using the gcloud spanner databases add-iam-policy-binding command as follows:

    gcloud spanner databases add-iam-policy-binding DATABASE_NAME \
    --instance=INSTANCE_NAME \
    --role=roles/spanner.databaseRoleUser \
    --member=MEMBER_NAME \
    --condition=CONDITION
    
    • MEMBER_NAME is the identifier for the principal. It must be of the form user|group|serviceAccount:email or domain:domain.

    • CONDITION is an IAM condition expression that specifies the roles to grant to the principal.

      CONDITION has the following form:

      --condition='expression=(resource.type == "spanner.googleapis.com/DatabaseRole" && (resource.name.endsWith("/ROLE1") || resource.name.endsWith("/ROLE2"))),title=TITLE,description=DESCRIPTION'
      

      This code grants two roles. Replace ROLE1 and ROLE2 with your role names. To grant only one database role, delete the expression after the || operator. To grant more than two roles, add more or conditions with the || operator.

      You can use any condition expression that is supported by IAM. For more information, see Overview of IAM conditions.

    The following example grants the database roles hr_rep and hr_manager to the principal jsmith@example.com.

    gcloud spanner databases add-iam-policy-binding myDatabase \
      --instance=myInstance \
      --role=roles/spanner.databaseRoleUser \
      --member=user:jsmith@example.com \
      --condition='expression=(resource.type == "spanner.googleapis.com/DatabaseRole" && (resource.name.endsWith("/hr_rep") || resource.name.endsWith("/hr_manager"))),title=HR roles,description=Grant permissions on HR roles'
    

Transition a principal to fine-grained access control

To transition an IAM principal from database-level access control to fine-grained access control, follow these steps:

  1. Enable fine-grained access control for the principal and grant access to all required database roles, as described in Grant access to database roles to IAM principals.

  2. Update all applications that run as this principal. Specify the appropriate database roles in calls to client library methods.

  3. Revoke all IAM database-level roles from the principal. This way, access for the principal is governed by only one method.

    console

    gcloud

    • To revoke an IAM database-level role, enter the following command:

      gcloud spanner databases remove-iam-policy-binding DATABASE_NAME \
      --instance=INSTANCE_NAME \
      --role=roles/IAM_ROLE_NAME \
      --member=MEMBER_NAME
      
      • MEMBER_NAME is the identifier for the principal. It must be of the form user|group|serviceAccount:email or domain:domain.

List database roles

To list the database roles by using the Google Cloud console, enter the following query on the Query page for the database:

  SELECT * FROM INFORMATION_SCHEMA.ROLES;

View the privileges granted to a database role

To view the privileges granted to a role for all tables, use the following query:

  SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE grantee = ROLE_NAME;
  

To view the privileges granted to a role for all columns, use the following query:

  SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE grantee = ROLE_NAME;
  

Drop a database role

Dropping a database role automatically revokes the membership of other roles from the role, and revokes the role's membership in other roles.

You must first revoke all privileges from a database role before you can drop it. You can drop a database role whether or not principals have permissions on it.

Before you drop a database role, remove any IAM policy bindings that refer to that role, so that a database role created later with the same name does not inherit those bindings.

Console

To drop a database role, follow these steps:

  1. On the database Overview page, click Write DDL.

  2. To revoke privileges from the role, enter the following statement:

    REVOKE PRIVILEGE_LIST ON TABLE TABLE_LIST FROM ROLE ROLE_NAME;
    

    • PRIVILEGE_LIST is a comma-delimited list of privileges. Allowed privileges are SELECT, INSERT, UPDATE, and DELETE.

    • TABLE_LIST is a comma-delimited list of tables.

    For example, to revoke SELECT, INSERT, and UPDATE on tables employees and contractors from the database role hr_manager, enter the following statement:

    REVOKE SELECT, INSERT, UPDATE ON TABLE employees, contractors FROM ROLE hr_manager;
    
  3. To drop the role, enter the following statement:

    DROP ROLE ROLE_NAME;
    

  4. To apply the updates, click Submit.

  5. Delete any IAM condition associated with the role.

    1. In the list of roles on the Info panel, locate the Cloud Spanner Database Role User role that has the condition title of interest adjacent to it, and then expand the role to view the principals that have access to the role.

    2. For one of the principals, click the Edit principal (pencil) icon.

    3. On the Edit permissions page, click the Edit IAM condition (pencil) icon adjacent to the condition.

    4. On the Edit condition page, click the DELETE.

    5. Confirm the deletion, and then click Save.

gcloud

  1. To revoke all privileges for a role and then drop the role, use the gcloud spanner databases ddl update command as follows:

    gcloud spanner databases ddl update DATABASE_NAME \
    --instance=INSTANCE_NAME \
    --ddl='REVOKE PERMISSIONS ON TABLE TABLE_NAME FROM ROLE ROLE_NAME; DROP ROLE ROLE_NAME;'
    

    Valid values for PERMISSIONS are SELECT, INSERT, UPDATE, and DELETE.

  2. To delete any related IAM conditions, use the gcloud spanner databases remove-iam-policy-binding command as follows:

    gcloud spanner databases remove-iam-policy-binding DATABASE_NAME \
    --instance=INSTANCE_NAME \
    --role=ROLE_NAME \
    --member=MEMBER_NAME \
    --condition=CONDITION
    
    • MEMBER_NAME is the identifier for the principal. It must be of the form user|group|serviceAccount:email or domain:domain.

    • CONDITION is an IAM condition expression that specifies the roles to grant to the principal.

      CONDITION has the following form:

      --condition='expression=(resource.type == "spanner.googleapis.com/DatabaseRole" && (resource.name.endsWith("/ROLE1") || resource.name.endsWith("/ROLE2"))),title=TITLE,description=DESCRIPTION'
      

      The entire condition specification must exactly match the condition specification that was used in the command that granted permission, including title and description.

More information