ARA-C01 Question 357
Single answerStored proceduresA data engineering team uses a Snowflake stored procedure to perform nightly maintenance across multiple schemas: it archives old rows, truncates staging tables, and rebuilds summary tables. The procedure is owned by a centralized ADMIN role, but it is executed by application roles that do not have direct privileges on the target tables. Recently, the team also added logic to dynamically construct and execute SQL statements based on a validated list of table names. The architect must ensure the procedure can complete its maintenance tasks without granting broad object privileges to the application roles, while also minimizing security risk from the dynamic SQL logic. Which approach best meets these requirements?
- A
Create the stored procedure to execute with owner's rights, and use bind variables or strictly validated identifiers when constructing dynamic SQL.
- B
Create the stored procedure to execute with caller's rights, and grant USAGE on the procedure to the application roles so the procedure can inherit the owner's table privileges.
- C
Convert the stored procedure into a user-defined function (UDF) because UDFs can run DDL and DML more securely than stored procedures.
- D
Keep the procedure as caller's rights, but grant the application roles OWNERSHIP on all target tables so dynamic SQL can run successfully.
- E
Create the stored procedure with owner's rights, and allow free-form table names from application input because owner execution mode prevents SQL injection.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests two key Snowflake stored procedure concepts: execution rights and safe dynamic SQL. For delegated administrative workflows, an owner's rights stored procedure is commonly used so callers can perform controlled operations without receiving direct privileges on every underlying object. This supports least privilege and simplifies access management. However, using dynamic SQL inside a stored procedure introduces security considerations. Best practice is to parameterize values with bind variables where supported and tightly validate any dynamic object names against approved lists or metadata before execution. Caller's rights would require the invoking role to hold all needed object privileges, which does not satisfy the scenario. UDFs are not appropriate for procedural maintenance workflows involving DDL/DML orchestration. These principles align with Snowflake documentation on stored procedure caller vs owner rights and dynamic SQL security best practices.
- A. Correct.
Correct. In Snowflake, stored procedures can run with owner's rights or caller's rights. If application roles should execute maintenance tasks without being granted direct privileges on underlying tables, owner's rights is the appropriate model because the procedure executes with the privileges of the procedure owner. However, when using dynamic SQL, owner execution mode does not remove injection risk. The secure approach is to use bind variables where possible and strictly validate object names or use safe identifier handling patterns for dynamic object references.
- B. Incorrect.
Incorrect. Caller's rights means the procedure runs with the privileges of the invoking role, not the owner. Granting USAGE on the procedure allows invocation, but it does not cause the caller to inherit the owner's table privileges. The application roles would still need direct permissions on the tables involved, which violates the requirement to avoid broad grants.
- C. Incorrect.
Incorrect. Snowflake UDFs are not a replacement for stored procedures in this scenario. UDFs are intended for returning a value and have important restrictions; they are not designed to orchestrate procedural maintenance tasks involving DDL and DML like truncating tables and rebuilding summary tables. This option reflects a common misconception that UDFs can be substituted for procedural administrative logic.
- D. Incorrect.
Incorrect. Granting OWNERSHIP on all target tables to application roles is the opposite of least privilege and creates significant governance and security problems. It would also allow those roles to transfer ownership and manage objects broadly, which is far more access than needed just to run a maintenance routine.
- E. Incorrect.
Incorrect. Owner's rights controls privilege context, not input safety. Dynamic SQL remains vulnerable to misuse if free-form identifiers or SQL fragments are accepted from application input. Even when a procedure runs with owner's rights, the code should constrain allowed object names and avoid concatenating untrusted input directly into executable SQL.