文章目录
使用部分撤销的权限限制
使用部分撤销
partial_revokes
系统变量控制是否可以对帐户设置权限限制。默认情况下,partial_revokes
被禁用,尝试部分撤销全局权限会产生错误:
mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> REVOKE INSERT ON world.* FROM u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
要允许 REVOKE
操作,请启用 partial_revokes
:
SET PERSIST partial_revokes = ON;
SET PERSIST
为正在运行的 MySQL 实例设置一个值。它还保存该值,使其延续到随后的服务器重新启动。要更改正在运行的 MySQL 实例的值而不使其延续到后续重新启动,请使用 GLOBAL
关键字而不是 PERSIST
启用 partial_revokes
后,部分撤销成功:
mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@% |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+
SHOW GRANTS
在其输出中将部分撤销列为 REVOKE
语句。结果表明 u1
具有全局 SELECT
和 INSERT
权限,但不能对 world
schem中的表执行 INSERT
。也就是说,u1
对world
表的访问是只读的。
服务器在mysql.user
系统表中记录了通过部分撤销实现的权限限制。如果帐户已部分撤销,则其 User_attributes
列值具有 Restrictions
属性:
mysql> SELECT User, Host, User_attributes->>'$.Restrictions'
FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';
+------+------+------------------------------------------------------+
| User | Host | User_attributes->>'$.Restrictions' |
+------+------+------------------------------------------------------+
| u1 | % | [{"Database": "world", "Privileges": ["INSERT"]}] |
+------+------+------------------------------------------------------+
部分撤销操作受以下条件的约束:
- 可以使用部分撤销对不存在的schema施加限制,但仅当撤销的权限是全局的。如果一个权限没不是全局的,对于一个不存在的schema撤销它将产生一个错误。
- 部分撤销仅适用于schema级别。不能对表、列或例程权限使用部分撤销。
- 在权限分配中,启用
partial_revokes
会导致MySQL将schema名中出现的未转义的_
和%
SQL通配符解释为文字字符,就像它们被转义为\_
和\%
一样。因为这改变了MySQL解释权限的方式,所以建议在可能启用partial_revokes
的安装中避免在权限分配中使用未转义的通配符。
如前所述,schema级别权限的部分撤销在SHOW GRANTS
输出中以REVOKE
语句的形式出现。这与SHOW GRANTS
表示“普通”schema级别权限的方式不同:
-
授予后,schema级别权限在输出中由它们自己的
GRANT
语句表示:mysql> CREATE USER u1; mysql> GRANT UPDATE ON mysql.* TO u1; mysql> GRANT DELETE ON world.* TO u1; mysql> SHOW GRANTS FOR u1; +---------------------------------------+ | Grants for u1@% | +---------------------------------------+ | GRANT USAGE ON *.* TO `u1`@`%` | | GRANT UPDATE ON `mysql`.* TO `u1`@`%` | | GRANT DELETE ON `world`.* TO `u1`@`%` | +---------------------------------------+
-
撤消后,schema级別权限只会从输出中消失。它们不会作为
REVOKE
语句出现:mysql> REVOKE UPDATE ON mysql.* FROM u1; mysql> REVOKE DELETE ON world.* FROM u1; mysql> SHOW GRANTS FOR u1; +--------------------------------+ | Grants for u1@% | +--------------------------------+ | GRANT USAGE ON *.* TO `u1`@`%` | +--------------------------------+
当用户授予权限时,授予者对该权限的任何限制都会由被授予者继承,考虑以下两个用户,其中一个拥有全局
SELECT
权限:CREATE USER u1, u2; GRANT SELECT ON *.* TO u2;
假设管理用户
admin
具有部分被撤销SELECT
的全局权限:mysql> CREATE USER admin; mysql> GRANT SELECT ON *.* TO admin WITH GRANT OPTION; mysql> REVOKE SELECT ON mysql.* FROM admin; mysql> SHOW GRANTS FOR admin; +------------------------------------------------------+ | Grants for admin@% | +------------------------------------------------------+ | GRANT SELECT ON *.* TO `admin`@`%` WITH GRANT OPTION | | REVOKE SELECT ON `mysql`.* FROM `admin`@`%` | +------------------------------------------------------+
如果管理员将
SELECT
全局权限授予u1
和u2
,则每个用户的结果都不同: -
如果
admin
授予全局SELECT
权限给没有SELECT
权限的u1
,则u1
继承admin
权限的限制:mysql> GRANT SELECT ON *.* TO u1;mysql> SHOW GRANTS FOR u1;+------------------------------------------+| Grants for u1@% |+------------------------------------------+| GRANT SELECT ON *.* TO `u1`@`%` || REVOKE SELECT ON `mysql`.* FROM `u1`@`%` |+------------------------------------------+
-
另一方面,
u2
已经拥有一个不受限制的全局SELECT
权限。u2
不会继承admin限制:mysql> GRANT SELECT ON *.* TO u2;mysql> SHOW GRANTS FOR u2;+---------------------------------+| Grants for u2@% |+---------------------------------+| GRANT SELECT ON *.* TO `u2`@`%` |+---------------------------------+
对授予帐户的新权限的限制将添加到该帐户的现有限制中:
mysql> CREATE USER u1;mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;mysql> REVOKE INSERT ON mysql.* FROM u1;mysql> SHOW GRANTS FOR u1;+---------------------------------------------------------+| Grants for u1@% |+---------------------------------------------------------+| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` || REVOKE INSERT ON `mysql`.* FROM `u1`@`%` |+---------------------------------------------------------+mysql> REVOKE DELETE, UPDATE ON db2.* FROM u1;mysql> SHOW GRANTS FOR u1;+---------------------------------------------------------+| Grants for u1@% |+---------------------------------------------------------+| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` || REVOKE UPDATE, DELETE ON `db2`.* FROM `u1`@`%` || REVOKE INSERT ON `mysql`.* FROM `u1`@`%` |+---------------------------------------------------------+