CODEWITHSUNDEEP
snowflake-cloud-data-platform
Rachel McGuigan
Rachel McGuigan

Reputation: 541

Troubleshoot permissions on a database I am trying to share across two snowflake accounts for same region

I have two Snowflake account and need to clone or copy the databases and tables on the same region.

Solutions that I am trying to replicate:

Data Sharing Intro Reference I read that:

"Snowflake supports using grants to provide granular access control to selected objects (schemas, tables, secure views, and secure UDFs) in the database (i.e., you grant access privileges for one or more specific objects within the database)."

It sounds like I could share a UDF with the database I want to share with the account, but read only features. This is confirmed:

" ...but cannot perform any of the DML tasks that are allowed in a full account (data loading, insert, update, etc.)."

Setting up one account as a provider: 

USE ROLE ACCOUNTADMIN;

CREATE MANAGED ACCOUNT reader_acct1
    ADMIN_NAME = user1 , ADMIN_PASSWORD = 'Sdfed43da!44' ,
    TYPE = READER;

//create share
CREATE SHARE Articlelibary_share;

GRANT USAGE ON DATABASE Snapshots TO SHARE Articlelibary_share;

GRANT USAGE ON SCHEMA Snapshots.public TO SHARE Articlelibary_share;

GRANT SELECT ON TABLE Snapshots.public.Articlelibary_TEST TO SHARE Articlelibary_share;

However the error I am getting the error in my worksheet that says:

SQL compilation error: Database 'SNAPSHOTS' does not exist or not authorized.

What I have found is that when I am in the ACCOUNTADMIN role I can see the snapshot table, however, in the SYSADMIN I cannot see the the Snapshots table.

So, how can I fix the Database/Table permissions so that I can add it to the share?

Upvotes: 0

Views: 305

Answers (1)

Balaji-Snowflake
Balaji-Snowflake

Reputation: 191

The activities of creating a share and allowing access to other accounts has to be performed only by the ACCOUNTADMIN and that is the reason for the error that you are seeing.

From Documentation it is very clear :

https://docs.snowflake.net/manuals/user-guide/data-sharing-gs.html#getting-started-with-secure-data-sharing

To perform the tasks described in this topic, you must use the ACCOUNTADMIN role.

Upvotes: 1

Related Questions