Creattion order of External Access Integration and Owner Role

[kokorin]

Describe the bug
We have:

1. A database with database_owner ruleset
2. Network Rules and Secrets created in a schema in the DB
3. External Access Integration using Network Rules and Secrets from the Schema
4. Database is configured with owner_integration_usage referencing the Integration

SnowDDL tries to grant usage on External Access Integration to DB Owner before creating the Integration itself.

First snowddl apply created Integrations successfully, so second snowddl apply completes successfully.

Expected behavior

Usage of External Access Integration is granted after it's creation.

[littleK0i]

Try skipping point 4. owner_integration_usage is for integrations created outside of SnowDDL.

In general, you have two options:

1. Let SnowDDL manage everything: functions, external access integration, secrets and network rules. In this case no need for extra grants.
2. You want users to be able to manage functions with external access directly. In this case create external access integration, secrets and network rules outside of SnowDDL. Use owner_integration_usage, if it is enough. If not enough, create a global role with correct grants and use owner_global_roles.

Also, you should think twice about using this feature. It is clearly half-baked and incomplete. External access integration is an account-level object, but it depends on schema-level objects, which makes no sense. While function is "waiting" for external network to respons, you pay for 8 cores server with huge premium on top. Most likely, you can do the same thing with lambda using 0.25x of core and pay tiny fraction of the price.

[kokorin]

Try skipping point 4. owner_integration_usage is for integrations created outside of SnowDDL.

Sorry for not stating this clear: the problem was with External Access Integration managed by SnowDDL. Here is redacted log:

2025-06-03 04:16:49.667 - INFO - Resolved ROLE [DEV_<PROJECT_NAME>__READ__D_ROLE]: GRANT
2025-06-03 04:17:01.998 - INFO - Resolved ROLE [DEV_<PROJECT_NAME>__WRITE__D_ROLE]: GRANT
...
(
    message   =>  SQL compilation error:
Integration 'UAT_<PROJECT_NAME>_<INTEGRATION_NAME>' does not exist or not authorized.
    errno     =>  2003
    sqlstate  =>  02000
    sfqid     =>  01bcc641-0106-a853-0001-3e9218e6c862
    sql       =>  GRANT USAGE ON INTEGRATION "UAT_<PROJECT_NAME>_<INTEGRATION_NAME>" TO ROLE "UAT_<PROJECT_NAME>__OWNER__D_ROLE"
)
2025-06-03 04:17:34.986 - WARNING - Resolved ROLE [<PROJECT_NAME>__OWNER__D_ROLE]: ERROR
(
    message   =>  SQL compilation error:
Integration '<PROJECT_NAME>_<INTEGRATION_NAME>' does not exist or not authorized.
    errno     =>  2003
    sqlstate  =>  02000
    sfqid     =>  01bcc641-0106-a9d0-0001-3e9218e6d47a
    sql       =>  GRANT USAGE ON INTEGRATION "<PROJECT_NAME>_<INTEGRATION_NAME>" TO ROLE "<PROJECT_NAME>__OWNER__D_ROLE"
)
2025-06-03 04:17:35.028 - WARNING - Resolved ROLE [DEV_<PROJECT_NAME>__OWNER__D_ROLE]: ERROR
(
    message   =>  SQL compilation error:
Integration 'DEV_<PROJECT_NAME>_<INTEGRATION_NAME>' does not exist or not authorized.
    errno     =>  2003
    sqlstate  =>  02000
    sfqid     =>  01bcc641-0106-ac08-0001-3e9218e6be42
    sql       =>  GRANT USAGE ON INTEGRATION "DEV_<PROJECT_NAME>_<INTEGRATION_NAME>" TO ROLE "DEV_<PROJECT_NAME>__OWNER__D_ROLE"
)
2025-06-03 04:17:42.400 - INFO - Resolved EXTERNAL_ACCESS_INTEGRATION [<PROJECT_NAME>_<INTEGRATION_NAME>]: CREATE
2025-06-03 04:17:42.400 - INFO - Resolved EXTERNAL_ACCESS_INTEGRATION [DEV_<PROJECT_NAME>_<INTEGRATION_NAME>]: CREATE
2025-06-03 04:17:42.452 - INFO - Resolved EXTERNAL_ACCESS_INTEGRATION [UAT_<PROJECT_NAME>_<INTEGRATION_NAME>]: CREATE

External access integration is an account-level object, but it depends on schema-level objects, which makes no sense.

Yes, it bothers me too.

Most likely, you can do the same thing with lambda using 0.25x of core and pay tiny fraction of the price.

Do you mean that the Lambda should connect to Snowflake and send data to integration service? If so, in our case it's probably even better to do so from our CI runner (we use to schedule DBT).

[littleK0i]

If all objects are managed by SnowDDL, it should be safe to skip point 4 entirely.

SnowDDL should be able to create functions referering to external access integration, since it has ownership of all relevant objects. Database owner role does not need access to integration. At least it was true a few months ago when this feature was originally implemented.

---

Use lambda or anything else to perform external connectivity tasks. Put results into S3 and let Snowflake consume it.

By using external connectivity inside of Snowflake you MASSIVELY overpay in most cases.

[kokorin]

Sorry, I didn't get your first reply. Now it's clear.

We create functions outside of SnowDDL, that's why we specified owner_integration_usage.

Thank you for advice, we will check our costs.

Please, feel free to convert this issue to a discussion or even close it, if it's not relevant.

[littleK0i]

What about the second option?

Create global role with custom grants. Give it to database owner role.

The universal "get out of jail" card.

[kokorin]

Our config is stable and we don't use environment feature of SnowDDL. So it's easier to click "apply" button twice (after adding new integration), rather then deal with global roles

[littleK0i]

I'll close it for now, since fixing the problem would require major rework of role resolvers. Specifically, we will have to completely separate creation of roles, future grants, normal grants and add delayed grants on top to cover command ordering issues.

It might be possible to do at some point, especially if Snowflake keeps introducing more inconsistent features in future. But I do not think it is worth doing specifically for SnowDDL managed EXTERNAL ACCESS INTEGRATION in combination with externally managed functions.

It is still possible to handle this use case by creating EXTERNAL ACCESS INTEGRATION manually prior to SnowDDL runs, similar to how other integration objects are implemented right now.