通过修改MySQL用户授权IP了解其用户管理

下文主要给大家带来通过修改Mysql用户授权IP了解其用户管理，希望这些内容能够带给大家实际用处，这也是我编辑通过修改mysql用户授权IP了解其用户管理这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。

近期把数据库用户的授权IP由IP段, 调整为具体IP了, 用意是排查问题时, 可以定位到具体应用云服务器, 或针对性的做某些设置.

本以为一个UPDATE就可搞定, 测试后却发现被修改的用户只剩下USAGE权限了, 演示如下:

mysql> SELECT user, host, passWord FROM mysql.userWHERE user = 'zzzz_acc'\G

*************************** 1. row***************************

   user: zzzz_acc

   host: 192.168.4.%

password:*2B979ED0716E5FCB08CA97C284FE270B65991F34

1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'zzzz_acc'@'192.168.4.%'\G

*************************** 1. row***************************

Grants for zzzz_acc@192.168.4.%: GRANTUSAGE ON *.* TO 'zzzz_acc'@'192.168.4.%' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

*************************** 2. row***************************

Grants for zzzz_acc@192.168.4.%: GRANTSELECT, INSERT, DELETE, EXECUTE ON `zzzz`.* TO 'zzzz_acc'@'192.168.4.%'

2 rows in set (0.00 sec)

mysql> UPDATE mysql.user SET host ='192.168.4.14' WHERE user = 'zzzz_acc' AND host = '192.168.4.%';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1 Warnings: 0

手动修改权限表后, 要FLUSH下:

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR'zzzz_acc'@'192.168.4.14'\G

*************************** 1. row***************************

Grants for zzzz_acc@192.168.4.14: GRANTUSAGE ON *.* TO 'zzzz_acc'@'192.168.4.14' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

1 row in set (0.00 sec)

原因呢, 是该用户权限是数据库级别的, mysql.db表也要做相应的修改:

mysql> SELECT host, db, user FROM dbWHERE host = '192.168.4.%'\G

*************************** 1. row***************************

host: 192.168.4.%

  db:zzzz

user: zzzz_acc

1 row in set (0.00 sec)

mysql> UPDATE mysql.db SET host ='192.168.4.14' WHERE user = 'zzzz_acc' AND host = '192.168.4.%';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1 Warnings: 0

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

瞧, 这时权限回来了:

mysql> SHOW GRANTS FOR'zzzz_acc'@'192.168.4.14'\G

*************************** 1. row***************************

Grants for zzzz_acc@192.168.4.14: GRANTUSAGE ON *.* TO 'zzzz_acc'@'192.168.4.14' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

*************************** 2. row***************************

Grants for zzzz_acc@192.168.4.14: GRANTSELECT, INSERT, DELETE, EXECUTE ON `zzzz`.* TO 'zzzz_acc'@'192.168.4.14'

2 rows in set (0.00 sec)

总结下, 就是一般不要手动直接修改权限表, 官方文档也不建议这样. MySQL中对于用户的管理, 如创建用户, 调整权限, 修改密码等, 都提供了相应的命令. 此处利用RENAME USER, 即可一步到位:

mysql> RENAME USER'zzzz_acc'@'192.168.4.%' TO 'zzzz_acc'@'192.168.4.14';

Query OK, 0 rows affected (0.01 sec)

但也存在着变通, 这里想起之前遇到的一个问题, 数据库的管理用户'root'@'localhost'有ALL PRIVILEGES的权限, 但没有了GRANT OPTioN权限(不要问我为什么会这样). 即该管理用户, 不能赋予和撤销其它用户的权限, 也意味着不能创建实际有效的用户了, 演示如下:

mysql> SHOW GRANTS\G

*************************** 1. row***************************

Grants for root@localhost: GRANT ALLPRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BYPASSWORD '*2B979ED0716E5FCB08CA97C284FE270B65991F34'

1 row in set (0.00 sec)

mysql> CREATE USER'zzzz_acc2'@'192.168.4.15' IDENTIFIED BY 'zzzz_acc2';

Query OK, 0 rows affected (0.15 sec)

mysql> GRANT SELECT, INSERT, DELETE,EXECUTE ON `zzzz`.* TO 'zzzz_acc2'@'192.168.4.15';

ERROR 1044 (42000): Access denied for user'root'@'localhost' to database 'zzzz'

怎么办呢, 也只能直接修改mysql.user表的grant_priv字段了:

mysql> SELECT user, host, password,grant_priv FROM mysql.user WHERE user = 'root' AND host = 'localhost'\G

*************************** 1. row***************************

      user: root

     host: localhost

 password: *2B979ED0716E5FCB08CA97C284FE270B65991F34

grant_priv: N

1 row in set (0.00 sec)

mysql> UPDATE mysql.user SET grant_priv= 'Y' WHERE user = 'root' AND host = 'localhost';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1 Warnings: 0

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS\G

*************************** 1. row***************************

Grants for root@localhost: GRANT ALLPRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34' WITH GRANTOPTION

1 row in set (0.00 sec)

下面注意了, 虽然看到了GRANT OPTION权限, 还要重新登陆下, 该权限才能生效, 类似于修改了全局参数, 要重新连接下一样, 否则还会遇到上面的报错.

mysql> GRANT SELECT, INSERT,DELETE, EXECUTE ON `zzzz`.* TO 'zzzz_acc2'@'192.168.4.15';

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR'zzzz_acc2'@'192.168.4.15'\G

*************************** 1. row***************************

Grants for zzzz_acc2@192.168.4.15: GRANTUSAGE ON *.* TO 'zzzz_acc2'@'192.168.4.15' IDENTIFIED BY PASSWORD'*0CF81DDE0A7213E4AB18F2925316C049BCF3F2E8'

*************************** 2. row***************************

Grants for zzzz_acc2@192.168.4.15: GRANTSELECT, INSERT, DELETE, EXECUTE ON `zzzz`.* TO 'zzzz_acc2'@'192.168.4.15'

2 rows in set (0.00 sec)

上面两个示例涉及到了MySQL权限表中的user, 和db. 前者记录了用户的全局权限和一些非权限信息, 后者记录了数据库级别权限; 与权限相关的还有table_priv, columns_priv和 proc_priv表, 分别对应了表, 列, 和存储过程(函数)级别的权限(proxies_priv代理用户权限暂不提).

最后再看下MySQL 5.6版本在管理用户密码部分中不太完善的地方, 由于项目临时调试, 需要开放线上数据库一个特定时长的临时只读权限(在从库上), 可是5.6版本只提供了密码过期设置, 并没有对过期时长做限制. 这些细节在5.7版本中有很多完善, 如密码何时过期, 用户锁定, SSL加密连接设置等.

对于以上关于通过修改MySQL用户授权IP了解其用户管理，大家是不是觉得非常有帮助。如果需要了解更多内容，请继续关注我们的数据库，相信你会喜欢上这些内容的。