共計(jì) 6509 個字符，預(yù)計(jì)需要花費(fèi) 17 分鐘才能閱讀完成。

這篇文章主要介紹 mysql 中用戶管理和權(quán)限控制的示例分析，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

一：用戶的創(chuàng)建（兩種方法）：

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

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

二：mysql root 用戶密碼設(shè)置以及修改。

方法 1：用 SET PASSWORD 命令

mysql -u root

mysql SET PASSWORD FOR root @ localhost = PASSWORD(newpass

方法 2：用 mysqladmin

mysqladmin -u root password newpass

如果 root 已經(jīng)設(shè)置過密碼，采用如下方法

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;

三：重點(diǎn)講解創(chuàng)建 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 網(wǎng)段登陸 mysql；

mysql CREATE USER liuwenhe @ 192.168.0.% IDENTIFIED BY liuwenhe0

Query OK, 0 rows affected (0.00 sec)

3）允許在 192.168.8 網(wǎng)段登陸 mysql；

mysql CREATE USER liuwenhe @ 192.168.8.% IDENTIFIED BY liuwenhe8

Query OK, 0 rows affected (0.00 sec)

4）沒有限制，也就是可以在任何網(wǎng)絡(luò)段登陸（前提是網(wǎng)絡(luò)得通）；

mysql CREATE USER liuwenhe @ % IDENTIFIED BY liuwenheall

Query OK, 0 rows affected (0.00 sec)

針對上面這幾個 liuwenhe 用戶做了一些測試，結(jié)果如下：

1）liuwenhe @ 192.168.0.% 這類的用戶是不能在本地登錄的，要想在本地登錄，需要有 localhost 或者 127.0.0.1 的登陸權(quán)限；

需要注意的是，如果你只創(chuàng)建了用戶 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  -h227.0.0.1

2. 如果你只創(chuàng)建了 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  -h227.0.0.1 登陸，不能通過 mysql -uliuwenhe -pliuwenhelocal  -hlocalhost 登陸；

[root@server02 ~]# mysql -uliuwenhe -pliuwenhelocal  -h227.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 登陸，如下報(bào)錯：

[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）如果你同時創(chuàng)建了 liuwenhe @ 192.168.0.% 和 liuwenhe @ % 這兩個用戶，那么當(dāng)你從 192.168.0 網(wǎng)段去登陸數(shù)據(jù)庫的時候，liuwenhe @ % 用戶是不能登陸數(shù)據(jù)庫的，只能通過 liuwenhe @ 192.168.0.% 登陸，但是當(dāng)你刪除 liuwenhe @ 192.168.0.% 用戶的時候，liuwenhe @ % 用戶就可以登陸了，可以理解為 mysql 優(yōu)先并且只會驗(yàn)證匹配度高的用戶，

具體驗(yàn)證過程如下：

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)

在另一臺機(jī)器 S244（192.168.0.244）嘗試登陸 mysql：

使用 liuwenhe @ % 用戶登錄失敗：如下

[root@S244 ~]# mysql -uliuwenhe -pliuwenheall  -h292.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  -h292.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

當(dāng)你刪除 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)

依舊在另一臺機(jī)器 S244（192.168.0.244）嘗試使用 liuwenhe @ % 用戶登陸 mysql，成功了：

[root@S244 ~]# mysql -uliuwenhe -pliuwenheall  -h292.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 所在的服務(wù)器上面有兩個 ip, 如下：

現(xiàn)在我創(chuàng)建了一個 liuwenhe @ 192.168.8.%，

那么只能通過

mysql -uliuwenhe -pliuwenhe8 -h292.168.8.238 登陸，不能通過 mysql -uliuwenhe -pliuwenhe8 -h292.168.0.12 登陸，同理創(chuàng)建了一個 liuwenhe @ 192.168.0.%，只能通過

mysql -uliuwenhe -pliuwenhe0 -h292.168.0.12 登陸，不能通過 mysql -uliuwenhe -pliuwenhe0 -h292.168.8.238 登陸

驗(yàn)證如下：

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  -h292.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  -h292.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)

以上是“mysql 中用戶管理和權(quán)限控制的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！