MySQL使用者管理

標籤：alt   查詢   授權   所有權   否則   技術分享   操作   option   body   

轉自：http://www.cnblogs.com/kissdodog/p/4173337.html

　MySQL分為普通使用者與root使用者。這兩種使用者的許可權不一樣。

一、許可權表

　　安裝MySQL時會自動安裝一個名為mysql的資料庫。mysql資料庫裡面儲存的都是許可權表。

　　1、User表

　　user表有39個欄位。這些欄位可以分為4類：

• 使用者列；
• 許可權列；
• 安全列；
• 資源控制列；

　　1、使用者列

　　使用者列包括Host、User、Password，分別表示主機名稱、使用者名稱和密碼。登入用的就是這個使用者名稱與密碼；

　　2、許可權列

　　user表的許可權列包括Select_priv、Insert_priv等以priv結尾的欄位。

　　這些欄位的值只有Y和N。Y表示該許可權可以用到所有資料庫上；N表示該許可權不能用到所有資料庫上；

　　通常，可以使用GRANT語句Wie使用者賦予一些許可權，也可以通過Update語句更新user表的方式來設定許可權；

　　不過，修改user表之後，一定要執行一下FLUSH PRIVILEGES，否則可能會出現如下錯誤：

　　Error (1133): Can’t find any matching row in the user table

列	說明
Select_priv	確定使用者是否可以通過SELECT命令選擇資料
Insert_priv	確定使用者是否可以通過INSERT命令插入資料
Update_priv	確定使用者是否可以通過UPDATE命令修改現有資料
Delete_priv	確定使用者是否可以通過DELETE命令刪除現有資料
Create_priv	確定使用者是否可以建立新的資料庫和表
Drop_priv	確定使用者是否可以刪除現有資料庫和表
Reload_priv	確定使用者是否可以執行重新整理和重新載入MySQL所用各種內部緩衝的特定命令，包括日誌、許可權、主機、查詢和表
Shutdown_priv	確定使用者是否可以關閉MySQL伺服器。在將此許可權提供給root賬戶之外的任何使用者時，都應當非常謹慎
Process_priv	確定使用者是否可以通過SHOW PROCESSLIST命令查看其他使用者的進程
File_priv	確定使用者是否可以執行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv	確定使用者是否可以將已經授予給該使用者自己的許可權再授予其他使用者
References_priv	目前只是某些未來功能的預留位置；現在沒有作用
Index_priv	確定使用者是否可以建立和刪除表索引
Alter_priv	確定使用者是否可以重新命名和修改表結構
Show_db_priv	確定使用者是否可以查看伺服器上所有資料庫的名字，包括使用者擁有足夠存取權限的資料庫
Super_priv	確定使用者是否可以執行某些強大的管理功能，例如通過KILL命令刪除使用者進程，使用SET GLOBAL修改全域MySQL變數，執行關於複製和日誌的各種命令
Create_tmp_table_priv	確定使用者是否可以建立暫存資料表
Lock_tables_priv	確定使用者是否可以使用LOCK TABLES命令阻止對錶的訪問/修改
Execute_priv	確定使用者是否可以執行預存程序
Repl_slave_priv	確定使用者是否可以讀取用於維護複製資料庫環境的二進位記錄檔。此使用者位於主系統中，有利於主機和客戶機之間的通訊
Repl_client_priv	確定使用者是否可以確定複製從伺服器和主伺服器的位置
Create_view_priv	確定使用者是否可以建立視圖
Show_view_priv	確定使用者是否可以查看視圖或瞭解視圖如何執行
Create_routine_priv	確定使用者是否可以更改或放棄預存程序和函數
Alter_routine_priv	確定使用者是否可以修改或刪除儲存函數及函數
Create_user_priv	確定使用者是否可以執行CREATE USER命令，這個命令用於建立新的MySQL賬戶
Event_priv	確定使用者能否建立、修改和刪除事件
Trigger_priv	確定使用者能否建立和刪除觸發器

　　Update修改許可權：

UPDATE user SET Select_priv = ‘N‘WHERE Host = ‘%‘

　　GRANT賦予許可權

　　3、安全列

　　user表的安全列有4個欄位：

• ssl_type；
• ssl_cipher；
• x509_issuer；
• x509_subject；

　　ssl用於加密；x509標準可以用來標識使用者。普通的發行版都沒有加密功能。可以使用SHOW VARIABLES LIKE ‘have_openssl‘語句來查看是否具有ssl功能。如果取值為DISABLED，那麼則沒有ssl加密功能。

　　4、資源控制列

　　user表的4個資源控制列是：

• max_questions：每小時可以允許執行多少次查詢；
• max_updates：每小時可以允許執行多少次更新；
• max_connections：每小時可以建立多少串連；
• max_user_connections：單個使用者可以同時具有的串連數。

　　預設值為0，表示無限制。

　　2、db表和host表

　　db表格儲存體了某個使用者對一個資料庫的許可權。

　　host表中儲存了某個主機對資料庫的操作許可權，配合db表對給定主機上資料庫級操作許可權做更細緻的控制；但是很少用，新版本已經取消了host表；

　　1、使用者列

　　db表的使用者列有3個欄位：

• Host：主機名稱；
• Db：資料庫名；
• User：使用者名稱；

　　2、許可權列

　　db表：

• Create_routine_priv：是否具有建立預存程序許可權；
• Alter_routine_priv：是否具有修改預存程序許可權；

　　user表中的許可權是針對所有資料庫的，如果user表中的Select_priv欄位取值為Y，那麼該使用者可以查詢所有資料庫中的表；

　　如果為某個使用者值設定了查詢test表的許可權，那麼user表的Select_priv欄位的取值為N。而這個SELECT許可權則記錄在db表中。db表中的Select_priv欄位的取值將會是Y。

　　使用者先根據user表的內容擷取許可權，然後再根據db表的內容擷取許可權。

　　3、tables_priv表和columns_priv表

　　　tables_priv：可以對單個表進行使用權限設定：

• tables_priv表包含8個欄位：
• Host：主機名稱；
• DB：資料庫名；
• User：使用者名稱；
• Table_name：表名
• Table_priv：對錶進行操作的許可權(Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter)
• Column_priv：對錶中的資料列進行操作的許可權(Select,Insert,Update,Rederences)；
• Timestamp：修改許可權的事件
• Grantor：許可權的設定者

　　columns_priv：可以對單個資料列進行使用權限設定，有7個列，作用同上：

 　　Host、Db、User、Table_name、Column_name、Column_priv、Timestamp。

　　MySQL許可權分配是按照user表-> db表 -> table_priv表 -> columns_priv表的順序進行分配的。

　　在資料庫系統中，先判斷user表中的值是否為‘Y‘，如果user表中的值是‘Y‘，就不需要檢查後面的表。如果user表為N，則一次檢查後面的表。

　　4、procs_priv表

• procs_priv表可以對預存程序和儲存函數進行使用權限設定。
• procs_priv表包含8個欄位，分別是：
• Host：主機名稱；
• Db：資料庫名；
• User：使用者名稱；
• Routine_name：預存程序或函數名稱；
• Routine_type：類型(取值有：FUNCTION或PROCEDURE)；
• Proc_priv：擁有的許可權(Execute：執行；Alter Routine：修改；Grant：許可權賦予)；
• Timestamp：欄位儲存更新的時間；
• Grantor：欄位設定者；

二、賬戶管理

　　賬戶管理是MySQL使用者管理最基本的內容。包括登入，退出MySQL伺服器、建立使用者、刪除使用者、密碼管理和許可權管理等內容。

　　其中登入方式非常簡單，在這個地址有：http://www.cnblogs.com/kissdodog/p/4154068.html

　　1、建立普通使用者

　　在MySQL資料庫中，建立使用者有3種方式：

• 使用CREATE USER語句來建立新的使用者；
• 直接在mysql.user表中INSERT使用者；
• 使用GRANT語句來建立使用者；

　　1、CREATE USER

　　使用CREATE USER語句建立使用者，必須要擁有CREATE USER許可權。其格式如下：

CREATE USER user[IDENTIFIED BY [PASSWORD] ‘password‘],
[user[IDENTIFIED BY [PASSWORD] ‘password‘]]...

　　其中，user參數表示建立使用者的賬戶，user由使用者名稱(User)和主機名稱(Host)構成；IDENTIFIED BY關鍵字用來設定使用者的密碼；password參數表示使用者的密碼；如果密碼是一個普通的字串，就不需要使用PASSWORD關鍵字。可以沒有初始密碼。

　　樣本：

CREATE USER ‘admin‘@‘%‘ IDENTIFIED BY ‘admin‘

　　執行之後user表會增加一行記錄，但許可權暫時全部為‘N’。

　　2、用INSERT語句建立普通使用者

　　可以使用INSERT語句直接將使用者的資訊添加到mysql.user表。但必須擁有mysql.user表的INSERT許可權。

　　另外，ssl_cipher、x509_issuer、x509_subject沒有值，必須要設定值，否則INSERT語句無法執行。

　　樣本：

INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘%‘,‘newuser1‘,PASSWORD(‘123456‘),‘‘,‘‘,‘‘)

　　執行INSERT之後，要使用命令：

FLUSH PRIVILEGES

　　命令來使使用者生效。

　　3、用GRANT語句來建立普通使用者

　　用GRANT來建立新的使用者時，能夠在建立使用者時為使用者授權。但需要擁有GRANT許可權。

　　文法如下：

GRANT priv_type ON database.tableTO user[IDENTIFIED BY [PASSWORD] ‘password‘][,user [IDENTIFIED BY [PASSWORD] ‘password‘]...]

• priv_type：參數表示新yoghurt的許可權；
• databse.table：參數表示新使用者的許可權範圍；
• user：參數新使用者的賬戶，由使用者名稱和主機構成；
• IDENTIFIED BY關鍵字用來設定密碼；
• password：新使用者密碼；

　　GRANT語句可以同時建立多個使用者。

GRANT SELECT ON *.* TO [email protected]‘%‘

　　*.*與db.*的區別在於。*.*對所有資料庫生效，所以user表的SELECT會變為Y。而db.*user表為‘N‘，更改的是Db表。

　　2、刪除普通使用者

　　1、DROP USER語句刪除普通使用者

　　需要擁有DROP USER許可權。文法如下：

DROP USER user[,user]...

　　user是需要刪除的使用者，由使用者名稱(User)和主機名稱(Host)構成。

DROP USER ‘newuser1‘@‘%‘

　　2、DELETE語句刪除普通使用者

　　可以使用DELETE語句直接將使用者的資訊從mysql.user表中刪除。但必須擁有對mysql.user表的DELETE許可權。

DELETE FROM mysql.user WHERE Host = ‘%‘ AND User = ‘admin‘

　　刪除完成後，一樣要FLUSH PRIVILEGES才生效。

　　3、修改密碼

　　1、使用mysqladmin命令來修改root使用者的密碼

　　文法：

mysqladmin -u -username -p password "new_password"

　　新密碼(new_password)必須用括弧括起來，單引號會報錯。

　　樣本：

　　（註：這個待驗證，我怎麼執行都不成功！）

　　2、修改user表

　　UPDATE user表的passwor欄位的值，也可以達到修改密碼的目的；

UPDATE user SET Password = PASSWORD(‘123‘) WHERE USER = ‘myuser‘

　　FLUSH PRIVILEGES後生效。

　　3、使用SET語句來修改密碼

　　使用root使用者登入到MySQL伺服器後，可以使用SET語句來修改密碼：

　　修改自己的密碼，不需要使用者名稱

SET PASSWORD = PASSWORD("123");

　　修改其他使用者密碼：

SET PASSWORD FOR ‘myuser‘@‘%‘=PASSWORD("123456")

　　FOR 使用者名稱@主機名稱

　　4、GRANT語句來修改普通使用者的密碼

　　使用GRANT語句修改普通使用者的密碼，必須擁有GRANT許可權。

GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] ‘password‘]

　　樣本：

GRANT SELECT ON *.* TO ‘test3‘@‘%‘ IDENTIFIED BY ‘123‘

　　4、忘記密碼的解決方案

　　如果root使用者密碼丟失了，會給使用者造成很大的麻煩。但是，可以通過某種特殊方法登入到root使用者下，然後修改密碼。

　　1、使用--skip-grant-tables選項啟動MySQL服務

　　該選項將使MySQL伺服器停止許可權判斷，任何使用者都能夠訪問資料庫。

　　在Windows作業系統下，使用mysqlid或mysqlid-nt來啟動MySQL服務。

　　mysqld命令：

　　mysqld --skip-grant-tables

　　mysqld-nt命令：

　　mysqld-nt --skip-grant-tables

　　net start mysql命令：

　　net start mysql --skip-grant-tables

　　2、登入root使用者，並且設定新密碼

　　mysql -u root

　　必須使用UPDATE語句更新mysql資料庫下的user表，而不能使用SET語句，因為--skip-grant-tables不能使用SET語句。

　　修改完密碼以後，FLUSH PRIVILEGES語句載入許可權表，新密碼即生效。

三、許可權管理

　　1、MySQL的各種許可權

許可權名稱	對應user表中的列	許可權的範圍
CREATE	Create_priv	資料庫、表或索引
DROP	Drop_priv	資料庫或表
GRANT OPTION	Grant_priv	資料庫、表、預存程序或函數
REFERENCES	References_priv	資料庫或表
ALTER	Alter_priv	修改表
DELETE	Delete_priv	刪除表
INDEX	Index_priv	用索引查詢表
INSERT	Insert_priv	插入表
SELECT	Select_priv	查詢表
UPDATE	Update_priv	更新表
CREATE VIEW	Create_view_priv	建立視圖
SHOW VIEW	Show_view_priv	查看視圖
ALTER ROUTINE	Alter_routine	修改預存程序或儲存函數
CREATE ROUTINE	Create_routine_priv	建立預存程序或儲存函數
EXECUTE	Execute_priv	執行預存程序或儲存函數
FILE	File_priv	載入伺服器主機上的檔案
CREATE TEMPORARY TABLES	Create_temp_table_priv	建立暫存資料表
LOCK TABLES	Lock_tables_priv	鎖定表
CREATE USER	Create_user_priv	建立使用者
PROCESS	Process_priv	伺服器管理
RELOAD	Reload_priv	重新載入許可權表
REPLICATION CLIENT	Repl_client_priv	伺服器管理
REPLICATION SLAVE	Repl_slave_priv	伺服器管理
SHOW DATABASES	Show_db_priv	查看資料庫
SHUTDOWN	Shutdown_priv	關閉伺服器
SUPER	Super_priv	超級許可權

 

　　2、授權

　　GRANT文法如下：

GRANT priv_type [(column_list)] ON database.tableTO user [IDENTIFIED BY [PASSWORD] ‘password‘][,user [IDENTIFIED BY [PASSWORD] ‘password‘]]...WITH with_option[with_option]

• priv_type參數表示權限類別型；
• column_list：參數表示許可權作用於哪些列上，沒設定則位於整個表上；
• user參數由使用者名稱和主機名稱構成；形式是"‘username‘@‘hostname‘"；
• IDENTIFIED BY參數用於為使用者佈建密碼；
• password：使用者新密碼；

　　WITH關鍵字後面帶有一個或多個with_option參數。有5個選項：

• GRANT OPTION：被授權的使用者可以將這些許可權賦予給別的使用者；
• MAX_QUERIES_PER_HOUR count：設定沒消失可以允許執行count次查詢；
• MAX_UPDATES_PER_HOUR count：設定每個消失可以允許執行count次更新；
• MAX_CONNECTIONS_PER_HOUR count：設定每小時可以建立count個串連；
• MAX_USER_CONNECTIONS count：設定單個使用者可以同時具有的count個串連數；

　　樣本：

GRANT SELECT,UPDATE ON *.*    TO ‘myuser‘@‘%‘WITH GRANT OPTION;

　　3、收回許可權

　　收回許可權，就是取消某個使用者的某些許可權。MySQL中使用REVOKE關鍵字來為使用者佈建許可權。

　　文法如下：

REVOKE priv_type[(column_list)]ON database.tableFROM user[,user]

　　收回全部許可權的REVOKE語句的基本文法如下：

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]...

　　樣本：回收使用者myuser的SELECT許可權

REVOKE SELECT ON *.*FROM ‘myuser‘@‘%‘

　　收回myuser的所有許可權：

REVOKE ALL PRIVILEGES,GRANT OPTION FROM ‘myuser‘@‘%‘

　　4、查看許可權

　　SHOW GRANTS語句用於查看許可權。同時mysql資料庫下的user表中儲存著使用者的基本許可權。

SELECT * FROM mysql.userSHOW GRANTS

　　

 

MySQL使用者管理