Can I grant CREATE DATABASE to a role?

[patmaddox]

I am trying to define a role that has CREATE DATABASE privileges. I haven't figured out the syntax for it, and am not sure if it's even possible.

[littleK0i]

In theory, you may grant any custom privilege via global_roles parameter of business roles.

1. Manually create a role in Snowflake account, e.g. DATABASE_CREATOR.
2. Grant CREATE DATABASE or any other privilege to it.
3. Mention this role while configuring business_role.yaml, e.g.:

my_custom_role:
  global_roles:
    - database_creator

  comment: "Business role which can create databases"

It is possible, but it is probably not what you are looking for.

Normally it is not enough to create a database. You may also need to create schemas with managed access, set future grants, fix object ownership, drop schema PUBLIC, etc. etc.

If databases and schemas are configured via SnowDDL, all the complexity is managed by SnowDDL. If you try to create databases yourself using SQL, you lose most of benefits.

---

If you have dynamic list of databases provided by some external data source, it is possible to generate SnowDDL config programmatically: https://docs.snowddl.com/advanced/programmatic-config

Just add a Python module with something like this:

from snowddl import *


def handler(config: SnowDDLConfig):
    database_bp = DatabaseBlueprint(
        full_name=DatabaseIdent(config.env_prefix, "my_custom_database"),
        comment="My custom database",
    )

    schema_bp = SchemaBlueprint(
        full_name=SchemaIdent(config.env_prefix, "my_custom_database", "my_lovely_schema"),
        comment="My custom schema in custom database",
    )

    config.add_blueprint(database_bp)
    config.add_blueprint(schema_bp )

---

If these solutions are not good enough, please provide a bit more context about your specific business use case. We'll try to find an alternative.

Thank you.

[acaruso7]

I'm gathering that this is not possible using a tech role? I tried the following:

db_creator:
  grants:
    ACCOUNT:CREATE DATABASE:
      - <my account name>

But I get a keyerror: KeyError: 'ACCOUNT'

I need to be able to create databases on the fly without the help of SnowDDL, my entire workflow for blue-green deploys of data transforms depends on it. @littleK0i what do you recommend in this case? I'd rather not use the global_roles approach because it kindof defeats the purpose of IaC if it's not version controlled and part of the config. I suppose I can just version control the manual scripts necessary to create such roles, but there's still no resolution process to check for resource drift etc . . . Thoughts?

[acaruso7]

What if the databases are meant to be transient? For example, my workflow looks something like this:

1. Clone production ANALYTICS database to a temporary "candidate" "run" database
2. Run and test data transformations in the clone
3. If all transforms and tests succeed, swap clone with prod db, then cleanup leftover db

I could invoke SnowDDL as part of that workflow to create the candidate db rather than using templated SQL expressions, but that doesn't feel right because I would probably experience additional unexpected side effects (resource drift etc)

[littleK0i]

Hmm, this kind of workflow is partially supported.

If you use env_prefix to create a "candidate" database, you may add option --clone-table, and tables from "original" database will be "cloned" into "candidate" database. All permissions will be handled correctly and assigned to new "candidate" schema roles.

In theory, it might be possible to implement a custom resolver to do the "swapping" part, but... you will most definitely run into serious limitations of Snowflake around cloning.

Clones are not perfect, and the original database is probably open for modifications while transformations are running on "candidate". Also, as far as I know, it is not possible to swap datases and ownership roles in one transaction. So you'll have a temporary race condition when database and database objects have incorrect ownership.

Currently I do not see how it can be implemented without being inheretently flawed and fragile.

---

How expensive the transformations are? Maybe classic workflow would still make sense?

1. Create a candidate database with cloned objects.
2. Run test transformations. Probably use LIMIT or sampling to improve performance.
3. If tests were successful, deploy changes to production and run transformations on production database.
4. Drop candidate database.

[acaruso7]

If you use env_prefix to create a "candidate" database, you may add option --clone-table, and tables from "original" database will be "cloned" into "candidate" database. All permissions will be handled correctly and assigned to new "candidate" schema roles.

I can consider this, though again I don't really want to be invoking SnowDDL as part of this transformation workflow. If I understand it correctly, this is also going to result in the duplication of a bunch of other objects which I don't necessarily need or want as well. I imagine cleaning up those objects would be a challenge

the original database is probably open for modifications while transformations are running on "candidate"

Yeah, though we generally don't modify the original database directly. All changes go through this clone-and-swap process with trunk based development. So the workflow is:

1. [If in CI/CD context] Create a branch
2. Clone db
   • (we call this the "PR" database in a feature branch context)
3. Run and test transforms in clone
4. Merge branch
5. Repeat steps 2 & 3
6. Swap clone with original db and update grants
   • (We keep the swapped clone around as the "previous version of prod" in case we need to rollback)
7. Drop PR database

So in theory it's possible for the original db to be updated by another developer's deploy between steps 5 and 6, and we do see this race condition sometimes, but it's pretty rare and not too tough to recover from

it is not possible to swap datases and ownership roles in one transaction. So you'll have a temporary race condition when database and database objects have incorrect ownership.

This is true, looking at my source code, it seems we also have this race condition

How expensive the transformations are?

The transforms can be quite expensive, but the cost of a given build is highly variable because we dynamically rebuild components of the DAG based on what was modified. If a highly interconnected subgraph is modified, then the build will be slow and expensive, but if a relatively isolated subgraph is modified, builds are fast

The suggested classic workflow would be a pretty major change in our deployment strategy, I'm quite uncertain about the path forward here. I know the workflow I described is complex, though we've had it in place for a couple of years and it has worked very well. While I would like to use SnowDDL to manage all objects, unfortunately my clone-heavy workflow involving a lot of transient dbs and grants via templated SQL does not really jive well with this strategy

[littleK0i]

@acaruso7 , I've slept on the problem and came up with another solution.

How about using basic auto-generated VIEWs instead of swapping actual tables?

1. Create any number of cloned databases with data.
2. Create special database with VIEWs pointing to objects in "active" database. Grant end users access to database with VIEWs only.
3. Create any number of databases with "clones", "candidates", etc. Run any tests.
4. When the time comes to switch to another database, just update the definition of VIEWs. Never touch physical tables. Never rename or move anything.

With this approach you will be able to avoid most of problems, except the very final small race condition when some VIEWs are still pointing to old database and other VIEWs are pointing to new database. But it is going to be a split second and much easier to manage.

No problems with grants and ownership. No extra costs.

[acaruso7]

Interesting idea, I'll need some time to think through this one and see if it can work for me thanks for sharing!