Databases

Managing fine-grained access at Spanner scale

May 5, 2023

Harshit Mehrotra

Senior Software Engineer, Google Cloud

Cloud Spanner is Google Cloud’s fully managed relational database that is strongly consistent, highly scalable, and offers up to 99.999% of availability. It powers applications of all sizes in industries such as financial services, games, retail, and healthcare.

Spanner has always provided access control with Identity and Access Management (IAM). IAM provides a simple and consistent access control interface for all Google Cloud services. With capabilities such as a built-in audit trail and context-aware access, IAM makes it easy to grant permissions at the instance and database level to Spanner users.

Nonetheless, there are a number of use cases – inventory ledgers, analytics, and more that need roles at a level that is more granular than the database-level. For this reason, a few months ago, we released the preview for fine-grained access control (FGAC) for Spanner, which allows access control at the granularity of schema objects. FGAC enables these use cases by providing access to tables, columns, views, and change streams.

We’re happy to announce that FGAC for Spanner is now generally available. FGAC combines the benefits of Identity and Access Management (IAM) with traditional SQL role-based access control.

To learn more about fine-grained access control in Spanner, check out Cloud Spanner role-based access control. In this blog, we’ll show you how to configure fine-grained access to schema objects and troubleshoot permission issues using an inventory ledger application.

Getting started

Set up a database and schema

We’ll use Terraform to manage the Cloud Spanner database and its IAM policies. If you’re not already familiar with using Terraform with Spanner, the Cloud Spanner with Terraform codelab and Provisioning Cloud Spanner With Terraform blog offer great introductions to the provisioning of instances, and creating and modifying databases.

First, create a Terraform configuration that creates a Cloud Spanner instance and database if it does not exist. Replace PROJECT_NAME, INSTANCE_ID, and DATABASE_NAME in the configuration. This will create a GoogleSQL dialect database.

Next, create the required tables in the database schema. Our application schema has two tables: Product and InventoryLedger. We recommend using a schema versioning tool like Liquibase to manage your schema because Terraform offers limited support for schema management.

Set up fine-grained roles and privileges

The application has multiple users with different levels of access to the database, as described in the following table.

https://storage.googleapis.com/gweb-cloudblog-publish/images/1_Managing_Fine-Grained_Access.max-1300x1300.png

Next, let’s create the database roles and grant them the required privileges. This can be done by executing the following DDL statements. Note that spanner_sys_reader is a pre-defined system role in Cloud Spanner that grants read access to all tables in the SPANNER_SYS schema.

These DDL statements create the following roles and privileges setup:

Database role product_admin has the SELECT, INSERT, UPDATE, and DELETE privileges on the PRODUCT table.
Database role inventory_viewer has the SELECT privilege on InventoryLedger table.
Database role inventory_manager has the SELECT and INSERT privileges on the InventoryLedger table. The SELECT privilege is inherited from the inventory_viewer role.
Database role inventory_superuser has the SELECT, INSERT, UPDATE, and DELETE privileges on the InventoryLedger table. The SELECT and INSERT privileges are inherited from the inventory_manager role.

https://storage.googleapis.com/gweb-cloudblog-publish/images/2_Managing_Fine-Grained_Access.max-1000x1000.png

Configure fine-grained access users

Next, we will configure IAM principals (users, service accounts, groups, workspace account or Cloud Identity domain) to have fine-grained access to the database (“fine-grained access users”). IAM policies can also be managed through gcloud or the Google Cloud console, as described in the documentation.

https://storage.googleapis.com/gweb-cloudblog-publish/images/3_Managing_Fine-Grained_Access.max-800x800.png

data "google_iam_policy" "fgac_users" {
  # First, we configure the IAM principals as FGAC users by binding them to the spanner.fineGrainedAccessUser role.
  binding {
    role = "roles/spanner.fineGrainedAccessUser"
    
    members = [
      "user:tim@example.com",
      "user:jason@example.com",
      "user:bella@example.com",
      "user:john@example.com",
      "user:mark@example.com",
    ]
  }

# Next, we create conditional IAM policy bindings to grant IAM principals access to the database role we created by DDL above.

# IAM policy binding for access to product_admin role.
  binding {
    role = "roles/spanner.databaseRoleUser"

members = [
      "user:tim@example.com",
    ]

condition {
      title       = "product_admin membership"
      description = "Membership to product_admin database role"
      expression  = "resource.type == 'spanner.googleapis.com/DatabaseRole' && resource.name.endsWith('/product_admin')"
    }    
  }

# IAM policy binding for access to inventory_viewer role.
  binding {
    role = "roles/spanner.databaseRoleUser"

members = [
      "user:jason@example.com",
    ]

condition {
      title       = "inventory_viewer membership"
      description = "Membership to inventory_viewer database role"
      expression  = "resource.type == 'spanner.googleapis.com/DatabaseRole' && resource.name.endsWith('/inventory_viewer')"
    }    
  }

# IAM policy binding for access to inventory_manager role.
  binding {
    role = "roles/spanner.databaseRoleUser"

members = [
      "user:bella@example.com",
    ]

condition {
      title       = "inventory_manager membership"
      description = "Membership to inventory_manager database role"
      expression  = "resource.type == 'spanner.googleapis.com/DatabaseRole' && resource.name.endsWith('/inventory_manager')"
    }    
  }

# IAM policy binding for access to inventory_superuser role.
  binding {
    role = "roles/spanner.databaseRoleUser"

members = [
      "user:john@example.com",
    ]

condition {
      title       = "inventory_superuser membership"
      description = "Membership to inventory_superuser database role"
      expression  = "resource.type == 'spanner.googleapis.com/DatabaseRole' && resource.name.endsWith('/inventory_superuser')"
    }    
  }
 
  # IAM policy binding for membership to spanner_sys_reader role.
  binding {
    role = "roles/spanner.databaseRoleUser"
    members = [
      "user:mark@example.com",
    ]
    condition {
      title       = "spanner_sys_reader membership"
      description = "Membership to spanner_sys_reader database role"
      expression  = "resource.type == 'spanner.googleapis.com/DatabaseRole' && resource.name.endsWith('/spanner_sys_reader')"
    }    
  }
}

resource "google_spanner_database_iam_policy" "database" {
  instance    = "INSTANCE_ID"
  database    = "DATABASE_NAME"
  policy_data = data.google_iam_policy.fgac_users.policy_data
}

Note that you could also specify the fully qualified database role name in a conditional IAM policy binding as follows:

Now, user:tim@example.com can read the data as follows:

Troubleshooting fine-grained access IAM permissions

Find IAM principals who are fine-grained access users

Use the following command to view a list of IAM principals configured as fine-grained access users on the database. To run this command, you should have enabled the Cloud Asset API on your project and you must have the cloudasset.assets.searchAllIamPolicies IAM permission.

Find IAM principals who have the inventory_manager role

Use the following command to view a list of IAM principals who have access to a particular database role. To run this command, you should have enabled the Cloud Asset API on your project and you must have the cloudasset.assets.searchAllIamPolicies IAM permission.

View IAM conditions for configuring access of an IAM principal

To troubleshoot access issues for user ‘user:mark@example.com’ you may want to view what conditional bindings have been set to configure access for this user. To run this command, you should have enabled the Cloud Asset API on your project and you must have the cloudasset.assets.searchAllIamPolicies IAM permission.

Note that since IAM conditions may be specified using the endsWith operator, for example ”resource.name.endsWith('/inventory_viewer')”, this may not return the actual database role name that the user has access to.

Managing and troubleshooting fine-grained access privileges

Consider a more interesting role hierarchy for the next few scenarios. In this example, roles two and three inherit privileges from role one. Likewise, role eight inherits from roles one, three and seven. Privileges enclosed in {} are inherited from parents/ancestors. So, INSERT and UPDATE are directly granted to role seven, whereas role seven inherits SELECT from its parent (role three). For this scenario, all privileges are granted on the Users table.

https://storage.googleapis.com/gweb-cloudblog-publish/images/4_Managing_Fine-Grained_Access.max-1300x1300.png

Find all database roles from which role seven inherits privileges

You need to have the roles/spanner.admin or roles/spanner.databaseAdmin IAM roles,or access to database role seven to execute this command. roles/spanner.admin and roles/spanner.databaseAdmin allow you to execute a query as any database role.

Find all privileges that role eight has on the Users table

You need to have the roles/spanner.admin or roles/spanner.databaseAdmin IAM roles or access to database role eight to execute this command.

Limitations

Cloud Spanner does not support recursive Common Table Expressions (CTEs). As a result, it is not possible to find out “Which roles have SELECT privilege (including inheritance) on a table” or “Find all roles that inherit privileges from role foo” with a simple query. Instead, you can break this down into multiple queries over INFORMATION_SCHEMA.TABLE_PRIVILEGES and INFORMATION_SCHEMA.ROLE_GRANTEES to find this information.

Get started with Spanner fine-grained access control today

Fine-grained access control provides a powerful, elegant, and more flexible authorization model by combining the benefits of IAM with traditional SQL role-based access control. Fine-grained access control unlocks several important use cases like inventory ledgers, banking applications, analytics, and others that require a more granular level of access control. It’s available today for you to try out!

To get started with Spanner, create a database or try it with a Spanner Qwiklab.
For more information about fine-grained access control, read Cloud Spanner role-based access control or see the documentation.

Posted in