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
- Let's try a SELECT with nothing granted
- Grant one: usage on the database
- Now let's grant usage on the schema
- Now let's grant select on the table itself
- And now the fourth one, which I was sure we needed
- Why our test could not see it
- Now let's try the ON ALL TABLES shortcut
- So what does ALL PRIVILEGES actually grant?
- Can a role pass a privilege on to another role?
- Now let's take it back with RESTRICT
- And now the same statement with CASCADE
- One last thing: three small refusals
- And putting the account back
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 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.
SELECT * FROM DEMO_DB.RBAC_T159.SALES_T159 ORDER BY SALE_ID;| SALE_ID | REGION | AMOUNT |
|---|---|---|
| 1 | north | 100.00 |
| 2 | south | 250.50 |
| 3 | east | 75.25 |
| 4 | west | 310.00 |
Four rows. Now let's ask what the new role is allowed to do.
SHOW GRANTS TO ROLE RREAD_T159;| created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_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.
USE ROLE RREAD_T159;SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;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.
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON DATABASE DEMO_DB TO ROLE RREAD_T159;And run the same query again as the reader.
SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;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
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON SCHEMA DEMO_DB.RBAC_T159 TO ROLE RREAD_T159;SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;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
USE ROLE ACCOUNTADMIN;
GRANT SELECT ON TABLE DEMO_DB.RBAC_T159.SALES_T159 TO ROLE RREAD_T159;SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;| 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.
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.
USE ROLE ACCOUNTADMIN;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE RREAD_T159;SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.RBAC_T159.SALES_T159;| 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.
SELECT * FROM DEMO_DB.RBAC_T159.SALES_T159 ORDER BY SALE_ID;| SALE_ID | REGION | AMOUNT |
|---|---|---|
| 1 | north | 100.00 |
| 2 | south | 250.50 |
| 3 | east | 75.25 |
| 4 | west | 310.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.
-- 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.
SHOW GRANTS TO ROLE RREAD_T159C;| created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by |
|---|---|---|---|---|---|---|---|
| 2026-10-06 19:11:21.146000-07:00 | USAGE | DATABASE | DEMO_DB | ROLE | RREAD_T159C | false | ACCOUNTADMIN |
| 2026-10-06 19:11:21.947000-07:00 | USAGE | SCHEMA | DEMO_DB.CACHE_T159C | ROLE | RREAD_T159C | false | ACCOUNTADMIN |
| 2026-10-06 19:11:22.319000-07:00 | SELECT | TABLE | DEMO_DB.CACHE_T159C.SALES_T159C | ROLE | RREAD_T159C | false | ACCOUNTADMIN |
Database, schema, table. Now let's take the warehouse off the session as well, and check both facts in one query.
SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_WAREHOUSE() AS THE_WAREHOUSE;| THE_ROLE | THE_WAREHOUSE |
|---|---|
| RREAD_T159C | NULL |
The role is on, and the warehouse is NULL. There is no compute attached to this session at all.
Now the count.
SELECT COUNT(*) AS HOW_MANY
FROM DEMO_DB.CACHE_T159C.SALES_T159C;| 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.
SELECT SALE_ID, REGION, AMOUNT, TAG
FROM DEMO_DB.CACHE_T159C.SALES_T159C
ORDER BY SALE_ID;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.
SELECT SUM(AMOUNT) AS TOTAL, MIN(REGION) AS FIRST_REGION
FROM DEMO_DB.CACHE_T159C.SALES_T159C;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?
SELECT SALE_ID, AMOUNT
FROM DEMO_DB.CACHE_T159C.SALES_T159C
WHERE AMOUNT > 200
ORDER BY SALE_ID;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?
SELECT REGION, AMOUNT * 2 AS DOUBLED
FROM DEMO_DB.CACHE_T159C.SALES_T159C
ORDER BY DOUBLED DESC;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.
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.
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;GRANT SELECT ON ALL TABLES IN SCHEMA DEMO_DB.RBAC_T159 TO ROLE RWRITE_T159;| 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.
SELECT "privilege", "granted_on", "name"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "granted_on", "name", "privilege";| privilege | granted_on | name |
|---|---|---|
| USAGE | DATABASE | DEMO_DB |
| USAGE | SCHEMA | DEMO_DB.RBAC_T159 |
| SELECT | TABLE | DEMO_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.
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.
SELECT "privilege", "granted_on", "name"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" LIKE '%LATER%';| privilege | granted_on | name |
|---|
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.
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.
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.
SHOW GRANTS ON TABLE DEMO_DB.RBAC_T159.LATER_T159;| created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by | granted_by_role_type |
|---|---|---|---|---|---|---|---|---|
| 2026-10-06 11:24:02.975000-07:00 | OWNERSHIP | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | ACCOUNTADMIN | true | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.866000-07:00 | APPLYBUDGET | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.149000-07:00 | DELETE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.201000-07:00 | DELETE ERROR TABLE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.949000-07:00 | EVOLVE SCHEMA | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.927000-07:00 | INSERT | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.966000-07:00 | REBUILD | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.122000-07:00 | REFERENCES | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.168000-07:00 | SELECT | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.840000-07:00 | SELECT ERROR TABLE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.104000-07:00 | TRUNCATE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
| 2026-10-06 11:24:05.889000-07:00 | UPDATE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ROLE | RWRITE_T159 | false | ACCOUNTADMIN | ROLE |
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.
SELECT "privilege", "granted_on", "name", "grantee_name"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "privilege";| privilege | granted_on | name | grantee_name |
|---|---|---|---|
| APPLYBUDGET | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| DELETE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| DELETE ERROR TABLE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| EVOLVE SCHEMA | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| INSERT | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| OWNERSHIP | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | ACCOUNTADMIN |
| REBUILD | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| REFERENCES | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| SELECT | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| SELECT ERROR TABLE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| TRUNCATE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_T159 |
| UPDATE | TABLE | DEMO_DB.RBAC_T159.LATER_T159 | RWRITE_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.
-- 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.
USE ROLE RDOWN_T159E;SELECT SALE_ID, REGION, AMOUNT
FROM DEMO_DB.CHAIN_T159E.SALES_T159E
ORDER BY SALE_ID;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.
GRANT SELECT ON TABLE DEMO_DB.CHAIN_T159E.SALES_T159E
TO ROLE RMID_T159E WITH GRANT OPTION;| status |
|---|
| Statement executed successfully. |
SELECT "privilege", "grantee_name", "grant_option", "granted_by"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "grantee_name", "privilege";| privilege | grantee_name | grant_option | granted_by |
|---|---|---|---|
| OWNERSHIP | ACCOUNTADMIN | true | ACCOUNTADMIN |
| SELECT | RMID_T159E | true | ACCOUNTADMIN |
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.
USE ROLE RMID_T159E;GRANT SELECT ON TABLE DEMO_DB.CHAIN_T159E.SALES_T159E TO ROLE RDOWN_T159E;| status |
|---|
| Statement executed successfully. |
Statement executed successfully. But a statement succeeding proves nothing on its own, so let's look at who granted what.
SELECT "privilege", "grantee_name", "grant_option", "granted_by"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "grantee_name", "privilege";| privilege | grantee_name | grant_option | granted_by |
|---|---|---|---|
| OWNERSHIP | ACCOUNTADMIN | true | ACCOUNTADMIN |
| SELECT | RDOWN_T159E | false | RMID_T159E |
| SELECT | RMID_T159E | true | ACCOUNTADMIN |
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.
USE ROLE RDOWN_T159E;SELECT SALE_ID, REGION, AMOUNT
FROM DEMO_DB.CHAIN_T159E.SALES_T159E
ORDER BY SALE_ID;| SALE_ID | REGION | AMOUNT |
|---|---|---|
| 1 | north | 100.00 |
| 2 | south | 250.50 |
| 3 | east | 75.25 |
Three rows, through a privilege ACCOUNTADMIN never gave it.
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.
USE ROLE ACCOUNTADMIN;REVOKE SELECT ON TABLE DEMO_DB.CHAIN_T159E.SALES_T159E
FROM ROLE RMID_T159E RESTRICT;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.
SELECT "privilege", "grantee_name", "grant_option", "granted_by"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "grantee_name", "privilege";| privilege | grantee_name | grant_option | granted_by |
|---|---|---|---|
| OWNERSHIP | ACCOUNTADMIN | true | ACCOUNTADMIN |
| SELECT | RDOWN_T159E | false | RMID_T159E |
| SELECT | RMID_T159E | true | ACCOUNTADMIN |
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.
SELECT SALE_ID, REGION, AMOUNT
FROM DEMO_DB.CHAIN_T159E.SALES_T159E
ORDER BY SALE_ID;| SALE_ID | REGION | AMOUNT |
|---|---|---|
| 1 | north | 100.00 |
| 2 | south | 250.50 |
| 3 | east | 75.25 |
And now the same statement with CASCADE
Same revoke, one word different.
REVOKE SELECT ON TABLE DEMO_DB.CHAIN_T159E.SALES_T159E
FROM ROLE RMID_T159E CASCADE;| status |
|---|
| Statement executed successfully. 2 objects affected. |
2 objects affected this time. Let's see what is left on the table.
SELECT "privilege", "grantee_name", "grant_option", "granted_by"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "grantee_name", "privilege";| privilege | grantee_name | grant_option | granted_by |
|---|---|---|---|
| OWNERSHIP | ACCOUNTADMIN | true | ACCOUNTADMIN |
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.
USE ROLE RDOWN_T159E;SELECT SALE_ID, REGION, AMOUNT
FROM DEMO_DB.CHAIN_T159E.SALES_T159E
ORDER BY SALE_ID;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.
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
GRANT TELEPORT ON TABLE DEMO_DB.RBAC_T159.SALES_T159 TO ROLE RREAD_T159;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
GRANT SELECT ON SCHEMA DEMO_DB.RBAC_T159 TO ROLE RREAD_T159;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
REVOKE DELETE ON TABLE DEMO_DB.RBAC_T159.SALES_T159 FROM ROLE RREAD_T159;| status |
|---|
| Statement executed successfully. 0 objects affected. |
No error. 0 objects affected, and that is all. Let's confirm nothing moved.
SHOW GRANTS TO ROLE RREAD_T159;| created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by |
|---|---|---|---|---|---|---|---|
| 2026-10-06 11:23:53.949000-07:00 | USAGE | DATABASE | DEMO_DB | ROLE | RREAD_T159 | false | ACCOUNTADMIN |
| 2026-10-06 11:23:54.781000-07:00 | USAGE | SCHEMA | DEMO_DB.RBAC_T159 | ROLE | RREAD_T159 | false | ACCOUNTADMIN |
| 2026-10-06 11:23:56.793000-07:00 | USAGE | WAREHOUSE | COMPUTE_WH | ROLE | RREAD_T159 | false | ACCOUNTADMIN |
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.
-- 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
SELECTneeds a chain: usage on the database, usage on the schema, thenSELECTon the table. - Every missing link refuses with
002003and 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 WAREHOUSEchanged nothing we could measure with aCOUNT(*). - A
COUNT(*)is answered from metadata and needs no warehouse at all, which is why it was the wrong probe. 000606is a session error, not a permission error. It means no warehouse is running.ON ALL TABLESis a snapshot. A table created afterwards gets nothing, and nothing warns you.ALL PRIVILEGESis a list, andSHOW GRANTSwill show you the list. Read it before you grant it.WITH GRANT OPTIONreally does pass a privilege on, and thegranted_bycolumn is how you prove it.RESTRICTrefuses with the count, the reason and the fix.CASCADEtakes 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.
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.