How to Configure Spanner Column Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up spanner column level security by creating fine-grained database roles. Then you grant those roles read or write rights on specific table columns. Most teams start by giving users broad project roles to move fast. Broad roles let every user read every column in the database by default. This exposes sensitive fields like emails, phone numbers, and card numbers during routine checks. How spanner column level security works Google Cloud Spanner separates cloud identity roles from inter

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up spanner column level security by creating fine-grained database roles. Then you grant those roles read or write rights on specific table columns. Most teams start by giving users broad project roles to move fast. Broad roles let every user read every column in the database by default. This exposes sensitive fields like emails, phone numbers, and card numbers during routine checks.

How spanner column level security works

Google Cloud Spanner separates cloud identity roles from internal database roles. First, an admin sets up fine-grained access control for the database instance. Next, the admin defines custom roles inside the database schema. Each database role lists the exact columns a user can read or update. The admin then links those internal database roles to cloud identity groups.

When a user logs in, they must pick a specific database role. The database engine checks that role before it runs any query. If a query asks for a restricted column, the engine blocks the request. Users must name only the allowed columns in their select statements. Running a wildcard select on a restricted table will fail right away.

Teams can also use authorized views to limit what columns a user sees. An admin creates a view that includes only safe columns from a base table. Then the admin grants the user role read rights on that view alone. The user queries the view without needing rights on the underlying table.

What native column rules solve and what they miss

Native column privileges stop unauthorized users from reading restricted fields at the database layer. This setup works well when roles are static and queries never change. An analyst role can read order totals while skipping card numbers and national IDs. Automated apps can update status columns without holding rights to alter user emails.

Still, native database roles create friction during live incidents and daily debugging. Engineers often need temporary read rights to check a broken row in production. Updating cloud identity bindings and database grants for one ticket takes too much time. To save time, teams often grant broad fallback roles to on-call engineers.

Column-Level Encryption + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Once a user holds a permitted role, the database trusts every query they send. Native column rules decide who can read a column, not how results return. If an engineer needs a row for debugging, they get the raw column values. The database does not rewrite emails, phone numbers, or card numbers in query results. It also cannot ask a peer to approve a single sensitive query in flight.

Managing column privileges across large teams

As schemas grow, keeping column grants in sync across tables becomes hard work. Every new column needs an update to existing database roles and view definitions. If a developer adds a column for national IDs, broad table grants expose it. Teams must audit their schema grants often to catch these open columns.

Shared service accounts make this problem worse for engineering teams. When multiple apps or scripts share one role, they all share the same column rights. Splitting those shared accounts into narrow roles requires changing query code across many apps. Teams need a simpler way to inspect queries and mask fields at runtime.

Check database and API queries on the wire

Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.

Frequently asked questions

Can I use wildcard select queries with Spanner column privileges?

No, wildcard queries fail if your role lacks read rights on every single column. You must write out the exact allowed column names in your query.

Admins bind cloud principals to specific database roles using cloud identity policies. That policy lets the user assume the database role when opening a session.

Do column privileges mask sensitive fields like card numbers in results?

No, native column privileges only allow or block the entire query. If your role has read rights, the database returns the raw column values.

The query runs. The emails stay hidden.