REVOKE

On this page

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

Parameters

Parameter	Description
`ALL` `ALL PRIVILEGES`	Revoke all privileges.
`targets`	A comma-separated list of database or table names, preceded by the object type (e.g., `DATABASE mydatabase`). Note: To revoke privileges on 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.
`name_list`	A comma-separated list of users and roles.
`target_types`	A comma-separated list of user-defined types.
`schema_name_list`	A comma-separated list of schemas.
`ALL TABLES IN SCHEMA`	New in v21.2: Revoke privileges on all tables in a schema or list of schemas.
`privilege_list`	A comma-separated list of privileges to revoke.

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
`CONNECT`	Database
`SELECT`	Table
`INSERT`	Table
`DELETE`	Table
`UPDATE`	Table
`USAGE`	Schema, Type
`ZONECONFIG`	Database, Table

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:

$ 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)

Note:

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
----------------+-------------+----------------------------+---------+-----------------
  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)

> 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

> 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)

Pricing

Contact us

Sign In