The REVOKE statement revokes privileges from users and/or roles. For the list of privileges that can be granted to and revoked from users and roles, see GRANT.
You can use REVOKE to directly revoke privileges from a role or user, or you can revoke membership to an existing role, which effectively revokes that role's privileges.
The REVOKE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Syntax
Parameters
| Parameter | Description |
|---|---|
ALLALL PRIVILEGES |
Revoke all privileges. |
privilege_list |
A comma-separated list of privileges to revoke. |
grant_targets |
A comma-separated list of database, table, sequence, or function names. The list should be preceded by the object type (e.g., DATABASE mydatabase). If the object type is not specified, all names are interpreted as table or sequence names. |
target_types |
A comma-separated list of user-defined types. |
ALL SEQUENCES IN SCHEMA |
Revoke privileges on all sequences in a schema or list of schemas. |
ALL TABLES IN SCHEMA |
Revoke privileges on all tables and sequences in a schema or list of schemas. |
ALL FUNCTIONS IN SCHEMA. |
Revoke privileges on all user-defined functions in a schema or list of schemas. |
schema_name_list |
A comma-separated list of schemas. |
role_spec_list |
A comma-separated list of roles. |
Supported privileges
The following privileges can be revoked:
| Privilege | Levels |
|---|---|
ALL |
Database, Schema, Table, Sequence, Type |
CREATE |
Database, Schema, Table, Sequence |
DROP |
Database, Table, Sequence |
EXECUTE |
Function |
GRANT |
Database, Function, Schema, Table, Sequence, Type |
CONNECT |
Database |
SELECT |
Table, Sequence |
INSERT |
Table, Sequence |
DELETE |
Table, Sequence |
UPDATE |
Table, Sequence |
USAGE |
Function, Schema, Sequence, Type |
ZONECONFIG |
Database, Table, Sequence |
EXTERNALCONNECTION |
System |
BACKUP |
System, Database, Table |
RESTORE |
System, Database |
EXTERNALIOIMPLICITACCESS |
System |
MODIFYCLUSTERSETTING |
System |
VIEWACTIVITY |
System |
VIEWACTIVITYREDACTED |
System |
VIEWCLUSTERMETADATA |
System |
VIEWCLUSTERSETTING |
System |
VIEWDEBUG |
System |
CANCELQUERY |
System |
NOSQLLOGIN |
System |
Required privileges
To revoke privileges, user revoking privileges must have the
GRANTprivilege on the target database, schema, table, or user-defined type. In addition to theGRANTprivilege, the user revoking privileges must have the privilege being revoked on the target object. For example, a user revoking theSELECTprivilege on a table to another user must have theGRANTandSELECTprivileges on that table.To revoke role membership, the user revoking role membership must be a role admin (i.e., members with the
WITH ADMIN OPTION) or a member of theadminrole. To remove membership to theadminrole, the user must haveWITH ADMIN OPTIONon theadminrole.
Considerations
- The
rootuser cannot be revoked from theadminrole.
Examples
Setup
The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.
To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:
$ cockroach demo
Revoke privileges on databases
CREATE USER max WITH PASSWORD 'roach';
GRANT CREATE ON DATABASE movr TO max;
SHOW GRANTS ON DATABASE movr;
database_name | grantee | privilege_type
----------------+---------+-----------------
movr | admin | ALL
movr | max | CREATE
movr | root | ALL
(3 rows)
REVOKE CREATE ON DATABASE movr FROM max;
SHOW GRANTS ON DATABASE movr;
database_name | grantee | privilege_type
----------------+---------+-----------------
movr | admin | ALL
movr | root | ALL
(2 rows)
Any tables that previously inherited the database-level privileges retain the privileges.
Revoke privileges on specific tables in a database
GRANT DELETE ON TABLE rides TO max;
SHOW GRANTS ON TABLE rides;
database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+------------+---------+-----------------
movr | public | rides | admin | ALL
movr | public | rides | max | DELETE
movr | public | rides | root | ALL
(3 rows)
REVOKE DELETE ON TABLE rides FROM max;
SHOW GRANTS ON TABLE rides;
database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+------------+---------+-----------------
movr | public | rides | admin | ALL
movr | public | rides | root | ALL
(2 rows)
Revoke privileges on all tables in a database or schema
GRANT CREATE, SELECT, DELETE ON TABLE rides, users TO max;
SHOW GRANTS ON TABLE movr.*;
database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+----------------------------+---------+----------------+---------------
movr | public | promo_codes | admin | ALL | t
movr | public | promo_codes | demo | ALL | t
movr | public | promo_codes | root | ALL | t
movr | public | rides | admin | ALL | t
movr | public | rides | demo | ALL | t
movr | public | rides | max | CREATE | f
movr | public | rides | max | DELETE | f
movr | public | rides | max | SELECT | f
movr | public | rides | root | ALL | t
movr | public | user_promo_codes | admin | ALL | t
movr | public | user_promo_codes | demo | ALL | t
movr | public | user_promo_codes | root | ALL | t
movr | public | users | admin | ALL | t
movr | public | users | demo | ALL | t
movr | public | users | max | CREATE | f
movr | public | users | max | DELETE | f
movr | public | users | max | SELECT | f
movr | public | users | root | ALL | t
movr | public | vehicle_location_histories | admin | ALL | t
movr | public | vehicle_location_histories | demo | ALL | t
movr | public | vehicle_location_histories | root | ALL | t
movr | public | vehicles | admin | ALL | t
movr | public | vehicles | demo | ALL | t
movr | public | vehicles | root | ALL | t
(24 rows)
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM max;
This is equivalent to the following syntax:
REVOKE DELETE ON movr.public.* FROM max;
database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+----------------------------+---------+----------------+---------------
movr | public | promo_codes | admin | ALL | t
movr | public | promo_codes | demo | ALL | t
movr | public | promo_codes | root | ALL | t
movr | public | rides | admin | ALL | t
movr | public | rides | demo | ALL | t
movr | public | rides | max | CREATE | f
movr | public | rides | max | SELECT | f
movr | public | rides | root | ALL | t
movr | public | user_promo_codes | admin | ALL | t
movr | public | user_promo_codes | demo | ALL | t
movr | public | user_promo_codes | root | ALL | t
movr | public | users | admin | ALL | t
movr | public | users | demo | ALL | t
movr | public | users | max | CREATE | f
movr | public | users | max | SELECT | f
movr | public | users | root | ALL | t
movr | public | vehicle_location_histories | admin | ALL | t
movr | public | vehicle_location_histories | demo | ALL | t
movr | public | vehicle_location_histories | root | ALL | t
movr | public | vehicles | admin | ALL | t
movr | public | vehicles | demo | ALL | t
movr | public | vehicles | root | ALL | t
(22 rows)
Revoke system-level privileges on the entire cluster
System-level privileges live above the database level and apply to the entire cluster.
root and admin users have system-level privileges by default, and are capable of revoking it from other users and roles using the REVOKE statement.
For example, the following statement removes the ability to use the SET CLUSTER SETTING statement from the user maxroach by revoking the MODIFYCLUSTERSETTING system privilege:
REVOKE SYSTEM MODIFYCLUSTERSETTING FROM maxroach;
Revoke privileges on schemas
CREATE SCHEMA cockroach_labs;
GRANT ALL ON SCHEMA cockroach_labs TO max;
SHOW GRANTS ON SCHEMA cockroach_labs;
database_name | schema_name | grantee | privilege_type
----------------+----------------+---------+-----------------
movr | cockroach_labs | admin | ALL
movr | cockroach_labs | max | ALL
movr | cockroach_labs | root | ALL
(3 rows)
REVOKE CREATE ON SCHEMA cockroach_labs FROM max;
SHOW GRANTS ON SCHEMA cockroach_labs;
database_name | schema_name | grantee | privilege_type
----------------+----------------+---------+-----------------
movr | cockroach_labs | admin | ALL
movr | cockroach_labs | max | GRANT
movr | cockroach_labs | max | USAGE
movr | cockroach_labs | root | ALL
(4 rows)
Revoke privileges on user-defined types
CREATE TYPE status AS ENUM ('available', 'unavailable');
GRANT ALL ON TYPE status TO max;
SHOW GRANTS ON TYPE status;
database_name | schema_name | type_name | grantee | privilege_type
----------------+-------------+-----------+---------+-----------------
movr | public | status | admin | ALL
movr | public | status | max | ALL
movr | public | status | public | USAGE
movr | public | status | root | ALL
(4 rows)
REVOKE GRANT ON TYPE status FROM max;
SHOW GRANTS ON TYPE status;
database_name | schema_name | type_name | grantee | privilege_type
----------------+-------------+-----------+---------+-----------------
movr | public | status | admin | ALL
movr | public | status | max | USAGE
movr | public | status | public | USAGE
movr | public | status | root | ALL
(4 rows)
Revoke role membership
CREATE ROLE developer WITH CREATEDB;
CREATE USER abbey WITH PASSWORD 'lincoln';
GRANT developer TO abbey;
SHOW GRANTS ON ROLE developer;
role_name | member | is_admin
------------+--------+-----------
developer | abbey | false
(1 row)
REVOKE developer FROM abbey;
SHOW GRANTS ON ROLE developer;
role_name | member | is_admin
------------+--------+-----------
(0 rows)
Revoke the admin option
GRANT developer TO abbey WITH ADMIN OPTION;
SHOW GRANTS ON ROLE developer;
role_name | member | is_admin
------------+--------+-----------
developer | abbey | true
(1 row)
REVOKE ADMIN OPTION FOR developer FROM abbey;
SHOW GRANTS ON ROLE developer;
role_name | member | is_admin
------------+--------+-----------
developer | abbey | false
(1 row)
See also
Was this helpful?
