MySQL中授權(grant)和撤銷授權(revoke)命令之用法

標籤：mysql grant revoke

MySQL的許可權系統圍繞著兩個概念： 
認證->確定使用者是否允許串連資料庫伺服器 

授權->確定使用者是否擁有足夠的許可權執行查詢請求等。

如果認證不成功的話，哪麼授權肯定是無法進行的。

revoke 跟 grant 的文法差不多，只需要把關鍵字 “to” 換成 “from”

表 GRANT和REVOKE管理的許可權

許可權	描述
ALL PRIVILEGES	影響除WITH GRANT OPTION之外的所有許可權
ALTER	影響ALTER TABLE命令的使用
ALTER ROUTINE	影響建立儲存常式的能力
CREATE	影響CREATE TABLE命令的使用
CREATE ROUTINE	影響更改和棄用儲存常式的能力
CREATE TEMPORARY TABLES	影響CREATE TEMPORARY TABLE命令的使用
CREATE USER	影響建立、棄用；重新命名和撤銷使用者權限的能力
CREATE VIEW	影響CREATE VIEW命令的使用
DELETE	影響DELETE命令的使用
DROP	影響DROP TABLE命令的使用
EXECUTE	影響使用者運行預存程序的能力
EVENT	影響執行事件的能力（從MySQL5.1.6開始）
FILE	影響SELECT INTO OUTFILE和LOAD DATA INFILE的使用
GRANT OPTION	影響使用者委派許可權的能力
INDEX	影響CREATE INDEX和DROP INDEX命令的使用
INSERT	影響INSERT命令的使用
LOCK TABLES	影響LOCK TABLES命令的使用
PROCESS	影響SHOW PROCESSLIST命令的使用
REFERENCES	未來MySQL特性的預留位置
RELOAD	影響FLUSH命令集的使用
REPLICATION CLIENT	影響使用者查詢從伺服器和主伺服器位置的能力

（續）

許可權	描述
REPLICATION SLAVE	複製從伺服器所需的許可權
SELECT	影響SELECT命令的使用
SHOW DATABASES	影響SHOW DATABASES命令的使用
SHOW VIEW	影響SHOW CREATE VIEW命令的使用
SHUTDOWN	影響SHUTDOWN命令的使用
SUPER	影響管理員級命令的使用，如CHANGE、MASTER、KILL thread、mysqladmin debug、PURGE MASTER LOGS和SET GLOBAL
TRIGGER	影響執行觸發器的能力（從MySQL5.1.6開始）
UPDATE	影響UPDATE命令的使用
USAGE	只串連，不授予許可權

 

1>.改表法。你的帳號不允許從遠程登陸MySql伺服器，只能在localhost。
解決辦法:
在localhost的那台電腦，登入mysql後，更改 "mysql" 資料庫裡的 "user" 表裡的 "host" 項，從"localhost"改稱"%"
(1). mysql -u root -pvmwaremysql>use mysql;
(2). mysql>update user set host = ‘%‘ where user = ‘root‘;
(3). mysql>select host, user from user;
   
2>. 授權法。例如，你想myuser使用mypassword從任何主機串連到mysql伺服器的話。 
(1). GRANT ALL PRIVILEGES ON *.* TO ‘myuser‘@‘%‘ IDENTIFIED BY ‘mypassword‘ WITH GRANT OPTION;
(2). FLUSH   PRIVILEGES;
如果你想允許使用者myuser從ip為192.168.1.6的主機串連到mysql伺服器，並使用mypassword作為密碼 
(1). GRANT ALL PRIVILEGES ON *.* TO ‘myuser‘@‘192.168.1.3‘ IDENTIFIED BY ‘mypassword‘ WITH GRANT OPTION;  
(2). FLUSH   PRIVILEGES; 
如果你想允許使用者myuser從ip為192.168.1.6的主機串連到mysql伺服器的dk資料庫，並使用mypassword作為密碼
(1). GRANT ALL PRIVILEGES ON dk.* TO ‘myuser‘@‘192.168.1.3‘ IDENTIFIED BY ‘mypassword‘ WITH GRANT OPTION;  
(3). FLUSH   PRIVILEGES; 
注意:授權後必須FLUSH PRIVILEGES;否則無法立即生效。 

另外一種方法: 

3>.在安裝mysql的機器上運行:
1、d:mysqlbin>mysql -h localhost -u root  //這樣應該可以進入MySQL伺服器
2、mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ WITH GRANT OPTION  //賦予任何主機訪問資料的許可權
3、mysql>FLUSH PRIVILEGES  //修改生效
4、mysql>EXIT  //退出MySQL伺服器
這樣就可以在其它任何的主機上以root身份登入了。

 

4>.查看 MySQL 使用者權限
查看目前使用者（自己）許可權:
show grants;
查看其他 MySQL 使用者權限:
show grants for [email protected];

 

5>.撤銷已經賦予給 MySQL 使用者權限的許可權
revoke 跟 grant 的文法差不多，只需要把關鍵字 “to” 換成 “from” 即可:
grant all on *.* to [email protected];
revoke all on *.* from [email protected]; 

 

6>.MySQL grant、revoke 使用者權限注意事項
1. grant, revoke 使用者權限後，該使用者只有重新串連 MySQL 資料庫，許可權才會生效。
2. 如果想讓授權的使用者，也可以將這些許可權 grant 給其他使用者，需要選項 "grant option"
grant select on testdb.* to [email protected] with grant option;
這個特性一般用不到。實際中，資料庫許可權最好由 DBA 來統一管理。

 

7>.使用者和許可權管理命令: 
create user :用於建立新的使用者賬戶（從5.0版本開始有這個命令），在建立這個使用者的時候不分配任何許可權，
需要在建立之後通過grant命令來給改使用者指派相應的許可權。
eg:create user [email protected] identified by ‘123456‘;
grant select on mydb.* to [email protected]; 
drop user:刪除一個使用者賬戶（注意在4.1.1版本之前只能刪除沒有任何許可權的賬戶，5.0.2之後可以刪除任何賬戶） 
eg:drop user guest; 
rename user:可以實現重新命名一個使用者帳號。 
grant:用於管理存取權限，也就是給使用者帳號授權。當然它同樣可以建立一個新的使用者賬戶。 
eg:grant select, insert, update, delete on new_db.* to [email protected]‘%‘ identified by ‘88888888‘; 
grant 許可權 on 資料庫.表 to 使用者 @ 訪問方式 identified by 密碼 
grant select on mydb.* to [email protected] identified by ‘123456‘; 
BTW: 如果需要一個空密碼或者無密碼的賬戶，必須先用Create User命令，然後通過 
grant來分配許可權。如果如下操作: 
grant all privileges on mydb.* to [email protected]‘%‘ ;而在資料庫user表中沒有先建立visitor 
使用者，則會發生1133錯誤"Can‘t find any matching row in the user table"。grant只能創 
有密碼的賬戶。 
revoke:刪除一個賬戶，具體查看MySQL的文檔。

 

8>.mysql中可以給你一個使用者授予如select,insert,update,delete等其中的一個或者多個許可權,主要使用grant命令,用法格式為:
grant 許可權 on 資料庫物件 to 使用者
grant 普通資料使用者，查詢、插入、更新、刪除 資料庫中所有表資料的許可權。
grant select on testdb.* to [email protected]’%’
grant insert on testdb.* to [email protected]’%’
grant update on testdb.* to [email protected]’%’
grant delete on testdb.* to [email protected]’%’
或者，用一條 MySQL 命令來替代:
grant select, insert, update, delete on testdb.* to [email protected]’%’

 

9>.grant 資料庫開發人員，建立表、索引、視圖、預存程序、函數。。。等許可權。
grant 建立、修改、刪除 MySQL 資料表結構許可權。
grant create on testdb.* to [email protected]’192.168.0.%’;
grant alter on testdb.* to [email protected]’192.168.0.%’;
grant drop on testdb.* to [email protected]’192.168.0.%’;
grant 操作 MySQL 外鍵許可權。
grant references on testdb.* to [email protected]’192.168.0.%’;
grant 操作 MySQL 暫存資料表許可權。
grant create temporary tables on testdb.* to [email protected]’192.168.0.%’;
grant 操作 MySQL 索引許可權。
grant index on testdb.* to [email protected]’192.168.0.%’;
grant 操作 MySQL 檢視、查看視圖原始碼 許可權。
grant create view on testdb.* to [email protected]’192.168.0.%’;
grant show view on testdb.* to [email protected]’192.168.0.%’;
grant 操作 MySQL 預存程序、函數 許可權。
grant create routine on testdb.* to [email protected]’192.168.0.%’; -- now, can show procedure status
grant alter routine on testdb.* to [email protected]’192.168.0.%’; -- now, you can drop a procedure
grant execute on testdb.* to [email protected]’192.168.0.%’;
 
10>.grant 普通 DBA 管理某個 MySQL 資料庫的許可權。
grant all privileges on testdb to [email protected]’localhost’
其中，關鍵字 “privileges” 可以省略。
 
11>.grant 進階 DBA 管理 MySQL 中所有資料庫的許可權。
grant all on *.* to [email protected]’localhost’
 
12>.MySQL grant 許可權，分別可以作用在多個層次上。
1. grant 作用在整個 MySQL 伺服器上:
grant select on *.* to [email protected]; -- dba 可以查詢 MySQL 中所有資料庫中的表。
grant all on *.* to [email protected]; -- dba 可以管理 MySQL 中的所有資料庫
2. grant 作用在單個資料庫上:
grant select on testdb.* to [email protected]; -- dba 可以查詢 testdb 中的表。
3. grant 作用在單個資料表上:
grant select, insert, update, delete on testdb.orders to [email protected];
4. grant 作用在表中的列上:
grant select(id, se, rank) on testdb.apache_log to [email protected];
5. grant 作用在預存程序、函數上:
grant execute on procedure testdb.pr_add to ’dba’@’localhost’
grant execute on function testdb.fn_add to ’dba’@’localhost’ 
注意:修改完許可權以後 一定要重新整理服務，或者重啟服務，重新整理服務用:FLUSH PRIVILEGES

MySQL 賦予使用者權限命令的簡單格式可概括為:
grant 許可權 on 資料庫物件 to 使用者

一、grant 普通資料使用者，查詢、插入、更新、刪除 資料庫中所有表資料的許可權
grant select on testdb.* to [email protected]‘%‘
grant insert on testdb.* to [email protected]‘%‘
grant update on testdb.* to [email protected]‘%‘
grant delete on testdb.* to [email protected]‘%‘
或者，用一條 MySQL 命令來替代:
grant select, insert, update, delete on testdb.* to [email protected]‘%‘

二、grant 資料庫開發人員，建立表、索引、視圖、預存程序、函數等許可權

grant 建立、修改、刪除 MySQL 資料表結構許可權:
grant create on testdb.* to [email protected]‘192.168.0.%‘;
grant alter  on testdb.* to [email protected]‘192.168.0.%‘;
grant drop   on testdb.* to [email protected]‘192.168.0.%‘; 
grant 操作 MySQL 外鍵許可權:
grant references on testdb.* to [email protected]‘192.168.0.%‘;
grant 操作 MySQL 暫存資料表許可權:
grant create temporary tables on testdb.* to [email protected]‘192.168.0.%‘;
grant 操作 MySQL 索引許可權:
grant index on testdb.* to [email protected]‘192.168.0.%‘;
grant 操作 MySQL 檢視、查看視圖原始碼許可權:
grant create view on testdb.* to [email protected]‘192.168.0.%‘;
grant show   view on testdb.* to [email protected]‘192.168.0.%‘;
grant 操作 MySQL 預存程序、函數許可權:
grant create routine on testdb.* to [email protected]‘192.168.0.%‘; -- now, can show procedure status
grant alter  routine on testdb.* to [email protected]‘192.168.0.%‘; -- now, you can drop a procedure
grant executeon testdb.* to [email protected]‘192.168.0.%‘;

三、grant 普通 DBA 管理某個 MySQL 資料庫的許可權
grant all privileges on testdb to [email protected]‘localhost‘;
其中，關鍵字 “privileges” 可以省略。

四、grant 進階 DBA 管理 MySQL 中所有資料庫的許可權
grant all on *.* to [email protected]‘localhost‘ 

五、MySQL grant 許可權，分別可以作用在多個層次上
1. grant 作用在整個 MySQL 伺服器上:
grant select on *.* to [email protected]; -- dba 可以查詢 MySQL 中所有資料庫中的表
grant all    on *.* to [email protected]; -- dba 可以管理 MySQL 中的所有資料庫
2. grant 作用在單個資料庫上:
grant select on testdb.* to [email protected]; -- dba 可以查詢 testdb 中的表。
3. grant 作用在單個資料表上:
grant select, insert, update, delete on testdb.orders to [email protected];
這裡在給一個使用者授權多張表時，可以多次執行以上語句。例如:
grant select(user_id,username) on smp.users to [email protected]‘%‘ identified by ‘123345‘; 
grant select on smp.mo_sms to [email protected]‘%‘ identified by ‘123345‘;        
4. grant 作用在表中的列上:
grant select(id, se, rank) on testdb.apache_log to [email protected];
5. grant 作用在預存程序、函數上:
grant execute on procedure testdb.pr_add to ‘dba‘@‘localhost‘
grant execute on function testdb.fn_add to ‘dba‘@‘localhost‘

六、查看 MySQL 使用者權限
查看目前使用者（自己）許可權:
show grants;
查看其他 MySQL 使用者權限:
show grants for [email protected];

七、撤銷已經賦予給 MySQL 使用者權限的許可權
revoke 跟 grant 的文法差不多，只需要把關鍵字 “to” 換成 “from” 即可:
grant  all on *.* to   [email protected];
revoke all on *.* from [email protected];

八、MySQL grant、revoke 使用者權限注意事項
1. grant, revoke 使用者權限後，該使用者只有重新串連 MySQL 資料庫，許可權才會生效。
2. 如果想讓授權的使用者，也可以將這些許可權 grant 給其他使用者，需要選項"grant option"
grant select on testdb.* to [email protected] with grant option; 
這個特性一般用不到。實際中，資料庫許可權最好由 DBA 來統一管理。

 

九、補充
mysql授權表共有5個表:user、db、host、tables_priv和columns_priv , 授權表的內容有如下用途:

user表
user表列出可以串連伺服器的使用者及其口令，並且它指定他們有哪種全域（超級使用者）許可權。在user表啟用的任何許可權均是全域許可權，並適用於所有資料庫。例如，如果你啟用了DELETE許可權，在這裡列出的使用者可以從任何錶中刪除記錄，所以在你這樣做之前要認真考慮。

db表
db表列出資料庫，而使用者有許可權訪問它們。在這裡指定的許可權適用於一個資料庫中的所有表。

host表
host表與db表結合使用在一個較好層次上控制特定主機對資料庫的存取權限，這可能比單獨使用db好些。這個表不受GRANT和REVOKE語句的影響，所以，你可能發覺你根本不是用它。

tables_priv表
tables_priv表指定表級許可權，在這裡指定的一個許可權適用於一個表的所有列。

columns_priv表
columns_priv表指定列級許可權。這裡指定的許可權適用於一個表的特定列。

本文出自 “sukhoi” 部落格，謝絕轉載！

MySQL中授權(grant)和撤銷授權(revoke)命令之用法