In this post we are going to cover SHOW GRANTS in Snowflake, and the question it looks like it answers: who can read this table.
SHOW GRANTS ON TABLE lists the privileges granted directly on that table. That is what it is specified to do, and it does it correctly. What it does not do is tell you who can reach the table some other way, and nothing about the grid says so.
So nothing on this page is a wrong answer. We are going to end up with two grids that are both true at the same time: one role reading a table, and a list of that table's grants that does not mention it.
We will build the two roles first, then ask the question the obvious way, and then go and find the fact that is missing.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 162 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's create some sample data and two roles
- Now let's prove the top role really can read the table
- So let's ask who can read this table, the obvious way
- So where is the fact we are missing?
- Now let's see the wider versions of the same question
- Can we ask all this as a table instead of a command?
- What happens when we try this through INFORMATION_SCHEMA?
- One last thing: a correction to Episode 153
Let's create some sample data and two roles
The arrangement matters more than the data here. One table with two rows, a base role that holds a SELECT on it, and a top role that holds nothing at all except the base role.
-- Setup for this post, and the arrangement is the whole experiment.
USE ROLE ACCOUNTADMIN;
USE DATABASE DEMO_DB;
CREATE OR REPLACE SCHEMA DEMO_DB.VIS_T162B;
CREATE OR REPLACE TABLE DEMO_DB.VIS_T162B.SECRETS_T162B (
SECRET_ID NUMBER, WHAT VARCHAR(30)
);
INSERT INTO DEMO_DB.VIS_T162B.SECRETS_T162B (SECRET_ID, WHAT)
VALUES (1,'public'),(2,'private');
-- The base role gets the chain, and the SELECT on the table. Ordinary stuff.
CREATE OR REPLACE ROLE RBASE_T162B;
GRANT USAGE ON DATABASE DEMO_DB TO ROLE RBASE_T162B;
GRANT USAGE ON SCHEMA DEMO_DB.VIS_T162B TO ROLE RBASE_T162B;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE RBASE_T162B;
GRANT SELECT ON TABLE DEMO_DB.VIS_T162B.SECRETS_T162B TO ROLE RBASE_T162B;
-- The top role is granted NOTHING, except the base role.
CREATE OR REPLACE ROLE RTOP_T162B;
GRANT ROLE RBASE_T162B TO ROLE RTOP_T162B;
-- Put your own user name in here so you can wear the top role.
GRANT ROLE RTOP_T162B TO USER YOUR_USER;SELECT * FROM DEMO_DB.VIS_T162B.SECRETS_T162B ORDER BY SECRET_ID;| SECRET_ID | WHAT |
|---|---|
| 1 | public |
| 2 | private |
Two rows, read as ACCOUNTADMIN. Now let's look at what the top role holds.
SELECT "privilege", "granted_on", "name", "granted_by"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));| privilege | granted_on | name | granted_by |
|---|---|---|---|
| USAGE | ROLE | RBASE_T162B | ACCOUNTADMIN |
One row. USAGE, granted on a ROLE. There is no table anywhere in that grid, and there never will be.
Now let's prove the top role really can read the table
This has to be done carefully, because USE ROLE only sets your primary role. Anything else you hold stays switched on underneath, so a read that works proves nothing by itself.
USE SECONDARY ROLES NONE;
USE ROLE RTOP_T162B;SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;| THE_ROLE | THE_SECONDARY_ROLES |
|---|---|
| RTOP_T162B | {"roles":"","value":""} |
Current role is the top role, and the secondary roles are empty. Nothing is hiding behind this. Now the read.
SELECT SECRET_ID, WHAT
FROM DEMO_DB.VIS_T162B.SECRETS_T162B
ORDER BY SECRET_ID;| SECRET_ID | WHAT |
|---|---|
| 1 | public |
| 2 | private |
Two rows. The top role can read that table, through a privilege it does not hold and was never granted.
So let's ask who can read this table, the obvious way
Back to ACCOUNTADMIN, and let's ask the table itself.
USE ROLE ACCOUNTADMIN;SELECT "privilege", "granted_on", "grantee_name", "granted_by"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "grantee_name", "privilege";| privilege | granted_on | grantee_name | granted_by |
|---|---|---|---|
| OWNERSHIP | TABLE | ACCOUNTADMIN | ACCOUNTADMIN |
| SELECT | TABLE | RBASE_T162B | ACCOUNTADMIN |
Two rows. The OWNERSHIP row, and the base role's SELECT. Let's count how many of them mention the role we just watched read the table.
SELECT COUNT(*) AS ROWS_MENTIONING_THE_TOP_ROLE
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "grantee_name" = 'RTOP_T162B';| ROWS_MENTIONING_THE_TOP_ROLE |
|---|
| 0 |
Zero, and that count is correct. The top role holds no direct grant on this table, so a list of direct grants is right to leave it out.
Both of those grids are true at the same time. One role reading the table, and a list of that table's grants that does not mention it.
The second grid is the one people screenshot and send to an auditor. It is an answer that is true, and incomplete. And it is dangerous rather than merely incomplete, because it does not look partial. It looks like a list.
So where is the fact we are missing?
Not on the table. It is on the role. SHOW GRANTS OF ROLE asks who holds a role, rather than what a role holds.
SHOW GRANTS OF ROLE RBASE_T162B;| created_on | role | granted_to | grantee_name | granted_by |
|---|---|---|---|---|
| 2026-10-06 19:43:20.730000-07:00 | RBASE_T162B | ROLE | RTOP_T162B | ACCOUNTADMIN |
There it is. The base role is granted to the top role.
That is the whole answer, and it is in a different statement, about a different object. Nothing in the table's own grid points at it.
So to answer who can read this table properly, here is what you actually have to do.
- Start at the table.
SHOW GRANTS ON TABLEgives you the roles with a direct grant. - For each of those roles,
SHOW GRANTS OF ROLEgives you who holds it. - For each of those, ask again. And keep going.
Ours was two levels. It could be five, and nothing in the first grid tells you how deep it goes.
The habit to take away is simple. The table tells you where to start, not where to stop.
Now let's see the wider versions of the same question
There are broader forms of SHOW GRANTS, and they are worth seeing for scale rather than for detail.
What do I personally hold?
SHOW GRANTS TO USER YOUR_USER;That grid is not on this page. Every row of it names the account's own user, in more than one column, and the builder here cannot leave a column out of a grid, so the whole grid is left out. Run it on your own account and read your own.
And the widest one there is
SHOW GRANTS ON ACCOUNT;That grid is not on the page either, for the same reason: every row carries the account locator. But the row count is the point, and the row count is safe to quote. It came back with 136 rows.
On an account where almost nothing has been created. A few tables and some test roles. Those rows are account level privileges that came with the account, held by ACCOUNTADMIN.
So 136 is the floor, not the ceiling. Which is the real reason the narrow question is hard to answer by reading: there is a lot of it.
Can we ask all this as a table instead of a command?
Yes. There is a view rather than a command, and a view can be filtered and joined, which is what you want for an audit.
Let's ask it about the grants we made about ninety seconds ago.
SELECT GRANTEE_NAME, PRIVILEGE, GRANTED_ON, NAME, CREATED_ON
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE GRANTEE_NAME LIKE '%\\_T162' ESCAPE '\\'
AND DELETED_ON IS NULL
ORDER BY CREATED_ON;| GRANTEE_NAME | PRIVILEGE | GRANTED_ON | NAME | CREATED_ON |
|---|
No rows. And counted, to be sure.
SELECT COUNT(*) AS MY_GRANTS_THE_VIEW_CAN_SEE
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE GRANTEE_NAME LIKE '%\\_T162' ESCAPE '\\'
AND DELETED_ON IS NULL;| MY_GRANTS_THE_VIEW_CAN_SEE |
|---|
| 0 |
Zero. Now that looks like the view is broken, or like the query is wrong. It is neither, and the way to show which it is was written into the script before it ever ran: ask the same view the same shape of question about a role that is as old as the account.
SELECT COUNT(*) AS GRANTS_TO_ACCOUNTADMIN_THE_VIEW_CAN_SEE
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE GRANTEE_NAME = 'ACCOUNTADMIN'
AND DELETED_ON IS NULL;| GRANTS_TO_ACCOUNTADMIN_THE_VIEW_CAN_SEE |
|---|
| 279 |
So the view works perfectly well. It simply had not caught up with us yet.
SNOWFLAKE.ACCOUNT_USAGE is maintained asynchronously. There is a lag, and the documentation says so. The reason both halves are on this page is that an empty grid on its own tells you nothing: it could be a lag, a wrong filter, or a view you cannot see. One grid cannot tell you which, and two can.
There is a GRANTS_TO_USERS view as well, and that one did have our account level roles in it. Its grid is not on this page, because every row of it carries the user name.
SELECT ROLE, GRANTEE_NAME, GRANTED_BY, CREATED_ON
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
WHERE GRANTEE_NAME = CURRENT_USER()
AND DELETED_ON IS NULL
ORDER BY CREATED_ON
LIMIT 10;What happens when we try this through INFORMATION_SCHEMA?
Two attempts, and both of them failed on a column name rather than on anything to do with grants.
SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE, TABLE_SCHEMA, TABLE_NAME
FROM DEMO_DB.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
WHERE TABLE_NAME = 'SECRETS_T162'
ORDER BY GRANTEE, PRIVILEGE_TYPE;000904 (42000): SQL compilation error: error line 1 at position 46 invalid identifier 'TABLE_SCHEMA'
000904, on TABLE_SCHEMA.
SELECT ROLE_NAME, ROLE_OWNER, IS_INHERITED, IS_GRANTABLE
FROM DEMO_DB.INFORMATION_SCHEMA.APPLICABLE_ROLES
WHERE ROLE_NAME LIKE '%\\_T162' ESCAPE '\\'
ORDER BY ROLE_NAME;000904 (42000): SQL compilation error: error line 1 at position 30 invalid identifier 'IS_INHERITED'
000904 again, on IS_INHERITED.
These are on the page as what they are: two queries that did not run. They prove nothing about what those views can and cannot tell you, and the lesson is only that the column list is not the one I assumed. Check the view's own columns before you write the WHERE clause.
One last thing: a correction to Episode 153
This is a correction to an episode you may have already watched, and it belongs here because the statement involved is SHOW GRANTS.
Here is a procedure that runs SHOW GRANTS and then scans the result on the very next line.
-- A procedure that runs SHOW GRANTS and reads the result on the next line.
CREATE OR REPLACE PROCEDURE PEEK_T162()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
HOW_MANY NUMBER;
BEGIN
SHOW GRANTS ON TABLE DEMO_DB.VIS_T162.SECRETS_T162;
SELECT COUNT(*) INTO :HOW_MANY FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
RETURN :HOW_MANY;
END;
$$;Let's call it.
CALL PEEK_T162();| PEEK_T162 |
|---|
| 2 |
And here is the same pair of statements run in the worksheet instead, for comparison.
SELECT COUNT(*) AS HOW_MANY_GRANTS
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));| HOW_MANY_GRANTS |
|---|
| 2 |
The same number both times. So the pattern works inside a procedure, and in Episode 153 I said it did not.
The grid in that episode is real. The sentence I said over it was too broad. What Episode 153 actually showed is that a procedure cannot see back past the call: the query it was scanning had run outside, before the procedure started, and that one really does come back empty.
A statement the procedure runs itself is visible to the next line. That is this grid.
Inside a procedure, LAST_QUERY_ID() sees only the statements that procedure has run itself. That is narrower than what I said in Episode 153, and more useful. I would rather two posts visibly correct each other than leave the wrong one standing.
And finally, let's check the other half of the inheritance story: if we revoke the base role's SELECT, does the top role lose its reach?
REVOKE SELECT ON TABLE DEMO_DB.VIS_T162B.SECRETS_T162B
FROM ROLE RBASE_T162B;SELECT "privilege", "grantee_name", "granted_by"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "grantee_name", "privilege";| privilege | grantee_name | granted_by |
|---|---|---|
| OWNERSHIP | ACCOUNTADMIN | ACCOUNTADMIN |
One row left on the table, the OWNERSHIP row. Now let's wear the top role again and read.
USE ROLE RTOP_T162B;SELECT SECRET_ID, WHAT
FROM DEMO_DB.VIS_T162B.SECRETS_T162B
ORDER BY SECRET_ID;002003 (42S02): SQL compilation error: Object 'DEMO_DB.VIS_T162B.SECRETS_T162B' does not exist or not authorized. Your primary role RTOP_T162B must have at least one privilege granted on TABLE DEMO_DB.VIS_T162B.SECRETS_T162B.
002003. It does cut the top role off, which is what you would hope: inheritance is live, not a copy taken at grant time.
Everything above was created by this post and removed again afterwards.
-- Putting the account back.
USE ROLE ACCOUNTADMIN;
USE SECONDARY ROLES ALL;
REVOKE SELECT ON TABLE DEMO_DB.VIS_T162B.SECRETS_T162B FROM ROLE RBASE_T162B;
REVOKE USAGE ON DATABASE DEMO_DB FROM ROLE RBASE_T162B;
REVOKE USAGE ON WAREHOUSE COMPUTE_WH FROM ROLE RBASE_T162B;
REVOKE ROLE RBASE_T162B FROM ROLE RTOP_T162B;
REVOKE ROLE RTOP_T162B FROM USER YOUR_USER;
DROP PROCEDURE IF EXISTS PEEK_T162();
DROP ROLE IF EXISTS RBASE_T162B;
DROP ROLE IF EXISTS RTOP_T162B;
DROP SCHEMA IF EXISTS DEMO_DB.VIS_T162B CASCADE;Key points
SHOW GRANTS ON TABLElists the direct grants on that table, correctly, and that is all it claims to do.- A role that reaches the table by inheriting another role does not appear in that list at all.
- So you can have two true grids: one role reading the table, and a list of the table's grants that never mentions it.
- A count of zero rows mentioning that role is also correct. It holds no direct grant.
- The missing fact is in a different statement about a different object:
SHOW GRANTS OF ROLE. - Answering who can read this means walking up from every role you find, and nothing tells you how far up to go.
SHOW GRANTS ON ACCOUNTcame back with 136 rows on an account with almost nothing in it.SNOWFLAKE.ACCOUNT_USAGEis maintained asynchronously. An empty grid there can be a lag, so ask a second question you know the answer to.- A procedure that runs
SHOW GRANTSand scans it on the next line does work. Inside a procedure,LAST_QUERY_ID()sees only that procedure's own statements. - Revoking the inherited privilege does cut the inheriting role off. Inheritance is live.
In the next post we move from roles to the things that hold them: users. Two names that are not interchangeable, forty-four properties, and a default role Snowflake will happily let you set to something that does not exist.
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 two runs: the second one, whose object names end in B, is the one with the secondary roles switched off and proven off, which is the only version of the inheritance read that can carry an argument. The grids that carry the account locator or the account's user name are left off the page rather than edited, and their statements are shown instead; the only figures quoted from them are row counts. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake SHOW GRANTS documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.