REVOKE - CockroachDB

The REVOKE revokes from . For the list of privileges that can be granted to and revoked from users and roles, see . 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 .

Syntax

Parameters

Parameter	Description
`ALL` `ALL PRIVILEGES`	Revoke all privileges.
`privilege\_list`	A comma-separated list of 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 .
`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 in a schema or list of schemas.
`schema\_name\_list`	A comma-separated list of .
`role\_spec\_list`	A comma-separated list of .

Supported privileges

The following privileges can be revoked:

Privilege	Levels	Description
`ALL`	System, Database, Schema, Table, Sequence, Type	For the object to which `ALL` is applied, grants all privileges at the system, database, schema, table, sequence, or type level.
`BACKUP`	System, Database, Table	Grants the ability to create at the system, database, or table level.
`CANCELQUERY`	System	Grants the ability to cancel queries.
`CHANGEFEED`	Table	Grants the ability to create on a table.
`CONNECT`	Database	Grants the ability to view a database’s metadata, which consists of objects in a database’s `information\_schema` and `pg\_catalog` system catalogs. This allows the role to view the database’s table, schemas, user-defined types, and list the database when running `SHOW DATABASES`. The `CONNECT` privilege is also required to run backups of the database.
`CONTROLJOB`	System	Grants the ability to , , and jobs. Non-admin roles cannot control jobs created by admin roles.
`CREATE`	Database, Schema, Table, Sequence	Grants the ability to create objects at the database, schema, table, or sequence level. When applied at the database level, grants the ability to configure . In CockroachDB v23.2 and later, the `sql.auth.public\_schema\_create\_privilege.enabled` controls whether users receive `CREATE` privileges on the public schema or not. The setting applies at the time that the , which happens whenever . The setting is `true` by default, but can be set to `false` for increased compatibility with PostgreSQL version 15 as described in this commit.
`CREATEDB`	System	Grants the ability to or a database.
`CREATELOGIN`	System	Grants the ability to manage authentication using the `WITH PASSWORD`, `VALID UNTIL`, and `LOGIN`/`NOLOGIN` role options.
`CREATEROLE`	System	Grants the ability to , modify, or non-admin roles.
`DELETE`	Table, Sequence	Grants the ability to delete objects at the table or sequence level.
`DROP`	Database, Table, Sequence	Grants the ability to drop objects at the database, table, or sequence level.
`EXECUTE`	Function	Grants the ability to execute .
`EXTERNALCONNECTION`	System	Grants the ability to connect to external systems such as object stores, key management systems, Kafka feeds, or external file systems. Often used in conjunction with the `BACKUP`, `RESTORE`, and `CHANGEFEED` privilege.
`EXTERNALIOIMPLICITACCESS`	System	Grants the ability to interact with external resources that require implicit access.
`INSERT`	Table, Sequence	Grants the ability to insert objects at the table or sequence level.
	`MODIFYCLUSTERSETTING`	System	Grants the ability to modify .
`MODIFYSQLCLUSTERSETTING`	System	Grants the ability to modify SQL (cluster settings prefixed with `sql.`).
`NOSQLLOGIN`	System	Prevents roles from connecting to the SQL interface of a cluster.
	`REPAIRCLUSTER`, `REPAIRCLUSTERMETADATA`	System	Grants the ability to perform cluster debugging and repair operations, including: edit , edit , , update , , and check for constraint violations with . See also: `VIEWCLUSTERMETADATA`.
`REPLICATION`	System	Grants the ability to create a stream.
`RESTORE`	System, Database	Grants the ability to restore at the system or database level. Refer to `RESTORE` for more details.
`SELECT`	Table, Sequence	Grants the ability to run at the table or sequence level.
`UPDATE`	Table, Sequence	Grants the ability to run at the table or sequence level.
`USAGE`	Schema, Sequence, Type	Grants the ability to use , , or .
	`VIEWACTIVITY`	System	Grants the ability to view other user’s activity statistics of a cluster.
	`VIEWACTIVITYREDACTED`	System	Grants the ability to view other user’s activity statistics, but prevents the role from accessing the statement diagnostics bundle in the DB Console, and viewing some columns in introspection queries that contain data about the cluster.
	`VIEWCLUSTERMETADATA`	System	Grants the ability to view range information, data distribution, store information, and Raft information.
	`VIEWCLUSTERSETTING`	System	Grants the ability to view and their values.
	`VIEWDEBUG`	System	Grants the ability to view the of the DB Console and work with the debugging and profiling endpoints.
	`VIEWJOB`	System	Grants the ability to view on the cluster.
`VIEWSYSTEMTABLE`	System	Grants read-only access (`SELECT`) on all tables in the `system` database, without granting the ability to modify the cluster. This privilege was introduced in v23.1.11.
`ZONECONFIG`	Database, Table, Sequence	Grants the ability to configure at the database, table, and sequence level.

Required privileges

To revoke privileges, user revoking privileges must have the GRANT privilege on the target , , , or . 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.

Known limitations

User/role management operations (such as and ) are . As such, they inherit the . For example, schema changes wait for concurrent using the same resources as the schema changes to complete. In the case of being modified inside a transaction, most transactions need access to the set of role memberships. Using the default settings, role modifications require schema leases to expire, which can take up to 5 minutes. This means that elsewhere in the system can cause user/role management operations inside transactions to take several minutes to complete. This can have a cascading effect. When a user/role management operation inside a transaction takes a long time to complete, it can in turn block all user-initiated transactions being run by your application, since the user/role management operation in the transaction has to commit before any other transactions that access role memberships (i.e., most transactions) can make progress. If you want user/role management operations to finish more quickly, and do not care whether concurrent transactions will immediately see the side effects of those operations, set the allow_role_memberships_to_change_during_transaction to true. When this session variable is enabled, any user/role management operations issued in the current session will only need to wait for the completion of statements in other sessions where allow_role_memberships_to_change_during_transaction is not enabled. To accelerate user/role management operations across your entire application, you have the following options:

Set the session variable in all sessions by .
Apply the allow_role_memberships_to_change_during_transaction setting globally to an entire cluster using the statement:
ALTER ROLE ALL SET allow_role_memberships_to_change_during_transaction = true;

Examples

Setup

To follow along, run to start a temporary, in-memory cluster with the sample 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 | is_grantable
----------------+---------+----------------+---------------
  movr          | admin   | ALL            |      t
  movr          | max     | CREATE         |      f
  movr          | root    | ALL            |      t
(3 rows)

REVOKE CREATE ON DATABASE movr FROM max;

SHOW GRANTS ON DATABASE movr;

  database_name | grantee | privilege_type | is_grantable
----------------+---------+----------------+---------------
  movr          | admin   | ALL            |      t
  movr          | root    | ALL            |      t
(2 rows)

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

Revoke privileges on specific tables in a database

GRANT ALL ON TABLE rides TO max;

SHOW GRANTS ON TABLE rides;

  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  movr          | public      | rides      | admin   | ALL            |      t
  movr          | public      | rides      | max     | ALL            |      f
  movr          | public      | rides      | root    | ALL            |      t
(3 rows)

REVOKE ALL ON TABLE rides FROM max;

SHOW GRANTS ON TABLE rides;

  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  movr          | public      | rides      | admin   | ALL            |      t
  movr          | public      | rides      | root    | ALL            |      t
(2 rows)

Revoke privileges on all tables in a database or schema

GRANT ALL 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                | root    | ALL            |      t
  movr          | public      | rides                      | admin   | ALL            |      t
  movr          | public      | rides                      | max     | ALL            |      f
  movr          | public      | rides                      | root    | ALL            |      t
  movr          | public      | user_promo_codes           | admin   | ALL            |      t
  movr          | public      | user_promo_codes           | root    | ALL            |      t
  movr          | public      | users                      | admin   | ALL            |      t
  movr          | public      | users                      | max     | ALL            |      f
  movr          | public      | users                      | root    | ALL            |      t
  movr          | public      | usertable                  | admin   | ALL            |      t
  movr          | public      | usertable                  | root    | ALL            |      t
  movr          | public      | vehicle_location_histories | admin   | ALL            |      t
  movr          | public      | vehicle_location_histories | root    | ALL            |      t
  movr          | public      | vehicles                   | admin   | ALL            |      t
  movr          | public      | vehicles                   | public  | SELECT         |      f
  movr          | public      | vehicles                   | root    | ALL            |      t
(17 rows)

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM max;

This is equivalent to the following syntax:

REVOKE ALL ON movr.public.* FROM 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                | root    | ALL            |      t
  movr          | public      | rides                      | admin   | ALL            |      t
  movr          | public      | rides                      | root    | ALL            |      t
  movr          | public      | user_promo_codes           | admin   | ALL            |      t
  movr          | public      | user_promo_codes           | root    | ALL            |      t
  movr          | public      | users                      | admin   | ALL            |      t
  movr          | public      | users                      | root    | ALL            |      t
  movr          | public      | usertable                  | admin   | ALL            |      t
  movr          | public      | usertable                  | root    | ALL            |      t
  movr          | public      | vehicle_location_histories | admin   | ALL            |      t
  movr          | public      | vehicle_location_histories | root    | ALL            |      t
  movr          | public      | vehicles                   | admin   | ALL            |      t
  movr          | public      | vehicles                   | public  | SELECT         |      f
  movr          | public      | vehicles                   | root    | ALL            |      t
(15 rows)

Revoke system-level privileges on the entire cluster

live above the database level and apply to the entire cluster. root and 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 statement from the user maxroach by revoking the MODIFYCLUSTERSETTING system privilege:

REVOKE SYSTEM MODIFYCLUSTERSETTING FROM max;

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 | is_grantable
----------------+----------------+---------+----------------+---------------
  movr          | cockroach_labs | admin   | ALL            |      t
  movr          | cockroach_labs | max     | ALL            |      t
  movr          | cockroach_labs | root    | ALL            |      t
(3 rows)

REVOKE CREATE ON SCHEMA cockroach_labs FROM max;

SHOW GRANTS ON SCHEMA cockroach_labs;

  database_name |  schema_name   | grantee | privilege_type | is_grantable
----------------+----------------+---------+----------------+---------------
  movr          | cockroach_labs | admin   | ALL            |      t
  movr          | cockroach_labs | max     | USAGE          |      t
  movr          | cockroach_labs | root    | ALL            |      t
(3 rows)

Revoke privileges on user-defined types

To revoke privileges on , use the following statements.

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 | is_grantable
----------------+-------------+-----------+---------+----------------+---------------
  movr          | public      | status    | admin   | ALL            |      t
  movr          | public      | status    | max     | ALL            |      f
  movr          | public      | status    | public  | USAGE          |      f
  movr          | public      | status    | root    | ALL            |      t
(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  |    f
(1 row)

REVOKE developer FROM abbey;

SHOW GRANTS ON ROLE developer;

SHOW GRANTS ON ROLE 0

Revoke the admin option

GRANT developer TO abbey WITH ADMIN OPTION;

SHOW GRANTS ON ROLE developer;

  role_name | member | is_admin
------------+--------+-----------
  developer | abbey  |    t
(1 row)

REVOKE ADMIN OPTION FOR developer FROM abbey;

SHOW GRANTS ON ROLE developer;

  role_name | member | is_admin
------------+--------+-----------
  developer | abbey  |    f
(1 row)

​Syntax

​Parameters

​Supported privileges

​Required privileges

​Considerations

​Known limitations

​Examples

​Setup

​Revoke privileges on databases

​Revoke privileges on specific tables in a database

​Revoke privileges on all tables in a database or schema

​Revoke system-level privileges on the entire cluster

​Revoke privileges on schemas

​Revoke privileges on user-defined types

​Revoke role membership

​Revoke the admin option

​See also

Syntax

Parameters

Supported privileges

Required privileges

Considerations

Known limitations

Examples

Setup

Revoke privileges on databases

Revoke privileges on specific tables in a database

Revoke privileges on all tables in a database or schema

Revoke system-level privileges on the entire cluster

Revoke privileges on schemas

Revoke privileges on user-defined types

Revoke role membership

Revoke the admin option

See also