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.

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

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