Welcome To TechBrothersIT: Snowflake SQL Tutorial 169 - One Statement Took A Role From 0 Grants To 673

Snowflake SQL Tutorial 169 - One Statement Took A Role From 0 Grants To 673

Snowflake SQL · Episode 169

In this post we are going to cover listings and the Snowflake Marketplace.

A listing is how one Snowflake account offers a database to other accounts. The account that publishes it is the provider. The account that takes it is the consumer, and what the consumer ends up with is a database that appears in their account, ready to read, that nobody in their account created.

We are not going to publish anything. We are going to show you that you already have the consumer side, because almost every Snowflake account does. We will start with the databases this account already has and build it up step by step.

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

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

On this page

Let's create a table of our own first
Let's start with the shares this account can see
Now let's see which databases came from somewhere else
Let's look at the shared database on its own
How big is a database that arrived with the account?
Now let's see who has been granted what on it
Reading it works perfectly well
Now let's try to put something of ours inside it
Can ACCOUNTADMIN drop it?
Now the loose end: GRANT IMPORTED PRIVILEGES
What can that role actually read?
Four statements I expected to be refused
One last thing: ACCOUNTADMIN is not the top of everything
And the clean-up

Let's create a table of our own first

Everything in this post is about databases we did not make. So before we look at one of those, let's make something we did, and keep it beside us to compare against.

Sample data

-- Sample data for this post.
-- A schema and a table of our own, so we have something we DO own to
-- compare the shared databases against.
USE DATABASE DEMO_DB;

CREATE OR REPLACE SCHEMA DEMO_DB.LIST_T169
    COMMENT = 'episode 169 - a database of my own, to compare with a shared one';

USE SCHEMA DEMO_DB.LIST_T169;

CREATE OR REPLACE TABLE DEMO_DB.LIST_T169.MINE_T169 (
    ID     NUMBER,
    LABEL  VARCHAR(30)
);

INSERT INTO DEMO_DB.LIST_T169.MINE_T169 (ID, LABEL)
VALUES (1, 'a table I own'), (2, 'and can write to');

Now let's read it back.

Query

SELECT * FROM DEMO_DB.LIST_T169.MINE_T169 ORDER BY ID;

Result2 rows · 2 columns

ID	LABEL
1	a table I own
2	and can write to

Two rows, in a table we created, in a schema we created, in a database we own. We could write to it, and we could drop it. Hold on to that, because the next few sections are about a database where none of those three things is true.

Let's start with the shares this account can see

SHOW SHARES lists every share this account is attached to, in either direction.

Query

SHOW SHARES;

That gives back a wide grid, so let's pull the columns that matter out of it with RESULT_SCAN.

Query

SELECT "kind", "name", "database_name", "to", "owner", "comment"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "kind", "name";

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 the kind column says INBOUND on both. Inbound means they are coming in to us. We did not make them and we are not sending them anywhere.

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, because there is only one kind here. Nothing on this account is outbound, which is another way of saying that this account has never published anything.

Empty is not NULL

Have a look at the empty cells in the grid above. Those are not NULL. SHOW reports a missing value as an empty string, and this page marks an empty string as an empty cell rather than printing a word into it. Later on we will read the same kind of information out of a view instead, and there the missing values come back as real NULLs. The two are not the same thing and the grids do not pretend they are.

Now let's see which databases came from somewhere else

Those two shares have to arrive as something. Let's list every database on the account and look at where each one came from.

Query

SHOW DATABASES;

Query

SELECT "name", "origin", "owner", "options", "kind", "comment"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result5 rows · 6 columns · showing first 4 · scroll →

Five databases. The fifth row is a personal database Snowflake creates for the user, and it is named after the user, so it is left off this page.

name	origin	owner	kind	comment
DEMO_DB		ACCOUNTADMIN	STANDARD
SNOWFLAKE	SNOWFLAKE.ACCOUNT_USAGE		APPLICATION
SNOWFLAKE_LEARNING_DB		ACCOUNTADMIN	STANDARD	Created by Snowflake during account provisioning
SNOWFLAKE_SAMPLE_DATA	SFSALESSHARED.SFC_SAMPLES_AWSUSEAST2.SAMPLE_DATA	ACCOUNTADMIN	IMPORTED DATABASE	Preloaded TPCH Data provided by Snowflake during account provisioning

The column to read is origin. On most rows it is empty. On two of them it is not, and what it holds is a fully qualified name: a provider account, a schema and a share.

Query

SELECT COUNT(*) AS DATABASES_WITH_AN_ORIGIN
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 WHERE NULLIF(TRIM("origin"), '') IS NOT NULL;

Result1 row · 1 column

DATABASES_WITH_AN_ORIGIN
2

Two databases with an origin, out of five. The kind column says it a second way. One of them is an APPLICATION and the other is an IMPORTED DATABASE.

That is what a listing delivers. A database that is simply there, in your account, with somebody else's name in its origin column.

Let's look at the shared database on its own

The one we have been using all through this level is SNOWFLAKE. Let's ask for just that row.

Query

SHOW DATABASES LIKE 'SNOWFLAKE';

Query

SELECT "name", "origin", "owner", "options", "retention_time", "kind", "comment"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 7 columns · scroll →

name	origin	owner	options	retention_time	kind	comment
SNOWFLAKE	SNOWFLAKE.ACCOUNT_USAGE			0	APPLICATION

Three things in that row are worth saying out loud. The origin is SNOWFLAKE.ACCOUNT_USAGE. The owner is empty. And retention_time is 0.

Nobody on this account owns it. That empty owner column is the honest answer, not a missing value.

How big is a database that arrived with the account?

Let's find out what is inside it.

Query

SHOW SCHEMAS IN DATABASE SNOWFLAKE;

Query

SELECT "name", "owner", "options", "comment"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result35 rows · 4 columns · showing first 6

The first six, alphabetically.

name	owner	options	comment
ACCESS_INTELLIGENCE	SNOWFLAKE
ACCESS_INTELLIGENCE_INTERNAL
ACCOUNT_USAGE
ALERT
BCR_ROLLOUT
BILLING

Query

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

Result1 row · 1 column

SCHEMAS_IN_THE_SHARED_DATABASE
35

Thirty five schemas, in a database that arrived with the account and cost nothing. Now let's take one of those schemas, ACCOUNT_USAGE, and count what is in it.

Query

SHOW VIEWS IN SCHEMA SNOWFLAKE.ACCOUNT_USAGE;

Query

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

Result1 row · 1 column

VIEWS_IN_ACCOUNT_USAGE
211

Two hundred and eleven views in that one schema. Let's print a few of their names.

Query

SELECT "name", "is_secure", "kind"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name"
 LIMIT 12;

Query failed — Snowflake returned an error

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

That one is my own mistake and it is staying on the page. SHOW VIEWS has no column called kind, so asking for it is an invalid identifier. The names come back fine if you ask for columns that exist.

Now let's see who has been granted what on it

SHOW GRANTS ON DATABASE works on a shared database just as it does on one of ours.

Query

SHOW GRANTS ON DATABASE SNOWFLAKE;

Query

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

Result165 rows · 4 columns · showing first 6

The first six of them, ordered by privilege.

privilege	granted_on	grantee_name
USAGE	DATABASE	ACCESS_INTELLIGENCE_ADMIN
USAGE	DATABASE	ACCESS_INTELLIGENCE_VIEWER
USAGE	DATABASE	AI_OBSERVABILITY_ADMIN
USAGE	DATABASE	AI_OBSERVABILITY_EVENTS_LOOKUP
USAGE	DATABASE	AI_OBSERVABILITY_READER
USAGE	DATABASE	ANOMALY_INSIGHTS_ADMIN

Query

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

Result1 row · 1 column

GRANTS_ON_THE_SHARED_DATABASE
165

A hundred and sixty five rows. Every single one of them is USAGE on the database, granted to a built-in application role.

And look at granted_by. It is empty on all of them. Nobody on this account granted any of this. It came with the share.

Reading it works perfectly well

Being unable to own something is not the same as being unable to read it. Reading is the entire point of a listing.

Query

SELECT COUNT(*) AS ROWS_I_CAN_READ
  FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES;

Result1 row · 1 column

ROWS_I_CAN_READ
12

Twelve rows out of one of those views, with no trouble at all. Let's look at some of them.

Query

SELECT DATABASE_NAME, DATABASE_OWNER, DELETED IS NULL AS STILL_THERE
  FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES
 ORDER BY DATABASE_NAME
 LIMIT 10;

Result10 rows · 3 columns

DATABASE_NAME	DATABASE_OWNER	STILL_THERE
CLONEDB_T134	ACCOUNTADMIN	FALSE
CLONEDB_T134	ACCOUNTADMIN	FALSE
CLONEDB_T134	ACCOUNTADMIN	FALSE
DDL_DEMO_T98	NULL	FALSE
DDL_DEMO_T98	NULL	FALSE
DEMO_DB	ACCOUNTADMIN	TRUE
NO_PUBLIC_T99	NULL	FALSE
PUBLIC_PROBE_T99	NULL	FALSE
SNOWFLAKE	NULL	TRUE
SNOWFLAKE_LEARNING_DB	ACCOUNTADMIN	TRUE

Two things in that grid. First, DATABASE_OWNER says NULL on some rows, and that is a real NULL this time, because we read it out of a view rather than out of a SHOW.

Second, look at the names. CLONEDB_T134 three times. DDL_DEMO_T98 twice. Those are databases this course created and destroyed many episodes ago, and STILL_THERE reads FALSE on them.

So twelve rows for an account with five databases, because every create and every drop is its own row and nothing is ever removed. ACCOUNT_USAGE is a history, not a picture. That matters for every count you ever take from it, and we will come back to it properly in Episode 171.

Now let's try to put something of ours inside it

We can read it. Let's see whether we can write to it. First a schema of our own, in the shared database.

Query

CREATE SCHEMA SNOWFLAKE.MINE_T169;

Query failed — Snowflake returned an error

003540 (42501): SQL execution error: Creating schema on shared database 'SNOWFLAKE' is not allowed.

003540. Read the sentence: creating a schema on a shared database is not allowed. That is not a privilege message. It is a statement about what kind of object that database is.

Let's try a table instead, in a schema that already exists in there.

Query

CREATE TABLE SNOWFLAKE.ACCOUNT_USAGE.MINE_T169 (ID NUMBER);

Query failed — Snowflake returned an error

003540 (42501): SQL execution error: Creating table on shared database 'SNOWFLAKE' is not allowed.

The same code and the same answer. Now let's check that neither of them left anything behind.

Query

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

Result1 row · 1 column

SCHEMAS_OF_MINE_IN_THE_SHARED_DATABASE
0

Nothing of ours in there, which is what we would want to know after two failed attempts at creating something.

Can ACCOUNTADMIN drop it?

This is the one everybody wants to try. We are ACCOUNTADMIN, which is the most powerful role on the account.

Query

DROP DATABASE SNOWFLAKE;

Query failed — Snowflake returned an error

003001 (42501): SQL access control error:
Insufficient privileges to operate on database 'SNOWFLAKE'. Your primary role ACCOUNTADMIN must have OWNERSHIP granted on DATABASE SNOWFLAKE.

003001, and the message names exactly what is missing: our primary role must have OWNERSHIP granted on that database.

ACCOUNTADMIN does not own it. Nobody on this account does. That is what inbound means.

Query

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

Result1 row · 1 column

SHARED_DATABASE_STILL_THERE
1

Query

SELECT COUNT(*) AS ROWS_I_CAN_STILL_READ
  FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES;

Result1 row · 1 column

ROWS_I_CAN_STILL_READ
12

Still there, and still readable. The same twelve rows as before we tried.

Now the loose end: GRANT IMPORTED PRIVILEGES

Back in Episode 161 we tried to grant a privilege on this database to a role, and Snowflake refused and told us to use a different verb. Let's finally do what it said.

First a role with nothing on it at all.

Setup

-- A brand new role, and the two grants it needs before it can do anything.
CREATE OR REPLACE ROLE RIMP_T169
    COMMENT = 'episode 169 - holds only what IMPORTED PRIVILEGES gives it';

GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE RIMP_T169;

-- Your own user name goes here. Mine is not on this page.
GRANT ROLE RIMP_T169 TO USER YOUR_USER_NAME;

Let's check what a brand new role holds before we give it anything on the shared database.

Query

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

Result1 row · 1 column

GRANTS_ON_A_NEW_ROLE
0

Nought grants showing. Now the statement Episode 161 was refused for.

Query

GRANT USAGE ON DATABASE SNOWFLAKE TO ROLE RIMP_T169;

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 verb we should be using instead. So let's use it.

Query

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE RIMP_T169;

Result1 row · 1 column

status
Statement executed successfully.

Now let's ask the same question we asked a moment ago.

Query

SHOW GRANTS TO ROLE RIMP_T169;

Query

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

Result673 rows · 4 columns · showing first 8

The first eight, ordered by what they are granted on.

privilege	granted_on	name
INSTANTIATE	CLASS	SNOWFLAKE.CORE.BUDGET
INSTANTIATE	CLASS	SNOWFLAKE.CORE.ORG_BUDGET
INSTANTIATE	CLASS	SNOWFLAKE.CORE.QUOTA
INSTANTIATE	CLASS	SNOWFLAKE.ML.DOCUMENT_INTELLIGENCE
INSTANTIATE	CLASS	SNOWFLAKE.WORKLOAD_INSIGHTS.ANOMALY_INSIGHTS
INSTANTIATE	CLASS	SNOWFLAKE.WORKLOAD_INSIGHTS.COST_INSIGHTS
INSTANTIATE	CLASS	SNOWFLAKE.WORKLOAD_OPTIMIZATION.PERFORMANCE_EXPLORER
USAGE	CLASS	SNOWFLAKE.CORE.BUDGET

Query

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

Result1 row · 1 column

GRANTS_AFTER_IMPORTED_PRIVILEGES
673

From nought to six hundred and seventy three, in one statement. Those rows cover views, functions, databases, database roles, procedures, schemas, classes and tags. Everything in that database, in one verb.

Why there is a separate verb for this

GRANT IMPORTED PRIVILEGES is the verb for a database that came from a share. You cannot grant USAGE or SELECT on one piece of it, and that is not a gap in your permissions. It is the only grant that shape of object accepts.

What can that role actually read?

Six hundred and seventy three grants sounds like a lot. Let's wear the role and find out what it buys.

Before we switch, one piece of housekeeping. Secondary roles have to be off, or USE ROLE proves nothing: the session would still be quietly carrying ACCOUNTADMIN alongside the role we are testing.

Query

USE SECONDARY ROLES NONE;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
ACCOUNTADMIN	{"roles":"","value":""}

Empty, so they are off. Now let's put the role on and check again from inside.

Query

USE ROLE RIMP_T169;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
RIMP_T169	{"roles":"","value":""}

The role is on and the secondary roles are still empty. Now let's read the shared database as this role.

Query

SELECT COUNT(*) AS ROWS_THIS_ROLE_CAN_READ
  FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES;

Result1 row · 1 column

ROWS_THIS_ROLE_CAN_READ
12

Twelve rows. The same twelve ACCOUNTADMIN read a few sections ago. This role holds nothing except what one statement gave it, and it can read the account's whole metadata history.

Query

SELECT DATABASE_NAME, DATABASE_OWNER
  FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES
 ORDER BY DATABASE_NAME
 LIMIT 6;

Result6 rows · 2 columns

DATABASE_NAME	DATABASE_OWNER
CLONEDB_T134	ACCOUNTADMIN
CLONEDB_T134	ACCOUNTADMIN
CLONEDB_T134	ACCOUNTADMIN
DDL_DEMO_T98	NULL
DDL_DEMO_T98	NULL
DEMO_DB	ACCOUNTADMIN

Now let's ask it for one table of ours.

Query

SELECT COUNT(*) AS ROWS_IN_MY_DATABASE
  FROM DEMO_DB.PUBLIC.SALES_RAW;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Database 'DEMO_DB' does not exist or not authorized. Your primary role RIMP_T169 must have USAGE or any other privilege granted on DATABASE DEMO_DB.

002003. As far as that role is concerned, our database does not exist. The whole account's metadata, and not one table of ours.

Query

USE ROLE ACCOUNTADMIN;

Result1 row · 1 column

status
Statement executed successfully.

Four statements I expected to be refused

This account has never published anything and has never requested anything from the Marketplace. I marked four statements as ones I expected a trial account to refuse. All four ran.

First, what listings does this account publish?

Query

SHOW LISTINGS;

Result0 rows · 30 columns · scroll →

global_name	name	title	subtitle	profile	created_on	updated_on	published_on	state	review_state	comment	owner	owner_role_type	regions	target_accounts	is_monetized	is_application	is_targeted	is_limited_trial	is_by_request	distribution	is_mountless_queryable	rejected_on	organization_profile_name	uniform_listing_locator	detailed_target_accounts	compliance_badges	share_restrictions	custom_attributes	external_consumers

Nought rows and thirty columns. No listings of ours, and the statement is fully available. An empty grid with thirty column headings is a different answer from an error, and the difference matters.

Second, what is the Marketplace offering?

Query

SHOW AVAILABLE LISTINGS;

That one came back with 4273 rows across 23 columns. The grid is not on this page, because it is thousands of rows of other people's listings and its regions column carries region names. The count is the part that matters: the whole Marketplace, from a trial account, with no setup at all.

Third, data exchanges.

Query

SHOW DATA EXCHANGES;

Result1 row · 10 columns · scroll →

name	created_on	membership_type	display_name	comment	logo	type	listing_approval_enabled	categories_per_listing	show_unpublished_by_admin_reason_enabled
SNOWFLAKE_DATA_MARKETPLACE	2020-04-02 20:19:38.393000-07:00	PROVIDER,CONSUMER	Snowflake Marketplace			PUBLIC	true	1	true

One row. The Snowflake Marketplace itself, created in 2020, and membership_type reads PROVIDER,CONSUMER. Both, on an account that has published nothing.

Fourth, the schema where the money would be recorded.

Query

SHOW VIEWS IN SCHEMA SNOWFLAKE.ORGANIZATION_USAGE;

Query

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

Result1 row · 1 column

VIEWS_IN_ORGANIZATION_USAGE
25

Twenty five views, every one of them secure, including a marketplace disbursement report and a daily paid usage view. The money plumbing, present on an account that has never sold anything to anybody.

Four predictions, all wrong in the generous direction. That is still four wrong predictions, and they are on the page rather than quietly cut.

One last thing: ACCOUNTADMIN is not the top of everything

There are two functions that tell you which organisation an account belongs to and what the account is called inside it: CURRENT_ORGANIZATION_NAME() and CURRENT_ACCOUNT_NAME(). They are worth knowing about, because that pair is how replication and sharing address an account. Our own values are not on this page.

Above the account there is an organisation level, and here is what happens when we ask about it.

Query

SHOW ORGANIZATION ACCOUNTS;

Refused, with 003001. The message says the primary role must have MANAGE ORGANIZATION ACCOUNTS granted on the account, and it quotes the account locator back at us. That is why the error pane is not on this page: the locator is in the message text. The code and the privilege name are the teaching, and those are both here.

ACCOUNTADMIN is not the top of everything. There is a level above it, and this role is not it.

And the clean-up

Let's take the imported privileges back off that role and see what is left.

Query

REVOKE IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE FROM ROLE RIMP_T169;

Result1 row · 1 column

status
Statement executed successfully.

Query

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

Result1 row · 1 column

GRANTS_LEFT_ON_THE_ROLE
1

One grant left, and it is the warehouse we gave it at the start so it could run a query at all. The six hundred and seventy three went out the same way they came in.

Query

DROP ROLE IF EXISTS RIMP_T169;

Result1 row · 1 column

status
RIMP_T169 successfully dropped.

Now let's check the account is as we found it. The shares first.

Query

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

Result2 rows · 4 columns

kind	name	database_name	owner
INBOUND	ACCOUNT_USAGE	SNOWFLAKE
INBOUND	SAMPLE_DATA	SNOWFLAKE_SAMPLE_DATA

The same two inbound shares we opened with. And the databases.

Query

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

Result1 row · 1 column

DATABASES_AT_THE_END
5

Query

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

Result1 row · 1 column

MY_ROLES_LEFT
0

Query

SELECT COUNT(*) AS MY_SCHEMAS_LEFT
  FROM DEMO_DB.INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'LIST_T169';

Result1 row · 1 column

MY_SCHEMAS_LEFT
0

Five databases, the same five as the start, and nothing of ours left behind. Nothing was published, requested, imported or mounted anywhere in this post. We only ever read.

Key points

A listing delivers a database that appears in your account, ready to read, that nobody in your account created.
The origin column on SHOW DATABASES is how you spot one. It holds a provider account, a schema and a share.
APPLICATION and IMPORTED DATABASE are both kinds of inbound database. Most accounts already have two.
Read only means read only. Creating a schema or a table inside a shared database is refused with 003540, which is a statement about the object, not about your privileges.
ACCOUNTADMIN cannot drop one either: 003001, for want of OWNERSHIP. Nobody on the account owns it.
Granting one privilege on an imported database gives 003028. The verb that error names is GRANT IMPORTED PRIVILEGES, and on this account it took one role from nought grants to 673.
A role holding nothing but imported privileges read the whole of ACCOUNT_USAGE and could not see one table of our own.
SHOW LISTINGS, SHOW AVAILABLE LISTINGS, SHOW DATA EXCHANGES and the ORGANIZATION_USAGE views all run on a trial account.
An empty grid is an answer. It tells you the feature exists and you are not using it, which is not what an error tells you.
ACCOUNT_USAGE is a history, not a picture: twelve rows for five databases, because every create and every drop is its own row.
SHOW ORGANIZATION ACCOUNTS is refused to ACCOUNTADMIN. There is a level above the account.

Next in this series

In the next post we look at INFORMATION_SCHEMA, which is not a catalogue, and the same COUNT run by two different roles will show you why.

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. Nothing was published, requested, imported or mounted: every statement either reads or is refused. Grids that identify the account — its locator, region, organisation, account name or user name — are deliberately left off the page, and the post says so each time one is missing. The SHOW DATABASES grids show four of their five rows for the same reason. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake GRANT documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 169 - One Statement Took A Role From 0 Grants To 673

Let's create a table of our own first

Let's start with the shares this account can see

Now let's see which databases came from somewhere else

Let's look at the shared database on its own

How big is a database that arrived with the account?

Now let's see who has been granted what on it

Reading it works perfectly well

Now let's try to put something of ours inside it

Can ACCOUNTADMIN drop it?

Now the loose end: GRANT IMPORTED PRIVILEGES

What can that role actually read?

Four statements I expected to be refused

One last thing: ACCOUNTADMIN is not the top of everything

And the clean-up

Key points

No comments:

Post a Comment