MySQL最佳化之——許可權管理，mysql許可權管理

MySQL最佳化之——許可權管理，mysql許可權管理

轉載請註明出處:http://blog.csdn.net/l1028386804/article/details/46763767

在mysql資料庫中，有mysql_install_db指令碼初始化許可權表，儲存許可權的表有：

1、user表

2、db表

3、host表

4、table_priv表

5、columns_priv表

6、proc_priv表

MySQL存取控制包含2個階段：

• 階段1：伺服器檢查你是否允許串連。
• 階段2：假定你能串連，伺服器檢查你發出的每個請求。看你是否有足夠的許可權實施它。例如，如果你從資料庫中一個表精選(select)行或從資料庫拋棄一個表，伺服器確定你對錶有select許可權或對資料庫有drop許可權。

伺服器在存取控制的兩個階段使用在mysql的資料庫中的user、db和host表，在這些授權表中欄位如下：

表名稱	user	db	host
範圍欄位	Host	Host	Host
	User	Db	Db
	Password	User
許可權欄位	Select_priv	Select_priv	Select_priv
	Insert_priv	Insert_priv	Insert_priv
	Update_priv	Update_priv	Update_priv
	Delete_priv	Delete_priv	Delete_priv
	Index_priv	Index_priv	Index_priv
	Alter_priv	Alter_priv	Alter_priv
	Create_priv	Create_priv	Create_priv
	Drop_priv	Drop_priv	Drop_priv
	Grant_priv	Grant_priv	Grant_priv
	Reload_priv
	Shutdown_priv
	Process_priv
	File_priv

對存取控制的第二階段(請求證實)，如果請求涉及表，伺服器可以另外參考tables_priv和columns_priv表。這些表的欄位如下：

表名稱	tables_priv	columns_priv
範圍欄位	Host	Host
	Db	Db
	User	User
	Table_name	Table_name
		Column_name
許可權欄位	Table_priv	Column_priv
	Column_priv
其他欄位	Timestamp	Timestamp
	Grantor

每個授權表包含範圍欄位和許可權欄位。

user表主要分為：使用者列、許可權列、安全列、資源控制列

host表主要分為：使用者列、許可權列

這裡美中不足的是mysql.user 沒有一個列是儲存使用者建立時間的

有時候排查使用者問題的時候，比如某個客戶在某個時間說串連不上資料庫，我們在user表裡只能查到是否存在那個使用者

但是不知道這個使用者的建立時間，也就是說客戶說的那個時間究竟使用者是否已經建立我們是不知道的

帳戶管理

MYSQL提供許多語句用來系統管理使用者帳號，這些語句可以用來包括登入和退出MYSQL伺服器、建立使用者、刪除使用者、密碼管理、許可權管理

MYSQL資料庫的安全性，需要通過帳戶管理來保證

登入和退出MYSQL

mysql命令的常用參數

-h：主機名稱或ip，預設是localhost，最好指定-h參數

-u：使用者名稱

-p：密碼，注意：該參數後面的字串和-p不能有空格

-P：連接埠號碼，預設為3306

資料庫名：可以在命令最後指定資料庫名

-e：執行SQL語句，如果指定該參數，將在登入後執行-e後面的命令或sql語句並退出

 

 

 命令執行完之後返回book表的結構，查詢返回之後會自動結束MYSQL

使用者

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']    [, user [IDENTIFIED BY [PASSWORD] 'password']] 

建立普通使用者

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

使用者名稱部分為“jeffrey”，主機名稱預設為“%”（即對所有主機開放許可權）

如果指定使用者登入不要求輸入密碼，則可以省略identified BY部分

對於使用外掛程式認證串連的使用者，伺服器調用指定名稱的外掛程式，用戶端需要提供驗證方法所需要的憑據。

如果建立使用者時或者串連伺服器時，伺服器找不到對應的外掛程式，將返回一個錯誤

identified with文法

CREATE user 'jeffrey'@'localhost' identified with my_auth_plugin;

identified with只能在MYSQL5.5.7及以上版本使用。

identified with和identified by是互斥的，所以對一個帳戶來說只能使用一個驗證方法。

CREATE USER語句的操作會被記錄到伺服器記錄檔或者操作曆史檔案中

例如 ~/.mysql_history。這意味著對這些檔案有讀取許可權的人，都可以讀取到新添加使用者的純文字密碼

一個辦法就是建立使用者的時候使用password關鍵字

CREATE user 'tom'@'localhost' identified BY password'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';SELECT password('mypass');SELECT * FROM `mysql`.`user` WHERE `User` ='tom';

先查出你的密碼的雜湊值，然後在建立使用者的時候輸入雜湊值

那麼在日誌裡面就只能看到雜湊值

 

使用GRANT語句建立新使用者

GRANT USER語句可以用來建立帳戶，通過該語句可以在user表中添加一條新記錄

比起CREATE USER語句建立的新使用者，還需要使用GRANT語句賦予使用者權限

使用GRANT語句建立新使用者時必須有GRANT許可權。

文法

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...    ON [object_type] {tbl_name | * | *.* | db_name.*}    TO user [IDENTIFIED BY [PASSWORD] 'password']        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...    [REQUIRE        NONE |        [{SSL| X509}]        [CIPHER 'cipher' [AND]]        [ISSUER 'issuer' [AND]]        [SUBJECT 'subject']]    [WITH with_option [with_option] ...]

使用GRANT語句建立一個新使用者testUser，密碼為testpwd，並授予使用者對所有資料表的SELECT和UPDATE許可權

GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd'SELECT `Host` ,`User` ,`Select_priv` ,`Update_priv`  FROM mysql.user WHERE `User` ='testUser';

執行結果顯示執行成功，使用SELECT語句查詢使用者testUser的許可權

 查詢結果顯示SELECT和UPDATE許可權欄位均為Y

注意：User表中的user和host欄位區分大小寫，在查詢的時候要指定正確的使用者名稱或主機名稱

直接操作MYSQL使用者表

不管是CREATE USER還是GRANT USER，在建立使用者時，實際上都是在user表中添加一條新記錄。

使用INSERT語句向mysql.user表INSERT一條記錄來建立一個新使用者

插入的時候必須要有INSERT許可權

INSERT INTO mysql.user(host,user,password,[privilegelist])VALUES ('host','username',password('password'),privilegevaluelist)

使用INSERT建立一個新使用者，其使用者名稱稱為customer1，主機名稱為localhost，密碼為customer1

INSERT INTO mysql.user(host,user,password)VALUES ('localhost','customer1',password('customer1'))

語句執行失敗，查看警告資訊如下：

show WARNINGS ;

因為ssl_cipher這個欄位在user表中沒有定義預設值，所以在這裡提示錯誤資訊。

影響insert語句的執行，使用SELECT語句查看user表中的記錄

可以看到，插入失敗

刪除普通使用者

使用DROP USER語句刪除使用者，也可以直接通過DELETE從mysql.user表中刪除對應的記錄來刪除使用者

DROP USER語句用於刪除一個或多個MYSQL帳戶。要使用DROP USER，必須擁有MYSQL資料庫的全域

CREATE USER 許可權或DELETE許可權。

刪除testUser這個使用者

DROP user 'testUser'@'localhost';

 

 可以發現testUser這個使用者已經刪除了

使用delete語句刪除使用者

DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'

root使用者修改自己的密碼

修改root密碼的方式有多種

1、使用mysqladmin命令在命令列指定新密碼

mysqladmin -u root -p password"rootpwd"

2、修改mysql資料庫的user表

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

password('')函數用來加密使用者密碼。執行update之後需要執行flush privileges語句重新載入使用者權限

3、使用SET語句修改root使用者的密碼

SET PASSWORD語句可以用來重新設定其他使用者的登入密碼或者自己使用的帳戶密碼

文法

SET PASSWORD=PASSWORD("ROOTPWD")

新密碼必須用PASSWORD函數加密

使用root使用者登入到mysql之後執行下面語句

SET password=password('123456')

執行之後需要使用執行flush privileges語句或者重啟MYSQL重新載入使用者權限

root使用者修改普通使用者密碼

1、使用SET語句修改普通使用者的密碼

SET PASSWORD FOR 'USER'@'HOST' =PASSWORD("ROOTPWD") 

2、使用update語句修改普通使用者的密碼

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

執行完畢之後需要使用flush privileges語句或者重啟MYSQL重新載入使用者權限

3、使用GRANT語句修改普通使用者密碼

GRANT USAGE ON *.* TO 'someuser'@'%'  IDENTIFIED BY 'somepwd'

使用下面語句把testUser使用者的密碼改為123456

grant USAGE ON *testUser*TO 'localhost' identified BY '123456';

注意：使用GRANT語句和MYSQLADMIN設定密碼，他們均會加密密碼，這種情況下，不需要使用PASSWORD()函數

普通使用者修改密碼

使用SET語句修改自己的密碼

SET password=password('newpassword'); 

比如修改testUser這個使用者的密碼，需要使用testUser這個使用者登入到mysql，然後執行

SET password=password('123456');

root使用者密碼丟失的解決辦法

使用--skip-grant-tables選項啟動MYSQL服務

使用--skip-grant-tables選項啟動MYSQL時，伺服器將不載入許可權判斷，任何使用者都能訪問資料庫

LINUX下

使用mysqld_safe來啟動MYSQL服務，也可以使用/etc/init.d/mysql命令來啟動mysql

mysqld_safe --skip-grant-tables user=mysql

或者

/etc/init.d/mysql start-mysqld --skip-grant-tables

啟動MYSQL服務後，就可以使用root使用者登入了

Windows下

詳細可以看一下這篇文章

Windows mysql提示：1045 access denied for user 'root'@'localhost' using password yes

許可權管理

MYSQL中的各種許可權

對於GRANT和REVOKE語句，priv_type可以被指定為以下任何一種：

許可權	意義
ALL [PRIVILEGES]	設定除GRANT OPTION之外的所有簡單許可權
ALTER	允許使用ALTER TABLE
ALTER ROUTINE	更改或取消已儲存的子程式
CREATE	允許使用CREATE TABLE
CREATE ROUTINE	建立已儲存的子程式
CREATE TEMPORARY TABLES	允許使用CREATE TEMPORARY TABLE
CREATE USER	允許使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW	允許使用CREATE VIEW
DELETE	允許使用DELETE
DROP	允許使用DROP TABLE
EXECUTE	允許使用者運行已儲存的子程式
FILE	允許使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX	允許使用CREATE INDEX和DROP INDEX
INSERT	允許使用INSERT
LOCK TABLES	允許對您擁有SELECT許可權的表使用LOCK TABLES
PROCESS	允許使用SHOW FULL PROCESSLIST
REFERENCES	未被實施
RELOAD	允許使用FLUSH
REPLICATION CLIENT	允許使用者詢問從屬伺服器或主伺服器的地址
REPLICATION SLAVE	用於複製型從屬伺服器（從主伺服器中讀取二進位日誌事件）
SELECT	允許使用SELECT
SHOW DATABASES	SHOW DATABASES顯示所有資料庫
SHOW VIEW	允許使用SHOW CREATE VIEW
SHUTDOWN	允許使用mysqladmin shutdown
SUPER	允許使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL語句，mysqladmin debug命令；允許您串連（一次），即使已達到max_connections。
UPDATE	允許使用UPDATE
USAGE	“無許可權”的同義字
GRANT OPTION	允許授予許可權

當從舊版本的MySQL升級時，要使用EXECUTE, CREATE VIEW, SHOW VIEW, CREATE USER, CREATE ROUTINE和ALTER ROUTINE許可權

授權

授權就是為某個使用者授予許可權

授與權限可以分為多個層級：

·         全域層級

全域許可權適用於一個給定伺服器中的所有資料庫。這些許可權儲存在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤銷全域許可權。

·         資料庫層級

資料庫許可權適用於一個給定資料庫中的所有目標。這些許可權儲存在mysql.db和mysql.host表中。GRANT ALL ONdb_name.*和REVOKE ALL ON db_name.*只授予和撤銷資料庫許可權。

·         表層級

表許可權適用於一個給定表中的所有列。這些許可權儲存在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表許可權。

·         列層級

列許可權適用於一個給定表中的單一列。這些許可權儲存在mysql.columns_priv表中。當使用REVOKE時，您必須指定與被授權列相同的列。

·         子程式層級

CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT許可權適用於已儲存的子程式。這些許可權可以被授予為全域層級和資料庫層級。而且，除了CREATE ROUTINE外，這些許可權可以被授予為子程式層級，並儲存在mysql.procs_priv表中。

當後續目標是一個表、一個已儲存的函數或一個已儲存的過程時，object_type子句應被指定為TABLE、FUNCTION或PROCEDURE。當從舊版本的MySQL升級時，要使用本子句，您必須升級您的授權表

使用GRANT語句建立一個新使用者grantUser，密碼為“grantpwd”

使用者對所有的資料有查詢、插入許可權，並授予GRANT許可權

GRANT SELECT ,INSERT ON *.*TO 'grantUser'@'localhost' identified BY '123456' WITH GRANT OPTION ;

查詢顯示grantUser被建立成功，並賦予了SELECT、INSERT、GRANT許可權，其相應欄位值為Y

被授予GRANT許可權的使用者可以登入MYSQL並建立其他使用者帳戶，在這裡是grantUser的使用者

收回許可權

收回許可權就是取消已經賦予使用者的某些許可權。收回使用者不必要的許可權可以在一定程度上保證系統的安全性。

使用REVOKE收回許可權之後，使用者帳戶的記錄將從db、host、tables_priv、columns_priv表中刪除，但是使用者帳號記錄依然

在user表中儲存。

文法

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...    ON [object_type] {tbl_name | * | *.* | db_name.*}    FROM user [, user] ...REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

使用REVOKE語句，必須擁有mysql資料庫的全域CREATE許可權或UPDATE許可權

使用REVOKE語句取消使用者grantUser的INSERT許可權

REVOKE INSERT ON *.* FROM 'grantUser'@'localhost';

可以看到grantUser使用者的INSERT許可權已經被收回了

注意：當從舊版本的MYSQL升級時，如果要使用EXECUTE、CREATE VIEW、SHOW VIEW、CREATE USER、CREATE ROUTINE、ALTER ROUTINE

許可權，必須先升級授權表

查看許可權

SHOW GRANT語句可以顯示使用者的許可權資訊

文法

show grants FOR 'user'@'host';

使用SHOW GRANT語句查詢使用者grantUser的許可權資訊

show grants FOR 'grantUser'@'localhost';

 

 返回結果顯示了user表中的帳戶資訊；接下來以為GRANT SELECT ON關鍵字開頭，表示使用者被授予了SELECT許可權；

*.*表示SELECT許可權作用於所有資料庫的所有資料表；

IDENTIFIED BY 後面的為使用者加密後的密碼

在這裡，只是定義了個別的使用者權限，GRANT可以顯示更加詳細的許可權資訊，包括全域級的和非全域級的許可權

如果表層級或者列層級的許可權被授予使用者的話，他們也能在結果中顯示出來。

查看MYSQL裡面匿名使用者

如果有匿名使用者，那麼用戶端就可以不用密碼登入MYSQL資料庫，這樣就會存在安全隱患

檢查匿名使用者的方法

SELECT * FROM mysql.user WHERE `User`='';

如果尋找到user欄位值為空白的那條記錄，說明存在匿名使用者，需要把這條記錄刪除

如果用匿名使用者登入MYSQL就可以看到使用者名稱是空的

刪除語句

DELETE FROM mysql.user WHERE `User`='';SELECT * FROM mysql.user WHERE `User`='';

總結

本文簡單的闡述了MYSQL的使用者管理和許可權方面的內容，希望對大家有協助

如果大家想更深入學習MYSQL存取控制方面的知識

可以參考這篇文章：MySQL許可權的架構體系

核心就是兩個圖

1、用戶端串連請求認證階段

 

 

2、用戶端操作請求認證階段

 

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。