Mysql使用者與許可權操作
1.使用者與許可權概述
使用者是資料庫的使用者和管理者。
MySQL通過使用者的設定來控制資料庫操作人員的存取與操作範圍。
伺服器中名為mysqI的資料庫，用於維護資料庫的使用者以及許可權的控制和管理。
MySQL中的所有使用者資訊都儲存在mysql.user資料表中。
根據mysql.user表欄位的功能可將其分為6類
- 使用者端存取伺服器的賬號欄位
Host和User欄位共同組成的複合主鍵用於區分MySQL中的賬戶。
- User欄位用於代表使用者的名稱。
- Host欄位表示允許存取的使用者端IP地址或主機地址。
- 當Host的值為「*」時，表示所有使用者端的使用者都可以存取。
mysql> SELEC Thost, user FROM mysql.user;
root:預設的超級使用者。
session: MySQL5.7新增使用者，用於使用者身份驗證。
sys:MySQL5.7新增使用者，用於系統模式物件的定義，防止DBA（資料庫管理員）重新命名或刪除root使用者時發生錯誤。
- 身份驗證欄位
在MySQL5.7中，mysql.user表中已不再包含Password欄位，而是使用plugin和authentication_string欄位儲存使用者身份驗證的資訊。
- plugin欄位用於指定使用者的驗證外掛名稱。
- authentication_string欄位是根據plugin指定的外掛演演算法對賬戶明文密碼（如123456）加密後的字串。
mysql> SELECT plugin,authentication_string FROM mysql.user
->WHERE user='root';
MySQL中root使用者的預設驗證外掛名為mysql_native_password。
authentication_string欄位儲存的則是一串不能看出具體含義的值，相對於能夠直接看懂的明文密碼（如123456），它是經過加密處理的暗碼。
其他與身份驗證的賬號密碼相關的欄位還有password_expired（密碼是否過期)、password_last_changed（密碼最後一次修改的時間）以及password_lifetime（密碼的有效期）。
- 安全連線的欄位
判斷當前連線是否符合SSL安全協定。
- ssl_type:用於儲存安全連線的型別，它的可選值有"(空）、ANY（任意型別）、X509(X509證書）、SPECIFIED（規定的）四種。
- ssl_cipher:用於儲存安全加密連線的特定密碼。
- x509_issuer:儲存由CA簽發的有效的X509證書。
- x509_subject:儲存包含主題的有效X509證書。
mysql>SHOW VARIABLES LIKE 'have_openssl';
- 資源限制的欄位
- 以「max_」開頭的欄位，儲存對使用者可使用的伺服器資源的限制。
- 用來防止使用者登入MySQL伺服器後的不法或不合規範的操作浪費伺服器的資源。
- 使用者資源限制欄位預設值均為0，表示對此使用者沒有任何的資源限制。
| 欄位 | 含義 |
|---|---|
| max_ _questions | 儲存每小時允許使用者執行查詢操作的最多次數 |
| max_ updates | 儲存每小時允許使用者執行更新操作的最多次數 |
| max_ connections | 儲存每小時允許使用者建立連線的最多次數 |
| max_ user_ connections | 儲存允許單個使用者同時建立連線的最多數量 |
- 許可權欄位
以「priv」結尾的欄位一共有29個，這些欄位儲存了使用者的全域性許可權，如Select_ priv 查詢許可權、Insert_ priv插入許可權，Update_ priv更 新許可權等。
user表對應的許可權欄位的資料型別都是ENUM列舉型別，取值只有N或Y兩種。
- N表示該使用者沒有對應許可權，預設值都為N。
- Y表示該使用者有對應許可權。
- 賬戶是否鎖定的欄位
account_ locked字 段用於儲存當前使用者是鎖定、還是解鎖狀態。
- 該欄位是一個列舉型別，當其值為N時表示解鎖，此使用者可以用於連線伺服器。
- 當其值為Y時表示該使用者已被鎖定，不能用於連線伺服器使用。
2.使用者管理
1.建立使用者
- 由於MySQL中所有使用者的資訊都儲存在mysql.user表中。建立使用者可以直接利用root使用者登入MySQL伺服器後，向mysql.user表中插入記錄，但是在開發中為保證資料的安全，並丕推茬使用此方式建立使用者。
- 採用MySQL提供的CREATE USER語句建立使用者。
- 使用CREATE USER語句每建立一個 新使用者，都會在mysql.user表中新增一條記錄，同時伺服器會自動修改相應的授權表。
- 該語句建立的新使用者預設情況下沒有任何許可權，需要使用GRANT進行授權。
建立使用者語法
CREATE USER [IF NOT EXISTS]
賬戶名[使用者身份驗證選項][,賬戶名[使用者身份驗證選項].
[WITH資源控制選項][密碼管理選項|賬戶鎖定選項]
CREATE USER可以一次建立多個使用者，多個使用者之間使用逗號分隔。
賬戶名是由「使用者名稱@主機地址」組成。
其餘選項在建立使用者時，若未設定則使用預設值。
使用者名稱的設定不能超過32個字元，且區分大小寫，但是主機地址不區分大小寫。
| 選項 | 預設值 |
|---|---|
| 使用者身份驗證選項 | 由default_ authentication _plugin 系統變數定義的外掛進行身份驗證 |
| 加密連線協定選項 | NONE |
| 資源控制選項 | N (表示無限制) |
| 密碼管理選項 | PASSWORD EXPIRE DEFAULT |
| 使用者鎖定選項 | ACCOUNT UNLOCK |
- 使用者身份驗證選項的設定僅適用於其前面的使用者名稱，可將其理解為某個使用者的私有屬性。
- 其餘的選項對宣告中的所有使用者都有效，可以將其理解為全域性屬性。
1.建立最簡單的使用者
mysql> CREATE USER' test1' ;
Query OK, 0 rows af fected .(0.00 sec)
mysql> SELECT host, user FROM mysql . user;
2.建立含有密碼的使用者
mysql> CREATE USER 'test2' @ 'localhost' IDENTIFIED BY ' 123456' ;
Query OK,0 rows affected (0.00 sec)
3.同時建立多個使用者
mysql> CREATE USER
-> 'test3'@' localhost' IDENTIFIED BY '333333' ,
-> 'test4'@' localhost' IDENTIFIED BY '444444' ;
Query OK，0 rows affected (0.01 sec)
多個使用者之間使用逗號分隔。
在建立每個使用者時可以單獨為其設定密碼，省略使用者身份驗證選項時，表明此使用者在登入伺服器時可以免密登入,但為了保證資料安全，不推薦使用者這樣做。
在建立使用者時，可以新增WITH直接為使用者指定可操作的資源範圍，如登入的使用者在一小時內可以查詢資料的次數等。
| 選項 | 描述 |
|---|---|
| MAX_ QUERIES PER_ HOUR | 在任何一個小時內，允許此使用者執行多少次查詢 |
| MAX_ UPDATES_ PER_ HOUR | 在任何一個小時內，允許此使用者執行多少次更新 |
| MAX_ CONNECTIONS_ PER_ HOUR | 在任何一個小時內，允許此使用者執行多少次伺服器連線 |
| MAX_ USER_ CONNECTIONS | 限制使用者同時連線伺服器的最大數量 |
MAX_USER_CONNECTIONS選 項的值為0時，伺服器將根據max_ user_ connections 系統變數的值確定使用者的同時連線數，若此變數值也為0,表示對該使用者沒有限制。
MAX_QUERIES_PER_HOUR選項不會計算從快取中查詢資料的次數。
例：限制其每小時最多可以更新10次
mysql> CREATE USER
-> 'test5'@' localhost' IDENTIFIED BY ' 555555'
-> WITH MAX_ UPDATES_ _PER_ HOUR 10;
Query OK，0 rows affected (0.00 sec)
檢視user表的max_ updates欄位
mysql> SELECT max_ _updates FROM user WHERE user='test5' ;
+-------------+
|max_ updatesI|
+-------------+
| 10 |
1 row in set (0.00 sec)
2.修改密碼
- ALTER USER是更改密碼的首選SQL語句，推薦使用。
- 第2種語法可能會被記錄到伺服器的紀錄檔或使用者端的歷史檔案中，會有密碼洩露.的風險，因此建議使用者儘量少的使用此方式設定密碼。
為指定使用者設定密碼
mysql> ALTER USER 'test1' @ '&' IDENTIFIED BY '123456' ;
Query 0K，0 rows affected (0.00 sec)
為登入戶設定密碼
mysql> ALTER USER USER() IDENTIFIED BY '000000' ;
Query 0K，0 rows affected (0.00 sec)
3.修改使用者
使用者建立完成後，管理員可以通過MySQL提供的專門SQL語句修改使用者的密碼、身份驗證的方式、資源限制、密碼的屬性、以及賬戶的鎖定和解鎖的狀態。
ALTER USER [IF EXISTS]
賬戶名[使用者身份驗證選項][,賬戶名[使用者身份驗證選項]].
[WITH資源限制選項][密碼管理選項|賬戶鎖定選項]
ALTER USER可同時修改一個或多個使用者，多個使用者之間使用逗號(,)分隔。
語法中選項的可選值與建立使用者時的選項完全相同。
每個修改的使用者，都會更新其在mysql.user表中對應的欄位值，而未修改的欄位仍然保留它原來的值。
例：修改使用者驗證外掛、密碼以及密碼過期時間
mysql> ALTER USER test1
-> IDENTIFIED WITH sha256_ password BY '111111 '
-> PASSWORD EXPIRE;
Query OK，0 rows affected (0.01 sec)
檢視修改後戶的密碼
mysq1> SELECT authentication_ string FROM mysql. user
-> WHERE user='test1' AND plugin= 'sha256_ password' ；
解鎖使用者
mysql> ALTER USER 'test7'@ ' localhost' ACCOUNT UNLOCK;
Query 0K，0 rows affected (0.00 sec)
同時修改多個戶資源
mysql> ALTER USER
-> 'test1' IDENTIFIED WITH mysql_native_password,
-> 'test2'@' localhost' IDENTIFIED BY '222222'
-> WITH max_ _user_ connections 2;
Query 0K，0 rows affected (0.00 sec)
4.刪除使用者
在MySQL中經常會建立多個普通使用者管理資料庫，但如果發現某些使用者是沒有必要的，就可以將其刪除，通常刪除使用者的方式採用MySQL提供的專門SQL語句。
DROP USER [IF EXISTS]賬戶名[,賬戶名]
例：
mysql> DROP USER IE EXISTS test7;
Query 0K，0 rows affected, 1 warning (0.01 sec) .
#在刪除賬戶時，如果省略主機地址，則預設為%’。
當DROP USER語句刪除當前正在開啟的使用者時，則該使用者的對談不會被自動關閉。只有在該使用者對談關閉後，刪除操作才會生效，再次登入將會失敗。另外，利用已刪除的使用者登入伺服器建立的資料庫或物件不會因此刪除操作而失效。
3.許可權管理
| 資料表 | 描述 |
|---|---|
| user | 儲存使用者被授予的全域性許可權 |
| db | 儲存使用者被授子的資料庫許可權 |
| tables_ priv | 儲存使用者被授子的表許可權 |
| columns_priv | 儲存使用者被授子的列許可權 |
| procs_priv | 儲存使用者被授予的儲存過程許可權 |
| proxies_priv | 儲存使用者被授予的代理許可權 |
1.授予許可權
根據許可權的操作內容可將許可權大致分為資料許可權、結構許可權以及管理許可權。
| 許可權 | 許可權級別 | 描述 |
|---|---|---|
| SELECT | 全域性、資料庫、表、列 | SELECT |
| UPDATE | 全域性、資料庫、表、列 | UPDATE |
| DELETE | 全域性、資料庫、表 | DELETE |
| INSERT | 全域性、資料庫、表、列 | INSERT |
| SHOW DATABASES | 全域性 | SHOW DATABASES |
| SHOW VIEW | 全域性、資料庫、表 | SHOW CREATE VIEW |
| PROCESS | 全域性 | SHOW PROCESSLIST |
| DROP | 全域性、資料庫、表 | 允許刪除資料庫、表和檢視 |
| CREATE | 全域性、資料庫、表 | 建立資料庫、表 |
| CREATE ROUTINE | 全域性、資料庫 | 建立儲存過程 |
| CREATE TABLESPACE | 全域性 | 允許建立、修改或刪除表空間和紀錄檔檔案組 |
| CREATE TEMPORARY TABLES | 全域性、資料庫 | CREATE TEMPORARY TABLE |
| CREATE VIEW | 全域性、資料庫、表 | 允許建立或修改檢視 |
| ALTER | 全域性、資料庫、表 | ALTER TABLE |
| ALTER ROUTINE | 全域性、資料庫、儲存過程 | 允許刪除或修改儲存過程 |
| INDEX | 全域性、資料庫、表 | 允許建立或刪除索引 |
| TRIGGER | 全域性、資料庫、表 | 允許觸發器的所有操作 |
| REFERENCES | 全域性、資料庫、表、列 | 允許建立外來鍵 |
| SUPER | 全域性 | 允許使用其他管理操作，如CHANGE MASTER TO等 |
| CREATE USER | 全域性 | DROP USER、CREATE USER、RENAME USER和REVOKEALL、PRIVILEGES等 |
| GRANT OPTION | 全域性、資料庫、表、儲存過程、代理 | 允許授予或刪除使用者許可權 |
| RELOAD | 全域性 | FLUSH操作 |
| PROXY | 與代理的使用者許可權相同 | |
| REPLICATION CLIENT | 全域性 | 允許使用者存取主伺服器或從伺服器 |
| REPLICATION SLAVE | 全域性 | 允許複製從伺服器讀取的主伺服器二進位制紀錄檔事件 |
| SHUTDOWN | 全域性 | 允許使用mysqladmin shutdown |
| LOCK TABLES | 全域性、資料庫 | 允許在有SELECT表許可權上使用LOCK TABLES |
許可權級別指的就是許可權可以被應用在哪些資料庫的內容中。
例如，SELECT許可權可以被授予到全域性(任意資料庫下的任意內容)、資料庫(指定資料庫下的任意內容)、表(指定資料庫下的指定資料表)、列(指定資料庫.下的指定資料表中的指定欄位)。
GRANT 許可權型別[欄位列表][，許可權型別[欄位列表] ...
ON [目標型別]許可權級別
TO 賬戶名[使用者身份驗證選項] [,賬戶名[使用者身份驗證選項]
...
[REQUIRE 連線方式]
[WITH {GRANT OPTION |資源控制選項}]
- 許可權型別:指的就是SELECT、DROP、CREATE等許可權。
- 欄位列表:用於設定列許可權。
- 目標型別:預設為TABLE，表示將全域性、資料庫、表或列中的某些許可權授予給指定的使用者。其他值為FUNCTION (函數)或PROCEDURE (儲存過程)。
- 許可權級別:用於定義全域性許可權、資料庫許可權和表許可權。
- 新增GRANT OPTION: 表示當前賬戶可以為其他賬戶進行授權。
- 其餘各引數均與CREATE USER中的使用者選項相同，這裡不再贅述。
例：檢視root使用者和test1使用者的授權情況
mysql>SHOW GRANTS FOR root'' localhost' ;
mysql> SHOW GRANTS FOR 'test1' @ '%' ;
- ALL_PRIVILEGES表示除GRANT OPTION (授權許可權)和PROXY (代理許可權)外的所有許可權。
- USAGE表示沒有任何許可權。
- ON後的
*.*表示全域性級別的許可權，即MySQL 伺服器下的所有資料庫下的所有表，「@"表示任何主機中的匿名使用者。
例：授予test1戶shop.sh_ goods表的SELECT許可權，以及對name和price欄位的插入許可權
mysql> GRANT SELECT, INSERT (name, price)
-> ON shop.sh goods
-> TO 'test1 '@'號';
Query 0K，0 rows affected (0.00 sec)
檢視許可權的儲存情況
mysql> SELECT db, table name, table priv, column_priv
-> FROM mysq1.tables priv WHERE user = 'test1' ;
mysql> SELECT db, table name , column name , column_priv
-> FROM mysql.columns_ priv WHERE user= ' test1' ;
2.回收許可權
在MySQL中，為了保證資料庫的安全性，需要將使用者不必要的許可權回收。
例如，資料管理員發現某個使用者不應該具有DELETE許可權，就應該及時將其收回。.
#①回收指定使用者的指定許可權
REVOKE許可權型別[(欄位列表)] [,許可權型別[(欄位列表)]]
ON [目標型別]許可權級別FROM賬戶名[,賬戶名] ....
#②回收所有許可權以及可為其他使用者授權的許可權
REVOKE ALL [PRIVIL EGES], GRANT OPTION FROM賬戶名[,賬戶名]
#③回收使用者的代理許可權
REVOKE PROXY ON賬戶名FROM賬戶名1[,賬戶名2] ...
回收test1使用者的插入許可權
mysq1> REVOKE INSERT (name, price)
-> ON shop.sh_ _goods FROM ' test1' @ '%' ;
Query OK, 0 rows affected (0.00 sec)
test1使用者登入MySQL伺服器，並插入資料
mysql> INSERT INTO shop.sh_ goods (name, price) VALUES('test', 23);
ERROR 1142 (42000):INSERT command denied to user ' test1'@ 'localhost' for table 'sh_goods'
3.重新整理許可權
重新整理許可權:指的是從系統資料庫mysq|中的許可權表中重新載入使用者的許可權。
原因在於: GRANT、CREATE USER等操作會將伺服器的快取資訊儲存到記憶體中,而REVOKE、DROP USER操作並不會同步到記憶體中，因此可能會造成伺服器記憶體的消耗，所以在REVOKE、DROP USER後推薦讀者使用MySQL提供的「FLUSH PRIVILEGES"重新載入使用者的許可權。
#方式1
FLUSH PRIVIL.EGES;
#方式2
mysqladmin -uroot -p reload
#方式3
mysqladmin -uroot -p flush-privileges