REVOKE privilege

Synopsis

REVOKE [ GRANT OPTION FOR ]
( privilege [, ...] | ALL PRIVILEGES )
ON ( table_name |
     TABLE table_name |
     SCHEMA schema_name |
     MY ACCOUNT |
     CLUSTER cluster_name |
     CATALOG catalog_name |
     COLUMN column_name |
     LOCATION table_location |
     FUNCTION function_name |
     DATA_PRODUCT data_product_name
)
FROM ROLE role

Description

Revokes the specified privileges from the specified grantee.

Specifying ALL PRIVILEGES revokes all entity kind privileges on the entity from the supplied role. If the entity is a table, SELECT, UPDATE, DELETE and INSERT privileges are be revoked.

The schema_name may be a “qualified name”, where the catalog is specified explicitly, e.g., my_catalog.my_schema. If the catalog is not supplied the current session catalog is used.

The table_name, function_name or column_name may be “qualified names” in which the schema and sometimes the catalog are explicit. For example, the table my_catalog.my_schema.my_table, in which all components are explicit, and my_schema.my_table, in which the schema is explicit and the catalog defaults to the session catalog.

Specifying ROLE PUBLIC revokes privileges from the PUBLIC role. Users retain privileges assigned to them directly or via other roles.

If the optional GRANT OPTION FOR clause is specified, only the GRANT OPTION is removed. Otherwise, both the GRANT and GRANT OPTION are revoked.

For REVOKE statement to succeed, the user executing it should possess the specified privileges as well as the GRANT OPTION for those privileges.

Revoke on a table revokes the specified privilege on all columns of the table.

Revoke on a schema revokes the specified privilege on all columns of all tables of the schema.

To revoke an account privilege, the entity kind and account must be MY ACCOUNT.

Examples

Revoke INSERT and SELECT privileges on the table orders from role orders_admin:

REVOKE INSERT, SELECT ON orders FROM ROLE orders_admin;

Revoke DELETE privilege on the schema finance from role finance_admin:

REVOKE DELETE ON SCHEMA finance FROM ROLE finance_admin;

Revoke SELECT privilege on the table nation from everyone, additionally revoking the privilege to grant SELECT privilege:

REVOKE GRANT OPTION FOR SELECT ON nation FROM ROLE PUBLIC;

Revoke all privileges on the table test from role orders_admin:

REVOKE ALL PRIVILEGES ON test FROM ROLE orders_admin;

Revoke cluster privilege USE_CLUSTER on cluster demo_cluster from role data_user:

REVOKE USE_CLUSTER ON CLUSTER demo_cluster FROM ROLE data_user;

Revoke the ability of role roles_admin to grant account privilege CREATE_ROLE to other roles:

REVOKE GRANT OPTION FOR CREATE_ROLE ON MY ACCOUNT FROM ROLE roles_admin;

See also

SQL entity kinds and privileges, DENY, GRANT privilege, SHOW GRANTS