ADA-C01 Question 56
Single answerImplement and manage future grants including restrictionsA Snowflake administrator is standardizing access for the ANALYST role in database FINANCE. The requirement is that ANALYST must automatically receive SELECT on all new tables created in any existing or future schema in FINANCE. However, one schema, FINANCE.PAYROLL, contains sensitive data and must not inherit this automatic access. The administrator wants the solution to minimize ongoing manual grant management. Which approach best meets these requirements?
- A
Grant future SELECT on tables in database FINANCE to ANALYST, and then revoke future SELECT on tables in schema FINANCE.PAYROLL from ANALYST.
- B
Grant future SELECT on tables in every schema in FINANCE except PAYROLL, and separately grant SELECT on all existing tables in those schemas to ANALYST.
- C
Grant future USAGE on database FINANCE to ANALYST, because future grants on the database automatically exclude restricted schemas such as PAYROLL.
- D
Grant SELECT on all existing and future tables in database FINANCE to ANALYST, and deny SELECT on schema FINANCE.PAYROLL using a negative grant.
Show answer and explanation
Correct answer: B
Explanation
The key restriction with future grants in Snowflake is that they are granted at the database or schema level and do not provide a native exclusion mechanism for a specific schema when a broader database-level future grant is used. If the goal is to automatically grant access across most schemas but exclude one sensitive schema, the practical design is to use schema-level future grants only on the permitted schemas. Because future grants affect only newly created objects, separate grants are required for existing tables. In Snowflake documentation, future grants are described for databases and schemas, and administrators should also remember that schema-level future grants take precedence over database-level future grants for the same object type. Best practice is to design grant boundaries around schemas when exceptions such as sensitive data domains are required.
- A. Incorrect.
Incorrect. In Snowflake, future grants defined at the database level apply to objects created in all schemas in that database. Also, schema-level future grants take precedence over database-level future grants for the same object type, but Snowflake does not support using a simple 'revoke future grant' on one schema as an exclusion mechanism to carve out an exception from a broader database-level future grant in the way this option suggests. A common misconception is to treat future grants like inherited ACLs with explicit deny or exclusion behavior.
- B. Correct.
Correct. To exclude one schema from automatic access while still minimizing manual work, the administrator should avoid a database-level future grant and instead grant future SELECT on tables at the schema level only for the allowed schemas. Because future grants only apply to objects created after the grant is established, the administrator must also grant SELECT on existing tables separately. This approach is operationally realistic and aligns with Snowflake's grant model when exceptions are needed for a specific schema.
- C. Incorrect.
Incorrect. USAGE on a database does not grant SELECT on tables, and future grants on a database do not automatically exclude any schema. Users need appropriate privileges such as USAGE on the database and schema plus SELECT on tables, but granting future USAGE alone does not satisfy the requirement. This distractor reflects confusion between container access and object-level data access.
- D. Incorrect.
Incorrect. Snowflake does not support negative grants or explicit deny semantics for object privileges. You can grant and revoke privileges, but you cannot grant a privilege broadly and then deny it for one schema using a deny rule. This option represents a common misunderstanding from other platforms with deny-based access control models.