Enabling non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks

.

Related Topics

Snowflake provides two global/account privileges for performing all tasks related to shares:

CREATE SHARE

In a provider account, enables creating and managing shares (for sharing data with consumer accounts).

IMPORT SHARE

In a consumer account, enables viewing the inbound shares shared with the account and creating databases from the shares.

By default, these privileges are granted only to the ACCOUNTADMIN role, ensuring that only account administrators can perform these tasks. However, the privileges can be granted to other roles, enabling the tasks to be delegated to other users in the account.

Attention

Granting CREATE SHARE to other roles makes managing shares more flexible, but also allows users with these roles to expose any objects they own (or on which they have the necessary privileges) to other accounts. This is particularly important to note if you are sharing data from an account that contains sensitive or proprietary data.

Please take this into consideration before granting CREATE SHARE to other roles.

In this Topic:

CREATE SHARE Privilege

If the CREATE SHARE privilege is granted to a role, any user with the role can create a share. As the creator and, therefore owner, of the share, the role can also be used to perform all tasks on the share, including:

  • Granting or revoking privileges on objects to/from the share.

  • Adding or removing consumer accounts to/from the share.

Permissions Required for Granting or Revoking Privileges on Objects to/from a Share

To perform these tasks on a share, the role used to perform the tasks must have the following permissions:

  • OWNERSHIP of the share, and

  • OWNERSHIP or USAGE/SELECT WITH GRANT OPTION on each of the objects to be granted/revoked:

    • Database

    • Tables

    • External tables

    • Secure views

    • Secure materialized views

    • Secure UDFs

If the role does not have the required combination of permissions, performing these tasks on the share will fail.

Note

This is by design, ensuring that sharing data with other accounts requires the bilateral consent/approval of both the owner of the share and the owner of the shared data (by virtue of the objects included in the share). Note that the same role may be the owner of the share and the objects in the share.

Blocking Access to Objects in a Share

Access to objects in a share can be blocked by either the role that owns share or the role that owns the objects:

  • If your role owns the share, you can block access by revoking privileges on the objects from the share.

  • If your role does not own the share, but owns the objects in the share, you can block access by revoking the USAGE or SELECT privileges with CASCADE on the objects from the share owner.

Note

Ownership of a share, as well as the objects in the share, may be either through a direct grant to the role or inherited from a lower-level role in the role hierarchy. For more details, see Role Hierarchy and Privilege Inheritance.

Also, it’s possible for the same role to own a share and the objects in the share.

Granting the Privilege to Another Role

To grant CREATE SHARE to a non-ACCOUNTADMIN role in a provider account, use the ACCOUNTADMIN role and the GRANT <privileges> … TO ROLE command.

For example, to grant the privilege to the SYSADMIN role:

USE ROLE ACCOUNTADMIN;

GRANT CREATE SHARE ON ACCOUNT TO SYSADMIN;

IMPORT SHARE Privilege

If the IMPORT SHARE privilege is granted to a role, any user with the role can perform the following tasks:

  • View all INBOUND shares (shared by provider accounts) and create databases for the shares.

  • View all OUTBOUND shares owned by the role.

Granting the Privilege to Another Role

To grant IMPORT SHARE to a non-ACCOUNTADMIN role in a consumer account, use the ACCOUNTADMIN role and the GRANT <privileges> … TO ROLE command.

For example, to grant the privilege to the SYSADMIN role:

USE ROLE ACCOUNTADMIN;

GRANT IMPORT SHARE ON ACCOUNT TO SYSADMIN;