Snowflake Access Control Practice: Roles and Future Grants
Unlimited Snowflake access control practice. Each problem gives a role hierarchy and the grants that were run: decide if the user can query the table, and if not, tap the grant that is missing. A wrong answer names the slip, such as reading a future grant as covering a table that already existed.
Role hierarchy
Statements, in order
Which grant is missing?
Names are invented. In every problem secondary roles are off (DEFAULT_SECONDARY_ROLES is empty), a warehouse is already in use, and SYSADMIN owns the database and its schemas and runs the CREATE TABLE. Managed access schemas, database roles and grants to PUBLIC are left out.
Snowflake exam prep
Practice for the SnowPro Core, Advanced Administrator and Advanced Architect exams. Start with the free real exam questions, then get the full pack.
Access control FAQ
How do privileges pass through a Snowflake role hierarchy?
Granting a role to another role makes it a child of that role. The privileges of a role are inherited by every role above it in the hierarchy, never by the roles below it. In the documentation example, Role 3 is granted to Role 2 and Role 2 to Role 1, so Role 2 inherits the privileges of Role 3, and Role 1 has the privileges of all three.
What does a role need to run SELECT on a table?
The SELECT privilege on the table, plus USAGE or another privilege on the database and on the schema that contain it. A SELECT grant on the table alone is not enough. The role that owns the table has every privilege on it, SELECT included.
Does a future grant cover tables that already exist?
No. A future grant gives its privileges to new objects of that type created after the grant, not to existing objects. A table that existed before the future grant needs a grant on the table itself.
Which wins, a database future grant or a schema future grant?
When future grants are defined on the same object type for a database and for a schema in it, the schema grants take precedence and the database grants are ignored, even when they name different roles. In the documentation example, SELECT on future tables in database d1 goes to r1 and INSERT and DELETE on future tables in schema d1.s1 go to r2: a new table in d1.s1 gives r1 nothing.
Who owns a new table?
The role used to create it, which is the primary role of the session. OWNERSHIP can also be given by a future grant, at most one per object type. Only one role holds OWNERSHIP at a time, and in a regular schema the owner role has all privileges on the object.
Do secondary roles change the answer?
They can. DEFAULT_SECONDARY_ROLES is ALL unless it is set to empty, and then every role granted to the user can authorize a SELECT, not only the primary role. In this drill secondary roles are off in every problem, so only the primary role and the roles below it count.
Does SYSADMIN owning the database help a custom role?
No. SYSADMIN sits above the custom roles, and privileges flow up the hierarchy, not down. Snowflake recommends granting the top custom role to SYSADMIN so that system administrators can manage the objects the custom roles own.
What does this drill leave out?
Managed access schemas, database roles, grants to PUBLIC or directly to users, ALL TABLES grants, WITH GRANT OPTION, ownership transfers of existing objects, revokes and views. Every problem has six grants and one CREATE TABLE, and the only privileges granted on a database or schema are USAGE. This is the tool’s own scope.
Sources
- Snowflake documentation, Overview of Access Control: unless allowed by a grant, access is denied; the privileges of a role are inherited by every role above it (the Role 1, 2 and 3 example); querying a table needs SELECT on the table and USAGE or another privilege on the database and schema; each object is owned by the role used to create it, and the owner has all privileges on it in a regular schema; for existing objects, privileges must be granted on the objects themselves.
- Snowflake documentation, GRANT <privileges> and Future grants on database or schema objects: future grants cover new objects rather than existing ones; schema future grants take precedence over database future grants for the same object type, for one role or different roles (the d1 and d1.s1 example); OWNERSHIP can be a future grant, at most one per object type.
- Snowflake documentation, Access control privileges: USAGE on a database and schema, SELECT and OWNERSHIP on a table, and operating on an object in a schema needs a privilege on the parent database and schema.
- Snowflake documentation, GRANT ROLE, USE SECONDARY ROLES and CREATE USER (DEFAULT_SECONDARY_ROLES, default ALL; empty means no roles).
- Checked 21 November 2026. The tool’s own approach: problems are invented; the cases listed under "What does this drill leave out?" never appear. An independent re-solver (a replay of the printed SQL statements in order) agreed with the drill on 8,000 generated problems.
Checked 21 November 2026 against the Snowflake documentation.
- DrillDataverse security role access practiceTap whether and how a user can read a record from business units, security role access levels, teams and shares.
- DrillServiceNow ACL practiceDecide if a read or write is granted and tap the ACL that decides it.
- DrillSalesforce record access practiceTap the access a user gets from OWD, roles, sharing rules, teams and View All.