Procedures, Tasks and Permissions

[josephniblo]

Hi @littleK0i,

We're trying to implement our first Procedure and Tasks using SnowDDL and hitting a few permissions issues.

We'd like to be able to define a Procedure, probably execute as caller, and call it from a Task, both managed in SnowDDL, but we've found:

• The Task owner (ie. the schema owner) doesn't have EXECUTE TASK on ACCOUNT, so the Task cannot be run (once we resume the Task manually)
• The Task owner doesn't have USAGE on the warehouse specified on the Task
• In our case, the ROLE executing the Procedure (either the Task owner if Execute as caller, or the Procedure owner if execute as owner) is going to need global_roles (in particular, it will make some GRANTs), but I don't think this is currently supported

Is there a reason not to automatically grant the Schema Owner EXECUTE TASK and/or the necessary Warehouse grant?

And wondered what would you recommend to support the the permissions we need in the Procedure - whether we should handle it in programmatic config, or if we could extend the schema owner config to allow global_roles, or something else?

Thanks!

(fyi @leopasta-enable)

[littleK0i]

I am currently working on better permission system, which allows setting custom grants for owner / write / read roles. It will be possible to specify and assign "permission models" on per-database and per-schema levels. I expect it to be released in the next few days.

In the meantime you may run procedures as "caller" and temporarily remove this line: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/resolver/schema_role.py#L73

It will cause tasks to be created with "SnowDDL admin role" ownership, which naturally has access to all objects it created, including warehouses.

---

Alternatively, I highly suggest to avoid tasks and use proper orchestration tool instead, like Dagster. You'll have much better visibility and control over workflows, including workflows outside of Snowflake.

[josephniblo]

Awesome - thanks for the quick response and we'll keep an eye out for that new functionality.

"Proper" orchestration is probably overkill for what we're doing so we'll stick to TASKs in this case. Just wondering if they have worked for others though? With the missing warehouse usage and EXECUTE TASK permissions, it seems at the moment like our SnowDDL tasks won't be able to execute without a bit of a work around.

In case it's helpful for others (while we wait for the new custom grants functionality), we were able to work around by adding the grants we need to the schema owner role blueprint in the __custom programmatic config, using a global role to grant EXECUTE TASK.

[littleK0i]

If you go with __custom route, you may use owner_additional_grants parameter in SchemaBlueprint and specify any extra grants directly in code.

In theory, global role is not needed.

[littleK0i]

@josephniblo , please check latest SnowDDL update v0.27+

Permission model and ownership privileges were substantially extended. Now it is possible to grant account-level privileges and warehouse usage to schema owner roles.

https://docs.snowddl.com/basic/yaml-configs/schema

For example:

owner_warehouse_usage:
  - my_task_wh
 
owner_account_grants:
  - EXECUTE TASK

This should entirely fix the problem.

Make sure to test it in DEV environment, since default permission model was slightly changed. For example, now you may have to specify owner_integration_usage for STAGE objects relying on STORAGE INTEGRATIONs.

Full list of potentially breaking changes: https://docs.snowddl.com/breaking-changes-log/0.27.0-may-2024

Now you may also customize permission models for schema roles, if necessary: https://docs.snowddl.com/basic/yaml-configs/permission-model