> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# REVOKE
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
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.
The \`\` 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](#supported-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](#supported-privileges) on all sequences in a schema or list of schemas. |
| `ALL TABLES IN SCHEMA` | Revoke [privileges](#supported-privileges) on all tables and sequences in a schema or list of schemas. |
| `ALL FUNCTIONS IN SCHEMA`. | Revoke [privileges](#supported-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 | 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 backups at the system, database, or table level. |
| `BYPASSRLS` | Table | Grants the ability to bypass row-level security (RLS) policies on a table. This privilege controls the access from an RLS perspective only; the user also needs sufficient `GRANT` privileges to read or write to the table. |
| `CANCELQUERY` | System | Grants the ability to cancel queries. |
| `CHANGEFEED` | Table | Grants the ability to create changefeeds 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 pause, resume, and cancel 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 multi-region zone configs. In CockroachDB v23.2 and later, the cluster setting `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 public schema is created, which happens whenever a database is created. The setting is `true` by default, but can be set to `false` for increased compatibility with [PostgreSQL version 15](https://www.postgresql.org/about/news/postgresql-15-released-2526/) as described in [this commit](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b073c3ccd06e4cb845e121387a43faa8c68a7b62). |
| `CREATEDB` | System | Grants the ability to create or rename 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 create, modify, or delete 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 functions. |
| `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 cluster settings. |
| `MODIFYSQLCLUSTERSETTING` | System | Grants the ability to modify SQL cluster settings (cluster settings prefixed with `sql.`). |
| `NOSQLLOGIN` | System | Prevents roles from connecting to the SQL interface of a cluster. |
| **Deprecated** `REPLICATION` | System | As of v25.2 `REPLICATION` is **deprecated**. Instead, use the `REPLICATIONSOURCE` and `REPLICATIONDEST` privileges at the table level. Grants the ability to create a logical data replication or physical cluster replication stream. |
| `REPAIRCLUSTER`, `REPAIRCLUSTERMETADATA` | System | Grants the ability to perform cluster debugging and repair operations, including: edit scheduled jobs, edit external connections, reset SQL statistics, update zone configurations, relocate ranges, and check for constraint violations with `SCRUB`. See also: [`VIEWCLUSTERMETADATA`](#viewclustermetadata). |
| `REPLICATIONDEST` | Table | Grants the ability to run logical data replication into an existing table on the destination cluster. For more details, refer to the Set Up Logical Data Replication tutorial. |
| `REPLICATIONSOURCE` | Table | Grants the ability to run logical data replication from a table on the source cluster. For more details, refer to the Set Up Logical Data Replication tutorial. |
| `RESTORE` | System, Database | Grants the ability to restore backups at the system or database level. Refer to `RESTORE` Required privileges for more details. |
| `SELECT` | Table, Sequence | Grants the ability to run selection queries at the table or sequence level. |
| `TRIGGER` | Table | Grants the ability to create triggers on a table. |
| `UPDATE` | Table, Sequence | Grants the ability to run update statements at the table or sequence level. |
| `USAGE` | Schema, Sequence, Type | Grants the ability to use schemas, sequences, or user-defined types. |
| `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 cluster settings and their values. |
| `VIEWDEBUG` | System | Grants the ability to view the Advanced Debug Page of the DB Console and work with the debugging and profiling endpoints. |
| `VIEWJOB` | System | Grants the ability to view jobs 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 replication zones at the database, table, and sequence level. |
## 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.
## Known limitations
User/role management operations (such as `GRANT` and `REVOKE`) are schema changes. As such, they inherit the limitations of schema changes.
For example, schema changes wait for concurrent transactions using the same resources as the schema changes to complete. In the case of role memberships 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 long-running transactions 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 session variable `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:
1. Set the session variable in all sessions by passing it in the client connection string.
2. Apply the `allow_role_memberships_to_change_during_transaction` setting globally to an entire cluster using the `ALTER ROLE ALL` statement:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER ROLE ALL SET allow_role_memberships_to_change_during_transaction = true;
```
## Examples
#### Setup
To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```
### Revoke privileges on databases
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE USER max WITH PASSWORD 'roach';
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT CREATE ON DATABASE movr TO max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REVOKE CREATE ON DATABASE movr FROM max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT ALL ON TABLE rides TO max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REVOKE ALL ON TABLE rides FROM max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT ALL ON TABLE rides, users TO max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM max;
```
This is equivalent to the following syntax:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REVOKE ALL ON movr.public.* FROM max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REVOKE SYSTEM MODIFYCLUSTERSETTING FROM max;
```
### Revoke privileges on schemas
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEMA cockroach_labs;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT ALL ON SCHEMA cockroach_labs TO max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REVOKE CREATE ON SCHEMA cockroach_labs FROM max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 user-defined types, use the following statements.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TYPE status AS ENUM ('available', 'unavailable');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT ALL ON TYPE status TO max;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE ROLE developer WITH CREATEDB;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE USER abbey WITH PASSWORD 'lincoln';
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT developer TO abbey;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS ON ROLE developer;
```
```
role_name | member | is_admin
------------+--------+-----------
developer | abbey | f
(1 row)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REVOKE developer FROM abbey;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS ON ROLE developer;
```
```
SHOW GRANTS ON ROLE 0
```
### Revoke the admin option
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT developer TO abbey WITH ADMIN OPTION;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS ON ROLE developer;
```
```
role_name | member | is_admin
------------+--------+-----------
developer | abbey | t
(1 row)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
REVOKE ADMIN OPTION FOR developer FROM abbey;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS ON ROLE developer;
```
```
role_name | member | is_admin
------------+--------+-----------
developer | abbey | f
(1 row)
```
## See also
* Authorization
* `GRANT`
* `SHOW GRANTS`
* `SHOW ROLES`
* `CREATE USER`
* `DROP USER`
* SQL Statements