Snowflake SQL Tutorial 160 - Revoking A Future Grant Doesn't Take Back What It Gave

Snowflake SQL · Episode 160

In this post we are going to cover future grants and ownership in Snowflake.

A future grant is a standing rule. It gives a role a privilege on tables that do not exist yet, so a table created tomorrow comes out with the privilege already on it. Ownership is the other half: who owns an object, and what travels with it when that moves.

We will start by writing one future grant and watching it fire, and then we will take the rule away again, which is the half that catches people.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back, refusals included.

Episode 160 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page
  1. Let's set up a schema and two roles
  2. Let's write a future grant on the schema
  3. Now let's make a table and see whether the rule fires
  4. Can we write that rule one level higher?
  5. Now let's take the rule away again
  6. And now a table created after the revoke
  7. Now let's move ownership, and see why there are two clauses
  8. COPY CURRENT GRANTS carries them across
  9. And REVOKE CURRENT GRANTS throws them away
  10. Can the old owner still read its own table?
  11. One last thing: hand ownership back before you drop a role

Let's set up a schema and two roles

We need an empty schema, so that every table in it is one we made on purpose, and two roles that hold nothing on any table.

Setup
-- Setup for this post.
-- An empty schema of our own, and two roles that hold nothing yet.
USE ROLE ACCOUNTADMIN;
USE DATABASE DEMO_DB;

CREATE OR REPLACE SCHEMA DEMO_DB.FUT_T160
    COMMENT = 'episode 160 - created and dropped by this file';

CREATE OR REPLACE ROLE ROWN_T160  COMMENT = 'episode 160 - the first role';
CREATE OR REPLACE ROLE ROWN2_T160 COMMENT = 'episode 160 - the second role';

-- Put your own user name in here so you can wear the roles yourself.
GRANT ROLE ROWN_T160  TO USER YOUR_USER;
GRANT ROLE ROWN2_T160 TO USER YOUR_USER;

-- The first role gets the chain it needs to run a SELECT, and nothing
-- on any table. Episode 159 is where that chain comes from.
GRANT USAGE ON DATABASE  DEMO_DB          TO ROLE ROWN_T160;
GRANT USAGE ON SCHEMA    DEMO_DB.FUT_T160 TO ROLE ROWN_T160;
GRANT USAGE ON WAREHOUSE COMPUTE_WH       TO ROLE ROWN_T160;

Let's check the first role before we start, so there is no doubt about where it began.

Query
SHOW GRANTS TO ROLE ROWN_T160;
Result3 rows · 8 columns · scroll →
created_onprivilegegranted_onnamegranted_tograntee_namegrant_optiongranted_by
2026-10-06 11:24:22.275000-07:00USAGEDATABASEDEMO_DBROLEROWN_T160falseACCOUNTADMIN
2026-10-06 11:24:22.583000-07:00USAGESCHEMADEMO_DB.FUT_T160ROLEROWN_T160falseACCOUNTADMIN
2026-10-06 11:24:22.781000-07:00USAGEWAREHOUSECOMPUTE_WHROLEROWN_T160falseACCOUNTADMIN

Three rows: the database, the schema and the warehouse. That is the chain from the last post, and nothing on any table.

Let's write a future grant on the schema

Here is the statement. It reads like an ordinary GRANT with one extra word in it.

Query
GRANT SELECT ON FUTURE TABLES IN SCHEMA DEMO_DB.FUT_T160 TO ROLE ROWN_T160;
Result1 row · 1 column
status
Statement executed successfully.

Now let's ask the schema what future grants it has, because the way Snowflake writes this down does the explaining for us.

Query
SHOW FUTURE GRANTS IN SCHEMA DEMO_DB.FUT_T160;
Result1 row · 7 columns · scroll →
created_onprivilegegrant_onnamegrant_tograntee_namegrant_option
2026-10-06 11:24:24.009000-07:00SELECTTABLEDEMO_DB.FUT_T160.<TABLE>ROLEROWN_T160false

Look at the name column: DEMO_DB.FUT_T160.<TABLE>. That is a literal stand-in, sitting where a table name would go.

That is what a future grant is. A rule with a hole in it, waiting for a name.

A separate listing

Future grants live in their own listing. SHOW GRANTS will not show you one, and SHOW FUTURE GRANTS IN SCHEMA or IN DATABASE is how you find them. If you are auditing an account, that is a second place you have to look.

Now let's make a table and see whether the rule fires

This table is created after the rule, and we are not going to grant anything on it.

A table made afterwards
CREATE OR REPLACE TABLE DEMO_DB.FUT_T160.AFTERWARDS_T160 (
    SALE_ID INT,
    AMOUNT  NUMBER(10,2)
);

INSERT INTO DEMO_DB.FUT_T160.AFTERWARDS_T160 (SALE_ID, AMOUNT)
VALUES (1, 100.00), (2, 250.50), (3, 75.25);

Now let's ask the table who can touch it.

Query
SELECT "privilege", "granted_on", "name", "grantee_name", "granted_by"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result2 rows · 5 columns
privilegegranted_onnamegrantee_namegranted_by
OWNERSHIPTABLEDEMO_DB.FUT_T160.AFTERWARDS_T160ACCOUNTADMINACCOUNTADMIN
SELECTTABLEDEMO_DB.FUT_T160.AFTERWARDS_T160ROWN_T160ACCOUNTADMIN

Two rows. OWNERSHIP to ACCOUNTADMIN, which is expected because ACCOUNTADMIN created it. And a SELECT to our first role.

We never ran a GRANT on this table. The rule did that, the moment the table existed.

A grid is not a guarantee though, so let's wear the role and actually read the table.

Wear the first role
USE ROLE ROWN_T160;
Query
SELECT * FROM DEMO_DB.FUT_T160.AFTERWARDS_T160 ORDER BY SALE_ID;
Result3 rows · 2 columns
SALE_IDAMOUNT
1100.00
2250.50
375.25

Three rows. It really can read a table that did not exist when the privilege was written.

Can we write that rule one level higher?

Yes. The same statement takes IN DATABASE instead of IN SCHEMA. Let's give that one to the second role.

Back to ACCOUNTADMIN
USE ROLE ACCOUNTADMIN;
Query
GRANT SELECT ON FUTURE TABLES IN DATABASE DEMO_DB TO ROLE ROWN2_T160;
Result1 row · 1 column
status
Statement executed successfully.
Query
SELECT "privilege", "grant_on", "name", "grant_to", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 WHERE "grantee_name" LIKE '%\\_T160' ESCAPE '\\';
Result1 row · 5 columns
privilegegrant_onnamegrant_tograntee_name
SELECTTABLEDEMO_DB.<TABLE>ROLEROWN2_T160

Have a look at the name column again. DEMO_DB.<TABLE>, with no schema in the middle. That rule covers every schema in the database.

Which is a bigger claim than it looks, so let's test it properly. A brand new schema, and a table inside that.

A schema made afterwards, and a table in it
CREATE OR REPLACE SCHEMA DEMO_DB.FUT2_T160
    COMMENT = 'episode 160 - a schema made after the database future grant';

CREATE OR REPLACE TABLE DEMO_DB.FUT2_T160.DEEPER_T160 (NOTE VARCHAR(40));
Query
SELECT "privilege", "granted_on", "name", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result2 rows · 4 columns
privilegegranted_onnamegrantee_name
OWNERSHIPTABLEDEMO_DB.FUT2_T160.DEEPER_T160ACCOUNTADMIN
SELECTTABLEDEMO_DB.FUT2_T160.DEEPER_T160ROWN2_T160

The deeper table already has a SELECT on it, for the second role. A rule written at database level reached into a schema that did not exist when the rule was written.

Now let's take the rule away again

This is the half that catches people. Let's revoke the schema level rule.

First, what future grants does that schema have?

Query
SELECT "privilege", "grant_on", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 3 columns
privilegegrant_ongrantee_name
SELECTTABLEROWN_T160

One. The one we wrote. Now the revoke.

Query
REVOKE SELECT ON FUTURE TABLES IN SCHEMA DEMO_DB.FUT_T160 FROM ROLE ROWN_T160;
Result1 row · 1 column
status
Statement executed successfully.
Query
SHOW FUTURE GRANTS IN SCHEMA DEMO_DB.FUT_T160;
Result0 rows · 7 columns · scroll →
created_onprivilegegrant_onnamegrant_tograntee_namegrant_option

No rows. The rule is gone, and that part is exactly what you would expect.

So what about the table the rule already covered?

Here is the question. That first table got its SELECT from a rule that no longer exists.

Query
SELECT "privilege", "granted_on", "name", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result2 rows · 4 columns
privilegegranted_onnamegrantee_name
OWNERSHIPTABLEDEMO_DB.FUT_T160.AFTERWARDS_T160ACCOUNTADMIN
SELECTTABLEDEMO_DB.FUT_T160.AFTERWARDS_T160ROWN_T160

Still there. The privilege the rule handed out is untouched.

Revoking the rule did not take back what the rule had already given. That is worth sitting with, because it is not what REVOKE usually means.

Revoking a future grant is not retroactive

A future grant and the privileges it created are two separate things in the catalogue. Removing the rule stops it firing again. It does not walk back through the tables it already fired on. If you need those gone too, you revoke them, table by table, as a second job.

And now a table created after the revoke

One more table in that same schema, made after the rule was revoked. This one should get nothing.

A table made after the revoke
CREATE OR REPLACE TABLE DEMO_DB.FUT_T160.TOOLATE_T160 (NOTE VARCHAR(40));
Query
SELECT "privilege", "granted_on", "name", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result2 rows · 4 columns
privilegegranted_onnamegrantee_name
OWNERSHIPTABLEDEMO_DB.FUT_T160.TOOLATE_T160ACCOUNTADMIN
SELECTTABLEDEMO_DB.FUT_T160.TOOLATE_T160ROWN2_T160

And it has a SELECT on it. Which looks like the revoke simply did not work.

Read the grantee_name column, not the privilege column. That is ROWN2_T160, the second role. The rule we revoked belonged to the first one.

So this is not our schema rule firing. It is the database level rule, which we never revoked, reaching down into this schema.

Two rules were in play, and the grantee name is the only thing on screen that says which one acted. That is why it is worth reading that column out loud rather than pointing at the word SELECT and moving on.

Now let's move ownership, and see why there are two clauses

Ownership next. Let's check who owns that first table right now.

Query
SELECT "name", "database_name", "schema_name", "owner", "rows"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 5 columns
namedatabase_nameschema_nameownerrows
AFTERWARDS_T160DEMO_DBFUT_T160ACCOUNTADMIN3

ACCOUNTADMIN, three rows in it. So let's hand it to the first role, with a plain GRANT OWNERSHIP and no extra clause.

Query
GRANT OWNERSHIP ON TABLE DEMO_DB.FUT_T160.AFTERWARDS_T160 TO ROLE ROWN_T160;
Query failed — Snowflake returned an error
003036 (23001): SQL execution error: Dependent grant of privilege 'SELECT' on securable 'DEMO_DB.FUT_T160.AFTERWARDS_T160' to role 'ROWN_T160' exists.  It must be revoked first.  More than one dependent grant may exist: use 'SHOW GRANTS' command to view them.  To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.

003036, and read all of it, because it is a generous refusal. It names the privilege, the object, the role that holds it, the fact that more than one may exist, the statement that will show you them, and the clause that fixes it.

And nothing was half done.

Query
SELECT "name", "owner" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 2 columns
nameowner
AFTERWARDS_T160ACCOUNTADMIN

Still ACCOUNTADMIN. The dependent grant it is complaining about is the SELECT the future grant created, which is still sitting on that table.

COPY CURRENT GRANTS carries them across

There are two clauses that deal with that dependent grant, and they do opposite things. The first one is COPY CURRENT GRANTS.

Query
GRANT OWNERSHIP ON TABLE DEMO_DB.FUT_T160.AFTERWARDS_T160
    TO ROLE ROWN_T160 COPY CURRENT GRANTS;
Result1 row · 1 column
status
Statement executed successfully.
Query
SELECT "name", "owner" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 2 columns
nameowner
AFTERWARDS_T160ROWN_T160

The owner is the first role now. And the grants underneath?

Query
SELECT "privilege", "granted_on", "name", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result2 rows · 4 columns
privilegegranted_onnamegrantee_name
OWNERSHIPTABLEDEMO_DB.FUT_T160.AFTERWARDS_T160ROWN_T160
SELECTTABLEDEMO_DB.FUT_T160.AFTERWARDS_T160ROWN_T160

Two rows, OWNERSHIP and SELECT, both sitting with that role. The dependent grant came along with it.

And REVOKE CURRENT GRANTS throws them away

Now let's hand the same table on again, to the second role, with the other clause.

Query
GRANT OWNERSHIP ON TABLE DEMO_DB.FUT_T160.AFTERWARDS_T160
    TO ROLE ROWN2_T160 REVOKE CURRENT GRANTS;
Result1 row · 1 column
status
Statement executed successfully.
Query
SELECT "name", "owner" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 2 columns
nameowner
AFTERWARDS_T160ROWN2_T160

Owner is the second role. And the grants?

Query
SELECT "privilege", "granted_on", "name", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result1 row · 4 columns
privilegegranted_onnamegrantee_name
OWNERSHIPTABLEDEMO_DB.FUT_T160.AFTERWARDS_T160ROWN2_T160

OWNERSHIP only. One row. The SELECT was stripped on the way across.

Those two grids are the entire difference between the two clauses. COPY carries the dependent grants. REVOKE throws them away. Nothing else changes.

Can the old owner still read its own table?

This is the question I actually wanted to ask. The first role used to own that table. Let's put it on and try.

Wear the first role
USE ROLE ROWN_T160;
Query
SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.FUT_T160.AFTERWARDS_T160;
Query failed — Snowflake returned an error
002003 (42S02): SQL compilation error:
Object 'DEMO_DB.FUT_T160.AFTERWARDS_T160' does not exist or not authorized. Your primary role ROWN_T160 must have at least one privilege granted on TABLE DEMO_DB.FUT_T160.AFTERWARDS_T160.

002003, and the message spells out why: the primary role must have at least one privilege on that table, and it has none.

Ownership moved, and because we used REVOKE CURRENT GRANTS on the way out, the SELECT went with it. The old owner is now an ordinary role with nothing on that object.

One more refusal while we are here. Let's hand ownership to a role that does not exist.

Back to ACCOUNTADMIN
USE ROLE ACCOUNTADMIN;
Query
GRANT OWNERSHIP ON TABLE DEMO_DB.FUT_T160.AFTERWARDS_T160
    TO ROLE NO_SUCH_ROLE_T160 COPY CURRENT GRANTS;
Query failed — Snowflake returned an error
002003 (02000): SQL compilation error:
Role 'NO_SUCH_ROLE_T160' does not exist or not authorized. Your primary role ACCOUNTADMIN must have at least one privilege granted on ROLE NO_SUCH_ROLE_T160.

002003 again, naming the role this time. The same message we have been reading all through the last two posts, pointed at a different kind of object.

One last thing: hand ownership back before you drop a role

This is housekeeping, and it is worth doing on screen rather than off it.

Ownership of that table is sitting with a role we are about to drop. An object owned by a role that no longer exists is somebody else's problem later, so let's hand it back first.

Query
SELECT "name", "owner" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) ORDER BY "name";
Result2 rows · 2 columns
nameowner
AFTERWARDS_T160ACCOUNTADMIN
TOOLATE_T160ACCOUNTADMIN

Both tables in our schema, both owned by ACCOUNTADMIN again. Now the revokes can run, and only then the roles.

Query
SHOW FUTURE GRANTS IN DATABASE DEMO_DB;
Result0 rows · 7 columns · scroll →
created_onprivilegegrant_onnamegrant_tograntee_namegrant_option

No rows, so no rule of ours is left standing on the database. Here is the whole teardown, in the order it has to happen.

Teardown
-- Putting the account back, and the order matters.
USE ROLE ACCOUNTADMIN;

-- Ownership first, while the roles still exist.
GRANT OWNERSHIP ON TABLE DEMO_DB.FUT_T160.AFTERWARDS_T160
    TO ROLE ACCOUNTADMIN REVOKE CURRENT GRANTS;

-- Then the grants, by name.
REVOKE USAGE ON WAREHOUSE COMPUTE_WH FROM ROLE ROWN_T160;
REVOKE USAGE ON DATABASE  DEMO_DB    FROM ROLE ROWN_T160;
REVOKE SELECT ON FUTURE TABLES IN DATABASE DEMO_DB FROM ROLE ROWN2_T160;

-- Then the roles come off the user, then the roles, then the schemas.
REVOKE ROLE ROWN_T160  FROM USER YOUR_USER;
REVOKE ROLE ROWN2_T160 FROM USER YOUR_USER;
DROP ROLE   IF EXISTS ROWN_T160;
DROP ROLE   IF EXISTS ROWN2_T160;
DROP SCHEMA IF EXISTS DEMO_DB.FUT_T160  CASCADE;
DROP SCHEMA IF EXISTS DEMO_DB.FUT2_T160 CASCADE;

-- And the session check that ends every one of these files.
SELECT CURRENT_USER()      AS THE_USER,
       CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

The result of that last query is not on this page. It returns CURRENT_USER(), and the builder here cannot leave one column out of a grid, so the whole grid is left out instead. Run it in your own worksheet and you will see your own session.

The order that matters

Ownership back first, roles dropped last. Not the other way round. If you drop the role while it still owns something, the object is left owned by a name that is no longer there, and the next person to touch it has to work out what happened.

Key points

  • A future grant is a standing rule, and the catalogue writes it with a literal <TABLE> stand-in where the table name would go.
  • A table created afterwards comes out with the privilege already on it, and no GRANT was run on that table.
  • IN DATABASE reaches further than IN SCHEMA, including into schemas that did not exist when the rule was written.
  • Future grants have their own listing: SHOW FUTURE GRANTS, not SHOW GRANTS.
  • Revoking a future grant is not retroactive. The rule goes, and the privileges it already handed out stay.
  • When two rules are in play, grantee_name is the only column that tells you which one acted.
  • A plain GRANT OWNERSHIP is refused with 003036 if anything depends on the object, and the message names the clause that fixes it.
  • COPY CURRENT GRANTS carries the dependent grants across. REVOKE CURRENT GRANTS strips them.
  • Ownership takes everything with it. The old owner could not read its own former table afterwards.
  • Hand ownership back before you drop a role, not after.
Next in this series

In the next post we will look at database roles: a role that lives inside one database rather than above the whole account, where it does and does not appear in the catalogue, and one grant Snowflake will happily let you write that does nothing whatsoever.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.36.101) running the demo script for this episode. The two schemas, the two roles and the three tables were created by that script and dropped again at the end, ownership first. No privilege was granted to or revoked from any built-in role except ACCOUNTADMIN taking its own table back. Every grid that returned CURRENT_USER() is left off the page rather than edited. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake GRANT <privilege> documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)