SnowPro Advanced: Security Engineer Question 7
Single answerAutomate RBAC management programmaticallyA security engineering team wants to automate RBAC provisioning for new application schemas in Snowflake. Their CI/CD pipeline must create a functional role for each application, grant the minimum required privileges on the schema and its future objects, and assign that role to a higher-level business role. The team also wants the automation to be rerunnable without causing excessive failures when objects already exist. Which approach best meets these requirements?
- A
Use a role with CREATE ROLE and MANAGE GRANTS privileges to run idempotent SQL such as CREATE ROLE IF NOT EXISTS, GRANT USAGE ON DATABASE, GRANT USAGE ON SCHEMA, and GRANT SELECT/INSERT/UPDATE/DELETE ON FUTURE TABLES IN SCHEMA as needed, then GRANT ROLE to the parent role.
- B
Use a role with only OWNERSHIP on the target schema to create the application role and grant all required privileges, because schema ownership implicitly allows account-level role creation and role hierarchy changes.
- C
Use a role with SECURITYADMIN privileges to transfer OWNERSHIP of the schema to the application role, because ownership automatically includes all future object privileges and eliminates the need for future grants.
- D
Use a role with USERADMIN privileges to create users and assign the application role directly to each service user, because direct user-role assignment is the preferred way to automate RBAC and avoids managing role hierarchies.
Show answer and explanation
Correct answer: A
Explanation
The best answer is the one that uses idempotent SQL and a role with the necessary account-level and grant-management privileges to automate RBAC safely and repeatably. In Snowflake, creating roles is separate from owning a schema or its objects. For automation, a common pattern is to create application-specific access roles, grant least-privilege access to the database, schema, and future objects, and then attach those access roles to higher-level functional or business roles. This supports separation of duties, reuse, and simpler user provisioning. Using future grants is important when the requirement includes ongoing access to newly created tables or other objects within a schema. Relevant Snowflake guidance includes documentation on access control, role hierarchies, CREATE ROLE, GRANT privileges on schemas and objects, and future grants. The key practical points are: account roles must be created with account-level privilege, future grants must be explicitly defined, and hierarchical role assignment is preferred over direct user-to-privilege management.
- A. Correct.
Correct. This approach aligns with Snowflake RBAC automation best practices. Role creation is an account-level action, so the automation needs the ability to create roles, and grant management requires sufficient privilege such as MANAGE GRANTS or ownership where applicable. Using CREATE ROLE IF NOT EXISTS makes the pipeline rerunnable. Granting USAGE on the database and schema, then object privileges on future objects in the schema, is the correct way to ensure ongoing access without re-granting on each newly created table. Granting the application role to a parent business role preserves a scalable role hierarchy instead of assigning privileges directly to users.
- B. Incorrect.
Incorrect. OWNERSHIP on a schema does not permit creating account roles. CREATE ROLE is an account-level privilege, typically associated with USERADMIN or a custom role that has been granted the necessary privilege. Schema ownership can help with grants on schema objects, but it does not allow creating roles or managing the account-level role hierarchy by itself. This option confuses object ownership with account-level administrative capabilities.
- C. Incorrect.
Incorrect. Transferring OWNERSHIP to the application role is usually not the least-privilege approach and does not automatically grant future object privileges in the way described. Future grants are configured explicitly with GRANT ... ON FUTURE objects. Ownership is powerful and often broader than necessary for an application access role. SECURITYADMIN can manage grants and roles, but using ownership transfer here is not the best design for controlled, repeatable RBAC automation.
- D. Incorrect.
Incorrect. USERADMIN is primarily responsible for users and roles, but direct assignment of access roles to users is not the preferred scalable pattern. Snowflake best practice is to assign object privileges to access roles, then grant those roles to functional or business roles, and only then assign higher-level roles to users. This option also fails to address granting minimum privileges on the schema and future objects.