Migrating grants on existing objects into newly created SnowDDL roles

[acaruso7]

I'm working through a large Terraform --> SnowDDL migration, so I have a large number of existing databases, schemas, and tables to migrate. My general approach has been:

1. Grant ownership on database to SNOWDDL_ADMIN role (and copy current grants)
2. Grant ownership on schemas to SNOWDDL_ADMIN role (and copy current grants)
3. Run snowddl plan, update config, and get to parity

^^ My understanding is that the first 2 steps are both necessary in order for SnowDDL to resolve the objects (or maybe the SnowDDL role just needs USAGE on an object to resolve it? I'm not sure). At this point schema tech roles are generated, and they contain pretty much most of the privileges I would ever need for FUTURE objects. However, I'm noticing that the schema roles don't grant anything on existing objects, only future. Is manually adding these grants the only way to successfully migrate?

Similarly, it makes sense that all of the objects in a schema should be owned by the schema role, even if they were created before the role itself. So I've been doing grant ownership on all tables in schema RAW.MYSCHEMA to role RAW__MYSCHEMA__OWNER_S_ROLE copy current grants; -- not sure if this is the right approach?

Example

Suppose I have a new business role TRANSFORMER__B_ROLE, which needs SELECT access on all tables in RAW.MYSCHEMA. Once I add RAW.MYSCHEMA to the SnowDDL config, it'll generate a schema role RAW__MYSCHEMA__READ_S_ROLE, which has SELECT ON FUTURE TABLES. But this won't grant SELECT on any of the existing tables. Is my only option manually running grant SELECT on all tables in schema RAW.MYSCHEMA to role RAW__MYSCHEMA__READ_S_ROLE?

[acaruso7]

My understanding is that the first 2 steps are both necessary in order for SnowDDL to resolve the objects (or maybe the SnowDDL role just needs USAGE on an object to resolve it? I'm not sure)

Answering my own question here . . . Just came across this in the docs: https://docs.snowddl.com/guides/other/admin

In my case I like the fact that SnowDDL can't resolve everything right off the bat, because it makes migration a lot easier (I can change ownership of objects a little bit at a time and migrate them into SnowDDL config). However it would be nice to be able to manage account level objects at some point so perhaps I will eventually move towards that approach

[littleK0i]

There is a CLI option called --refresh-future-grants. It applies permissions on existing objects according to future grants.

SnowDDL/snowddl/resolver/abc_role_resolver.py

Lines 257 to 270 in 1c278b4

	def refresh_future_grant(self, role_name, grant: FutureGrant):
	# Bulk grant on objects of type PIPE to ROLE is restricted (by Snowflake)
	if grant.on == ObjectType.PIPE:
	return
	self.engine.execute_safe_ddl(
	"GRANT {privilege:r} ON ALL {on_plural:r} IN SCHEMA {name:i} TO ROLE {role_name:i}",
	{
	"privilege": grant.privilege,
	"on_plural": grant.on.plural,
	"name": grant.name,
	"role_name": role_name,
	},
	)

Normally this operation is not applied, since it is quite expensive on large number of objects, and we assume that all grants are processed automatically via future grants.

Please make sure your schemas have "MANAGED ACCESS". The role hierarchy was designed with this option in mind.

Also, it might be a good idea to create a separate account, apply SnowDDL config and compare results with original account. Snowflake has a lot of new features and a lot of features in public / private preview. SnowDDL may not be aware of some new forms of grants, like grants to "DATABASE ROLES", for example.

[acaruso7]

Exactly what I needed, thanks!

Please make sure your schemas have "MANAGED ACCESS". The role hierarchy was designed with this option in mind.

Could you elaborate on this? What problems might I run into if I don't use managed access, or if I convert existing schemas to managed access?

[littleK0i]

https://docs.snowflake.com/en/user-guide/security-access-control-configure#creating-managed-access-schemas

Without MANAGED ACCESS object owners will be able to grant access on individual objects.

WIth MANAGED ACCESS only schema owner can grant access. And schema owner is SnowDDL role, which is not assigned to normal users.