Snowflake SQL Error [3056] [0A000]: SQL compilation error: A view or function being shared cannot reference objects from other databases

0
votes

I am trying to create a secure view, add to my share so that reader account can access it.

My sample code is below:

USE ROLE accountadmin;

create share DEV_SHARE1;

grant usage on database dev_infomart_db to share DEV_SHARE1;
grant usage on schema dev_infomart_db.sch to share DEV_SHARE1;

ALTER SHARE DEV_SHARE1 ADD ACCOUNTS = XXXXXX;

grant reference_usage on database DEV_EDW_DB to share DEV_SHARE1;

GRANT SELECT ON VIEW dev_infomart_db.sch.view1 TO SHARE DEV_SHARE1;

This view dev_infomart_db.sch.view1 is referring to tables/views present in database DEV_EDW_DB.

I referred to this doc from snowflake for sharing data from multiple DBs. https://docs.snowflake.com/en/user-guide/data-sharing-mutiple-db.html

I have followed steps as provided in link above. I am still getting below error, while sharing my view:

SQL Error [3056] [0A000]: SQL compilation error: A view or function being shared cannot reference objects from other databases.

Any help would be much appreciated.

1
snowflake-cloud-data-platform
Please add the view definition (the complete CREATE VIEW statement) to your question - NickW
Are any of those views in dev_edw_db referencing tables from yet another database? - Mike Walton

1 Answers

0
votes

This usually happens when a view contains other views, and some of them referencing objects from another database.

You may use the GET_OBJECT_REFERENCES function to list the objects used by the view:

https://docs.snowflake.com/en/sql-reference/functions/get_object_references.html

Make sure that they do not reference the objects on the other databases.