加强MySQL用户安全
- - CSDN博客推荐文章 很多亲们在安装好了MySQL数据库之后,对于mysql用户表并没有做任何特殊的处理,因此缺省情况下,存在密码为空的用户,也有很多用户名和密码都为空的情形,我们称之为双空用户. 这种情形下的登录,在此统称为异常登陆. 对于生产环境的数据库来说,这会带来一些不确定的安全隐患. 下面是关于这个问题的描述以及清理掉无关用户的方法.
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://blog.csdn.net/leshami (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)