TopolearnEvery query runs under a role, and a clean role design is what keeps a Snowflake account secure and manageable.
Snowflake uses role-based access control. Privileges (SELECT, INSERT, USAGE, CREATE TABLE, OPERATE...) are granted to roles, roles are granted to users, and roles can be granted to other roles, forming a hierarchy in which a parent inherits its children's privileges. A session has a current role (USE ROLE), plus optional secondary roles.
System roles to know:
ACCOUNTADMIN: top-level; use sparingly, never as a daily role.SECURITYADMINmanages grants,USERADMINcreates users and roles.SYSADMINcreates warehouses and databases; custom roles should be granted to it so admins can see what they create.PUBLIC: granted to everyone.
Reading a table needs a chain of privileges: USAGE on the database, USAGE on the schema, SELECT on the table, and USAGE on a warehouse to run the query. A missing link produces "does not exist or not authorized", which is deliberately vague.
Every object has an owner role (the role that created it) with full control. Future grants (GRANT SELECT ON FUTURE TABLES IN SCHEMA ...) apply to objects created later, which avoids re-granting after every deploy.
A common design is two layers: access roles holding privileges on objects (e.g. SALES_READ) and functional roles matching jobs (e.g. ANALYST) that are granted the access roles they need.
Example
USE ROLE USERADMIN;
CREATE ROLE sales_read;
CREATE ROLE analyst;
GRANT ROLE sales_read TO ROLE analyst; -- analyst inherits sales_read
GRANT ROLE analyst TO ROLE SYSADMIN; -- keep the hierarchy connected
GRANT ROLE analyst TO USER ana;
USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE shop TO ROLE sales_read;
GRANT USAGE ON SCHEMA shop.raw TO ROLE sales_read;
GRANT SELECT ON ALL TABLES IN SCHEMA shop.raw TO ROLE sales_read;
GRANT SELECT ON FUTURE TABLES IN SCHEMA shop.raw TO ROLE sales_read;
GRANT USAGE ON WAREHOUSE analytics_wh TO ROLE analyst;
SHOW GRANTS TO ROLE sales_read;Remember
- Privileges go to roles; roles go to users and other roles
- Reading needs USAGE on database, schema and warehouse plus SELECT
- Future grants cover tables created later
- Keep ACCOUNTADMIN for rare admin work
Watch out for
- Granting SELECT on a table but forgetting USAGE on its database or schema.