Categories:

Database, Schema, & Share DDL

ALTER SHARE

Snowflake logo in black (no text) Provider sharing not enabled for all accounts

Provider sharing is enabled by default for most, but not all accounts.

If you encounter errors when attempting to share data with consumers, the feature may not be enabled for your account. To inquire about enabling it, please contact Snowflake Support.

Modifies the properties for an existing share:

  • Adds or removes accounts from the list of accounts.

  • Sets a new list of accounts with which the corresponding database for the share is shared.

  • Modifies other properties (COMMENT is the only other currently-supported property).

See also:

CREATE SHARE , DESCRIBE SHARE , SHOW SHARES

Syntax

ALTER SHARE [ IF EXISTS ] <name> { ADD | REMOVE } ACCOUNTS = <consumer_account> [ , <consumer_account> , ... ]

ALTER SHARE [ IF EXISTS ] <name> SET { [ ACCOUNTS = <consumer_account> [ , <consumer_account> ... ] ]
                                       [ COMMENT = '<string_literal>' ] }

ALTER SHARE [ IF EXISTS ] <name> UNSET COMMENT

Parameters

name

Specifies the identifier for the share to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

ADD | REMOVE ACCOUNTS = consumer_account [ , consumer_account , ... ]

Specifies the name of the account(s) to add or remove from the list of accounts for the share:

  • Adding an account to a share that was already in the list has no effect.

  • Removing an account that has already imported the shared database immediately revokes that account’s access to the database. If the account is later added back to the share, the account must re-create the database before they can use it again.

  • Removing an account from a share that was not already in the list of shared accounts has no effect.

This parameter adds to (or removes from) the existing list of accounts for the share. If you want to replace the entire list of accounts, use SET instead.

SET...

ACCOUNTS = consumer_account [ , consumer_account ... ]

Specifies the account(s) to replace all previous accounts with which the share was shared. To add/remove individual accounts from the list, use ADD | REMOVE instead.

COMMENT = 'string'

Adds a comment or overwrites an existing comment for the share.

UNSET ...

Specifies the properties to unset for the share, which resets them to the defaults.

Currently, the only property you can unset is COMMENT, which removes the comment, if one exists, for the share.

Usage Notes

  • Only the ACCOUNTADMIN role has the privileges to alter a share. Executing this command with any role other than ACCOUNTADMIN returns an error.

  • Keywords ACCOUNT and ACCOUNTS are both supported and can be used interchangeably.

Examples

Add two accounts to the existing share named sales_s:

ALTER SHARE sales_s ADD ACCOUNTS=xy12345,yz23456;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Remove account yz23456 from sales_s:

ALTER SHARE sales_s REMOVE ACCOUNT=yz23456;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Set a new comment for sales_s:

ALTER SHARE sales_s SET COMMENT='This share contains sales data for 2017';

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+