On this page
Warning:
As of May 12, 2021, CockroachDB v19.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
.
Synopsis
Required privileges
The user revoking privileges must have the GRANT
privilege on the target databases or tables.
Parameters
Parameter | Description |
---|---|
table_name |
The name of the table for which you want to revoke privileges. To revoke privileges for multiple tables, use a comma-separated list of table names. To revoke privileges for all tables, use * . |
database_name |
The name of the database for which you want to revoke privileges. To revoke privileges for multiple databases, use a comma-separated list of database names. Privileges revoked for databases will be revoked for any new tables created in the databases. |
user_name |
A comma-separated list of users and/or roles from whom you want to revoke privileges. |
Examples
Revoke privileges on databases
> SHOW GRANTS ON DATABASE db1, db2;
+----------+------------+------------+
| Database | User | Privileges |
+----------+------------+------------+
| db1 | betsyroach | CREATE |
| db1 | maxroach | CREATE |
| db1 | root | ALL |
| db2 | betsyroach | CREATE |
| db2 | maxroach | CREATE |
| db2 | root | ALL |
+----------+------------+------------+
(6 rows)
> REVOKE CREATE ON DATABASE db1, db2 FROM maxroach, betsyroach;
> SHOW GRANTS ON DATABASE db1, db2;
+----------+------+------------+
| Database | User | Privileges |
+----------+------+------------+
| db1 | root | ALL |
| db2 | 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
> SHOW GRANTS ON TABLE db1.t1, db1.t2;
+-------+------------+------------+
| Table | User | Privileges |
+-------+------------+------------+
| t1 | betsyroach | CREATE |
| t1 | betsyroach | DELETE |
| t1 | maxroach | CREATE |
| t1 | root | ALL |
| t2 | betsyroach | CREATE |
| t2 | betsyroach | DELETE |
| t2 | maxroach | CREATE |
| t2 | root | ALL |
+-------+------------+------------+
(8 rows)
> REVOKE CREATE ON TABLE db1.t1, db1,t2 FROM betsyroach;
> SHOW GRANTS ON TABLE db1.t1, db1.t2;
+-------+------------+------------+
| Table | User | Privileges |
+-------+------------+------------+
| t1 | betsyroach | DELETE |
| t1 | maxroach | CREATE |
| t1 | root | ALL |
| t2 | betsyroach | DELETE |
| t2 | maxroach | CREATE |
| t2 | root | ALL |
+-------+------------+------------+
(6 rows)
Revoke privileges on all tables in a database
> SHOW GRANTS ON TABLE db2.t1, db2.t2;
+-------+------------+------------+
| Table | User | Privileges |
+-------+------------+------------+
| t1 | betsyroach | DELETE |
| t1 | root | ALL |
| t2 | betsyroach | DELETE |
| t2 | root | ALL |
+-------+------------+------------+
(4 rows)
> REVOKE DELETE ON db2.* FROM betsyroach;
+-------+------+------------+
| Table | User | Privileges |
+-------+------+------------+
| t1 | root | ALL |
| t2 | root | ALL |
+-------+------+------------+
(2 rows)