Snowflake Security, RBAC, and User Administration · Topic 4 of 16
Object Privileges, Granting Through Inheritance and SHOW GRANTS Commands
Apply the right privileges per object type, grant access to objects inside a database, and use SHOW commands to review roles, users and grants.
- 4 min
- Table access needs USAGE on database and schema plus SELECT on the table.
- ALL covers existing objects only; FUTURE covers later ones.
TO ROLEis what it holds;OF ROLEis who has it;ON objectis who can touch it.
What it is
Access to a Snowflake object is the sum of privileges on the object and on its containers. A table lives in a schema, and a schema lives in a database, so a role needs privileges at every level. An administrator must know which privilege belongs to which object type, and how to read who holds what.
The SHOW commands and the GRANTS_TO_* views answer the second question, from the point of view of a role, a user or an object.
How it works
Granting access to specific objects inside a database
To query one table a role needs three grants. Operating on an object also needs at least one privilege on the parent database and schema.
GRANT USAGE ON DATABASE sales TO ROLE sales_ro;
GRANT USAGE ON SCHEMA sales.public TO ROLE sales_ro;
GRANT SELECT ON TABLE sales.public.orders TO ROLE sales_ro;
To cover many objects at once, use the bulk form, and add a future grant for objects that do not exist yet.
GRANT SELECT ON ALL TABLES IN SCHEMA sales.public TO ROLE sales_ro;
GRANT SELECT ON FUTURE TABLES IN SCHEMA sales.public TO ROLE sales_ro;
ALL covers only objects that exist at that moment; future grants are covered on the next page. Roles inherit privileges, so give them to a low level access role and pass them up the hierarchy instead of repeating grants on each role.
Granting USAGE on a database does not let a role see tables in it. It only lets the role resolve the database name. Table access needs USAGE on the schema and SELECT on the table too.
A view works through its owner: users need SELECT on the view, and the view's owner needs privileges on the underlying tables at query time.
Privileges by object type
| Object | Common privileges |
|---|---|
| Account | MANAGE GRANTS, MONITOR USAGE, MONITOR EXECUTION, CREATE INTEGRATION, ATTACH POLICY, APPLY MASKING POLICY, USE AI FUNCTIONS |
| Database | USAGE, MODIFY, MONITOR, CREATE SCHEMA |
| Schema | USAGE, MODIFY, MONITOR, MANAGE GRANTS, CREATE TABLE, CREATE VIEW and other CREATE privileges |
| Table | SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, EVOLVE SCHEMA |
| Warehouse | USAGE, OPERATE, MODIFY, MONITOR |
| Internal stage | READ, WRITE |
| Other objects such as file formats, sequences, functions | USAGE |
| Any object | OWNERSHIP, ALL |
Points that scenarios use:
OWNERSHIPis held by one role per object and carries every privilege on it.ALL [PRIVILEGES]grants all privileges for that object type except OWNERSHIP.MANAGE GRANTSlets a role grant and revoke privileges on objects it does not own.CREATE INTEGRATIONandCREATE ACCOUNT(reader accounts) must be granted by ACCOUNTADMIN.- A database created from a share is opened to roles with
GRANT IMPORTED PRIVILEGES ON DATABASE, not with per object grants.
Custom security roles and users: the SHOW commands
| Question | Command |
|---|---|
| Which roles exist? | SHOW ROLES |
| Which users exist? | SHOW USERS |
| What does a role hold? | SHOW GRANTS TO ROLE r |
| What does a user hold? | SHOW GRANTS TO USER u |
| Who has this role? | SHOW GRANTS OF ROLE r |
| Who has access to this object? | SHOW GRANTS ON TABLE db.sch.t |
| What future grants exist? | SHOW FUTURE GRANTS IN SCHEMA db.sch |
Direction is the trap: TO shows privileges given to the principal, OF shows who received the role, ON shows grants on an object. SHOW output needs USAGE on the containing database and schema for object lists.
Monitoring granted privileges
SHOW commands are live. For a history, use the SNOWFLAKE.ACCOUNT_USAGE views GRANTS_TO_ROLES, GRANTS_TO_USERS and ROLES, which also show deleted grants and roles and can lag by up to a couple of hours. Reading them needs the SECURITY_VIEWER database role or an imported privilege on the SNOWFLAKE database.
SELECT grantee_name, privilege, granted_on, name
FROM snowflake.account_usage.grants_to_roles
WHERE deleted_on IS NULL AND privilege = 'OWNERSHIP';
Querying only the grants of one role misses privileges it inherits. Follow SHOW GRANTS TO ROLE down through each granted role to see the full set.
Common mistakes
Granting SELECT without USAGE on schema and database. The table stays invisible.
Reading the wrong SHOW direction. TO and OF answer opposite questions.
Expecting ALL TABLES to cover new tables. It does not; add a future grant.
Forgetting that ACCOUNT_USAGE lags. Use SHOW for the present state.
What to remember for the exam
- Table access needs USAGE on database and schema plus SELECT on the table.
- ALL covers existing objects only; FUTURE covers later ones.
TO ROLEis what it holds;OF ROLEis who has it;ON objectis who can touch it.GRANTS_TO_ROLESandGRANTS_TO_USERSgive history in ACCOUNT_USAGE.- MANAGE GRANTS lets a role grant on objects it does not own.
Check yourself
Which command lists the users and roles that hold role ANALYST?
SHOW GRANTS OF ROLE analyst.What does a role need to read one table?
USAGE on the database and schema and SELECT on the table.
Which ACCOUNT_USAGE view shows privileges granted to roles, including dropped grants?
GRANTS_TO_ROLES.
Topic 4 of 16 in Snowflake Security, RBAC, and User Administration
Test yourself on Snowflake Security, RBAC, and User Administration
The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.