REVOKE <privileges>

On this page Carat arrow pointing down

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

REVOKE ALL CREATE GRANT SELECT DROP 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 A comma-separated list of users and/or roles from whom you want to revoke privileges.

Examples

Revoke privileges on databases

icon/buttons/copy
> 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)
icon/buttons/copy
> REVOKE CREATE ON DATABASE db1, db2 FROM maxroach, betsyroach;
icon/buttons/copy
> 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

icon/buttons/copy
> 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)
icon/buttons/copy
> REVOKE CREATE ON TABLE db1.t1, db1,t2 FROM betsyroach;
icon/buttons/copy
> 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

icon/buttons/copy
> 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)
icon/buttons/copy
> REVOKE DELETE ON db2.* FROM betsyroach;
+-------+------+------------+
| Table | User | Privileges |
+-------+------+------------+
| t1    | root | ALL        |
| t2    | root | ALL        |
+-------+------+------------+
(2 rows)

See also


Yes No
On this page

Yes No