Mysql用戶授權(quán)（GRANT）語法

當(dāng)成功創(chuàng)建用戶賬戶后，還不能執(zhí)行任何操作，需要為該用戶分配適當(dāng)?shù)脑L問權(quán)限。可以使用 SHOW GRANT FOR 語句來查詢用戶的權(quán)限。

注意：新創(chuàng)建的用戶只有登錄 MySQL 服務(wù)器的權(quán)限，沒有任何其他權(quán)限，不能進(jìn)行其他操作。

USAGE ON*.* 表示該用戶對(duì)任何數(shù)據(jù)庫和任何表都沒有權(quán)限。

授予用戶權(quán)限語法

對(duì)于新建的 MySQL 用戶，必須給它授權(quán)，可以用 GRANT 語句來實(shí)現(xiàn)對(duì)新建用戶的授權(quán)。

語法格式：

GRANT
<權(quán)限類型> [ ( <列名> ) ] [ , <權(quán)限類型> [ ( <列名> ) ] ]
ON <對(duì)象> <權(quán)限級(jí)別> TO <用戶>
其中<用戶>的格式：
<用戶名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>
[ WITH GRANT OPTION]
| MAX_QUERIES_PER_HOUR <次數(shù)>
| MAX_UPDATES_PER_HOUR <次數(shù)>
| MAX_CONNECTIONS_PER_HOUR <次數(shù)>
| MAX_USER_CONNECTIONS <次數(shù)>

語法說明如下：

（1）<列名>

可選項(xiàng)。用于指定權(quán)限要授予給表中哪些具體的列。

（2） ON 子句

用于指定權(quán)限授予的對(duì)象和級(jí)別，如在 ON 關(guān)鍵字后面給出要授予權(quán)限的數(shù)據(jù)庫名或表名等。

（3）<權(quán)限級(jí)別>

用于指定權(quán)限的級(jí)別?？梢允谟璧臋?quán)限有如下幾組：

• 1-列權(quán)限，和表中的一個(gè)具體列相關(guān)。例如，可以使用 UPDATE 語句更新表 students 中 student_name 列的值的權(quán)限。
• 2-表權(quán)限，和一個(gè)具體表中的所有數(shù)據(jù)相關(guān)。例如，可以使用 SELECT 語句查詢表 students 的所有數(shù)據(jù)的權(quán)限。
• 3-數(shù)據(jù)庫權(quán)限，和一個(gè)具體的數(shù)據(jù)庫中的所有表相關(guān)。例如，可以在已有的數(shù)據(jù)庫 mytest 中創(chuàng)建新表的權(quán)限。
• 4-用戶權(quán)限，和 MySQL 中所有的數(shù)據(jù)庫相關(guān)。例如，可以刪除已有的數(shù)據(jù)庫或者創(chuàng)建一個(gè)新的數(shù)據(jù)庫的權(quán)限。

對(duì)應(yīng)地，在 GRANT 語句中可用于指定權(quán)限級(jí)別的值有以下幾類格式：

• 1-：表示當(dāng)前數(shù)據(jù)庫中的所有表。
• 2-.：表示所有數(shù)據(jù)庫中的所有表。
• 3-db_name.：表示某個(gè)數(shù)據(jù)庫中的所有表，db_name 指定數(shù)據(jù)庫名。
• 4-db_name.tbl_name：表示某個(gè)數(shù)據(jù)庫中的某個(gè)表或視圖，db_name 指定數(shù)據(jù)庫名，tbl_name 指定表名或視圖名。
• 5-tbl_name：表示某個(gè)表或視圖，tbl_name 指定表名或視圖名。
• 6-db_name.routine_name：表示某個(gè)數(shù)據(jù)庫中的某個(gè)存儲(chǔ)過程或函數(shù)，routine_name 指定存儲(chǔ)過程名或函數(shù)名。
• 7-TO 子句：用來設(shè)定用戶口令，以及指定被賦予權(quán)限的用戶 user。若在 TO 子句中給系統(tǒng)中存在的用戶指定口令，則新密碼會(huì)將原密碼覆蓋；如果權(quán)限被授予給一個(gè)不存在的用戶，MySQL 會(huì)自動(dòng)執(zhí)行一條 CREATE USER 語句來創(chuàng)建這個(gè)用戶，但同時(shí)必須為該用戶指定口令。

GRANT語句中的<權(quán)限類型>的使用

說明如下：

（1）授予數(shù)據(jù)庫權(quán)限時(shí)，<權(quán)限類型>可以指定為以下值：

• SELECT：表示授予用戶可以使用 SELECT 語句訪問特定數(shù)據(jù)庫中所有表和視圖的權(quán)限。
• INSERT：表示授予用戶可以使用 INSERT 語句向特定數(shù)據(jù)庫中所有表添加數(shù)據(jù)行的權(quán)限。
• DELETE：表示授予用戶可以使用 DELETE 語句刪除特定數(shù)據(jù)庫中所有表的數(shù)據(jù)行的權(quán)限。
• UPDATE：表示授予用戶可以使用 UPDATE 語句更新特定數(shù)據(jù)庫中所有數(shù)據(jù)表的值的權(quán)限。
• REFERENCES：表示授予用戶可以創(chuàng)建指向特定的數(shù)據(jù)庫中的表外鍵的權(quán)限。
• CREATE：表示授權(quán)用戶可以使用 CREATE TABLE 語句在特定數(shù)據(jù)庫中創(chuàng)建新表的權(quán)限。
• ALTER：表示授予用戶可以使用 ALTER TABLE 語句修改特定數(shù)據(jù)庫中所有數(shù)據(jù)表的權(quán)限。
• SHOW VIEW：表示授予用戶可以查看特定數(shù)據(jù)庫中已有視圖的視圖定義的權(quán)限。
• CREATE ROUTINE：表示授予用戶可以為特定的數(shù)據(jù)庫創(chuàng)建存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• ALTER ROUTINE：表示授予用戶可以更新和刪除數(shù)據(jù)庫中已有的存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• INDEX：表示授予用戶可以在特定數(shù)據(jù)庫中的所有數(shù)據(jù)表上定義和刪除索引的權(quán)限。
• DROP：表示授予用戶可以刪除特定數(shù)據(jù)庫中所有表和視圖的權(quán)限。
• CREATE TEMPORARY TABLES：表示授予用戶可以在特定數(shù)據(jù)庫中創(chuàng)建臨時(shí)表的權(quán)限。
• CREATE VIEW：表示授予用戶可以在特定數(shù)據(jù)庫中創(chuàng)建新的視圖的權(quán)限。
• EXECUTE ROUTINE：表示授予用戶可以調(diào)用特定數(shù)據(jù)庫的存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• LOCK TABLES：表示授予用戶可以鎖定特定數(shù)據(jù)庫的已有數(shù)據(jù)表的權(quán)限。
• ALL 或 ALL PRIVILEGES：表示以上所有權(quán)限。

（2）授予表權(quán)限時(shí)，<權(quán)限類型>可以指定為以下值：

• SELECT：授予用戶可以使用 SELECT 語句進(jìn)行訪問特定表的權(quán)限。
• INSERT：授予用戶可以使用 INSERT 語句向一個(gè)特定表中添加數(shù)據(jù)行的權(quán)限。
• DELETE：授予用戶可以使用 DELETE 語句從一個(gè)特定表中刪除數(shù)據(jù)行的權(quán)限。
• DROP：授予用戶可以刪除數(shù)據(jù)表的權(quán)限。
• UPDATE：授予用戶可以使用 UPDATE 語句更新特定數(shù)據(jù)表的權(quán)限。
• ALTER：授予用戶可以使用 ALTER TABLE 語句修改數(shù)據(jù)表的權(quán)限。
• REFERENCES：授予用戶可以創(chuàng)建一個(gè)外鍵來參照特定數(shù)據(jù)表的權(quán)限。
• CREATE：授予用戶可以使用特定的名字創(chuàng)建一個(gè)數(shù)據(jù)表的權(quán)限。
• INDEX：授予用戶可以在表上定義索引的權(quán)限。
• ALL 或 ALL PRIVILEGES：所有的權(quán)限名。

（3）授予列權(quán)限時(shí)，<權(quán)限類型>的值只能指定為 SELECT、INSERT 和 UPDATE，同時(shí)權(quán)限后面需要加上列名列表 column-list。

（4）最有效率的權(quán)限是用戶權(quán)限。

授予用戶權(quán)限時(shí)，<權(quán)限類型>除了可以指定為授予數(shù)據(jù)庫權(quán)限時(shí)的所有值之外，還可以是下面這些值：

• 1-CREATE USER：表示授予用戶可以創(chuàng)建和刪除新用戶的權(quán)限。
• 2-SHOW DATABASES：表示授予用戶可以使用 SHOW DATABASES 語句查看所有已有的數(shù)據(jù)庫的定義的權(quán)限。

【實(shí)例】使用 GRANT 語句創(chuàng)建一個(gè)新的用戶 testUser，密碼為 testPwd。用戶 testUser 對(duì)所有的數(shù)據(jù)有查詢、插入權(quán)限，并授予 GRANT 權(quán)限。輸入的 SQL 語句和執(zhí)行過程如下所示。

mysql> GRANT SELECT,INSERT ON *.*
    -> TO 'testUser'@'localhost'
    -> IDENTIFIED BY 'testPwd'
    -> WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.05 sec)

使用 SELECT 語句查詢用戶 testUser 的權(quán)限，如下所示。

mysql> SELECT Host,User,Select_priv,Grant_priv
    -> FROM mysql.user
    -> WHERE User='testUser';
+-----------+----------+-------------+------------+
| Host      | User     | Select_priv | Grant_priv |
+-----------+----------+-------------+------------+
| localhost | testUser | Y           | Y          |
+-----------+----------+-------------+------------+
1 row in set (0.01 sec)

WITH GRANT OPTION的作用

數(shù)據(jù)庫添加用戶語句：

grant all privileges on testdb.* to ‘test_user'@'localhost' identified by “jack” with grant option;

WITH GRANT OPTION 這個(gè)選項(xiàng)表示該用戶可以將自己擁有的權(quán)限授權(quán)給別人。

注意：經(jīng)常有人在創(chuàng)建操作用戶的時(shí)候不指定WITH GRANT OPTION選項(xiàng)導(dǎo)致后來該用戶不能使用GRANT命令創(chuàng)建用戶或者給其它用戶授權(quán)。

如果不想這個(gè)用戶有這個(gè)grant的權(quán)限，可以不加這句

使用 SELECT 語句查詢所有用戶的權(quán)限

如下所示：

MySQL [mysql]>  SELECT Host,Db,User,Select_priv,Grant_priv FROM mysql.db ; 
+--------------+--------------------+---------------+-------------+------------+
| Host         | Db                 | User          | Select_priv | Grant_priv |
+--------------+--------------------+---------------+-------------+------------+
| localhost    | performance_schema | mysql.session | Y           | N          |
| localhost    | sys                | mysql.sys     | N           | N          |
| %            | cloud_manager_v2   | feeduser      | Y           | N          |
| %            | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.12 | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.13 | xiaojin_airflow    | rw_airflow    | Y           | N          |
| 10.20.250.12 | data_center        | rw_airflow    | Y           | N          |
| 10.20.250.13 | data_center        | rw_airflow    | Y           | N          |
+--------------+--------------------+---------------+-------------+------------+
8 rows in set (0.00 sec)
 
MySQL [mysql]>  SELECT Host,User,Select_priv,Grant_priv FROM mysql.user ; 
+--------------+---------------+-------------+------------+
| Host         | User          | Select_priv | Grant_priv |
+--------------+---------------+-------------+------------+
| localhost    | root          | Y           | Y          |
| localhost    | mysql.session | N           | N          |
| localhost    | mysql.sys     | N           | N          |
| %            | root          | Y           | Y          |
| %            | feeduser      | N           | N          |
| %            | rw_airflow    | N           | N          |
| 10.20.250.12 | rw_airflow    | N           | N          |
| 10.20.250.13 | rw_airflow    | N           | N          |
+--------------+---------------+-------------+------------+
8 rows in set (0.00 sec)

mysql授權(quán)GRANT ALL PRIVILEGES三種示例

改表法

可能是你的帳號(hào)不允許從遠(yuǎn)程登陸，只能在localhost。這個(gè)時(shí)候只要在localhost的那臺(tái)電腦，登入mysql后，更改 “mysql” 數(shù)據(jù)庫里的 “user” 表里的 “host” 項(xiàng)，從"localhost"改成"%"

mysql -u root -p vmware
mysql>use mysql;  
mysql>update user set host = '%' where user = 'root';  
mysql>select host, user from user; 

授權(quán)法

例如，你想myuser使用mypassword從任何主機(jī)連接到mysql服務(wù)器的話。

1-如果你想允許用戶myuser從ip為192.168.1.6的主機(jī)連接到mysql服務(wù)器，并使用mypassword作為密碼

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY  'mypassword' WITH GRANT OPTION; 
FLUSH   PRIVILEGES; 

2-如果你想允許用戶myuser從ip為192.168.1.6的主機(jī)連接到mysql服務(wù)器的dk數(shù)據(jù)庫，并使用mypassword作為密碼

GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;   
FLUSH   PRIVILEGES;  

注意授權(quán)后必須FLUSH PRIVILEGES;否則無法立即生效。

另外一種方法

在安裝mysql的機(jī)器上運(yùn)行：

1）、d:\mysql\bin\>mysql -h localhost -u root 
//這樣應(yīng)該可以進(jìn)入MySQL服務(wù)器 
2）、mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION 
//賦予任何主機(jī)訪問數(shù)據(jù)的權(quán)限 
3）、mysql>FLUSH PRIVILEGES 
//修改生效 
4）、mysql>EXIT 
//退出MySQL服務(wù)器 

這樣就可以在其它任何的主機(jī)上以root身份登錄啦！

其他

mysql> grant all privileges on *.* to 'energy_pf'@'192.168.2.65' identified by 'energy_pf' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> plush privileges;

允許用戶energy_pf從ip為192.168.2.65的主機(jī)連接到mysql服務(wù)器的任意數(shù)據(jù)庫（.），并使用energy_pf作為密碼

MySQL增刪改查報(bào)（INSERT、DROP、UPDATE、SELECT、CREATE）command denied to user ‘xx’@‘localhost’ for table ‘test’

查看用戶權(quán)限

show grants; 

結(jié)果

+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.01 sec)
————————————————

創(chuàng)建mysql用戶

mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

命令說明：

（1）username：新建的用戶名，用于鏈接數(shù)據(jù)庫的登錄名

（2）host：指定該用戶在哪個(gè)主機(jī)上可以登陸，如果是本地用戶可用localhost，如果想讓該用戶可以從任意遠(yuǎn)程主機(jī)登陸，可以使用通配符%

（3）password：該用戶的登陸密碼，密碼可以為空，如果為空則該用戶可以不需要密碼登陸服務(wù)器

列舉一些常見的創(chuàng)建例子：

mysql> CREATE USER 'mumu'@'localhost' IDENTIFIED BY '123456';  # 只能本地登錄
mysql> CREATE USER 'thomas'@'192.168.1.73' IDENDIFIED BY '123456';# 僅限192.168.1.73登錄
mysql> CREATE USER 'lin'@'%' IDENTIFIED BY '123456'; # 任意遠(yuǎn)程主機(jī)，需要密碼
mysql> CREATE USER 'huea'@'%' IDENTIFIED BY '';# 任意遠(yuǎn)程主機(jī)，無需密碼
mysql> CREATE USER 'thomas'@'%';# 任意遠(yuǎn)程主機(jī)，無需密碼

以上就完成了用戶的創(chuàng)建，用戶也能成功連接上，但是一旦對(duì)數(shù)據(jù)庫增刪改查操作均會(huì)報(bào)錯(cuò)：

（INSERT、DROP、UPDATE、SELECT、CREATE, ALTER等）command denied to user 'xxx'@'localhost' for table 'table'

出現(xiàn)這個(gè)問題原因在于該用戶沒有這些權(quán)限，解決方式就是需要給用戶授權(quán)增刪改查的權(quán)限。

用戶權(quán)限授權(quán)

mysql> GRANT privileges ON databasename.tablename TO 'username'@'host'

命令說明：

• privileges: 要賦予用戶的權(quán)限，如INSERT、DROP、UPDATE、SELECT、CREATE, ALTER等
• databasename: 數(shù)據(jù)庫，如果要授予該用戶對(duì)所有數(shù)據(jù)庫和表的相應(yīng)操作權(quán)限則可用表示，如.*
• tablename: 數(shù)據(jù)表，如果要授予該用戶對(duì)所有數(shù)據(jù)庫和表的相應(yīng)操作權(quán)限則可用表示，如.*
• username: 用戶名，用于鏈接數(shù)據(jù)庫的登錄名
• host: 指定該用戶在哪個(gè)主機(jī)上可以登陸，如果是本地用戶可用localhost，如果想讓該用戶可以從任意遠(yuǎn)程主機(jī)登陸，可以使用通配符%

列舉一些常見的創(chuàng)建例子：

mysql> GRANT SELECT, INSERT ON test.user TO 'thomas'@'%'; # 給thomas分配test庫的user表查詢、插入權(quán)限 
mysql> GRANT ALL ON *.* TO 'thomas'@'%'; # 給thomas分配所有庫所有表的所有權(quán)限  
mysql> GRANT ALL ON test.* TO 'thomas'@'%'; # 給thomas分配test庫的所有表的所有權(quán)限

注意：以上面這種授權(quán)方式給用戶授權(quán)權(quán)限，該用戶沒有權(quán)限創(chuàng)建新用戶，更沒權(quán)限授權(quán)用戶的權(quán)限

（1）創(chuàng)建用戶

mysql> CREATE USER 'lin'@'%' IDENTIFIED BY '123456';
1227 - Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

（2）用戶授權(quán)

mysql> GRANT INSERT ON `comment`.* TO 'lin'@'%';
1044 - Access denied for user 'lin'@'%' to database 'comment'

如果想讓創(chuàng)建的用戶經(jīng)過授權(quán)也可以創(chuàng)建并授權(quán)的權(quán)限，那么需要用以下命令：

mysql> GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

也就是在常規(guī)授權(quán)的命令后加上’WITH GRANT OPTION’即可,如果想讓執(zhí)行后的命令立即生效，可以在執(zhí)行語句后執(zhí)行以下命令：

mysql> FLUSH PRIVILEGES;

FLUSH PRIVILEGES 命令本質(zhì)上的作用是將當(dāng)前user和privilige表中的用戶信息/權(quán)限設(shè)置從mysql庫(MySQL數(shù)據(jù)庫的內(nèi)置庫)中提取到內(nèi)存里。MySQL用戶數(shù)據(jù)和權(quán)限有修改后，希望在"不重啟MySQL服務(wù)"的情況下直接生效，那么就需要執(zhí)行這個(gè)命令。通常是在修改ROOT帳號(hào)的設(shè)置后，怕重啟后無法再登錄進(jìn)來，那么直接flush之后就可以看權(quán)限設(shè)置是否生效。而不必冒太大風(fēng)險(xiǎn)!!

設(shè)置與更改用戶密碼

SET PASSWORD FOR 'username'@'host' = '123456';
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

如果是當(dāng)前登陸用戶用：

SET PASSWORD = '123456';

撤銷用戶權(quán)限

REVOKE跟GRANT的語法差不多，只需要把關(guān)鍵字 “to” 換成 “from” 即可：

REVOKE privilege ON databasename.tablename FROM 'lin'@'host';

刪除用戶

DROP USER 'lin'@'host';

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。