Outbound Share granting Iceberg Tables leads to GRANT + REVOKE

[kokorin]

Describe the bug
We grant Iceberg Tables to outbound share, SnowDDL suggest first granting of Iceberg Tables and then immediately after revoking them:

GRANT SELECT ON ICEBERG TABLE <database>.<schema>.<iceberg_table> TO SHARE <share_name>;
REVOKE SELECT ON TABLE <database>.<schema>.<iceberg_table> FROM SHARE <share_name>;

Iceberg Tables already granted, but on every plan we see above suggested statements.

Expected behavior
Only grant is suggested if not already granted

GRANT SELECT ON ICEBERG TABLE <database>.<schema>.<iceberg_table> TO SHARE <share_name>;

Attach YAML config (if applicable)

<share_name>:
  accounts:
    - <account>
  grants:
    DATABASE:USAGE:
      - <database>
    SCHEMA:USAGE:
      - <database>.<schema>
    ICEBERG_TABLE:SELECT:
      - <database>.<schema>.<iceberg_table>

[littleK0i]

If you add property "singular_for_grant": "TABLE" here: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/blueprint/object_type.py#L130C1-L136

Does it help?

[kokorin]

Yes, it fixed the problem.

It lead me to an idea that Snowflake treats GRANT SELECT ON ICEBERG TABLE and GRANT SELECT ON TABLE the same.
So I changed

# ...
    ICEBERG_TABLE:SELECT:
      - <database>.<schema>.<iceberg_table>

to

# ...
    TABLE:SELECT:
      - <database>.<schema>.<iceberg_table>

and it also worked (without singular_for_grant).

Of course this approach should be considered as a workaround.

[littleK0i]

The biggest problem here is the general Snowflake inconsistency.

In a perfect world full object type name should be used everywhere. Multiple object types should not share the same name in any context.

But in Snowflake one object type can be named one way for "CREATE" statement, but use another name for "GRANT" or for "REFERENCE". This can also change without notice, especially for future grants.

I'll check the current state of ICEBERG TABLE and try to include broad fixes in the next update.

[kokorin]

I checked GRANT SELECT ON ICEBERG TABLE and GRANT SELECT ON TABLE the result is the same: a table is granted.
Also REVOKE SELECT ON TABLE works on Iceberg Tables.

[littleK0i]

The question is.. what Snowflake returns in SHOW GRANTS, SHOW FUTURE GRANTS and system views.

[kokorin]

Would you like me to check?

[littleK0i]

@kokorin , ok, I had a chance to take a look.

It seems, Snowflake becomes very odd here. It makes distinction between TABLE and ICEBERG_TABLE in SHOW GRANTS TO ROLE, SHOW FUTURE GRANTS IN SCHEMA.

But when dealing with SHARES specifically, Snowflake silently converts ICEBERG_TABLE into TABLE. Looks like a bug to me. Or maybe they want any types of tables to look like a "native" table when sharing.

I see two possible options:

1. Document this gimmick and ask users to use TABLE object type when dealing with grants to shares. This is how Snowflake displays it, so we basically follow their own metadata output.
2. We can add one more optional property to object types: "singular_for_share_grant".

I like the first approach a little bit more, since it means "less parameters" and being "closer to how things actually are". But the second approach is fine too, as long as it is covered with tests.