REVOKE <privilege> … FROM SHARE

Revokes access privileges for databases and other supported database objects (schemas, tables, and views) from a share. Revoking privileges on these objects effectively removes the objects from the share, disabling access to the objects granted via the database role in all consumer accounts that have created a database from the share.

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

See also:

GRANT <privilege> … TO SHARE

REVOKE <privileges>

Syntax

REVOKE objectPrivilege ON
     {  DATABASE <name>
      | SCHEMA <name>
      | { TABLE <name> | ALL TABLES IN SCHEMA <schema_name> }
      | { VIEW <name> | ALL VIEWS IN SCHEMA <schema_name> }  }
  FROM SHARE <share_name>
Copy

Where:

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

Parameters

name

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

schema_name

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

share_name

Specifies the identifier for the share to which the specified privilege is revoked.

Usage Notes

  • Each object privilege must be revoked individually from a role, except for tables and views. Using an ALL clause, you can revoke the SELECT privilege on all the tables or views in a specified schema from a role.

Examples

REVOKE SELECT ON VIEW mydb.shared_schema.view1 FROM SHARE share1;

REVOKE SELECT ON VIEW mydb.shared_schema.view3 FROM SHARE share1;

REVOKE USAGE ON SCHEMA mydb.shared_schema FROM SHARE share1;

REVOKE SELECT ON ALL TABLES IN SCHEMA mydb.public FROM SHARE share1;

REVOKE USAGE ON SCHEMA mydb.public FROM SHARE share1;

REVOKE USAGE ON DATABASE mydb FROM SHARE share1;
Copy

This example disallows a shared secure view to reference objects from a different database:

REVOKE REFERENCE_USAGE ON DATABASE database2 FROM SHARE share1;
Copy
Language: English