On this page 
Warning:
As of May 10, 2022, CockroachDB v20.2 is no longer supported. For more details, refer to the Release Support Policy.
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, Database |
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:
$ 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 | 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)
> REVOKE CREATE ON DATABASE movr FROM max;
> 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
> 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)
See also
- Authorization
GRANT <privileges>GRANT <roles>REVOKE <roles>SHOW GRANTSSHOW ROLESCREATE USERDROP USER- Other SQL Statements
Was this helpful?
