REVOKE <privileges> 语句 撤销 用户或者角色的权限 。 对于角色和用户可以授予的权限和撤销的权限类型,详情参阅 GRANT文档。

概要

图片

所需权限

撤销权限需要对指定的数据库或表 GRANT 权限。

Parameters

参数 描述
table_name 撤销权限对应的表。若想要撤销对多个表的权限,用逗号分隔表名称,若要撤销所有表权限则使用符号 *
database_name 撤销权限对应的数据库。若想要撤销多个库的权限,用逗号分隔库名称。
user_name 想要撤销权限的 用户 和/或者 角色

示例

撤销数据库权限

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)

注意:任何一个在这之前继承数据库权限的表 都保留其权限。

撤销数据库中指定表的权限

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)

撤销数据库中所有表的权限

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)

其他