REVOKE

On this page Carat arrow pointing down

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.

Note:

The REVOKE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Syntax

REVOKE ALL PRIVILEGES ON grant_targets TYPE target_types ALL TABLES SEQUENCES FUNCTIONS IN SCHEMA schema_name_list ADMIN OPTION FOR privilege_list SYSTEM ALL PRIVILEGES privilege_list privilege_list ON grant_targets TYPE target_types ALL TABLES SEQUENCES FUNCTIONS IN SCHEMA schema_name_list GRANT OPTION FOR ALL PRIVILEGES privilege_list ON grant_targets TYPE target_types ALL TABLES FUNCTIONS IN SCHEMA schema_name_list SYSTEM ALL PRIVILEGES privilege_list FROM role_spec_list

Parameters

Parameter Description
ALL
ALL 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

Required privileges

  • To revoke privileges, user revoking privileges must have the GRANT privilege on the target database, schema, table, or user-defined type. In addition to the GRANT privilege, the user revoking privileges must have the privilege being revoked on the target object. For example, a user revoking the SELECT privilege on a table to another user must have the GRANT and SELECT privileges 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 the admin role. To remove membership to the admin role, the user must have WITH ADMIN OPTION on the admin role.

Considerations

  • The root user cannot be revoked from the admin role.

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:

icon/buttons/copy
$ cockroach demo

Revoke privileges on databases

icon/buttons/copy
CREATE USER max WITH PASSWORD 'roach';
icon/buttons/copy
GRANT CREATE ON DATABASE movr TO max;
icon/buttons/copy
SHOW GRANTS ON DATABASE movr;
  database_name | grantee | privilege_type
----------------+---------+-----------------
  movr          | admin   | ALL
  movr          | max     | CREATE
  movr          | root    | ALL
(3 rows)
icon/buttons/copy
REVOKE CREATE ON DATABASE movr FROM max;
icon/buttons/copy
SHOW GRANTS ON DATABASE movr;
  database_name | grantee | privilege_type
----------------+---------+-----------------
  movr          | admin   | ALL
  movr          | root    | ALL
(2 rows)
Note:

Any tables that previously inherited the database-level privileges retain the privileges.

Revoke privileges on specific tables in a database

icon/buttons/copy
GRANT DELETE ON TABLE rides TO max;
icon/buttons/copy
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)
icon/buttons/copy
REVOKE DELETE ON TABLE rides FROM max;
icon/buttons/copy
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

icon/buttons/copy
GRANT CREATE, SELECT, DELETE ON TABLE rides, users TO max;
icon/buttons/copy
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)
icon/buttons/copy
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM max;

This is equivalent to the following syntax:

icon/buttons/copy
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:

icon/buttons/copy
REVOKE SYSTEM MODIFYCLUSTERSETTING FROM maxroach;

Revoke privileges on schemas

icon/buttons/copy
CREATE SCHEMA cockroach_labs;
icon/buttons/copy
GRANT ALL ON SCHEMA cockroach_labs TO max;
icon/buttons/copy
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)
icon/buttons/copy
REVOKE CREATE ON SCHEMA cockroach_labs FROM max;
icon/buttons/copy
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

icon/buttons/copy
CREATE TYPE status AS ENUM ('available', 'unavailable');
icon/buttons/copy
GRANT ALL ON TYPE status TO max;
icon/buttons/copy
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)
icon/buttons/copy
REVOKE GRANT ON TYPE status FROM max;
icon/buttons/copy
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

icon/buttons/copy
CREATE ROLE developer WITH CREATEDB;
icon/buttons/copy
CREATE USER abbey WITH PASSWORD 'lincoln';
icon/buttons/copy
GRANT developer TO abbey;
icon/buttons/copy
SHOW GRANTS ON ROLE developer;
  role_name | member | is_admin
------------+--------+-----------
  developer | abbey  |  false
(1 row)
icon/buttons/copy
REVOKE developer FROM abbey;
icon/buttons/copy
SHOW GRANTS ON ROLE developer;
  role_name | member | is_admin
------------+--------+-----------
(0 rows)

Revoke the admin option

icon/buttons/copy
GRANT developer TO abbey WITH ADMIN OPTION;
icon/buttons/copy
SHOW GRANTS ON ROLE developer;
  role_name | member | is_admin
------------+--------+-----------
  developer | abbey  |   true
(1 row)
icon/buttons/copy
REVOKE ADMIN OPTION FOR developer FROM abbey;
icon/buttons/copy
SHOW GRANTS ON ROLE developer;
  role_name | member | is_admin
------------+--------+-----------
  developer | abbey  |  false
(1 row)

See also


Yes No
On this page

Yes No