The REVOKE statement revokes privileges from users.

For the list of privileges that can be granted to and revoked from users, see GRANT.

Synopsis

REVOKE ALL CREATE DROP GRANT SELECT INSERT DELETE UPDATE , ON TABLE table_name , DATABASE database_name , FROM user_name ,

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 The name of the users from whom you want to revoke privileges. To revoke privileges from multiple users, use a comma-separated list of users.

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 that 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)

See Also



Yes No