Share data in non-secured views

To take full advantage of the performance gains of query optimizations on the views that you share, you can create a share that lets you share non-secure views with other accounts.

Note

When possible, use secure views to enforce the security of your data. See Use secure objects to control data access.

You can only add non-secure views to shares that have been explicitly configured to allow non-secure objects. You cannot share other non-secure objects, such as non-secure functions. See Limitations of sharing non-secure views.

Create a share that allows non-secure objects

To share non-secure views, create a share that allows non-secure objects.

For example, run the following:

CREATE OR REPLACE SHARE allow_non_secure_views
 SECURE_OBJECTS_ONLY=FALSE
 COMMENT="Share views that require query optimization";
Copy

Note

For full syntax, see Syntax for sharing non-secure views in this topic.

After you create a share that allows sharing views, use the GRANT <privilege> … TO SHARE command to grant a view to a share. For example, to grant a view named non_secure_view to the share, run the following:

GRANT SELECT ON VIEW non_secure_view TO SHARE allow_non_secure_views;
Copy

Alternatively, you can grant the SELECT privilege on the view to a database role, and then grant that database role to the share. For example, to grant SELECT privileges on the view non_secure_view to the database role performance_engineer and then grant the role to the share, run the following:

GRANT SELECT ON VIEW non_secure_view TO DATABASE ROLE performance_engineer;
GRANT DATABASE ROLE performance_engineer TO SHARE allow_non_secure_views;
Copy

Convert an existing share to allow sharing non-secure views

You can convert an existing share with secure views into a share that supports sharing non-secure views.

For example, to convert an existing share secure_views_only into one that supports sharing non-secure views, do the following:

  1. Use the SHOW GRANTS command to determine which objects are granted to the share, and which accounts have access to the share, respectively:

    SHOW GRANTS TO SHARE secure_views_only;
SHOW GRANTS OF SHARE secure_views_only;
    Copy

  2. Convert the existing share with one that allows sharing views:

    ALTER SHARE secure_views_only
 SET SECURE_OBJECTS_ONLY = FALSE,
 COMMENT = "Convert to allow sharing non-secure views that require
 query optimization";
    Copy

  3. Optionally convert an existing secure view into a view. In this example, alter secure_view2 into a non-secure view:

    ALTER VIEW secure_view2 UNSET SECURE;
    Copy

Convert a secure view in a share to a non-secure view

If you want to convert an existing secure view into a view, you can do that before or after granting the view to a share.

To convert an existing secure view in a share to a view, the following must be true:

  • The secure view must only be granted to shares that are configured to allow sharing non-secure objects.

  • The secure view cannot be granted to:

    • Database roles granted to shares that do not allow sharing non-secure objects.

    • Shares that do not allow sharing non-secure objects.

For example, for an existing secure view named high_performance_view, unset the SECURE property:

ALTER VIEW high_performance_view UNSET SECURE;
Copy

Alternatively, you can recreate the secure view as a view:

CREATE OR REPLACE VIEW high_performance_view WITH COPY GRANTS;
Copy

Limitations of sharing non-secure views

If you plan to share views, consider the following:

  • After you create a share with the SECURE_OBJECTS_ONLY property set to FALSE, you cannot unset this property or set this property to TRUE.

  • You can only share non-secure views. Other non-secure objects, such as functions, cannot be shared.

Syntax for sharing non-secure views

CREATE [ OR REPLACE ] SHARE <name>
[ SECURE_OBJECTS_ONLY = <boolean> ]
[ COMMENT = '<string_literal>' ]
Copy

Required Parameters

name

Specifies the identifier for the share; must be unique for the account in which the share is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes. For example, "My object". Identifiers enclosed in double quotes are also case-sensitive.

For more information about identifier requirements, see Identifier requirements.

Optional Parameters

SECURE_OBJECTS_ONLY = boolean

Specifies whether allow granting only secure objects, or also allow granting non-secure objects to the share.

Default: true

COMMENT = 'string_literal'

Specifies a comment for the share.

Default: No value

Access control requirements

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege

Object

Notes

CREATE SHARE

Account

Only the ACCOUNTADMIN role has this privilege by default. The privilege can be granted to additional roles as needed.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

For more information about access control requirements for Snowflake Secure Data Sharing specifically, see Enable non-ACCOUNTADMIN roles to perform data sharing tasks.

Usage notes

  • You cannot see the value of the SECURE_OBJECTS_ONLY property when you run SHOW SHARES. Use the COMMENT property to note the value of the SECURE_OBJECTS_ONLY property.

  • The existing notes for CREATE SHARE also apply.

Examples

For an example on how to create a share with non-secure views, see Create a share that allows non-secure objects.

For an example using ALTER SHARE, see Convert an existing share to allow sharing non-secure views.

Language: English