mysql root 登录权限管理_MySQL用户管理和权限控制详述

最近朋友问我一些MySQL用户的问题，就做了一些测试，记录如下：

一：用户的创建(两种方法)：

方法一：CREATE USER 'username'@'%' IDENTIFIED BY 'password';

方法二：GRANT select ON databasename.tablename TO 'username'@'%' ;

二：mysql root用户密码设置以及修改。

方法1： 用SET PASSWORD命令

mysql -u root

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

方法2：用mysqladmin

mysqladmin -u root password "newpass"

如果root已经设置过密码，采用如下方法

mysqladmin -u root password oldpass "newpass"

方法3： 用UPDATE直接编辑user表

mysql -u root

mysql> use mysql;

mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

mysql> FLUSH PRIVILEGES;

在丢失root密码的时候，可以这样

mysqld_safe --skip-grant-tables&

mysql -u root mysql

mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

mysql> FLUSH PRIVILEGES;

三：重点讲解创建mysql用户时，@后面的ip的意义：就是为了限制登陆mysql的ip，具体有如下：

1)只允许在本地登录；

mysql> CREATE USER 'liuwenhe'@'localhost' IDENTIFIED BY 'liuwenhelocal';

Query OK, 0 rows affected (0.00 sec)

2)允许在192.168.0网段登陆mysql；

mysql> CREATE USER 'liuwenhe'@'192.168.0.%' IDENTIFIED BY 'liuwenhe0';

Query OK, 0 rows affected (0.00 sec)

3)允许在192.168.8网段登陆mysql；

mysql> CREATE USER 'liuwenhe'@'192.168.8.%' IDENTIFIED BY 'liuwenhe8';

Query OK, 0 rows affected (0.00 sec)

4)没有限制，也就是可以在任何网络段登陆(前提是网络得通)；

mysql> CREATE USER 'liuwenhe'@'%' IDENTIFIED BY 'liuwenheall';

Query OK, 0 rows affected (0.00 sec)

针对上面这几个liuwenhe用户做了一些测试，结果如下：

1) 'liuwenhe'@'192.168.0.%'这类的用户是不能在本地登录的，要想在本地登录，需要有localhost或者127.0.0.1的登陆权限；

需要注意的是，如果你只创建了用户 'liuwenhe'@'localhost' ，

1.mysql> CREATE USER 'liuwenhe'@'localhost' IDENTIFIED BY 'liuwenhelocal';

Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user;

+--------------+----------+

| host | user |

+--------------+----------+

| % | ogg |

| % | root |

| 127.0.0.1 | root |

| 192.168.0.% | ncms |

| 192.168.0.13 | rep |

| localhost | liuwenhe |

| localhost | ncms |

| localhost | ogg |

| localhost | root |

| server01 | root |

+--------------+----------+

10 rows in set (0.00 sec)

如下两种登陆方式都能成功：

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal -hlocalhost

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal -h127.0.0.1

2.如果你只创建了liuwenhe'@'l127.0.0.1'，

mysql> select host,user from mysql.user;

+--------------+----------+

| host | user |

+--------------+----------+

| % | ogg |

| % | root |

| 127.0.0.1 | liuwenhe |

| 127.0.0.1 | root |

| 192.168.0.% | ncms |

| 192.168.0.13 | rep |

| localhost | ncms |

| localhost | ogg |

| localhost | root |

| server01 | root |

+--------------+----------+

10 rows in set (0.00 sec)

只能通过mysql -uliuwenhe -pliuwenhelocal -h127.0.0.1登陆，不能通过 mysql -uliuwenhe -pliuwenhelocal -hlocalhost登陆；

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal -h127.0.0.1

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3628

Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

不能通过localhost登陆，如下报错：

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal -hlocalhost

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'liuwenhe'@'localhost' (using password: YES)

2)如果你同时创建了'liuwenhe'@'192.168.0.%'和'liuwenhe'@'%'这两个用户，那么当你从192.168.0网段去登陆数据库的时候，'liuwenhe'@'%'用户是不能登陆数据库的，只能通过'liuwenhe'@'192.168.0.%'登陆，但是当你删除'liuwenhe'@'192.168.0.%'用户的时候，'liuwenhe'@'%'用户就可以登陆了，可以理解为mysql优先并且只会验证匹配度高的用户，

具体验证过程如下：

mysql> select host,user from mysql.user;

+--------------+----------+

| host | user |

+--------------+----------+

| % | liuwenhe |

| % | ogg |

| % | root |

| 127.0.0.1 | root |

| 192.168.0.% | liuwenhe |

| 192.168.0.% | ncms |

| 192.168.0.13 | rep |

| localhost | ncms |

| localhost | ogg |

| localhost | root |

| server01 | root |

+--------------+----------+

11 rows in set (0.00 sec)

在另一台机器S244(192.168.0.244)尝试登陆mysql：

使用'liuwenhe'@'%'用户登录失败：如下

[root@S244 ~]# mysql -uliuwenhe -pliuwenheall -h192.168.0.12

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'liuwenhe'@'192.168.0.244' (using password: YES)

使用'liuwenhe'@'192.168.0.%'用户登录成功，如下：

[root@S244 ~]# mysql -uliuwenhe -pliuwenhe0 -h192.168.0.12

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3679

Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

当你删除'liuwenhe'@'192.168.0.%'用户的时候，'liuwenhe'@'%'用户就可以登陆了，如下：

mysql> delete from mysql.user where user='liuwenhe' and host='192.168.0.%';

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

依旧在另一台机器S244(192.168.0.244)尝试使用'liuwenhe'@'%'用户登陆mysql，成功了：

[root@S244 ~]# mysql -uliuwenhe -pliuwenheall -h192.168.0.12

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3681

Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3)我的这台mysql所在的服务器上面有两个ip,如下：

现在我创建了一个 'liuwenhe'@'192.168.8.%' ，

那么只能通过

mysql -uliuwenhe -pliuwenhe8 -h192.168.8.238登陆，不能通过mysql -uliuwenhe -pliuwenhe8 -h192.168.0.12登陆，同理创建了一个 'liuwenhe'@'192.168.0.%' ，只能通过

mysql -uliuwenhe -pliuwenhe0 -h192.168.0.12登陆，不能通过mysql -uliuwenhe -pliuwenhe0 -h192.168.8.238登陆

验证如下：

mysql> CREATE USER 'liuwenhe'@'192.168.0.%' IDENTIFIED BY 'liuwenhe0';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@server02 ~]# mysql -uliuwenhe -pliuwenhe0 -h192.168.0.12

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3704

Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit

Bye

[root@server02 ~]# mysql -uliuwenhe -pliuwenhe0 -h192.168.8.238

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'liuwenhe'@'192.168.8.238' (using password: YES)

小结：192.168.0.12 和192.168.8.268是不同的两个网段，需要网关才能互联，但是mysql你创建的用户CREATE USER 'liuwenhe'@'192.168.8.%' IDENTIFIED BY 'liuwenhe8';允许从8网段的ip登录mysql，然后你mysql -uliuwenhe -pliuwenhe8 -h192.168.0.12是登录不上的，因为这俩网段是不通的，但是你mysql -uliuwenhe -pliuwenhe8 -h192.168.8.238就可以；