Sharing unstructured data with a secure view¶
This topic briefly covers how to share unstructured data files by using a secure view and Secure Data Sharing. With Secure Data Sharing, data providers can share selected objects in a database from one Snowflake account with data consumers in another Snowflake account.
For more information and additional examples, see Working with shares.
Step 1: Create a secure view¶
First, use the CREATE SECURE VIEW command to create a secure view from unstructured data on a stage. A view allows the result of a query to be accessed like a table, and a secure view is specifically designated for data privacy. For more information, see Overview of Views.
You can allow data consumers to retrieve either scoped or pre-signed URLs from the secure view. Scoped URLs provide better security, while pre-signed URLs can be accessed without authorization or authentication. To choose the correct URL for your use case, see Types of URLs available to access files.
Snowflake does not create scoped or pre-signed URLs until a user in a consumer account queries a secure view. This create-on-demand behavior helps you manage the lifetime of pre-signed URLs. To minimize the risk of leaking pre-signed URLs, you can also set a short time interval for the EXPIRATION_TIME parameter of the GET_PRESIGNED_URL function.
The following examples create secure views that allow data consumers to query the scoped or pre-signed URLs for a specific set of staged files. Both views query the RELATIVE_PATH column in a directory table to retrieve the scoped or pre-signed URL.
This example calls the BUILD_SCOPED_FILE_URL function to create a secure view with the scoped URLs for a set of staged files.
The example passes the RELATIVE_PATH column in a directory table on a stage named
mystage to the BUILD_SCOPED_FILE_URL function:
CREATE OR REPLACE SECURE VIEW images_scoped_v AS
SELECT BUILD_SCOPED_FILE_URL(@mystage, relative_path) AS scoped_file_url
You can also create a secure view from a subset of files on a stage so that you do not have to share the entire stage.
The following example creates a secure view of images on a stage where the
client_name field is equal to
CREATE OR REPLACE SECURE VIEW images_for_client_abc AS
SELECT build_scoped_file_url(@myStage, relative_path) AS scoped_file_url
FROM directory(@mystage) d join clients c on d.relative_path = c.relative_path
WHERE c.client_name = 'abc';
This example calls the GET_PRESIGNED_URL function to retrieve the pre-signed URLs for a set of staged files. The example specifies 60 seconds for the EXPIRATION_TIME parameter so that the pre-signed URLs will only be accessible for one minute.
CREATE OR REPLACE SECURE VIEW images_presigned_v AS
SELECT GET_PRESIGNED_URL(@mystage, relative_path, 60) AS presigned_url
Step 2: Create a share¶
Next, create an empty share, and then grant access privileges for your secure view to the share. Doing so adds the secure view object to the share.
CREATE SHARE my_share;
GRANT SELECT ON my_secure_view TO SHARE my_share;
Step 3: Add accounts to the share¶
Finally, you must provide access for consumer accounts to your share by adding the accounts to your share.
The following example uses the ALTER SHARE command to add an account named
consumer_account_1 to the share named
ALTER SHARE my_share ADD ACCOUNTS=consumer_account_1;
After you complete this step, the
consumer_account_1 account can see the share and access the files in the secure view.