The REVOKE <privileges> 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.

Syntax

Revoke privileges

To revoke privileges from a user or role, use the following syntax:

REVOKE {ALL | <privileges...> } ON {DATABASE | SCHEMA | TABLE | TYPE} <targets...> FROM <grantees...>

Revoke admin

To revoke the admin privileges from a role, use the following syntax:

REVOKE [ADMIN OPTION FOR] <roles...> FROM <grantees...>

Parameters

Parameter Description
ALL Revoke all privileges.
privileges A comma-separated list of privileges to revoke. For a list of supported privileges, see Supported privileges.
targets A comma-separated list of database, schema, table, or user-defined type names.

Note:
To revoke privileges from all tables in a database or schema, you can use REVOKE ... ON TABLE *. For an example, see Revoke privileges on all tables in a database or schema.
grantees A comma-separated list of users and/or roles from whom to revoke privileges.

Supported privileges

The following privileges can be revoked:

Privilege Levels
ALL Database, Schema, Table, Type
CREATE Database, Schema, Table
DROP Database, Table
GRANT Database, Schema, Table, Type
SELECT Table
INSERT Table
DELETE Table
UPDATE Table
USAGE Schema, Type
ZONECONFIG Database, Table

Required privileges

The 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.

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:

copy
icon/buttons/copy
$ cockroach demo

Revoke privileges on databases

copy
icon/buttons/copy
> CREATE USER max WITH PASSWORD roach;
copy
icon/buttons/copy
> GRANT CREATE ON DATABASE movr TO max;
copy
icon/buttons/copy
> SHOW GRANTS ON DATABASE movr;
  database_name |    schema_name     | grantee | privilege_type
----------------+--------------------+---------+-----------------
  movr          | crdb_internal      | admin   | ALL
  movr          | crdb_internal      | max     | CREATE
  movr          | crdb_internal      | root    | ALL
  movr          | information_schema | admin   | ALL
  movr          | information_schema | max     | CREATE
  movr          | information_schema | root    | ALL
  movr          | pg_catalog         | admin   | ALL
  movr          | pg_catalog         | max     | CREATE
  movr          | pg_catalog         | root    | ALL
  movr          | pg_extension       | admin   | ALL
  movr          | pg_extension       | max     | CREATE
  movr          | pg_extension       | root    | ALL
  movr          | public             | admin   | ALL
  movr          | public             | max     | CREATE
  movr          | public             | root    | ALL
(15 rows)
copy
icon/buttons/copy
> REVOKE CREATE ON DATABASE movr FROM max;
copy
icon/buttons/copy
> SHOW GRANTS ON DATABASE movr;
  database_name |    schema_name     | grantee | privilege_type
----------------+--------------------+---------+-----------------
  movr          | crdb_internal      | admin   | ALL
  movr          | crdb_internal      | root    | ALL
  movr          | information_schema | admin   | ALL
  movr          | information_schema | root    | ALL
  movr          | pg_catalog         | admin   | ALL
  movr          | pg_catalog         | root    | ALL
  movr          | pg_extension       | admin   | ALL
  movr          | pg_extension       | root    | ALL
  movr          | public             | admin   | ALL
  movr          | public             | root    | ALL
(10 rows)
Note:

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

Revoke privileges on specific tables in a database

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

copy
icon/buttons/copy
> GRANT CREATE, SELECT, DELETE ON TABLE rides, users TO max;
copy
icon/buttons/copy
> SHOW GRANTS ON TABLE movr.*;
  database_name | schema_name |         table_name         | grantee | privilege_type
----------------+-------------+----------------------------+---------+-----------------
  movr          | public      | promo_codes                | admin   | ALL
  movr          | public      | promo_codes                | root    | ALL
  movr          | public      | rides                      | admin   | ALL
  movr          | public      | rides                      | max     | CREATE
  movr          | public      | rides                      | max     | DELETE
  movr          | public      | rides                      | max     | SELECT
  movr          | public      | rides                      | root    | ALL
  movr          | public      | user_promo_codes           | admin   | ALL
  movr          | public      | user_promo_codes           | root    | ALL
  movr          | public      | users                      | admin   | ALL
  movr          | public      | users                      | max     | CREATE
  movr          | public      | users                      | max     | DELETE
  movr          | public      | users                      | max     | SELECT
  movr          | public      | users                      | root    | ALL
  movr          | public      | vehicle_location_histories | admin   | ALL
  movr          | public      | vehicle_location_histories | root    | ALL
  movr          | public      | vehicles                   | admin   | ALL
  movr          | public      | vehicles                   | root    | ALL
(18 rows)
copy
icon/buttons/copy
> REVOKE DELETE ON movr.* FROM max;
  database_name | schema_name |         table_name         | grantee | privilege_type
----------------+-------------+----------------------------+---------+-----------------
  movr          | public      | promo_codes                | admin   | ALL
  movr          | public      | promo_codes                | root    | ALL
  movr          | public      | rides                      | admin   | ALL
  movr          | public      | rides                      | max     | CREATE
  movr          | public      | rides                      | max     | SELECT
  movr          | public      | rides                      | root    | ALL
  movr          | public      | user_promo_codes           | admin   | ALL
  movr          | public      | user_promo_codes           | root    | ALL
  movr          | public      | users                      | admin   | ALL
  movr          | public      | users                      | max     | CREATE
  movr          | public      | users                      | max     | SELECT
  movr          | public      | users                      | root    | ALL
  movr          | public      | vehicle_location_histories | admin   | ALL
  movr          | public      | vehicle_location_histories | root    | ALL
  movr          | public      | vehicles                   | admin   | ALL
  movr          | public      | vehicles                   | root    | ALL
(16 rows)

Revoke privileges on schemas

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

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

See also



Yes No