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.
code_block[StructValue([(u’code’, u’provider “google” {rn project = “PROJECT_NAME”rn}rnrnresource “google_spanner_instance” “main” {rn config = “regional-us-central1″rn display_name = “INSTANCE_ID”rn num_nodes = 3rn}rnrnresource “google_spanner_database” “database” {rn instance = google_spanner_instance.main.namern name = “DATABASE_NAME”rn}’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2fca3dd0>)])]
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.
code_block[StructValue([(u’code’, u’CREATE TABLE Product (rn ProductId INT64 NOT NULL,rn Title STRING(500) NOT NULL,rn Sku STRING(500) NOT NULL,rn Msrp FLOAT64 NOT NULL,rn Discount FLOAT64,rn QuantityAvailable INT64,rn CreatedAt DATE,rn) PRIMARY KEY(ProductId);rnrnCREATE TABLE InventoryLedger (rn Id INT64 NOT NULL,rn ProductId INT64 NOT NULL,rn UserId INT64 NOT NULL,rn Quantity INT64 NOT NULL,rn Price FLOAT64,rn Tax FLOAT64,rn Status STRING(20),rn CreatedAt DATE NOT NULL,rn) PRIMARY KEY(Id);’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2fc831d0>)])]
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.
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.
code_block[StructValue([(u’code’, u’CREATE ROLE product_admin;rnCREATE ROLE inventory_viewer;rnCREATE ROLE inventory_manager;rnCREATE ROLE inventory_superuser;rnrn– Set up inventory_manager to inherit privilege from inventory_viewer.rnGRANT ROLE inventory_viewer TO ROLE inventory_manager;rnrn– Set up inventory_superuser to inherit privilege from inventory_manager.rnGRANT ROLE inventory_manager TO ROLE inventory_superuser;rnrnGRANT SELECT, INSERT, UPDATE, DELETE ON TABLE Product TO ROLE product_admin;rnGRANT SELECT ON TABLE InventoryLedger TO ROLE inventory_viewer;rnGRANT INSERT ON TABLE InventoryLedger TO ROLE inventory_manager;rnGRANT UPDATE, DELETE ON TABLE InventoryLedger TO ROLE inventory_superuser;’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2ea78b50>)])]
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.
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.
code_block[StructValue([(u’code’, u’data “google_iam_policy” “fgac_users” {rn # First, we configure the IAM principals as FGAC users by binding them to the spanner.fineGrainedAccessUser role.rn binding {rn role = “roles/spanner.fineGrainedAccessUser”rn rn members = [rn “user:
[email protected]”,rn “user:
[email protected]”,rn “user:
[email protected]”,rn “user:
[email protected]”,rn “user:
[email protected]”,rn ]rn }rnrn # Next, we create conditional IAM policy bindings to grant IAM principals access to the database role we created by DDL above.rnrn # IAM policy binding for access to product_admin role.rn binding {rn role = “roles/spanner.databaseRoleUser”rnrn members = [rn “user:
[email protected]”,rn ]rnrn condition {rn title = “product_admin membership”rn description = “Membership to product_admin database role”rn expression = “resource.type == ‘spanner.googleapis.com/DatabaseRole’ && resource.name.endsWith(‘/product_admin’)”rn } rn }rnrn # IAM policy binding for access to inventory_viewer role.rn binding {rn role = “roles/spanner.databaseRoleUser”rnrn members = [rn “user:
[email protected]”,rn ]rnrn condition {rn title = “inventory_viewer membership”rn description = “Membership to inventory_viewer database role”rn expression = “resource.type == ‘spanner.googleapis.com/DatabaseRole’ && resource.name.endsWith(‘/inventory_viewer’)”rn } rn }rnrn # IAM policy binding for access to inventory_manager role.rn binding {rn role = “roles/spanner.databaseRoleUser”rnrn members = [rn “user:
[email protected]”,rn ]rnrn condition {rn title = “inventory_manager membership”rn description = “Membership to inventory_manager database role”rn expression = “resource.type == ‘spanner.googleapis.com/DatabaseRole’ && resource.name.endsWith(‘/inventory_manager’)”rn } rn }rnrn # IAM policy binding for access to inventory_superuser role.rn binding {rn role = “roles/spanner.databaseRoleUser”rnrn members = [rn “user:
[email protected]”,rn ]rnrn condition {rn title = “inventory_superuser membership”rn description = “Membership to inventory_superuser database role”rn expression = “resource.type == ‘spanner.googleapis.com/DatabaseRole’ && resource.name.endsWith(‘/inventory_superuser’)”rn } rn }rn rn # IAM policy binding for membership to spanner_sys_reader role.rn binding {rn role = “roles/spanner.databaseRoleUser”rn members = [rn “user:
[email protected]”,rn ]rn condition {rn title = “spanner_sys_reader membership”rn description = “Membership to spanner_sys_reader database role”rn expression = “resource.type == ‘spanner.googleapis.com/DatabaseRole’ && resource.name.endsWith(‘/spanner_sys_reader’)”rn } rn }rn}rnrnresource “google_spanner_database_iam_policy” “database” {rn instance = “INSTANCE_ID”rn database = “DATABASE_NAME”rn policy_data = data.google_iam_policy.fgac_users.policy_datarn}’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2f386b50>)])]
Note that you could also specify the fully qualified database role name in a conditional IAM policy binding as follows:
code_block[StructValue([(u’code’, u’condition {rn title = “inventory_viewer membership”rn description = “Membership to inventory_viewer database role”rn expression = “resource.type == ‘spanner.googleapis.com/DatabaseRole’ && resource.name == ‘projects/PROJECT_NAME/instances/INSTANCE_ID/databases/DATABASE_NAME/databaseRoles/inventory_viewer'”rn}’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2f386a90>)])]
code_block[StructValue([(u’code’, u’gcloud spanner databases execute-sql DATABASE_NAME \rn–instance=INSTANCE_ID \rn–sql=”SELECT * FROM Product” \rn–database-role=product_admin’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2f3868d0>)])]
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 APIon your project and you must have the cloudasset.assets.searchAllIamPolicies IAM permission.
code_block[StructValue([(u’code’, u”gcloud asset search-all-iam-policies \rn –scope=projects/PROJECT_NAME \rn –query=’roles=roles/spanner.fineGrainedAccessUser AND resource=//spanner.googleapis.com/projects/PROJECT_NAME/instances/INSTANCE_ID/databases/DATABASE_NAME’ \rn –flatten=policy.bindings[].members[] \rn –format=’table(policy.bindings.members)’rnrnExample OutputrnrnMEMBERSrnuser:
[email protected]:
[email protected]:
[email protected]:
[email protected]:
[email protected]”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2ea78710>)])]
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.
code_block[StructValue([(u’code’, u’gcloud asset search-all-iam-policies \rn –scope=projects/PROJECT_NAME \rn –query=’roles=roles/spanner.databaseRoleUser AND policy:”resource.name” AND policy:/inventory_manager AND resource=//spanner.googleapis.com/projects/PROJECT_NAME/instances/INSTANCE_NAME/databases/DATABASE_NAME’ \rn –flatten=policy.bindings[].members[] \rn –format=’table(policy.bindings.members)’rnrnExample OutputrnrnMEMBERSrnuser:
[email protected]’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2f3a2790>)])]
View IAM conditions for configuring access of an IAM principal
To troubleshoot access issues for user ‘user:[email protected]’ 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.
code_block[StructValue([(u’code’, u’gcloud asset search-all-iam-policies \rn –scope=projects/PROJECT_NAME \rn –query=’roles=roles/spanner.databaseRoleUser AND policy:resource.name AND policy:”user:
[email protected]” AND resource=//spanner.googleapis.com/projects/PROJECT_NAME/instances/INSTANCE_NAME/databases/DATABASE_NAME’ \rn –flatten=policy.bindings[] \rn –format=’table(policy.bindings.condition.expression)’rnrnExample OutputrnrnEXPRESSIONrnresource.type == “spanner.googleapis.com/DatabaseRole” && resource.name == “projects/PROJECT_NAME/instances/INSTANCE_NAME/databases/DATABASE_NAME’/databaseRoles/spanner_sys_reader”‘), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2f3a20d0>)])]
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.
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.
code_block[StructValue([(u’code’, u”gcloud spanner databases execute-sql DATABASE_NAME \rn–instance=INSTANCE_NAME \rn–sql=’SELECT ROLE_NAME FROM INFORMATION_SCHEMA.ROLE_GRANTEES’ \rn–database-role=sevenrnrnExample OutputrnrnROLE_NAMErnthreernone”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2f3a29d0>)])]
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.
code_block[StructValue([(u’code’, u’gcloud spanner databases execute-sql DATABASE_NAME \rn–instance=INSTANCE_NAME \rn–sql=’SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_NAME=”Users”‘ \rn–database-role=eightrnrnExample OutputrnrnTABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE GRANTEErn Users INSERT sevenrn Users SELECT threern Users UPDATE seven’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3eda2f3ad250>)])]
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 ofIAM 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 controlor see the documentation.
Cloud Blog
