Categories:

User & Security DDL (Access Control)

GRANT <privilege> … TO SHARE

Grants access privileges for databases and other supported database objects (schemas, UDFs, 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>
      | FUNCTION <name>
      | { TABLE <name> | ALL TABLES IN SCHEMA <schema_name> }
      | VIEW <name>  }
  TO SHARE <share_name>

Where:

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

Parameters

name

Specifies the identifier for the object (database, schema, UDF, 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.

share_name

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

Usage Notes

• The USAGE privilege on only a single database can be granted to a share; however, within that database, privileges on multiple schemas, UDFs, tables, and views can be granted to the share.

• Privileges on individual objects must be granted to a share in separate GRANT statements. The only exception is the SELECT privilege on tables. Using an ALL clause, you can grant SELECT on all tables in a specified schema to a share.

• The SELECT privilege on views can only be granted on secure views. Attempting to grant the SELECT privilege on a non-secure view to a share returns an error.

• The USAGE privilege can only be granted on secure UDFs. Attempting to grant the USAGE privilege on a non-secure UDF to a share returns an error.

• 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.

• Use the REFERENCE_USAGE privilege when sharing a secure view that references objects belonging to multiple databases, as follows:

  • 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 USAGE ON FUNCTION mydb.shared_schema.function1 TO SHARE share1;

GRANT USAGE ON FUNCTION mydb.shared_schema.function2 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;