ADA-C01 Question 30
Select 21.3 Given a scenario, create and manage access control.A Snowflake administrator is asked to onboard a new analytics team. The team must be able to query all existing and future tables and views in schema PROD_DB.SALES, but they must not be able to modify objects or grant access to anyone else. The administrator wants to follow least-privilege and use role-based access control with minimal ongoing maintenance. Which actions should the administrator take?
- A
Create a custom role, grant USAGE on database PROD_DB and schema PROD_DB.SALES to the role, then grant SELECT on all existing tables and views in the schema and grant SELECT on future tables and views in the schema to the role.
- B
Grant OWNERSHIP on schema PROD_DB.SALES to the analytics team role so team members automatically inherit access to all current and future objects in the schema.
- C
Grant the analytics team role to SYSADMIN so the team inherits the privileges already available to SYSADMIN on PROD_DB.SALES.
- D
Grant the custom role to the analytics users after assigning the required database, schema, and object privileges to that role.
- E
Grant imported privileges on database PROD_DB to the custom role so it can query tables and views in PROD_DB.SALES.
Show answer and explanation
Correct answers: A, D
Explanation
The correct approach is to create a custom read-only role and grant only the minimum required privileges: USAGE on the database and schema, plus SELECT on existing and future tables and views in PROD_DB.SALES. Then grant that role to the analytics users. This satisfies the requirement for least privilege and minimizes administration by using future grants for newly created objects. Snowflake documentation on access control and future grants emphasizes assigning privileges to roles rather than users directly, using USAGE on parent containers, and using future grants to automate access for new objects. OWNERSHIP is too permissive, IMPORTED PRIVILEGES is only relevant to shared databases, and using SYSADMIN for end-user access violates best-practice separation of duties.
- A. Correct.
Correct. To query objects in Snowflake, a role needs USAGE on the parent database and schema, plus SELECT on the tables and views. To minimize maintenance, the administrator should grant SELECT on all existing tables and views and also grant SELECT on future tables and future views in the schema. This matches Snowflake best practice for RBAC and future grants when a team needs read-only access to both current and newly created objects.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and would allow the role to manage the schema and transfer privileges, which violates the requirement that the team must not modify objects or grant access to others. Although ownership can affect access management, it is far too broad for a read-only analytics team.
- C. Incorrect.
Incorrect. Granting the analytics team role to SYSADMIN reverses the intended role hierarchy and is not how least-privilege access should be implemented. In Snowflake, lower-level functional roles are typically granted to higher-level administrative roles when needed, not the other way around to provide end-user access. Also, this would expose excessive privileges.
- D. Correct.
Correct. After creating and configuring a custom read-only role, the administrator must grant that role to the analytics users (or to another business role assigned to those users). This is the standard RBAC pattern in Snowflake: assign object privileges to roles, then assign roles to users.
- E. Incorrect.
Incorrect. IMPORTED PRIVILEGES applies to shared databases created from data shares, not to standard databases owned within the account like PROD_DB in this scenario. It does not replace the need for USAGE and SELECT grants on regular database objects.