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.
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>
Where:
objectPrivilege ::= -- For DATABASE or SCHEMA USAGE -- For TABLE or VIEW SELECT -- For DATABASE REFERENCE_USAGECopy
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