三、刪除MySQL系統(tǒng)多余賬號

語法：drop user “user”@“主機(jī)域”? ?

四、創(chuàng)建MySQL用戶及賦予用戶權(quán)限

1、通過help查看grant命令幫助

1)通過在mysql中輸入“help grant”得到如下幫助信息

mysql>help grant

CREATE USER'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

GRANT SELECT ON db2.invoice TO'jeffrey'@'localhost';

GRANT USAGE ON*.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

通過查看grant的命令幫助，可以很容易的找到創(chuàng)建用戶并授權(quán)的例子！

2)運(yùn)維人員比較常用的創(chuàng)建用戶的方法是，使用grant命令在創(chuàng)建用戶的同時進(jìn)行權(quán)限授權(quán)。具體授權(quán)例子為：

grant all on db1.* to 'jeffrey'@'localhost' identified by 'mypass';

3)上述grant命令幫助里還提供了一個先用create命令創(chuàng)建用戶，然后再用grant授權(quán)的方法，即創(chuàng)建用戶和授權(quán)權(quán)限分開進(jìn)行，例：

create user 'jeffrey'@'localhost' identified by 'mypass';

grant all on db1.* to 'jeffrey'@'localhost';

以上倆條命令相當(dāng)于下面一條命令：

grant all on db1.* to 'jeffrey'@'loaclhost' identified by 'mypass';

2、通過grant命令創(chuàng)建用戶并授權(quán)

1)grant命令簡單語法如下：

grant all privilege on dbname.* username@'localhost' identified by 'passwd';

2)列表說明如下：

grant

all privilege

on dbname.*

to username@localhost

identified by 'passwd'

授權(quán)命令

對應(yīng)權(quán)限

目標(biāo)：庫和表

用戶名和客戶端主機(jī)

用戶密碼

說明：上述命令是授權(quán)l(xiāng)ocalhost主機(jī)上通過用戶username管理dbname數(shù)據(jù)庫的所有權(quán)限，密碼為passwd。其中username，dbname，passwd可根據(jù)業(yè)務(wù)的情況修改。

3)操作案例1：創(chuàng)建oldboy用戶，對test庫具備所有權(quán)限，允許從localhost主機(jī)登陸管理數(shù)據(jù)庫，密碼是oldboy123。

mysql> select user,host frommysql.user;+------+-----------------------+

| user | host |

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

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| root | localhost |

| | localhost.localdomain |

| root | localhost.localdomain |

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

6 rows in set (0.00sec)

mysql> grant all privileges on test.* to oldboy@'localhost' identified by 'oldboy123';

Query OK,0 rows affected (0.28sec)

mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

mysql> select user,host frommysql.user;+--------+-----------------------+

| user | host |

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

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| oldboy | localhost |

| root | localhost |

| | localhost.localdomain |

| root | localhost.localdomain |

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

7 rows in set (0.00 sec)

查看授權(quán)用戶oldboy具體權(quán)限

mysql> show grants foroldboy@localhost;+---------------------------------------------------------------------------------------------------------------+

| Grants for oldboy@localhost |

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

| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost' |

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

2 rows in set (0.00 sec)

3、create和grant配合法

1)首先創(chuàng)建用戶username及密碼passwd，授權(quán)主機(jī)localhost

create user 'username'@'localhost' identified by 'passwd';

2)然后授權(quán)l(xiāng)ocalhost主機(jī)上通過用戶username管理dbname數(shù)據(jù)庫的所有權(quán)限，無需密碼。

grant all on dbname.* to 'username'@'loaclhost';

3)操作案例2：創(chuàng)建oldgirl用戶，對test庫具備所有權(quán)限，允許從localhost主機(jī)登陸管理數(shù)據(jù)庫，密碼是oldgirl123.

4)實(shí)戰(zhàn)演示

查看當(dāng)前數(shù)據(jù)庫用戶情況，然后執(zhí)行對應(yīng)命令創(chuàng)建用戶如下：

mysql> create user oldgirl@localhost identified by 'oldgirl123';

Query OK,0 rows affected (0.00sec)

mysql> show grants foroldgirl@localhost;+----------------------------------------------------------------------------------------------------------------+

| Grants for oldgirl@localhost |

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

| GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C' |

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

1 row in set (0.00sec)

mysql> grant all on test.* to 'oldgirl'@'localhost';

Query OK,0 rows affected (0.00sec)

mysql> show grants foroldgirl@localhost;+----------------------------------------------------------------------------------------------------------------+

| Grants for oldgirl@localhost |

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

| GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'oldgirl'@'localhost' |

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

2 rows in set (0.00 sec)

4、授權(quán)局域網(wǎng)內(nèi)主機(jī)遠(yuǎn)程連接數(shù)據(jù)庫：

根據(jù)grant命令語法，我們知道test@‘localhost’位置為授權(quán)訪問數(shù)據(jù)庫的主機(jī)，localhost可以用域名，IP地址或IP段來替代，因此，要授權(quán)局域網(wǎng)內(nèi)主機(jī)可以通過如下發(fā)放實(shí)現(xiàn)：

a.百分號匹配法

mysql> create user test@'10.0.0.%' identified by 'test';

Query OK,0 rows affected (0.00sec)

mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

mysql>quit

Bye

[root@localhost~]# mysql -utest -ptest -h 10.0.0.7Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 8Server version:5.5.32Source distribution

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

Oracleis 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>

b.子網(wǎng)掩碼匹配法

mysql> create user test1@'10.0.0.0/255.255.255.0' identified by 'test1';

Query OK,0 rows affected (0.00sec)

mysql> select user,host frommysql.user;+---------+------------------------+

| user | host |

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

| test | 10.0.0.% |

| test1 | 10.0.0.0/255.255.255.0 |

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| oldboy | localhost |

| oldgirl | localhost |

| root | localhost |

| | localhost.localdomain |

| root | localhost.localdomain |

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

10 rows in set (0.00sec)

mysql>quit

Bye

[root@localhost~]# mysql -utest1 -ptest1 -h 10.0.0.7Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 10Server version:5.5.32Source distribution

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

Oracleis 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>

通過mysql客戶端連接異地?cái)?shù)據(jù)庫服務(wù)：

1)本地mysql -uroot -p‘oldboy123’ 連接數(shù)據(jù)庫相當(dāng)于?mysql -uroot -p‘oldboy123’ -h localhost

2)要遠(yuǎn)程連接10.0.0.7的數(shù)據(jù)庫，命令為mysql -utest -p‘test’ -h 10.0.0.7，如果要能成功連接，還需要在10.0.0.7的數(shù)據(jù)庫服務(wù)器上通過如下命令授權(quán)：

grant all on *.* to test@‘10.0.0.%’ identified by ‘test’；

3)通過php服務(wù)器連接mysql服務(wù)器的代碼寫法為；

//$link_id=mysql_connect('主機(jī)名','用戶','密碼')；

$link_id=mysql_connect('10.0.0.7','test','test') or mysql_error();if($link_id){

echo"musql successful by oldboy!";}else{

echo mysql_error();}

?>

5、MySQL用戶可以授權(quán)的權(quán)限有哪些

1)先看看前面授權(quán)過的oldboy的權(quán)限

mysql> show grants for oldboy@localhost;

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

| Grants for oldboy@localhost |

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

| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost' |

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

2 rows in set (0.00 sec)

2)取消oldboy的只讀權(quán)限(SELECT)看看。

mysql> REVOKE INSERT ON test.* FROM 'oldboy'@'localhost';Query OK, 0 rows affected (0.00sec)

mysql> show grants for oldboy@localhost;+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for oldboy@localhost |

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

| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |

| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT,TRIGGER ON `test`.* TO 'oldboy'@'localhost' |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

提示：此時我們再查看oldboy用戶權(quán)限，ALL PRIVILEGES 權(quán)限已經(jīng)被細(xì)分了，但是沒有select權(quán)限了。

因?yàn)?#xff0c;我們可以得出結(jié)論，ALL PRIVILEGES包括權(quán)限為：

[root@localhost ~]# mysql -uroot -pdubin -e "show grants for oldboy@localhost;"|grep -i grant |tail -1 |tr ',' '\n' >all.txt[root@localhost ~]# cat all.txt -n1SELECT2INSERT3UPDATE4DELETE5CREATE6DROP7REFERENCES8INDEX9ALTER10CREATE TEMPORARY TABLES11LOCK TABLES12EXECUTE13CREATE VIEW14SHOW VIEW15CREATE ROUTINE16ALTER ROUTINE17EVENT18 TRIGGER

因此可以說，ALL PRIVILEGES的權(quán)限包括：

GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT,TRIGGER ON `test`.* TO 'oldboy'@'localhost'

即在授權(quán)時，可以授權(quán)用戶最小的滿足業(yè)務(wù)需求的權(quán)限，而不是一味的授權(quán)“ALL PRIVILEGES”。

6、企業(yè)生產(chǎn)環(huán)境如何授權(quán)用戶權(quán)限？

1)博客，CMS等產(chǎn)品的數(shù)據(jù)庫授權(quán)：

對于web連接用戶授權(quán)盡量采用最小化原則，很多開源軟件都是web界面安裝，因此，在安裝期間除了select，insert，update，delete4個權(quán)限外，還需要create，drop等比較危險的權(quán)限。

mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'10.0.0.%' identified by 'oldboy';Query OK, 0 rows affected (0.00 sec)

常規(guī)情況下授權(quán)select，insert，update，delete4個權(quán)限即可，有的開源軟件，例如discuzbbs，還需要create，drop等比較危險的權(quán)限

2)生成數(shù)據(jù)庫表后，要收回create、drop授權(quán)：

mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'10.0.0.%' identified by 'oldboy';Query OK, 0 rows affected (0.00sec)

mysql> revoke create on blog.* from 'blog'@'10.0.0.%';Query OK, 0 rows affected (0.00sec)

mysql> show grants for blog@'10.0.0.%';+------------------------------------------------------------------------------------------------------------+

| Grants for blog@10.0.0.% |

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

| GRANT USAGE ON *.* TO 'blog'@'10.0.0.%' IDENTIFIED BY PASSWORD '*7495041D24E489A0096DCFA036B166446FDDD992' |

| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `blog`.* TO 'blog'@'10.0.0.%' |

+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

總結(jié)

以上是生活随笔為你收集整理的mysql sql应用_MySQL数据库应用（5）SQL语言（2）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。