REVOKE <privileges>

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

REVOKEALLCREATEGRANTSELECTDROPINSERTDELETEUPDATE,ONTABLEtable_name,DATABASEdatabase_name,FROMuser_name,

Required privileges

The user revoking privileges must have the GRANT privilege on the target databases or tables.

Parameters

ParameterDescription
table_nameThe 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_nameThe 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_nameA comma-separated list of users and/or roles from whom you want to revoke privileges.

Examples

Revoke privileges on databases

  1. > SHOW GRANTS ON DATABASE db1, db2;
  1. +----------+------------+------------+
  2. | Database |    User    | Privileges |
  3. +----------+------------+------------+
  4. | db1      | betsyroach | CREATE     |
  5. | db1      | maxroach   | CREATE     |
  6. | db1      | root       | ALL        |
  7. | db2      | betsyroach | CREATE     |
  8. | db2      | maxroach   | CREATE     |
  9. | db2      | root       | ALL        |
  10. +----------+------------+------------+
  11. (6 rows)
  1. > REVOKE CREATE ON DATABASE db1, db2 FROM maxroach, betsyroach;
  1. > SHOW GRANTS ON DATABASE db1, db2;
  1. +----------+------+------------+
  2. | Database | User | Privileges |
  3. +----------+------+------------+
  4. | db1      | root | ALL        |
  5. | db2      | root | ALL        |
  6. +----------+------+------------+
  7. (2 rows)

Note:
Any tables that previously inherited the database-level privileges retain the privileges.

Revoke privileges on specific tables in a database

  1. > SHOW GRANTS ON TABLE db1.t1, db1.t2;
  1. +-------+------------+------------+
  2. | Table |    User    | Privileges |
  3. +-------+------------+------------+
  4. | t1    | betsyroach | CREATE     |
  5. | t1    | betsyroach | DELETE     |
  6. | t1    | maxroach   | CREATE     |
  7. | t1    | root       | ALL        |
  8. | t2    | betsyroach | CREATE     |
  9. | t2    | betsyroach | DELETE     |
  10. | t2    | maxroach   | CREATE     |
  11. | t2    | root       | ALL        |
  12. +-------+------------+------------+
  13. (8 rows)
  1. > REVOKE CREATE ON TABLE db1.t1, db1,t2 FROM betsyroach;
  1. > SHOW GRANTS ON TABLE db1.t1, db1.t2;
  1. +-------+------------+------------+
  2. | Table |    User    | Privileges |
  3. +-------+------------+------------+
  4. | t1    | betsyroach | DELETE     |
  5. | t1    | maxroach   | CREATE     |
  6. | t1    | root       | ALL        |
  7. | t2    | betsyroach | DELETE     |
  8. | t2    | maxroach   | CREATE     |
  9. | t2    | root       | ALL        |
  10. +-------+------------+------------+
  11. (6 rows)

Revoke privileges on all tables in a database

  1. > SHOW GRANTS ON TABLE db2.t1, db2.t2;
  1. +-------+------------+------------+
  2. | Table |    User    | Privileges |
  3. +-------+------------+------------+
  4. | t1    | betsyroach | DELETE     |
  5. | t1    | root       | ALL        |
  6. | t2    | betsyroach | DELETE     |
  7. | t2    | root       | ALL        |
  8. +-------+------------+------------+
  9. (4 rows)
  1. > REVOKE DELETE ON db2.* FROM betsyroach;
  1. +-------+------+------------+
  2. | Table | User | Privileges |
  3. +-------+------+------------+
  4. | t1    | root | ALL        |
  5. | t2    | root | ALL        |
  6. +-------+------+------------+
  7. (2 rows)

See also

Was this page helpful?
YesNo