REVOKE <privileges>

On this page

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.

New in v20.1 In addition to the GRANT privilege, the user revoking privileges must have the privilege being revoked on the target database or tables. For example, a user revoking the SELECT privilege on a table to another user must have the GRANT and SELECT privileges on that table.

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)

Pricing

Contact us

Sign In