ADA-C01 Question 38
Single answer1.4 Given a scenario, fine-tune access controls.A Snowflake administrator is asked to tighten access to a sensitive database named FINANCE_PROD. Analysts in the FINANCE_ANALYST role must be able to query only approved secure views in schema FINANCE_PROD.REPORTING. They must not be able to query base tables in schema FINANCE_PROD.CORE, and they should not be able to discover object definitions beyond what is required to use the approved views. The approved views already exist and are owned by a custom role. Which action should the administrator take to meet these requirements with the least privilege model?
- A
Grant USAGE on database FINANCE_PROD and schema REPORTING, and grant SELECT only on the approved secure views in REPORTING to FINANCE_ANALYST. Do not grant privileges on schema CORE or its base tables.
- B
Grant imported privileges on database FINANCE_PROD to FINANCE_ANALYST so the role can use the secure views without access to the underlying objects.
- C
Grant SELECT on all tables in FINANCE_PROD.CORE to FINANCE_ANALYST, then use masking policies to prevent the analysts from seeing sensitive columns.
- D
Grant USAGE on database FINANCE_PROD, USAGE on schemas REPORTING and CORE, and grant SELECT on the approved secure views. The lack of SELECT on CORE tables will prevent access, while USAGE on CORE is required for view resolution.
Show answer and explanation
Correct answer: A
Explanation
The least-privilege approach is to grant only the minimum privileges required to use the approved views: USAGE on the containing database and schema, and SELECT on the specific secure views. Snowflake access control is object-based and supports granting privileges on a view without granting access to the underlying tables to the querying role. This is a common pattern for restricting consumers to curated data access paths. Secure views are especially useful when exposing sensitive logic or limiting metadata exposure because they provide stronger privacy characteristics than standard views. Best practice is to avoid granting privileges on underlying schemas or tables unless there is a clear business need. Relevant Snowflake documentation includes the sections on access control privileges, secure views, and the required privileges for querying database objects.
- A. Correct.
Correct. To query a view, a role needs USAGE on the database and schema containing the view, plus SELECT on the view itself. Snowflake allows querying a view without granting access to the underlying base tables, assuming the querying role has the necessary privileges on the view and container objects. By avoiding grants on the CORE schema and base tables, the administrator follows least privilege and reduces object discoverability.
- B. Incorrect.
Incorrect. IMPORTED PRIVILEGES applies to shared databases and certain system-defined databases, not to a regular local database such as FINANCE_PROD. It is not the mechanism for allowing access to secure views in a database owned within the same account.
- C. Incorrect.
Incorrect. Granting SELECT on all base tables in CORE directly violates the requirement that analysts must not be able to query base tables. Masking policies protect column values, but they do not prevent broad table access or metadata visibility to the same extent as restricting object privileges.
- D. Incorrect.
Incorrect. USAGE on CORE is not required for a consumer role to query a view in REPORTING. Granting USAGE on CORE would unnecessarily expose the existence of that schema and increase discoverability of sensitive objects, which conflicts with the stated requirement to limit visibility beyond what is needed.