Custom SQL in Programmatic config

[treysmithdev]

Is there a preferred approach to executing additional SQL within the programmatic config? I was wondering if there was a way to leverage the connection, potentially the engine, to execute some non-blueprint driven statements.

My initial use case is to revoke SNOWDDL_ADMIN role from ENV_ADMIN role so that it no longer inherits SYSADMIN / SECURITYADMIN privileges, but maintains full access on the DB's it owns.

[littleK0i]

Currently there is no easy way to run additional SQL with programmatic config specifically, since config is being built and validated before we open the connection.

A lot of "hooks" are possible by overloading BaseApp class and adding custom logic before / after standard methods. In fact, SingleDBApp is an extension of BaseApp: https://github.com/littleK0i/SnowDDL/tree/master/snowddl/app

Naturally, cloning repo and changing code directly is always an option. Not friendly for updates, but possible for complex use cases.

---

About revoking SNOWDDL_ADMIN role from ENV_ADMIN. I assume, it is about automatically created env prefixed role, e.g. PREFIX__SNOWDDL_ADMIN.

In theory, this revoke does not achieve anything meaningful. The main purpose of env prefixed role is to prevent saturation of main admin role. When you have 50+ environments all attached to a single admin role, SHOW GRANTS may become a bit slow.

Prefixed role should not be used by any user other than SNOWDDL_ADMIN, so you should not experience any differences regarding security.

Another problem is ... if you revoke SNOWDDL_ADMIN from prefixed role and run SnowDDL again, it will most likely fail on attempts to access any global objects referenced in config, e.g. global roles, integrations, resource monitors.