What you will be able to do
- Pick the privilege that permits a given operation on a given object type, including the difference between internal and external stages
- Tell object privileges apart from global (account) privileges, and explain what OWNERSHIP and MANAGE GRANTS allow
- Use SHOW ROLES, SHOW USERS and SHOW GRANTS to inspect custom roles and users, and predict what a role without enough privilege sees in the output
Key concept
Privileges depend on the object type — In Snowflake, a privilege is granted to a role on a specific object, and the same privilege name can mean different things on different object types. Before you grant anything, check which privileges that object type actually supports.
1.How privileges attach to roles and objects
Snowflake access control rests on one chain. Privileges are granted to roles, and roles are granted to users. A user can run an operation only if one of their roles holds the privilege that operation requires on that object. A privilege name is not universal, though. USAGE on a schema and USAGE on a function permit different things, and many privileges exist on only a few object types. If you are not sure what an object supports, Snowflake provides a definitive answer: call the EXPLAIN_GRANTABLE_PRIVILEGES function on it.
Privileges come in two scopes. Object privileges are granted on a particular database, schema, table, stage and so on. Global (account) privileges are granted ON ACCOUNT and cover account-wide actions such as CREATE ROLE, CREATE USER, CREATE DATABASE, MONITOR USAGE or CANCEL QUERY. Some global privileges must be granted by ACCOUNTADMIN; CREATE DATABASE and CREATE WAREHOUSE are two of them.
Two privileges control everything else. OWNERSHIP is granted automatically to the role that created an object. It lets that role drop and alter the object and grant or revoke access to it. The owning role, or any role with MANAGE GRANTS, can move it to another role with GRANT OWNERSHIP. MANAGE GRANTS is a global privilege that lets a role grant or revoke privileges on any object as though it owned that object. SECURITYADMIN holds it at account level by default. It can also be granted on a single database or schema, which limits it to that container and the objects inside it.
Checkpoint 1 of 7· Check yourself
A custom role GRANT_ADMIN must be able to grant and revoke SELECT on tables it does not own, anywhere in the account. Which grant achieves this?
MANAGE GRANTS lets a role grant or revoke privileges on any object as if it were the owner, so ownership never has to change hands. MONITOR ROLE only lets a role view roles, and CREATE ROLE only lets it create them.
“Grants the ability to grant or revoke privileges on any object as if the invoking role were the owner of the object.”Source: docs.snowflake.com
Checkpoint 2 of 7· Exam question
The security team suspects password-guessing attacks over the last four months. An administrator must report failed sign-in attempts per user and client IP address for that whole period. Which source is the MOST appropriate?
Correct answer: C — The SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY view, filtered on is_success = 'NO', which keeps 365 days of login events
- A. The table function only returns events from the last seven days, so most of the four-month window would be missing.
- B. SESSIONS records sessions that were created. Failed authentication attempts never create a session, so they do not appear there.
- C. ACCOUNT_USAGE.LOGIN_HISTORY retains a year of login attempts including client_ip, user_name and error_message, so a filter on is_success = 'NO' covers the full four months.
- D. USERS holds one row per user with summary attributes. It has no per-attempt rows, so it cannot show failed attempts by IP address.
Sources1
2.Which privilege for which object
Exam questions on this topic usually come down to matching an operation to an object type. The table below lists the privileges you will meet most often in scenarios. Look at how they split. Data privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE) sit on tables and table-like objects. USAGE sits on containers and on reusable objects such as file formats, sequences, procedures and functions. On a stage, the privilege depends on where the stage lives: external stages take USAGE, while internal stages take READ and WRITE.
| Privilege | Object types (selection) | What it allows |
|---|---|---|
| SELECT | Table, Iceberg table, external table, view, materialized view, semantic view, stream | Run a SELECT statement on the table or view |
| INSERT / UPDATE / DELETE | Table, hybrid table, Iceberg table | Run the matching DML command |
| TRUNCATE | Table, hybrid table, event table, Iceberg table | Run TRUNCATE TABLE |
| REFERENCES | Table, view, materialized view, semantic view | View the structure of the object but not its data |
| USAGE | Database, Schema, Warehouse, Stage (external only), File Format, Sequence, Stored Procedure, User-Defined Function, Integration | Run USE <object> and SHOW <objects> on it |
| READ / WRITE | Stage (internal only) | READ: GET, LIST, COPY INTO <table>. WRITE: PUT, REMOVE, COPY INTO <location> |
| MONITOR | User, Warehouse, Database, Schema, Task, Resource Monitor | See details within the object |
| CREATE <object_type> | Global, Database, Schema | Create that kind of object in the account or container |
Some audit-related abilities are global privileges, not object privileges. MONITOR USER lets a role view users and all their properties. MONITOR ROLE lets it view the roles in the account. MONITOR USAGE covers account-level usage and history for databases and warehouses. If you need an auditor role that can see users and roles but cannot change anything, these are the grants to use. MANAGE GRANTS would also let the role change who has access.
Checkpoint 3 of 7· Match them up
Match each privilege to what it grants
Tap a term, then the definition that fits it.
REFERENCES exposes structure only. WRITE covers the stage operations that write files. MONITOR USER is an account-wide read-only privilege on users. USAGE allows USE and SHOW.
“Grants the ability to view the structure of an object (but not the data).”Source: docs.snowflake.com
Checkpoint 4 of 7· Exam question
After a reorganization, a security reviewer must list every user and every role that has been granted the custom role FINANCE_ANALYST, so access can be removed from the right accounts. Which command returns this information?
Correct answer: D — SHOW GRANTS OF ROLE finance_analyst, which lists each user and role that the role has been granted to
- A. SHOW GRANTS TO ROLE lists what the role holds (privileges and roles granted to it), not who holds the role. It answers the opposite question.
- B. SHOW ROLES includes counts such as assigned_to_users, but only counts, not the names of the users or roles that were granted the role.
- C. SHOW USERS filters on user names, not role membership, and its output shows default_role rather than a complete list of granted roles.
- D. SHOW GRANTS OF ROLE returns the grantees of the role: every user and role to which it has been granted. That is the list the reviewer needs for revocation.
Sources1
3.Inspecting roles, users and grants with SHOW
Once custom roles and users exist, you inspect them with SHOW commands. SHOW ROLES lists the system-defined roles and the custom roles you can see. Its output includes is_default, is_current, is_inherited, assigned_to_users, granted_to_roles, granted_roles and owner. Seeing a role in this list does not let you use it. A role name on its own gives no extra access. What appears also depends on your current role: the command returns only objects on which that role has at least one privilege. A holder of MANAGE GRANTS sees every object in the account.
SHOW ROLES LIMIT 10 FROM 'my_role2';SHOW USERS is open to everyone, but it hides details. Any user can run it, and the name column is always filled. Every other column is returned only when the active role holds OWNERSHIP on that user or MANAGE GRANTS on the account. For any other role, those columns are NULL. With the right privilege, the output is a useful audit snapshot. Look at disabled, default_role, default_secondary_roles, last_success_login, has_password, has_rsa_public_key, has_mfa, has_pat, type and owner.
SHOW GRANTS lists privileges explicitly granted to roles, users and shares, and its syntax differs from the other SHOW commands. On its own, SHOW GRANTS is equivalent to SHOW GRANTS TO USER current_user, which lists the current user's roles. Other forms include SHOW GRANTS ON ACCOUNT, SHOW GRANTS ON <object>, SHOW GRANTS TO ROLE, SHOW GRANTS TO USER and SHOW GRANTS OF ROLE. SHOW output column names are lowercase. To filter the output with SQL, use the pipe operator (->>) or RESULT_SCAN, and put the column names in double quotes.
SHOW GRANTS ON <object_type> <object_name> [ LIMIT <rows> ]Checkpoint 5 of 7· Check yourself
An analyst's active role has no OWNERSHIP on any user and no MANAGE GRANTS. What happens when they run SHOW USERS?
Any user can run SHOW USERS. The name column is always populated, and the remaining columns are NULL unless the role holds OWNERSHIP on the user or MANAGE GRANTS on the account.
“Any user can execute the SHOW USERS command. The output always includes the username in the name column.”Source: docs.snowflake.com
Checkpoint 6 of 7· Fill the gap
Which keyword makes this SHOW ROLES statement page through results after a given role name?
SHOW ROLES LIMIT 10 ? 'my_role2';LIMIT rows FROM 'name_string' acts as a cursor and returns rows that follow the first matching name. STARTS WITH and LIKE filter by name and cannot follow LIMIT.
Source: docs.snowflake.comCheckpoint 7 of 7· Exam question
A role named LOAD_OPS runs nightly batch jobs and must be able to suspend and resume the warehouse ETL_WH around each run and abort stuck queries on it. It must not be able to resize the warehouse or change its auto-suspend setting. Which grant is the MOST appropriate?
Correct answer: B — GRANT OPERATE ON WAREHOUSE etl_wh TO ROLE load_ops, which allows suspend, resume and aborting queries without changing properties
- A. MODIFY permits changing warehouse properties such as size and AUTO_SUSPEND, which is exactly what the role must not do. It also does not on its own cover starting, stopping or aborting queries.
- B. OPERATE lets a role change the warehouse state (start, stop, suspend, resume) and view and abort queries running on it. It does not allow altering size or auto-suspend, so it matches the requirement exactly.
- C. MONITOR is read-only visibility into queries and usage on the warehouse. It cannot suspend, resume or abort anything, so the batch job would still fail.
- D. USAGE only allows running queries on the warehouse (and triggering auto-resume). It does not permit an explicit ALTER WAREHOUSE ... SUSPEND or RESUME, nor aborting other sessions' queries.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A role that can list a role in SHOW ROLES can also use it.Why is that wrong?
Listing roles and using them are separate. Seeing a role's name grants nothing extra. Access still requires the role to be granted to you.
Covered in Inspecting roles, users and grants with SHOW
2.SHOW USERS needs a special privilege to run at all.Why is that wrong?
Anyone can run it. Without OWNERSHIP on the user or MANAGE GRANTS on the account, every column except name comes back NULL.
Covered in Inspecting roles, users and grants with SHOW
3.Only the current owner can transfer OWNERSHIP of an object.Why is that wrong?
A role with MANAGE GRANTS can also transfer ownership with GRANT OWNERSHIP.
Covered in How privileges attach to roles and objects
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“To obtain a definitive list of all possible privileges for one or more objects, call the EXPLAIN_GRANTABLE_PRIVILEGES function.”
↩︎ How privileges attach to roles and objects“OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object”
↩︎ How privileges attach to roles and objects“The SECURITYADMIN role holds account-level MANAGE GRANTS by default.”
↩︎ How privileges attach to roles and objects“Also grants the ability to execute a SHOW <objects> command on the object.”
↩︎ Which privilege for which object“Grants the ability to view users and all their properties in the account.”
↩︎ Which privilege for which object“The meaning of each privilege varies depending on the object type to which it is applied, and not all objects support all privileges”
↩︎ Key concept“transferred using the GRANT OWNERSHIP command to a different role by the owning role or any role with the MANAGE GRANTS privilege”
↩︎ Exam trap 3“Grants the ability to grant or revoke privileges on any object as if the invoking role were the owner of the object.”
↩︎ Checkpoint“Grants the ability to perform any operations that require reading from an internal stage (GET, LIST, COPY INTO <table>, etc.).”
↩︎ Prediction“Grants the ability to view the structure of an object (but not the data).”
↩︎ Checkpoint - 2.
“The command only returns objects for which the current user’s current role has been granted at least one access privilege.”
↩︎ Inspecting roles, users and grants with SHOW“The MANAGE GRANTS access privilege implicitly allows its holder to see every object in the account.”
↩︎ Inspecting roles, users and grants with SHOW“Knowing the names of roles does not allow any additional access.”
↩︎ Exam trap 1 - 3.
“Lists all access control privileges that have been explicitly granted to roles, users, and shares.”
↩︎ Inspecting roles, users and grants with SHOW
Also cited
“Otherwise, the other columns contain NULL.”
↩︎ Exam trap 2“Any user can execute the SHOW USERS command. The output always includes the username in the name column.”
↩︎ Checkpoint