Snowflake SQL Tutorial 159 - I Said A SELECT Needs Four Privileges. It Needs Three

Snowflake SQL · Episode 159

In this post we are going to cover GRANT and REVOKE in Snowflake.

A role in Snowflake starts with nothing. GRANT is how we give it a privilege on one object, and REVOKE is how we take that privilege back again.

We will start with a role that can do nothing at all, and give it one privilege at a time until an ordinary SELECT works. Then we will count how many privileges that took, because it is not the number I expected when I wrote this episode.

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

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

Let's create some sample data

We need three things before we can grant anything: a schema of our own, a table in it, and a role that has nothing.

Sample data
-- Sample data for this post.
-- A schema of our own, one table, four rows, and a role that has nothing.
USE ROLE ACCOUNTADMIN;
USE DATABASE DEMO_DB;

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

CREATE OR REPLACE TABLE DEMO_DB.RBAC_T159.SALES_T159 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

INSERT INTO DEMO_DB.RBAC_T159.SALES_T159 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'south', 250.50),
       (3, 'east',   75.25), (4, 'west',  310.00);

-- The role that will do the reading. It is given nothing at all.
CREATE OR REPLACE ROLE RREAD_T159 COMMENT = 'episode 159 - the reader';

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

Let's look at the table first, as ACCOUNTADMIN, so we know what a working read looks like.

Query
SELECT * FROM DEMO_DB.RBAC_T159.SALES_T159 ORDER BY SALE_ID;
Result4 rows · 3 columns
SALE_IDREGIONAMOUNT
1north100.00
2south250.50
3east75.25
4west310.00

Four rows. Now let's ask what the new role is allowed to do.

Query
SHOW GRANTS TO ROLE RREAD_T159;
Result0 rows · 8 columns · scroll →
created_onprivilegegranted_onnamegranted_tograntee_namegrant_optiongranted_by

Nothing at all. The grid has its eight column headings and no rows under them. That is what a brand new role looks like, and it is worth seeing once, because nothing on screen announces it.

Let's try a SELECT with nothing granted

Let's put that role on and run the most ordinary query there is.

Wear the role
USE ROLE RREAD_T159;
Query
SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;
Query failed — Snowflake returned an error
002003 (02000): SQL compilation error:
Database 'DEMO_DB' does not exist or not authorized. Your primary role RREAD_T159 must have USAGE or any other privilege granted on DATABASE DEMO_DB.

002003, and the database does not exist.

Read that message carefully, because it is doing two jobs. Does not exist or not authorized. An unauthorised role is told exactly the same thing as somebody asking for a database that was never there.

That is deliberate. Snowflake will not tell a role it does not trust whether the object is even on the account.

Grant one: usage on the database

So let's give it the first thing the message asked for.

Grant one
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON DATABASE DEMO_DB TO ROLE RREAD_T159;

And run the same query again as the reader.

Query
SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;
Query failed — Snowflake returned an error
002003 (02000): SQL compilation error:
Schema 'DEMO_DB.RBAC_T159' does not exist or not authorized. Your primary role RREAD_T159 must have USAGE or any other privilege granted on SCHEMA DEMO_DB.RBAC_T159.

Same code, 002003, and the same sentence. But look at what moved. It is the schema that does not exist now, not the database.

The refusal walked one step further in before it stopped.

Now let's grant usage on the schema

Grant two
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON SCHEMA DEMO_DB.RBAC_T159 TO ROLE RREAD_T159;
Query
SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;
Query failed — Snowflake returned an error
002003 (42S02): SQL compilation error:
Object 'DEMO_DB.RBAC_T159.SALES_T159' does not exist or not authorized. Your primary role RREAD_T159 must have at least one privilege granted on TABLE DEMO_DB.RBAC_T159.SALES_T159.

And now it is the table. Each refusal names the next thing we have not got, which makes this the easiest permission problem in Snowflake to debug. The error tells you what to grant next.

Now let's grant select on the table itself

Grant three
USE ROLE ACCOUNTADMIN;
GRANT SELECT ON TABLE DEMO_DB.RBAC_T159.SALES_T159 TO ROLE RREAD_T159;
Query
SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;
Result1 row · 1 column
HOW_MANY
4

It works. Four rows counted, by a role that twenty seconds ago could not see the database.

So that is the chain, built one link at a time: usage on the database, usage on the schema, select on the table. Which is three grants.

What usage is for

Usage on a database or a schema does not let a role read anything in them. It lets the role see through them to the object you actually grant on. That is why the chain has to be built from the outside in.

And now the fourth one, which I was sure we needed

I wrote this episode expecting four privileges, with USAGE ON WAREHOUSE as the fourth. So let's grant it and run the same query again.

Grant four
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE RREAD_T159;
Query
SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;
Result1 row · 1 column
HOW_MANY
4

Nothing changed. Same query, same count, same everything.

And the reader can fetch the rows themselves too, which is worth seeing before we go any further.

Query
SELECT * FROM DEMO_DB.RBAC_T159.SALES_T159 ORDER BY SALE_ID;
Result4 rows · 3 columns
SALE_IDREGIONAMOUNT
1north100.00
2south250.50
3east75.25
4west310.00

So either the warehouse grant does nothing, or our test could not see what it does. It was the second one, and the reason is the most useful thing in this episode.

Why our test could not see it

Every probe above was a COUNT(*). And a COUNT(*) over a whole table is answered from metadata. It never touches a warehouse at all, so it cannot possibly tell us whether we needed one.

So let's set the question up properly, in a second run, where a missing warehouse has nowhere to hide.

Setup for the second run
-- The second run, set up so that a missing warehouse cannot hide.
-- The result cache is switched off BY NAME, for this session only.
ALTER SESSION SET USE_CACHED_RESULT = FALSE;

-- A schema, a table, and four rows carrying a tag unique to this run,
-- so no earlier query can possibly have answered this one already.
CREATE OR REPLACE SCHEMA DEMO_DB.CACHE_T159C;
CREATE OR REPLACE TABLE DEMO_DB.CACHE_T159C.SALES_T159C (
    SALE_ID INT, REGION VARCHAR(10), AMOUNT NUMBER(10,2), TAG VARCHAR(40)
);

-- The reader gets exactly three grants. No warehouse privilege.
CREATE OR REPLACE ROLE RREAD_T159C;
GRANT USAGE  ON DATABASE DEMO_DB                        TO ROLE RREAD_T159C;
GRANT USAGE  ON SCHEMA   DEMO_DB.CACHE_T159C            TO ROLE RREAD_T159C;
GRANT SELECT ON TABLE    DEMO_DB.CACHE_T159C.SALES_T159C TO ROLE RREAD_T159C;

First, let's prove the reader has exactly three grants and no warehouse among them.

Query
SHOW GRANTS TO ROLE RREAD_T159C;
Result3 rows · 8 columns · scroll →
created_onprivilegegranted_onnamegranted_tograntee_namegrant_optiongranted_by
2026-10-06 19:11:21.146000-07:00USAGEDATABASEDEMO_DBROLERREAD_T159CfalseACCOUNTADMIN
2026-10-06 19:11:21.947000-07:00USAGESCHEMADEMO_DB.CACHE_T159CROLERREAD_T159CfalseACCOUNTADMIN
2026-10-06 19:11:22.319000-07:00SELECTTABLEDEMO_DB.CACHE_T159C.SALES_T159CROLERREAD_T159CfalseACCOUNTADMIN

Database, schema, table. Now let's take the warehouse off the session as well, and check both facts in one query.

Query
SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_WAREHOUSE() AS THE_WAREHOUSE;
Result1 row · 2 columns
THE_ROLETHE_WAREHOUSE
RREAD_T159CNULL

The role is on, and the warehouse is NULL. There is no compute attached to this session at all.

Now the count.

Query
SELECT COUNT(*) AS HOW_MANY
  FROM DEMO_DB.CACHE_T159C.SALES_T159C;
Result1 row · 1 column
HOW_MANY
4

It worked. No warehouse, no warehouse privilege, and the result cache switched off by name, and the count still came back. That is the proof that a count needs no compute.

Now let's ask for the rows instead.

Query
SELECT SALE_ID, REGION, AMOUNT, TAG
  FROM DEMO_DB.CACHE_T159C.SALES_T159C
 ORDER BY SALE_ID;
Query failed — Snowflake returned an error
000606 (57P03): No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.

000606. No active warehouse selected in the current session. Let's try adding them up.

Query
SELECT SUM(AMOUNT) AS TOTAL, MIN(REGION) AS FIRST_REGION
  FROM DEMO_DB.CACHE_T159C.SALES_T159C;
Query failed — Snowflake returned an error
000606 (57P03): No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.

Same wall. Filtering on a value?

Query
SELECT SALE_ID, AMOUNT
  FROM DEMO_DB.CACHE_T159C.SALES_T159C
 WHERE AMOUNT > 200
 ORDER BY SALE_ID;
Query failed — Snowflake returned an error
000606 (57P03): No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.

And sorting on something it has to work out first?

Query
SELECT REGION, AMOUNT * 2 AS DOUBLED
  FROM DEMO_DB.CACHE_T159C.SALES_T159C
 ORDER BY DOUBLED DESC;
Query failed — Snowflake returned an error
000606 (57P03): No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.

Four for four. Every query that has to look at a value stopped, and the count did not.

000606 is not a permission error

Notice which error we got. 000606 is a session error, not a permission error. It is not telling us a grant is missing. It is telling us there is no warehouse running. Those are different problems with different fixes, and the message says which one it is.

So a SELECT needs three privileges. The fourth thing it needs is not a privilege at all. And the reason I could not see that is worth more than the fact itself: I tested it with the one query in the language that could not detect the thing I was testing for.

Now let's try the ON ALL TABLES shortcut

Granting one table at a time gets old quickly, so there is a shortcut. Let's make a second role and use it.

A second role
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE ROLE RWRITE_T159 COMMENT = 'episode 159 - the writer';
GRANT USAGE ON DATABASE DEMO_DB TO ROLE RWRITE_T159;
GRANT USAGE ON SCHEMA DEMO_DB.RBAC_T159 TO ROLE RWRITE_T159;
Query
GRANT SELECT ON ALL TABLES IN SCHEMA DEMO_DB.RBAC_T159 TO ROLE RWRITE_T159;
Result1 row · 1 column
status
Statement executed successfully. 1 objects affected.

Have a look at the status line. 1 objects affected, because there is one table in that schema so far. Let's see what the role holds now.

Query
SELECT "privilege", "granted_on", "name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "granted_on", "name", "privilege";
Result3 rows · 3 columns
privilegegranted_onname
USAGEDATABASEDEMO_DB
USAGESCHEMADEMO_DB.RBAC_T159
SELECTTABLEDEMO_DB.RBAC_T159.SALES_T159

Three rows: the database, the schema, and a select on our one table. Exactly what we asked for.

Now let's make a table afterwards

Here is the question that matters. We never granted anything on this new table.

A table made later
CREATE OR REPLACE TABLE DEMO_DB.RBAC_T159.LATER_T159 (
    NOTE VARCHAR(40)
);
INSERT INTO DEMO_DB.RBAC_T159.LATER_T159 VALUES ('made after the grant');

Let's look through the writer's grants for it.

Query
SELECT "privilege", "granted_on", "name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 WHERE "name" LIKE '%LATER%';
Result0 rows · 3 columns
privilegegranted_onname

Nothing. The new table got nothing at all.

ALL TABLES meant all the tables that existed when we ran the statement, and not one more. It is a snapshot, not a standing rule.

Which of the two words you typed

That is not a criticism. Snapshots are useful. But if what you wanted was a rule that keeps applying, this is not it, and nothing warns you. The thing that is a standing rule is ON FUTURE TABLES, and that is the first half of Episode 160.

So what does ALL PRIVILEGES actually grant?

The other shortcut is ALL PRIVILEGES, and this one is worth seeing spelled out. Let's grant it on the table we just made.

Query
GRANT ALL PRIVILEGES ON TABLE DEMO_DB.RBAC_T159.LATER_T159
    TO ROLE RWRITE_T159;

And now let's ask the table who can touch it.

Query
SHOW GRANTS ON TABLE DEMO_DB.RBAC_T159.LATER_T159;
Result12 rows · 9 columns · scroll →
created_onprivilegegranted_onnamegranted_tograntee_namegrant_optiongranted_bygranted_by_role_type
2026-10-06 11:24:02.975000-07:00OWNERSHIPTABLEDEMO_DB.RBAC_T159.LATER_T159ROLEACCOUNTADMINtrueACCOUNTADMINROLE
2026-10-06 11:24:05.866000-07:00APPLYBUDGETTABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.149000-07:00DELETETABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.201000-07:00DELETE ERROR TABLETABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.949000-07:00EVOLVE SCHEMATABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.927000-07:00INSERTTABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.966000-07:00REBUILDTABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.122000-07:00REFERENCESTABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.168000-07:00SELECTTABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.840000-07:00SELECT ERROR TABLETABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.104000-07:00TRUNCATETABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE
2026-10-06 11:24:05.889000-07:00UPDATETABLEDEMO_DB.RBAC_T159.LATER_T159ROLERWRITE_T159falseACCOUNTADMINROLE

Twelve rows. The first one is the OWNERSHIP row, and that was already there before we ran anything: ACCOUNTADMIN got it by creating the table. Every other row on that grid is the grant we just ran, written out one privilege at a time.

Here they are on their own, without the timestamps.

Query
SELECT "privilege", "granted_on", "name", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "privilege";
Result12 rows · 4 columns
privilegegranted_onnamegrantee_name
APPLYBUDGETTABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
DELETETABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
DELETE ERROR TABLETABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
EVOLVE SCHEMATABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
INSERTTABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
OWNERSHIPTABLEDEMO_DB.RBAC_T159.LATER_T159ACCOUNTADMIN
REBUILDTABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
REFERENCESTABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
SELECTTABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
SELECT ERROR TABLETABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
TRUNCATETABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159
UPDATETABLEDEMO_DB.RBAC_T159.LATER_T159RWRITE_T159

SELECT, INSERT, UPDATE, DELETE and TRUNCATE are the ones you were picturing. REFERENCES and REBUILD are reasonable once you see them.

Then APPLYBUDGET, EVOLVE SCHEMA, DELETE ERROR TABLE and SELECT ERROR TABLE. I would not have guessed any of those, and I wrote the statement.

So ALL PRIVILEGES is not a vague gesture. It is a list, and Snowflake will show you the list. Which is worth doing before you grant it to somebody, rather than after.

Can a role pass a privilege on to another role?

Not by default. We have to say so, with WITH GRANT OPTION.

This needs setting up carefully, so it is a separate run with three roles and the secondary roles switched off. The reason for that care is in a note at the end of this section.

Setup for the third run
-- A third run, for WITH GRANT OPTION, with the test built so it can fail.
-- Three roles: the owner (ACCOUNTADMIN), a middle role, a downstream role.
CREATE OR REPLACE SCHEMA DEMO_DB.CHAIN_T159E;
CREATE OR REPLACE TABLE DEMO_DB.CHAIN_T159E.SALES_T159E (
    SALE_ID INT, REGION VARCHAR(10), AMOUNT NUMBER(10,2)
);
INSERT INTO DEMO_DB.CHAIN_T159E.SALES_T159E
VALUES (1,'north',100.00), (2,'south',250.50), (3,'east',75.25);

CREATE OR REPLACE ROLE RMID_T159E;
CREATE OR REPLACE ROLE RDOWN_T159E;

-- Both roles get the database, the schema and the warehouse.
-- Neither gets anything on the table. That part is the experiment.
GRANT USAGE ON DATABASE  DEMO_DB             TO ROLE RMID_T159E;
GRANT USAGE ON SCHEMA    DEMO_DB.CHAIN_T159E TO ROLE RMID_T159E;
GRANT USAGE ON WAREHOUSE COMPUTE_WH          TO ROLE RMID_T159E;
GRANT USAGE ON DATABASE  DEMO_DB             TO ROLE RDOWN_T159E;
GRANT USAGE ON SCHEMA    DEMO_DB.CHAIN_T159E TO ROLE RDOWN_T159E;
GRANT USAGE ON WAREHOUSE COMPUTE_WH          TO ROLE RDOWN_T159E;

-- And the secondary roles are switched off, so only the role we put on
-- can be answering.
USE SECONDARY ROLES NONE;

First, let's prove the downstream role genuinely cannot read the table yet.

Wear the downstream role
USE ROLE RDOWN_T159E;
Query
SELECT SALE_ID, REGION, AMOUNT
  FROM DEMO_DB.CHAIN_T159E.SALES_T159E
 ORDER BY SALE_ID;
Query failed — Snowflake returned an error
002003 (42S02): SQL compilation error:
Object 'DEMO_DB.CHAIN_T159E.SALES_T159E' does not exist or not authorized. Your primary role RDOWN_T159E must have at least one privilege granted on TABLE DEMO_DB.CHAIN_T159E.SALES_T159E.

Good. 002003, and it names the downstream role. Now let's give the middle role a select it is allowed to hand on.

Query
GRANT SELECT ON TABLE DEMO_DB.CHAIN_T159E.SALES_T159E
  TO ROLE RMID_T159E WITH GRANT OPTION;
Result1 row · 1 column
status
Statement executed successfully.
Query
SELECT "privilege", "grantee_name", "grant_option", "granted_by"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result2 rows · 4 columns
privilegegrantee_namegrant_optiongranted_by
OWNERSHIPACCOUNTADMINtrueACCOUNTADMIN
SELECTRMID_T159EtrueACCOUNTADMIN

Look at the grant_option column on the middle role's row. It reads true. That is the whole difference the clause makes.

So let's wear that role and grant the select onward.

Wear the middle role
USE ROLE RMID_T159E;
Query
GRANT SELECT ON TABLE DEMO_DB.CHAIN_T159E.SALES_T159E TO ROLE RDOWN_T159E;
Result1 row · 1 column
status
Statement executed successfully.

Statement executed successfully. But a statement succeeding proves nothing on its own, so let's look at who granted what.

Query
SELECT "privilege", "grantee_name", "grant_option", "granted_by"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result3 rows · 4 columns
privilegegrantee_namegrant_optiongranted_by
OWNERSHIPACCOUNTADMINtrueACCOUNTADMIN
SELECTRDOWN_T159EfalseRMID_T159E
SELECTRMID_T159EtrueACCOUNTADMIN

There it is, in the granted_by column. The downstream role's select was granted by RMID_T159E, not by ACCOUNTADMIN. The middle role did that, and the catalogue says so.

And now let's see whether the downstream role can read.

Wear the downstream role
USE ROLE RDOWN_T159E;
Query
SELECT SALE_ID, REGION, AMOUNT
  FROM DEMO_DB.CHAIN_T159E.SALES_T159E
 ORDER BY SALE_ID;
Result3 rows · 3 columns
SALE_IDREGIONAMOUNT
1north100.00
2south250.50
3east75.25

Three rows, through a privilege ACCOUNTADMIN never gave it.

Why this run is set up so carefully

It took three attempts to make that one column say something true. The first two times, the role being tested already held the privilege another way, so the statement succeeded, the grid looked right, and it proved absolutely nothing. A test that cannot fail is not a test. That is why the read above is run before the onward grant as well as after, and why the secondary roles are switched off.

Now let's take it back with RESTRICT

So there is a privilege hanging off another privilege. What happens if we revoke the one underneath? That depends on one word, and the two words do opposite things.

RESTRICT first.

Back to ACCOUNTADMIN
USE ROLE ACCOUNTADMIN;
Query
REVOKE SELECT ON TABLE DEMO_DB.CHAIN_T159E.SALES_T159E
  FROM ROLE RMID_T159E RESTRICT;
Query failed — Snowflake returned an error
003935 (23001): Revoke partially executed: 0 grant(s) revoked, 1 grant(s) skipped due to dependent grants and no CASCADE is specified.

003935, and read what that message gives us. The count, the reason, and the fix, all in one line: no grants revoked, one skipped because something depends on it, and no CASCADE was specified.

Let's check the grants are untouched.

Query
SELECT "privilege", "grantee_name", "grant_option", "granted_by"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result3 rows · 4 columns
privilegegrantee_namegrant_optiongranted_by
OWNERSHIPACCOUNTADMINtrueACCOUNTADMIN
SELECTRDOWN_T159EfalseRMID_T159E
SELECTRMID_T159EtrueACCOUNTADMIN

All three still there. It refused cleanly rather than doing half the job, which is exactly what you want from a statement you ran by mistake.

And the downstream role still reads.

Query
SELECT SALE_ID, REGION, AMOUNT
  FROM DEMO_DB.CHAIN_T159E.SALES_T159E
 ORDER BY SALE_ID;
Result3 rows · 3 columns
SALE_IDREGIONAMOUNT
1north100.00
2south250.50
3east75.25

And now the same statement with CASCADE

Same revoke, one word different.

Query
REVOKE SELECT ON TABLE DEMO_DB.CHAIN_T159E.SALES_T159E
  FROM ROLE RMID_T159E CASCADE;
Result1 row · 1 column
status
Statement executed successfully. 2 objects affected.

2 objects affected this time. Let's see what is left on the table.

Query
SELECT "privilege", "grantee_name", "grant_option", "granted_by"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "grantee_name", "privilege";
Result1 row · 4 columns
privilegegrantee_namegrant_optiongranted_by
OWNERSHIPACCOUNTADMINtrueACCOUNTADMIN

Down to the OWNERSHIP row. Both selects are gone, the middle role's and the downstream role's.

So let's ask the downstream role to read it one more time.

Wear the downstream role
USE ROLE RDOWN_T159E;
Query
SELECT SALE_ID, REGION, AMOUNT
  FROM DEMO_DB.CHAIN_T159E.SALES_T159E
 ORDER BY SALE_ID;
Query failed — Snowflake returned an error
002003 (42S02): SQL compilation error:
Object 'DEMO_DB.CHAIN_T159E.SALES_T159E' does not exist or not authorized. Your primary role RDOWN_T159E must have at least one privilege granted on TABLE DEMO_DB.CHAIN_T159E.SALES_T159E.

002003. It lost the privilege it never knew it was borrowing, and nobody revoked anything from it directly.

The difference in one line

RESTRICT refuses while anything depends on the grant, and tells you the count, the reason and the remedy.
CASCADE takes the dependent grants with it.
If you are not sure which you want, run the RESTRICT one first. The refusal is free and it tells you what you would have broken.

One last thing: three small refusals

Three quick ones to finish, because each fails in a different way.

A privilege name that does not exist

Query
GRANT TELEPORT ON TABLE DEMO_DB.RBAC_T159.SALES_T159 TO ROLE RREAD_T159;
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 1 at position 15 unexpected 'ON'.

001003, a syntax error, at the word after the invented privilege. Privilege names are parsed as keywords, so a misspelled one is not a permissions problem at all. The grammar simply cannot read the sentence.

The right privilege on the wrong kind of object

Query
GRANT SELECT ON SCHEMA DEMO_DB.RBAC_T159 TO ROLE RREAD_T159;
Query failed — Snowflake returned an error
003008 (42601): SQL compilation error:
Invalid object type 'SCHEMA' for privilege 'SELECT'.

003008, and it names both halves of the mismatch: the object type it was given and the privilege it cannot put on it.

Revoking something that was never granted

Query
REVOKE DELETE ON TABLE DEMO_DB.RBAC_T159.SALES_T159 FROM ROLE RREAD_T159;
Result1 row · 1 column
status
Statement executed successfully. 0 objects affected.

No error. 0 objects affected, and that is all. Let's confirm nothing moved.

Query
SHOW GRANTS TO ROLE RREAD_T159;
Result3 rows · 8 columns · scroll →
created_onprivilegegranted_onnamegranted_tograntee_namegrant_optiongranted_by
2026-10-06 11:23:53.949000-07:00USAGEDATABASEDEMO_DBROLERREAD_T159falseACCOUNTADMIN
2026-10-06 11:23:54.781000-07:00USAGESCHEMADEMO_DB.RBAC_T159ROLERREAD_T159falseACCOUNTADMIN
2026-10-06 11:23:56.793000-07:00USAGEWAREHOUSECOMPUTE_WHROLERREAD_T159falseACCOUNTADMIN

Three rows, the same three the reader had before. A revoke of nothing is simply nothing, which makes REVOKE safe to run twice.

And putting the account back

Everything above was created by this post and removed again afterwards, in this order.

Teardown
-- Putting the account back, in this order.
USE ROLE ACCOUNTADMIN;

REVOKE USAGE ON WAREHOUSE COMPUTE_WH FROM ROLE RREAD_T159;
REVOKE USAGE ON DATABASE  DEMO_DB    FROM ROLE RREAD_T159;
REVOKE USAGE ON WAREHOUSE COMPUTE_WH FROM ROLE RWRITE_T159;
REVOKE USAGE ON DATABASE  DEMO_DB    FROM ROLE RWRITE_T159;

REVOKE ROLE RREAD_T159  FROM USER YOUR_USER;
REVOKE ROLE RWRITE_T159 FROM USER YOUR_USER;

DROP ROLE   IF EXISTS RREAD_T159;
DROP ROLE   IF EXISTS RWRITE_T159;
DROP SCHEMA IF EXISTS DEMO_DB.RBAC_T159 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, which is the only version of it worth having.

Key points

  • A SELECT needs a chain: usage on the database, usage on the schema, then SELECT on the table.
  • Every missing link refuses with 002003 and names the next thing you have not got, so the error tells you what to grant.
  • Every one of those refusals says does not exist or not authorized. Snowflake will not tell an unauthorised role whether the object is even there.
  • That is three privileges, not four. USAGE ON WAREHOUSE changed nothing we could measure with a COUNT(*).
  • A COUNT(*) is answered from metadata and needs no warehouse at all, which is why it was the wrong probe.
  • 000606 is a session error, not a permission error. It means no warehouse is running.
  • ON ALL TABLES is a snapshot. A table created afterwards gets nothing, and nothing warns you.
  • ALL PRIVILEGES is a list, and SHOW GRANTS will show you the list. Read it before you grant it.
  • WITH GRANT OPTION really does pass a privilege on, and the granted_by column is how you prove it.
  • RESTRICT refuses with the count, the reason and the fix. CASCADE takes the dependent grant with it.
  • A bad privilege name is a syntax error, and a revoke of something never granted is a no-op.
  • A test that cannot fail is not a test.
Next in this series

In the next post we will cover the thing that is a standing rule: future grants and ownership. A grant on tables that do not exist yet, what happens when you revoke that rule again, and the two clauses that decide what travels with ownership when it moves.

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, across three runs: the privilege chain, a second run with the result cache switched off by name and no warehouse on the session, and a third for WITH GRANT OPTION with the secondary roles switched off. No privilege was granted to or revoked from any built-in role. 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.