How to Configure Snowflake Read Only User

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a snowflake read only user by building a custom role first. Then you grant usage and select permissions on your data objects to that role. Teams often assign default roles to save time during setup. Still, broad permissions let users change tables or drop important schemas by mistake. A strict read role stops unwanted writes and protects your production data. How to set up a snowflake read only user role Snowflake uses role-based permissions to manage every user session. Users neve

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 a snowflake read only user by building a custom role first. Then you grant usage and select permissions on your data objects to that role. Teams often assign default roles to save time during setup. Still, broad permissions let users change tables or drop important schemas by mistake. A strict read role stops unwanted writes and protects your production data.

How to set up a snowflake read only user role

Snowflake uses role-based permissions to manage every user session. Users never get permissions on tables or views directly. Instead, an admin creates a custom role for reading data. The admin then assigns that role to the user account.

This role needs four distinct permission layers to run queries. First, the role needs usage permissions on a compute warehouse. Without a warehouse, the user cannot run any query at all. Second, the role needs usage permissions on the parent database.

Third, the role needs usage permissions on each schema inside that database. Fourth, the role needs select permissions on the tables and views. Granting select on existing tables only covers objects that exist right now. If your team builds new tables tomorrow, the read role will miss them. Admins fix this by adding future grants on the schema. Future grants give the role select permissions on new tables as they appear.

Why role permissions alone leave data gaps

Native roles are great for stopping accidental table drops or row updates. They define who can log in and which tables they can read. Still, static role permissions do not inspect individual queries in flight. Once a user has select permissions, they can read every row and column by default.

Production tables often hold emails, phone numbers, card numbers, and national IDs. A basic read role returns all of those raw values in query results. Admins can build native masking rules inside the data platform to hide fields. Yet managing custom rules across dozens of databases takes heavy work. Teams also struggle when they run Postgres or MySQL alongside their cloud warehouse. Each database engine uses a different syntax for roles and masking rules.

Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static roles also stay active all day long. An analyst or support engineer keeps standing permissions even when they do not need them. If someone steals their login key, the attacker can dump full tables right away. Security teams need a way to check queries and mask sensitive fields in real time.

Managing read sessions and sensitive columns at scale

Growing engineering teams need more than static database roles. They need a clear audit trail that ties every query to a real person. Shared read accounts hide who ran a slow or risky select query. When everyone logs in with their own identity, audit logs stay clean.

Teams also benefit when they remove standing permissions from daily work. If an engineer needs to check a production bug, they can request a short session. Once their work finishes, the permission expires on its own. Dynamic data masking can also rewrite matched fields like emails or card numbers in results. This approach keeps sensitive customer data safe during routine checks and reporting.

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

Why can my snowflake read only user see the database but not the tables?

The user role likely has database usage permissions but lacks schema usage or table select permissions. You must grant permissions at the database, schema, and table levels for queries to work.

How do I let a read role query new tables without manual updates?

You can apply future grants at the database or schema level for that custom role. Future grants give the role select permissions on any new tables your team creates later.

Does a read role need warehouse permissions to run select queries?

Yes, every query requires active compute resources to run inside the platform. You must grant warehouse usage permissions to the role so the user can run select statements.

The query runs. The emails stay hidden.