很多亲们在安装好了mysql数据库之后，对于mysql用户表并没有做任何特殊的处理，因此缺省情况下，存在密码为空的用户，也有很多用户名和密码都为空的情形，我们称之为双空用户。这种情形下的登录，在此统称为异常登陆。对于生产环境的数据库来说，这会带来一些不确定的安全隐患。下面是关于这个问题的描述以及清理掉无关用户的方法。
有关mysql用户相关参考：
mysql 用户与权限管理
mysql 修改用户密码及重置root密码
1、演示异常登录
a、演示双空用户登陆 [root@xlkoracel ~]# mysql -uroot -p Enter password: (root@localhost) [(none)]> show variables like 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.6.26 | +---------------+--------+ (root@localhost) [(none)]> select user,host,password from mysql.user; +-------+-------------+-------------------------------------------+ | user | host | password | +-------+-------------+-------------------------------------------+ | root | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | | root | xlkoracel | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | xlkoracel | | | mycat | localhost | *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD | | mycat | 192.168.1.% | *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD | | mycat | 192.168.%.% | *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD | | root | 192.168.%.% | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | +-------+-------------+-------------------------------------------+ (root@localhost) [(none)]> -- 可以看到存在用户名和密码同时为空的情形 (root@localhost) [(none)]> -- 退出后尝试使用任意用户名登录 (root@localhost) [(none)]> exit Bye [root@xlkoracel ~]# mysql -uxx ###无需指定密码参数-p (xx@localhost) [(none)]> -- 可以成功登陆 (xx@localhost) [(none)]> -- 下面查看一下自身的权限 (xx@localhost) [(none)]> show grants; --当前只有usage权限 +--------------------------------------+ | Grants for @localhost | +--------------------------------------+ | GRANT USAGE ON *.* TO ''@'localhost' | +--------------------------------------+ (xx@localhost) [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ (xx@localhost) [(none)]> use test; Database changed (xx@localhost) [test]> show tables; Empty set (0.00 sec) (xx@localhost) [test]> create table t(id int); Query OK, 0 rows affected (0.14 sec) (xx@localhost) [test]> insert into t values(1); Query OK, 1 row affected (0.01 sec) (xx@localhost) [test]> select * from t; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) (xx@localhost) [test]> --从上可以看出，usage权限已经可以完成很多任务 (xx@localhost) [test]> use infromation_schema; ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'infromation_schema' (xx@localhost) [test]> exit; b、演示密码为空的用户登陆 [root@xlkoracel ~]# mysql -uroot -hxlkoracel ###注，此时也无需指定参数-p (root@xlkoracel) [(none)]> --可以成功登陆 (root@xlkoracel) [(none)]> show grants; --查看自身权限，为ALL PRIVILEGES，权限更大 +---------------------------------------------------------------------+ | Grants for root@xlkoracel | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'xlkoracel' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'xlkoracel' WITH GRANT OPTION | +---------------------------------------------------------------------+
2、清理异常用户
[root@xlkoracel ~]# mysql -uroot -p Enter password: (root@localhost) [(none)]> select user,host,password from mysql.user -> where (user is null or user='') and (password is null or password=''); +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | | localhost | | | | xlkoracel | | +------+-----------+----------+ 2 rows in set (0.01 sec) (root@xlkoracel) [(none)]> -- Author : Leshami (root@xlkoracel) [(none)]> -- Blog : http://www.php.cn/ (root@localhost) [(none)]> -- 使用drop 方式清理用户 (root@localhost) [(none)]> drop user ''@'localhost'; Query OK, 0 rows affected (0.24 sec) (root@localhost) [(none)]> select user,host,password from mysql.user -> where (user is null or user='') and (password is null or password=''); +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | | xlkoracel | | +------+-----------+----------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> -- 直接用delete从mysql.user表清理用户 (root@localhost) [(none)]> delete from mysql.user -> where (user is null or user='') and (password is null or password=''); Query OK, 1 row affected (0.06 sec) (root@localhost) [(none)]> -- 直接用delete从mysql.user表清理所有密码为空的用户 (root@xlkoracel) [(none)]> delete from mysql.user where password is null or password=''; Query OK, 3 rows affected (0.00 sec)
3、小结
a、对于部署到生产环境的mysql服务器建议清理所有密码为空的用户以及双空用户
b、建议清理前先备份，使用drop user方式来清理用户更稳妥
以上就是加强MySQL用户安全 的内容，更多相关内容请关注PHP中文网（www.php.cn）！
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
