Blueprint for DATABASE_ROLE object and its usage in Outbound Share

[kokorin]

In our SF Account we use DBT to create tables we want to share with other SF account through Outbound Share.

These tables are actively developed and column names and types can change from time to time. We created Snowflake's Database Role. This role is granted with privileges on tables we want to share.

Unfortunately, it's not possible to provide Snowflake's Database Role to Outbound Share Blueprint. And because table schemas can change we can't use Outbound Share: SnowDDL suggests re-creating tables if something is changed.

I remember we discussed this topic earlier, but I can't find the thread. To what I remember, the problem was in possible confusion between SnowDDL's DB Role and Snowflake's DB Role.

One of the solutions I can think about is creating programmatic config for Snowflake's Database Roles. This config can include a reference to Outbound Share like this:

# <DB>/database_role.yaml
- name: <name>
  granted_to_shares:
    - <share_1>
    - <share_2>

If we add Outbound Share configuration (share_1 and share_2) it should be possible to achieve our goal. But such solution would contradict to SnowDDL Outbound Share usage.

[littleK0i]

Ok, I see two different problems mentioned here.

(1) Creating database roles and granting database roles to shares
It seems, Snowflake has settled on this model of providing granular access to objects in share.

How we could possibly implement it in SnowDDL:

# <DB>/database_role.yaml
<database_role_name>:
  grants: <similar_to_share_grants>

And this role can be granted to share using normal grants from here: https://docs.snowddl.com/basic/yaml-configs/share-outbound

test_share:
  accounts: <list_of_accounts>
  grants:
    DATABASE_ROLE:USAGE:
      - <database_role_1>
      - <database_role_2>

Object type DATABASE_ROLE is available, so it should work just fine.

(2) Granting objects created by DBT to shares
This is a major problem, unless Snowflake changed something in their approach.

As far as I know, it is not possible to use future grants with shares. It includes direct grants to share and indirect grants via database roles. Please see usage notes here: https://docs.snowflake.com/en/sql-reference/sql/grant-database-role-share#usage-notes

If future grants are not supported, it means DBT does not work well with shares, since it wants to re-create objects. Even if we add an explicit GRANT ... TO SHARE at the end of execution of each DBT model, it would still cause brief intervals when old object was deleted from share, but new object is not granted yet. Consumers will experience random errors from time to time.

---

In my view, the best possible solution is to ditch DBT. At this point it creates more problem than it solves. Shares is just a small cherry on top.

Please consider the following approach:

1. Keep object definitions in SnowDDL. Define structure of tables, define structure of views. Everything should be created during SnowDDL run.
2. If you have orchestration software, use basic SQL files for transformations. Use TRUNCATE TABLE or INSERT OVERWRITE instead of CREATE OR REPLACE TABLE. With this approach tables are not going to be dropped every time, so interruptions would be minimised.
3. If you do not have orhectration software, TASKS may suffice.

Alternatively, we may add some sort of "sandbox" flag to share grants, which is going to permit extra grants on objects not explicitly defined in config. DBT should be able to run post-hook with grants. Probability of interruption will be high, so not good for production usage. But it might be a "good enough" solution for DEV environment. Especially if shares objects are queries manually by humans rather than scripts.

[littleK0i]

Currently SnowDDL will revoke extra grants assigned directly to SnowDDL-managed SHARE.

SnowDDL will NOT revoke grants assigned to SHARES created manually.

SnowDDL will NOT revoke grants assigned to DATABASE ROLES, since SnowDDL currently does not manage DATABASE ROLES. But if I implement DATABASE ROLES and it appears in config, SnowDDL will revoke extra grants by default. Unless we add special logic to prevent it.

[littleK0i]

@kokorin , do you absolutely have to use DATABASE ROLES in this case?

What if I adjust existing share grants mechanism to allow extra grants from "sandbox" objects? This can be done relatively quickly.

I am not sure how long it may take to implement DATABASE ROLES, since there are many other considerations related to this object type.

[littleK0i]

@kokorin , interestingly enough, implementation of GrantPattern object in one of the previous versions allowed relatively clean implementation this time.

Since version 0.49.0, grants to shares will not be dropped as long as they match patterns in config.

For example:

test_share_3:
  grants:
    DATABASE:USAGE:
      - fivetran_db
    SCHEMA:USAGE:
      - fivetran_db.test_schema
    TABLE:SELECT:
      - fivetran_db.test_schema.*

This will initially grant SELECT on all tables known to SnowDDL in this schema. But if more tables with grants appear later on, these will not be dropped as long as they match pattern fivetran_db.test_schema.*.

Now the question is, should we implement DATABASE ROLES now or do it a bit later.

[kokorin]

What if I adjust existing share grants mechanism to allow extra grants from "sandbox" objects? This can be done relatively quickly.

Will it work if sandbox objects i.e. tables haven't been created yet? Or do you mean that it will also not revoke grants of SF DB Roles to Shares? In both cases, it can solve our issue partially.

I don't know if such sandbox feature fits your vision of SnowDDL, so I can't insist on having it implemented.

But if more tables with grants appear later on, these will not be dropped as long as they match pattern fivetran_db.test_schema.*

I suppose that on the next apply all tables from fivetran_db.test_schema will be granted to share.

Now the question is, should we implement DATABASE ROLES now or do it a bit later.

I would like to help with that. We could have a call to discuss implementation in more details. I think a pair of SF DB Role Blueprint and Resolver should be enough for our use case: if a resolver is added in the list between Database and Outbound Share Resolvers. But again, I can't insist.

[littleK0i]

@kokorin , sure, let's have a chat. Please accept LinkedIn invite.

[littleK0i]

To be honest, it is really weird Snowflake does not provide future grants for shares.

Naturally, it would be the best and the most straightforward solution.

[littleK0i]

@kokorin , how about the following implementation?

(1) Add database_roles directly to SHARE config. For example:

test_share:
  accounts:
    - SFSALESSHARED.SFC_SAMPLES_AWS_EU_WEST_2
  grants:
    DATABASE:USAGE:
      - test_db
    SCHEMA:USAGE:
      - test_db.test_schema
    TABLE:SELECT:
      - test_db.test_schema.*
    FUNCTION:USAGE:
      - test_db.test_schema.test_secure_udf(varchar)

  comment: Test share

  database_roles:
    <database_role_name>:
      grants:
        DATABASE:USAGE:
          - test_db
        SCHEMA:USAGE:
          - test_db.test_schema
        TABLE:SELECT:
          - test_db.test_schema.specific_table

(2) Internally implement blueprint and resolver for OutboundShareDatabaseRole. It will be executed before OutboundShare.

(3) Grant logic will be the same both for shares and for database roles. If you use wildcard pattern (e.g. test_db.test_schema.*), SnowDDL will not remove externally created grants matching this pattern.

---

With this approach we logically attach database roles to outbound shares only. If ever need to implement database roles for anything else in future, we'll come back to this and re-design. But currently I do not see why it might be useful for anything else.

No future grants for database roles, only normal grants.

No name modifications for database roles, since these roles are public-facing objects. No suffies, no prefixes.

[kokorin]

This sounds interesting, my main concern is if grants on tables not specified in TABLE:SELECT will be revoked on apply.

(3) Grant logic will be the same both for shares and for database roles. If you use wildcard pattern (e.g. test_db.test_schema.*), SnowDDL will not remove externally created grants matching this pattern.

And in this case, will SnowDDL grant select on all test_db.test_schema.* tables on apply?

No name modifications for database roles, since these roles are public-facing objects. No suffies, no prefixes.

Outbound Share is Account-level object, what if 2 databases have the same DB Role name? Consider DB_1.DATA_SHARING and DB_2.DATA_SHARING roles.

I think OB Share should reference DB Roles by <DB_NAME>.<ROLE_NAME>. What if SnowDDL doesn't revoke select privileges on tables if a table is in a sandbox Schema? It can be in-sync with SnowDDL behavior when tables in sandbox schemas are not dropped if not configured.

The main problem is that DB Role must be created even in sandbox DB to be granted to OB Share. But tables in sandbox DB can be created at will. So we can't list tables granted to DB Role in sandbox DB. And on top of that it's possible to have sandbox Schemas in non-sandbox DB.

[littleK0i]

And in this case, will SnowDDL grant select on all test_db.test_schema.* tables on apply?

Currently it won't add grants for unkown objects, but won't drop existing grants. If we want GRANT ON ALL TABLES, it would require a separate parameter or a CLI option, similar to --refresh-future-grants.

Outbound Share is Account-level object, what if 2 databases have the same DB Role name? Consider DB_1.DATA_SHARING and DB_2.DATA_SHARING roles.

It might be worth throwing validation error in this case. In my view, we should have either multiple shares pointing to the same database or one shares with multiple database roles. Multiple shares with multiple possibly intersecting database roles seems a bit complicated. Especially if you have very large number of shares with such relationships.

[kokorin]

It might be worth throwing validation error in this case. In my view, we should have either multiple shares pointing to the same database or one shares with multiple database roles. Multiple shares with multiple possibly intersecting database roles seems a bit complicated. Especially if you have very large number of shares with such relationships.

Sorry, but the problem I see is technical: SF allows DB roles with the same name in different DBs, so SnowDDL should allow it too.

I would suggest the following configuration for Shares:

# outbound_share.yaml
test_share:
  accounts: [...]
  grants:
    ...

  database_roles:
    - <database_name>.<database_role_name>:

With database roles defined in separate YAML

# <database_name>/database_role.yaml
<database_role_name>:
  grants:
    SCHEMA:USAGE:
      - <schema_name>
    TABLE:SELECT:
      - <schema_name>.<table_name>

If a DB is a sandbox (or a schema is a sandbox), then Schema-level grants should not be revoked on apply if an object is not defined in YAML SnowDDL config.

IMHO, DB Roles should not be defined in OB Share YAML because they are separate object, As well as tables are defined in separate YAML files.

[littleK0i]

Yes, this config format makes sense in general.

I am a bit worried about people eventually using database roles for something outside of outbound sharing. Features requests may pile up, starting with future grants.

The idea of putting database roles in outbound share config is to explicitly prevent this from happening.

Let me think about it a bit longer.