Summary
After a dbt-coves blue-green run, every inherited grant on the production
database is gone. Grants held directly on the database and on its schemas are
restored correctly, so the failure is partial and silent: the run reports
success, the database and its schemas stay reachable, and reads fail.
In practice this means a reader role that had SELECT on all tables and views
in the database — granted once via an inherited grant — can still see the
database and its schemas after the swap, but can no longer select from anything
in it. Because the role retains USAGE but loses SELECT, Snowflake omits the
objects from SHOW/INFORMATION_SCHEMA entirely for that role, so it presents
to users as "the tables and views disappeared" rather than as a privilege error.
Nothing in the run's output indicates it happened.
Root cause
CloneDB.clone_database_grants rebuilds database-level grants on the staging
database by reading them off production and re-issuing them:
grants_sql_stg_1 = f"""show grants on database {blue_database}"""
dict_cursor.execute(grants_sql_stg_1)
grants = dict_cursor.fetchall()
for grant in grants:
grant_sql = (
f"GRANT {grant['privilege']} ON {grant['granted_on']} {green_database} "
f"TO ROLE {grant['grantee_name']};"
)SHOW GRANTS ON DATABASE <db> returns only the privileges held on the database
object itself — OWNERSHIP and USAGE. It does not return inherited
grants on the tables, views and dynamic tables inside the database. Those are
visible only from the grantee side, via SHOW GRANTS TO ROLE <role>, where they
appear as granted_on = TABLE|VIEW|DYNAMIC_TABLE with is_inherited = true
and inherited_from = DATABASE.
Since the copy loop cannot see them, it cannot re-issue them. The staging
database is built without them, and after
alter database <prod> swap with <staging> the production name points at an
object that never had them. The original grants belonged to the old database
object and are dropped along with it.
clone_database_schemas has the same blind spot at schema level, and neither
method queries SHOW FUTURE GRANTS, so future grants are lost the same way.
Reproduction
-- given a reader role holding an inherited read grant on a database
grant usage on database my_db to role reader_role;
grant usage on inherited schemas in database my_db to role reader_role;
grant select on inherited tables in database my_db to role reader_role;
grant select on inherited views in database my_db to role reader_role;
Confirm the grants are in place, and note that they are invisible to the query
the copy loop relies on:
show grants to role reader_role; -- inherited SELECT rows present
show grants on database my_db; -- only OWNERSHIP + USAGE; no SELECT rows
Then run dbt-coves blue-green against my_db and re-check:
show grants to role reader_role; -- inherited SELECT rows gone
USAGE on the database and on each schema is intact; the SELECT grants are
not. The run exits 0.
Notes
Observed on dbt-coves 1.12.1; clone_database_grants on main is identical
at the time of writing.
Inherited grants require FEATURE_RBAC_INHERITED_GRANTS. They are an
attractive pairing with blue-green precisely because they are durable single
records that survive object creation inside the database, which is what makes
losing them on swap surprising.
Summary
After a
dbt-coves blue-greenrun, every inherited grant on the productiondatabase is gone. Grants held directly on the database and on its schemas are
restored correctly, so the failure is partial and silent: the run reports
success, the database and its schemas stay reachable, and reads fail.
In practice this means a reader role that had
SELECTon all tables and viewsin the database — granted once via an inherited grant — can still see the
database and its schemas after the swap, but can no longer select from anything
in it. Because the role retains
USAGEbut losesSELECT, Snowflake omits theobjects from
SHOW/INFORMATION_SCHEMAentirely for that role, so it presentsto users as "the tables and views disappeared" rather than as a privilege error.
Nothing in the run's output indicates it happened.
Root cause
CloneDB.clone_database_grantsrebuilds database-level grants on the stagingdatabase by reading them off production and re-issuing them:
SHOW GRANTS ON DATABASE <db>returns only the privileges held on the databaseobject itself —
OWNERSHIPandUSAGE. It does not return inheritedgrants on the tables, views and dynamic tables inside the database. Those are
visible only from the grantee side, via
SHOW GRANTS TO ROLE <role>, where theyappear as
granted_on = TABLE|VIEW|DYNAMIC_TABLEwithis_inherited = trueand
inherited_from = DATABASE.Since the copy loop cannot see them, it cannot re-issue them. The staging
database is built without them, and after
alter database <prod> swap with <staging>the production name points at anobject that never had them. The original grants belonged to the old database
object and are dropped along with it.
clone_database_schemashas the same blind spot at schema level, and neithermethod queries
SHOW FUTURE GRANTS, so future grants are lost the same way.Reproduction
Confirm the grants are in place, and note that they are invisible to the query
the copy loop relies on:
Then run
dbt-coves blue-greenagainstmy_dband re-check:show grants to role reader_role; -- inherited SELECT rows goneUSAGEon the database and on each schema is intact; theSELECTgrants arenot. The run exits 0.
Notes
Observed on
dbt-coves1.12.1;clone_database_grantsonmainis identicalat the time of writing.
Inherited grants require
FEATURE_RBAC_INHERITED_GRANTS. They are anattractive pairing with blue-green precisely because they are durable single
records that survive object creation inside the database, which is what makes
losing them on swap surprising.