Categories:

User & Security DDL (Access Control)

GRANT <privilege> … TO SHARE

Grants access privileges for databases and other supported database objects (schemas, tables, and views) to a share. Granting privileges on these objects effectively adds the objects to the share, which can then be shared with one or more consumer accounts.

For more details, see Introduction to Secure Data Sharing and Working with Shares.

See also:

REVOKE <privilege> … FROM SHARE

GRANT <privileges> … TO ROLE

Syntax

GRANT objectPrivilege ON
     {  DATABASE <name>
      | SCHEMA <name>
      | { TABLE <name> | ALL TABLES IN SCHEMA <schema_name> }
      | { VIEW <name> | ALL VIEWS IN SCHEMA <schema_name> }  }
  TO SHARE <share_name>

Where:

objectPrivilege ::=
-- For DATABASE or SCHEMA
    USAGE
-- For TABLE or VIEW
    SELECT
-- For DATABASE
    REFERENCE_USAGE

Parameters

name

Specifies the identifier for the object (database, schema, table, or secure view) for which the specified privilege is granted.

schema_name

Specifies the identifier for the schema for which the specified privilege is granted for all tables or views.

share_name

Specifies the identifier for the share from which the specified privilege is granted.

Usage Notes

• Each object privilege must be granted individually to a role, except for tables. Using an ALL clause, you can grant SELECT on all tables in a specified schema to a role.

  Currently, the ALL clause cannot distinguish between views that are secured or not secured. As a result, bulk grants of the SELECT privilege on views in a schema to a share fails with a user error.

• A share can be granted USAGE for a single database; however, within the database, multiple schemas, tables, and views can be granted.

• Currently, sharing a UDF that references an object from another database is not supported. For example, if you attempt to grant USAGE on a UDF that references a secure view from another database, an error is returned.

• Currently, a share can only be granted SELECT for secure views. If you attempt to grant a non-secure view to a share, an error is returned.

  Because of this limitation, only use ALL VIEWS IN SCHEMA schema_name if all the views in the specified schema are secure.

• Use the REFERENCE_USAGE privilege when sharing a secure view that references objects belonging to multiple databases.

• The REFERENCE_USAGE privilege must be granted individually to each database.

• The REFERENCE_USAGE privilege must be granted to a database before granting SELECT on a secure view to a share.

Examples

This is an example of sharing objects from a single database:

GRANT USAGE ON DATABASE mydb TO SHARE share1;

GRANT USAGE ON SCHEMA mydb.public TO SHARE share1;

GRANT SELECT ON ALL TABLES IN SCHEMA mydb.public TO SHARE share1;

GRANT USAGE ON SCHEMA mydb.shared_schema TO SHARE share1;

GRANT SELECT ON VIEW mydb.shared_schema.view1 TO SHARE share1;

GRANT SELECT ON VIEW mydb.shared_schema.view3 TO SHARE share1;

This is an example of sharing a secure view that references objects from a different database:

CREATE SECURE VIEW view2 AS SELECT * FROM database2.public.sampletable;

GRANT USAGE ON DATABASE database1 TO SHARE share1;

GRANT USAGE ON SCHEMA database1.schema1 TO SHARE share1;

GRANT REFERENCE_USAGE ON DATABASE database2 TO SHARE share1;

GRANT SELECT ON VIEW view2 TO SHARE share1;