Welcome To TechBrothersIT: Snowflake SQL Tutorial 168 - The One Sharing Statement I Refused To Run, And Why

Snowflake SQL Tutorial 168 - The One Sharing Statement I Refused To Run, And Why

Snowflake SQL · Episode 168

In this post we are going to cover secure data sharing in Snowflake.

A share is a named object that holds grants. You create it, you grant objects into it, and the accounts you add to it can read those objects. Nothing is copied and nothing is moved. A share is a list of what the other side may read.

We will start with the shares this account already has, create one of our own, and fill it up step by step.

There is one statement in this post that was not run, on purpose, and we will get to why. Every product and price below is invented.

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

On this page

First, two shares you already have
Let's create some sample data
Now let's create a share
Now let's fill it, from the top down
Can we put a view in it?
One database per share
Now the statement that is not in this file
And now five refusals, and they are not the same kind of no
Let's take things back out again
What happens when we drop the table a share points at?
Now the names that exist and the names that do not
One last thing: let's put the account back

First, two shares you already have

Before creating anything, let's see what is already there. I had made no share at all at this point.

Query

-- What shares this account already has.
SHOW SHARES;

The full SHOW SHARES grid has a column naming the provider account of each share, so it is not reproduced here. The demo read it straight back with the columns that matter, and that is the grid below.

Result2 rows · 6 columns · scroll →

kind	name	database_name	to	owner	comment
INBOUND	ACCOUNT_USAGE	SNOWFLAKE
INBOUND	SAMPLE_DATA	SNOWFLAKE_SAMPLE_DATA

Two shares, and both of them are INBOUND. One is ACCOUNT_USAGE, whose database is SNOWFLAKE. The other is SAMPLE_DATA, whose database is SNOWFLAKE_SAMPLE_DATA.

You have been using one all along

So the SNOWFLAKE database this whole level has been reading is itself an inbound share. Somebody else's data, sitting in your account, read only. That is the shape to hold on to for the rest of this post.

And let's count them by direction.

Query

SELECT "kind", COUNT(*) AS HOW_MANY
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 GROUP BY "kind"
 ORDER BY "kind";

Result1 row · 2 columns

kind	HOW_MANY
INBOUND	2

One row, INBOUND. There is no OUTBOUND row at all, which means nothing is leaving this account. That is how we want to start.

There is also a pair of functions that give the identifier another account would use to name this one. They are worth knowing by name, so here they are, and their values are deliberately not printed anywhere in this post.

Query

-- The pair of functions that give the identifier another account
-- would use to find this one. The values are not printed in this post.
SELECT CURRENT_ORGANIZATION_NAME() AS THE_ORGANISATION,
       CURRENT_ACCOUNT_NAME()      AS THE_ACCOUNT_NAME;

Let's create some sample data

We need something worth sharing, and something that is deliberately not shared, so we can prove the difference.

Sample data

-- Sample data for this post. Every product and price is invented.
USE DATABASE DEMO_DB;

CREATE OR REPLACE SCHEMA DEMO_DB.SHARED_T168
  COMMENT = 'episode 168 - the objects a share points at, dropped at the end';

USE SCHEMA DEMO_DB.SHARED_T168;

CREATE OR REPLACE TABLE DEMO_DB.SHARED_T168.PRICES_T168 (
  SKU        VARCHAR(12),
  PRODUCT    VARCHAR(40),
  LIST_PRICE NUMBER(10,2),
  REGION     VARCHAR(10)
);

INSERT INTO DEMO_DB.SHARED_T168.PRICES_T168 (SKU, PRODUCT, LIST_PRICE, REGION)
VALUES ('SKU-1001', 'Brass Hinge 40mm',   4.75,  'NORTH'),
       ('SKU-1002', 'Oak Dowel 12mm',     1.20,  'NORTH'),
       ('SKU-1003', 'Steel Bracket L',    8.95,  'SOUTH'),
       ('SKU-1004', 'Copper Washer 8mm',  0.45,  'SOUTH'),
       ('SKU-1005', 'Nylon Spacer 6mm',   0.30,  'EAST'),
       ('SKU-1006', 'Rubber Grommet 10mm',0.85,  'EAST');

-- And a second table that is deliberately never granted to anything.
CREATE OR REPLACE TABLE DEMO_DB.SHARED_T168.COSTS_T168 (
  SKU       VARCHAR(12),
  UNIT_COST NUMBER(10,2)
);

INSERT INTO DEMO_DB.SHARED_T168.COSTS_T168 (SKU, UNIT_COST)
VALUES ('SKU-1001', 2.10),
       ('SKU-1002', 0.55),
       ('SKU-1003', 4.40);

Query

SELECT * FROM DEMO_DB.SHARED_T168.PRICES_T168 ORDER BY SKU;

Result6 rows · 4 columns

SKU	PRODUCT	LIST_PRICE	REGION
SKU-1001	Brass Hinge 40mm	4.75	NORTH
SKU-1002	Oak Dowel 12mm	1.20	NORTH
SKU-1003	Steel Bracket L	8.95	SOUTH
SKU-1004	Copper Washer 8mm	0.45	SOUTH
SKU-1005	Nylon Spacer 6mm	0.30	EAST
SKU-1006	Rubber Grommet 10mm	0.85	EAST

Six rows of prices. And the second table, which nobody outside this account is ever meant to see.

Query

SELECT * FROM DEMO_DB.SHARED_T168.COSTS_T168 ORDER BY SKU;

Result3 rows · 2 columns

SKU	UNIT_COST
SKU-1001	2.10
SKU-1002	0.55
SKU-1003	4.40

Three rows of costs. Only one of these two tables goes anywhere.

Here is the first thing I did not know the answer to. Can a trial account create a share at all?

Query

CREATE OR REPLACE SHARE SHARE_T168
  COMMENT = 'episode 168 - created, read, emptied and dropped. No accounts added.';

Result1 row · 1 column

status
Share SHARE_T168 successfully created.

It can. Everything after this depends on that one status message.

Let's look at it as a row. The wide SHOW SHARES grid names this account's own identifier, so again it is the read-back that is on the page.

Query

-- And the one we just made.
SHOW SHARES LIKE 'SHARE\_T168';

Result1 row · 6 columns · scroll →

kind	name	database_name	to	owner	comment
OUTBOUND	SHARE_T168			ACCOUNTADMIN	episode 168 - created, read, emptied and dropped. No accounts added.

OUTBOUND now, which is the direction we did not have a moment ago. The database_name cell is empty and so is the to cell.

That to column is the whole episode. It lists the accounts that hold this share, and it is empty.

And what an empty share looks like

Query

DESCRIBE SHARE SHARE_T168;

Result0 rows · 3 columns

kind	name	shared_on

Nought rows and three columns. SHOW GRANTS TO SHARE agrees.

Query

SHOW GRANTS TO SHARE SHARE_T168;

Result0 rows · 8 columns · scroll →

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by

Query

SELECT COUNT(*) AS GRANTS_ON_AN_EMPTY_SHARE
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

GRANTS_ON_AN_EMPTY_SHARE
0

So the object exists and its contents are empty. Those are two different facts, and two different statements tell you them.

Now let's fill it, from the top down

You cannot start at the table. A share is filled from the database downwards, and the first grant is usage on the database.

Query

GRANT USAGE ON DATABASE DEMO_DB TO SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT "privilege", "granted_on", "name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "granted_on", "privilege";

Result1 row · 3 columns

privilege	granted_on	name
USAGE	DATABASE	DEMO_DB

One grant, granted on a database. Now does DESCRIBE SHARE show us the table yet?

Query

SELECT "kind", "name", "shared_on"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "kind", "name";

Result1 row · 3 columns

kind	name	shared_on
DATABASE	DEMO_DB	2026-10-07 12:40:50.554000-07:00

No. It shows the database and that is all, because we have not granted the schema and the table lives inside the schema.

So let's grant the schema.

Query

GRANT USAGE ON SCHEMA DEMO_DB.SHARED_T168 TO SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT "privilege", "granted_on", "name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "granted_on", "privilege";

Result2 rows · 3 columns

privilege	granted_on	name
USAGE	DATABASE	DEMO_DB
USAGE	SCHEMA	DEMO_DB.SHARED_T168

Two grants, two levels. And finally the table itself.

Query

GRANT SELECT ON TABLE DEMO_DB.SHARED_T168.PRICES_T168 TO SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT "privilege", "granted_on", "name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "granted_on", "privilege";

Result3 rows · 3 columns

privilege	granted_on	name
USAGE	DATABASE	DEMO_DB
USAGE	SCHEMA	DEMO_DB.SHARED_T168
SELECT	TABLE	DEMO_DB.SHARED_T168.PRICES_T168

Three grants and three levels: database, schema, table, in that order. Note that the table grant is SELECT and the two above it are USAGE.

Query

SELECT COUNT(*) AS GRANTS_IN_THE_SHARE
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

GRANTS_IN_THE_SHARE
3

And DESCRIBE SHARE lists all three, each with the time it was shared on.

Query

SELECT "kind", "name", "shared_on"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "kind", "name";

Result3 rows · 3 columns

kind	name	shared_on
DATABASE	DEMO_DB	2026-10-07 12:40:50.554000-07:00
SCHEMA	DEMO_DB.SHARED_T168	2026-10-07 12:40:52.881000-07:00
TABLE	DEMO_DB.SHARED_T168.PRICES_T168	2026-10-07 12:40:53.942000-07:00

And the table we never granted

Query

SELECT COUNT(*) AS PRIVATE_TABLE_MENTIONS
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 WHERE "name" LIKE '%COSTS\\_T168%' ESCAPE '\\';

Result1 row · 1 column

PRIVATE_TABLE_MENTIONS
0

Nought mentions. It is in the same schema as the shared table, and the schema is in the share, and it is still not in there. A share holds what you put in it and nothing else.

Nothing was copied anywhere by any of those three statements. All we did was write down what the other side may read.

Can we put a view in it?

A view is the usual way to share less than a whole table. Let's make an ordinary one over the shared table.

Query

CREATE OR REPLACE VIEW DEMO_DB.SHARED_T168.NORTH_PRICES_T168 AS
SELECT SKU, PRODUCT, LIST_PRICE
  FROM DEMO_DB.SHARED_T168.PRICES_T168
 WHERE REGION = 'NORTH';

Result1 row · 1 column

status
View NORTH_PRICES_T168 successfully created.

Query

SELECT * FROM DEMO_DB.SHARED_T168.NORTH_PRICES_T168 ORDER BY SKU;

Result2 rows · 3 columns

SKU	PRODUCT	LIST_PRICE
SKU-1001	Brass Hinge 40mm	4.75
SKU-1002	Oak Dowel 12mm	1.20

Two rows, north prices only. A perfectly normal object. Let's grant it to the share.

Query

GRANT SELECT ON VIEW DEMO_DB.SHARED_T168.NORTH_PRICES_T168 TO SHARE SHARE_T168;

Query failed — Snowflake returned an error

090838 (0A000): Non-secure object can only be granted to shares with "secure_objects_only" property set to false.

090838. A non-secure object can only go into a share whose secure_objects_only property is false, and the default is true. So an ordinary view is not allowed in.

Query

SELECT COUNT(*) AS GRANTS_AFTER_THE_VIEW_ATTEMPT
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

GRANTS_AFTER_THE_VIEW_ATTEMPT
3

Still three. The refusal landed nothing, so there is no half-granted state to clean up.

Now the same view with one word added

Query

CREATE OR REPLACE SECURE VIEW DEMO_DB.SHARED_T168.SOUTH_PRICES_T168 AS
SELECT SKU, PRODUCT, LIST_PRICE
  FROM DEMO_DB.SHARED_T168.PRICES_T168
 WHERE REGION = 'SOUTH';

Result1 row · 1 column

status
View SOUTH_PRICES_T168 successfully created.

Let's check what the catalogue makes of it. My reader for this asked for a column that does not exist, so here is that mistake first.

Query

SELECT "name", "is_secure", "kind"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 28
invalid identifier '"kind"'

000904, invalid identifier. SHOW VIEWS has no kind column. The grid the statement was trying to trim is the one underneath, and is_secure is in it.

Query

SHOW VIEWS LIKE 'SOUTH\\_PRICES\\_T168' IN SCHEMA DEMO_DB.SHARED_T168;

Result1 row · 12 columns · scroll →

is_secure is the ninth column — scroll right

created_on	name	reserved	database_name	schema_name	owner	comment	text	is_secure	is_materialized	owner_role_type	change_tracking
2026-10-07 12:40:58.704000-07:00	SOUTH_PRICES_T168		DEMO_DB	SHARED_T168	ACCOUNTADMIN		CREATE OR REPLACE SECURE VIEW DEMO_DB.SHARED_T168.SOUTH_PRICES_T168 AS SELECT SKU, PRODUCT, LIST_PRICE FROM DEMO_DB.SHARED_T168.PRICES_T168 WHERE REGION = 'SOUTH'	true	false	ROLE	OFF

true. Now let's grant that one.

Query

GRANT SELECT ON VIEW DEMO_DB.SHARED_T168.SOUTH_PRICES_T168 TO SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT "privilege", "granted_on", "name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "granted_on", "privilege";

Result4 rows · 3 columns

privilege	granted_on	name
USAGE	DATABASE	DEMO_DB
USAGE	SCHEMA	DEMO_DB.SHARED_T168
SELECT	TABLE	DEMO_DB.SHARED_T168.PRICES_T168
SELECT	VIEW	DEMO_DB.SHARED_T168.SOUTH_PRICES_T168

Four grants, and the new one reads VIEW under granted_on. One word in the CREATE statement was the whole difference.

Here is the other structural rule, and a refusal explains it better than I could. First, look at the share's database column now.

Result1 row · 4 columns

kind	name	database_name	to
OUTBOUND	SHARE_T168	DEMO_DB

It was empty when we created the share. It reads DEMO_DB now, after the first grant. The share took its database from what we put in it.

So let's try to grant something that is not in that database.

Query

GRANT USAGE ON WAREHOUSE COMPUTE_WH TO SHARE SHARE_T168;

Query failed — Snowflake returned an error

003033 (0A000): SQL compilation error:
Warehouse 'COMPUTE_WH' does not belong to the database that is being shared.

003033, and the message says it plainly: the warehouse does not belong to the database that is being shared. A share holds exactly one database.

And the same code as Episode 161

Let's try to grant usage on the inbound SNOWFLAKE database to our share.

Query

GRANT USAGE ON DATABASE SNOWFLAKE TO SHARE SHARE_T168;

Query failed — Snowflake returned an error

003028 (0A000): SQL compilation error:
Granting individual privileges on imported database is not allowed. Use 'GRANT IMPORTED PRIVILEGES' instead.

003028. Granting individual privileges on an imported database is not allowed, and the message names the statement to use instead by name. Episode 161 ran into the same code and could not act on it. The next post finally can.

Query

SELECT COUNT(*) AS GRANTS_AFTER_THE_SECOND_DATABASE
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

GRANTS_AFTER_THE_SECOND_DATABASE
4

Four. Neither of those two refusals changed anything.

Now the statement that is not in this file

There is one statement left to make a share do anything useful, and it is the moment data actually leaves the account.

Not run

-- *** NOT RUN. This is the statement that sends the share
-- *** to another account, and there is no second account here.
ALTER SHARE SHARE_T168 ADD ACCOUNTS = <another account's identifier>;

It is not in the demo file. There is no second account here, and a mistyped account identifier is somebody else's account. So it sits in a forbidden list that the demo runner enforces, with no marker that overrides it.

Marking it as expected to fail would not have helped, because the statement would still have run. The only safe version of that test is not running it.

And the post loses nothing, because three grids show the share went nowhere. The first is the to column.

Result1 row · 5 columns

kind	name	to	owner	comment
OUTBOUND	SHARE_T168		ACCOUNTADMIN	episode 168 - created, read, emptied and dropped. No accounts added.

Empty. The second is the statement that asks who holds the share.

Query

SHOW GRANTS OF SHARE SHARE_T168;

Result0 rows · 4 columns

created_on	share	granted_to	account

Nought rows and four columns. And the third is the count of that.

Query

SELECT COUNT(*) AS ACCOUNTS_HOLDING_MY_SHARE
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

ACCOUNTS_HOLDING_MY_SHARE
0

Nobody holds this share. It was created, filled, read, emptied and dropped, and no account was ever added to it.

And now five refusals, and they are not the same kind of no

Let's try to grant a privilege other than SELECT on a table.

Query

GRANT INSERT ON TABLE DEMO_DB.SHARED_T168.PRICES_T168 TO SHARE SHARE_T168;

Query failed — Snowflake returned an error

003104 (42501): Grant not executed: Operation not supported on a SHARE object.

003104, operation not supported on a share object. That is a permission answer: the statement is valid and the thing it asks for is not allowed.

Now let's try to grant a role to a share.

Query

GRANT ROLE PUBLIC TO SHARE SHARE_T168;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 21 unexpected 'SHARE'.

001003, a syntax error at the word SHARE. That one is not a permission problem at all. There is no such statement in the language, and the parser stopped before anything was checked.

The warehouse refusal in the last section was the third of these five. The last two are a table that does not exist and a share that does not exist.

Query

-- A share that does not exist, three different ways.
GRANT SELECT ON TABLE DEMO_DB.SHARED_T168.PRICES_T168
   TO SHARE NO_SUCH_SHARE_T168;

DESCRIBE SHARE NO_SUCH_SHARE_T168;

DROP SHARE NO_SUCH_SHARE_T168;

The table one comes back 002003, naming the table.

Query

GRANT SELECT ON TABLE DEMO_DB.SHARED_T168.NO_SUCH_TABLE_T168 TO SHARE SHARE_T168;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Table 'DEMO_DB.SHARED_T168.NO_SUCH_TABLE_T168' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE DEMO_DB.SHARED_T168.NO_SUCH_TABLE_T168.

The share one is also 002003, and the message says the share does not exist or is not authorised. It is not shown as an error pane here, and neither are the DESCRIBE and DROP versions of it further down, because Snowflake qualifies a share name with this account's own identifier when it quotes it back.

Why three panes are missing from this section

An error pane on this site is printed exactly as the message came back rather than edited, so a message that names the account cannot be shown at all. The code and the sentence are above; only the pane is missing.

And the count after all five.

Query

SELECT COUNT(*) AS GRANTS_STILL_IN_THE_SHARE
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

GRANTS_STILL_IN_THE_SHARE
4

Four, which is what it was before any of them. The share is completely untouched by the lot.

Let's take things back out again

REVOKE is the other direction, and it works one grant at a time.

Query

REVOKE SELECT ON TABLE DEMO_DB.SHARED_T168.PRICES_T168 FROM SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully. 1 objects affected.

Query

SELECT "privilege", "granted_on", "name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "granted_on", "privilege";

Result3 rows · 3 columns

privilege	granted_on	name
USAGE	DATABASE	DEMO_DB
USAGE	SCHEMA	DEMO_DB.SHARED_T168
SELECT	VIEW	DEMO_DB.SHARED_T168.SOUTH_PRICES_T168

Four became three, and the table entry is the one that went. Let's take the rest out too.

Query

REVOKE SELECT ON VIEW DEMO_DB.SHARED_T168.SOUTH_PRICES_T168 FROM SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully. 1 objects affected.

Query

REVOKE USAGE ON SCHEMA DEMO_DB.SHARED_T168 FROM SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully. 1 objects affected.

Query

REVOKE USAGE ON DATABASE DEMO_DB FROM SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully. 1 objects affected.

Query

SHOW GRANTS TO SHARE SHARE_T168;

Result0 rows · 8 columns · scroll →

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by

Query

SELECT COUNT(*) AS GRANTS_IN_THE_EMPTIED_SHARE
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

GRANTS_IN_THE_EMPTIED_SHARE
0

Nought grants, and the share object still exists. Emptying a share is not the same as dropping it.

Let's put the three grants back first.

Query

GRANT USAGE ON DATABASE DEMO_DB TO SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully.

Query

GRANT USAGE ON SCHEMA DEMO_DB.SHARED_T168 TO SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully.

Query

GRANT SELECT ON TABLE DEMO_DB.SHARED_T168.PRICES_T168 TO SHARE SHARE_T168;

Result1 row · 1 column

status
Statement executed successfully.

And a probe of mine that was wrong

I wrote a line to confirm the share held three grants again before the next test.

Query

SELECT COUNT(*) AS GRANTS_BEFORE_THE_DROP
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

GRANTS_BEFORE_THE_DROP
1

One. The share held three at that moment, so that number is not about the share at all. The statement above it was a GRANT, and a GRANT returns one status row, so RESULT_SCAN handed my count that one row to count.

The worst kind of wrong answer

It did not fail. It came back with a plausible number about nothing, which is worse than failing. A bare COUNT(*) off RESULT_SCAN(LAST_QUERY_ID()) always returns something, so point it at a statement whose result you actually meant to count.

Now the drop

Query

DROP TABLE DEMO_DB.SHARED_T168.PRICES_T168;

Result1 row · 1 column

status
PRICES_T168 successfully dropped.

successfully dropped. No refusal, no warning, and no mention of the share anywhere in that message.

Query

SELECT "privilege", "granted_on", "name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "granted_on", "privilege";

Result2 rows · 3 columns

privilege	granted_on	name
USAGE	DATABASE	DEMO_DB
USAGE	SCHEMA	DEMO_DB.SHARED_T168

Query

SELECT COUNT(*) AS GRANTS_AFTER_THE_TABLE_WENT
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

GRANTS_AFTER_THE_TABLE_WENT
2

Two grants where there were three. The table entry is simply gone, and DESCRIBE SHARE agrees.

Query

SELECT "kind", "name", "shared_on"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "kind", "name";

Result2 rows · 3 columns

kind	name	shared_on
DATABASE	DEMO_DB	2026-10-07 12:41:08.113000-07:00
SCHEMA	DEMO_DB.SHARED_T168	2026-10-07 12:41:08.332000-07:00

What protects what

Four posts, four answers to the same shape of question.
Episode 165: drop an attached masking policy — refused, 003531.
Episode 166: drop an attached row access policy — refused, 003531.
Episode 167: drop a tag that objects are using — dropped, and the references go with it.
Episode 168: drop the table a share points at — dropped, and the share quietly forgets it.

Now the names that exist and the names that do not

Let's create a share with a name that is already taken.

Query

CREATE SHARE SHARE_T168 COMMENT = 'episode 168 - should not appear';

Query failed — Snowflake returned an error

002002 (42710): SQL compilation error:
Object 'SHARE_T168' already exists.

002002. With IF NOT EXISTS it succeeds instead.

Query

CREATE SHARE IF NOT EXISTS SHARE_T168
  COMMENT = 'episode 168 - should not appear';

Result1 row · 1 column

status
SHARE_T168 already exists, statement succeeded.

Result1 row · 2 columns

name	comment
SHARE_T168	episode 168 - created, read, emptied and dropped. No accounts added.

And the comment is the original one, not the one in the statement we just ran.

The DESCRIBE and DROP of a share that is not there both come back 002003, with the same sentence as the grant in the refusals section. Those two panes are the other ones left off this page, for the reason given there. With IF EXISTS the drop goes quiet.

Query

DROP SHARE IF EXISTS NO_SUCH_SHARE_T168;

Result1 row · 1 column

status
Drop statement executed successfully (NO_SUCH_SHARE_T168 already dropped).

One last thing: let's put the account back

Our share, the views, the tables and the schema are all dropped. Here is what is left.

Query

-- What shares this account already has.
SHOW SHARES;

Query

SELECT COUNT(*) AS MY_SHARES_LEFT
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 WHERE "name" LIKE '%SHARE\\_T168%' ESCAPE '\\';

Result1 row · 1 column

MY_SHARES_LEFT
0

Nothing of ours. And the two inbound shares are the same two the post opened with, which the read-back below shows.

Result2 rows · 4 columns

kind	name	database_name	owner
INBOUND	ACCOUNT_USAGE	SNOWFLAKE
INBOUND	SAMPLE_DATA	SNOWFLAKE_SAMPLE_DATA

Both still INBOUND, both still pointing at the databases they came with. They were read on this page and never written to, and no account was ever added to anything.

Key points

A share is a named object that holds grants. Nothing is copied and nothing moves.
Two inbound shares already exist on a fresh account, and the SNOWFLAKE database is one of them.
You fill a share from the top down: usage on the database, usage on the schema, then SELECT on the table. DESCRIBE SHARE shows nothing below the level you have granted.
A table you never granted is simply absent from the share, even when its schema is in there.
A share holds exactly one database, and 003033 on a warehouse is the refusal that says so.
An ordinary view is 090838. A SECURE VIEW goes straight in.
003104 on GRANT INSERT is a privilege answer. 001003 on GRANT ROLE ... TO SHARE is a grammar answer. The second is not a permission problem at all.
003028 on an imported database names the statement to use instead, which is the same answer Episode 161 got.
Dropping a table a share points at succeeds, and the share silently loses that grant.
A bare COUNT(*) off RESULT_SCAN counts whatever the last statement returned, including a single status row.

Next in this series

In the next post we go to the other side of a share: listings and the marketplace, starting from the inbound share this account already had.

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. ALTER SHARE ... ADD ACCOUNTS was never run, and the empty to column, the empty SHOW GRANTS OF SHARE and the closing count are on this page as the evidence. This account's organisation name, account name and locator are kept off the page, which is why some wide SHOW SHARES grids appear here as the read-back the demo ran next and three refusals are described rather than shown; that is said where it happens. Every product and price is invented. Everything created here was dropped here. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake CREATE SHARE documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 168 - The One Sharing Statement I Refused To Run, And Why

First, two shares you already have

Let's create some sample data

And what an empty share looks like

Now let's fill it, from the top down

And the table we never granted

Can we put a view in it?

Now the same view with one word added

And the same code as Episode 161

Now the statement that is not in this file

And now five refusals, and they are not the same kind of no

Let's take things back out again

And a probe of mine that was wrong

Now the drop

Now the names that exist and the names that do not

One last thing: let's put the account back

Key points

No comments:

Post a Comment

Label

Snowflake SQL Tutorial 168 - The One Sharing Statement I Refused To Run, And Why

First, two shares you already have

Let's create some sample data

Now let's create a share

And what an empty share looks like

Now let's fill it, from the top down

And the table we never granted

Can we put a view in it?

Now the same view with one word added

One database per share

And the same code as Episode 161

Now the statement that is not in this file

And now five refusals, and they are not the same kind of no

Let's take things back out again

What happens when we drop the table a share points at?

And a probe of mine that was wrong

Now the drop

Now the names that exist and the names that do not

One last thing: let's put the account back

Key points

No comments:

Post a Comment