Managing fine-grained access at Spanner scale
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](https://storage.googleapis.com/gweb-cloudblog-publish/images/1_Managing_Fine-Grained_Access.max-1300x1300.png)
![https://storage.googleapis.com/gweb-cloudblog-publish/images/1_Managing_Fine-Grained_Access.max-1300x1300.png](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
, andDELETE
privileges on thePRODUCT
table.Database role
inventory_viewer
has theSELECT
privilege onInventoryLedger
table.Database role
inventory_manager
has theSELECT
andINSERT
privileges on theInventoryLedger
table. TheSELECT
privilege is inherited from the inventory_viewer role.Database role
inventory_superuser
has theSELECT
,INSERT
,UPDATE
, andDELETE
privileges on theInventoryLedger
table. TheSELECT
andINSERT
privileges are inherited from theinventory_manager
role.
![https://storage.googleapis.com/gweb-cloudblog-publish/images/2_Managing_Fine-Grained_Access.max-1000x1000.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/2_Managing_Fine-Grained_Access.max-1000x1000.png)
![https://storage.googleapis.com/gweb-cloudblog-publish/images/2_Managing_Fine-Grained_Access.max-1000x1000.png](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](https://storage.googleapis.com/gweb-cloudblog-publish/images/3_Managing_Fine-Grained_Access.max-800x800.png)
![https://storage.googleapis.com/gweb-cloudblog-publish/images/3_Managing_Fine-Grained_Access.max-800x800.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/3_Managing_Fine-Grained_Access.max-800x800.png)
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](https://storage.googleapis.com/gweb-cloudblog-publish/images/4_Managing_Fine-Grained_Access.max-1300x1300.png)
![https://storage.googleapis.com/gweb-cloudblog-publish/images/4_Managing_Fine-Grained_Access.max-1300x1300.png](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.