Optionally deploy grants requiring ACCOUNTADMIN?

[josephniblo]

Hi @littleK0i,

Enjoying the new permissions model - this is allowing us some great flexibility and the ability to drop some of our custom logic.

In production, we're using an ACCOUNTADMIN-granted role (let's call it SNOWDDL_ADMIN) to deploy against the non-prefixed environment, but so that we don't have to grant it to every developer, we have another ROLE that we use to deploy against prefixed environments, which does not have ACCOUNTADMIN (let's call this role SNOWDDL_DEV).

With the new permissions config in 0.27, this means our SNOWDDL_ADMIN role can apply the account_grants on technical roles, but our SNOWDDL_DEV role can't. That's great actually and fits our intention to have reduced permissions on the developer SnowDDL role, but the problem is that I haven't found a way to only suggest the grants that require ACCOUNTADMIN when running in the prefixed environments (akin to eg. RESOURCE_MONTIORS which also (inexplicably) require specifically ACCOUNTADMIN). This will include MONITOR and EXECUTE TASK permissions for us, but looks it like there are quite a few.

Have I missed a way to exclude these when running without ACCOUNTADMIN?
Or how would you feel about another switch in the CLI?

Thanks!

[littleK0i]

Ok, it's a tricky subject. Let's see if we can unpack it.

SnowDDL currently relies on having MANAGE GRANTS privilege. It comes from SECURITYADMIN system role, which is granted to ACCOUNTADMIN.

Currently there are two modes in which SnowDDL can operate: "normal" mode and "SingleDB" mode. With "normal" mode it assumes that MANAGE GRANTS are present, and in "SingleDB" mode it does not manage any grants at all.

Adding any special rules around specific privileges requiring ACCOUNTADMIN or SECURITYADMIN is probably not very productive, since... it is really hard to tell which privileges can be granted without hardcoding it. And if we do the hardcode, it will break once Snowflake decices to change something.

---

What you can do instead:

1. Try granting EXECUTE TASK and MONITOR to SNOWDDL_DEV role and add WITH GRANT OPTION. SnowDDL dev should be able to grant specific privileges without having blanket MANAGE GRANTS.
2. Move prefixed schemas and dev environments to a separate account, which is highly recommended. On separate account you can probably give SECURITYADMIN to dev roles without any issues.

[josephniblo]

Thanks, looks like WITH GRANT OPTION works well for our purposes