'Referencing an Imported Database In a Shared View

I have a feeling I know the answer to this question but I don't like it, so I'm hoping somewhere out there can save me from a massive refactor. Here goes.

My Snowflake project is made up of two key databases:

  1. external_sales_data_share - an imported database provided by another Snowflake account
  2. client_facing_database - an internally owned and local database

The client_facing_database contains a View that leverages data from external_sales_data_share. For example:

CREATE OR REPLACE SECURE VIEW client_facing_database.external.data.sales_data AS
SELECT 
col1 field_1,
col3 field_2
FROM external_sales_data_share.data.sales_data
where client_id = xxxx;

The View works as expected, no problems there. However, when I attempt to share the View with my client I am unable to because my View references an imported database.

//Create Share    
CREATE OR REPLACE SHARE CLIENT_SHARE;

// Allow share to access the database and schema of the views    
grant usage on database client_facing_database to share CLIENT_SHARE;
grant usage on schema client_facing_database.data to share CLIENT_SHARE;

The above works fine. When I try to add the specific view to the share is when I receive an error.

// Command to add View to share
grant select on view client_facing_database.data.sales_data to share CLIENT_SHARE;


// Error returned
"A view or function being shared cannot reference objects from other databases."

I understand that the above error is expected and that I should grant reference_usage to my share so that it is allowed to read from tables being leveraged in my View. However, the following command, which I believe is correct returns an error.

 // Command to allow the share access to the reference database (which is share/imported)
 grant reference_usage on database external_sales_data_share to share CLIENT_SHARE;

 // The error message returned
 "Granting individual privileges on imported database is not allowed. Use 'GRANT IMPORTED PRIVILEGES' instead."

Digging into the Snowflake documentation it appears as if what I'm trying to do is not possible, since their suggestion of GRANTING IMPORTED PRIVILEGES is done at the role, not the share level.

SO.... what are my questions?

  1. Am I indeed correct that one cannot share a View that references an imported database?
  2. Is there a work-around for using an imported database in a View that will be shared?

Thanks all for the help!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source