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

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對(duì)這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡(jiǎn)單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：申請(qǐng)域名、雅安服務(wù)器托管、營(yíng)銷軟件、網(wǎng)站建設(shè)、剛察網(wǎng)站維護(hù)、網(wǎng)站推廣。

一：用戶的創(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密碼的時(shí)候，可以這樣

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用戶時(shí)，@后面的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)

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

1） 'liuwenhe'@'192.168.0.%'這類的用戶是不能在本地登錄的，要想在本地登錄，需要有l(wèi)ocalhost或者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)錯(cuò)：

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

在另一臺(tái)機(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.%'用戶的時(shí)候，'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)

依舊在另一臺(tái)機(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）我的這臺(tái)mysql所在的服務(wù)器上面有兩個(gè)ip,如下：

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

那么只能通過

mysql -uliuwenhe -pliuwenhe8 -h292.168.8.238登陸，不能通過mysql -uliuwenhe -pliuwenhe8 -h292.168.0.12登陸，同理創(chuàng)建了一個(gè) '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)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

本文標(biāo)題：mysql中用戶管理和權(quán)限控制的示例分析
新聞來源：http://jinyejixie.com/article22/ppsgjc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、做網(wǎng)站、網(wǎng)站排名、微信小程序、全網(wǎng)營(yíng)銷推廣、手機(jī)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)