Mysql用戶與權限操作

1.用戶與權限概述

用戶是資料庫的使用者和管理者，
MySQL通過用戶的設定來控制資料庫操作人員的訪問與操作范圍，
服務器中名為mysqI的資料庫，用于維護資料庫的用戶以及權限的控制和管理，
MySQL中的所有用戶資訊都保存在mysql.user資料表中，

根據mysql.user表欄位的功能可將其分為6類

• 客戶端訪問服務器的賬號欄位

Host和User欄位共同組成的復合主鍵用于區分MySQL中的賬戶，

1. User欄位用于代表用戶的名稱，
2. Host欄位表示允許訪問的客戶端IP地址或主機地址，
3. 當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欄位保存用戶身份驗證的資訊，

1. plugin欄位用于指定用戶的驗證插件名稱，
2. 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安全協議，

1. ssl_type:用于保存安全連接的型別，它的可選值有"(空）、ANY（任意型別）、X509(X509證書）、SPECIFIED（規定的）四種，
2. ssl_cipher:用于保存安全加密連接的特定密碼，
3. x509_issuer:保存由CA簽發的有效的X509證書，
4. x509_subject:保存包含主題的有效X509證書，

mysql>SHOW VARIABLES LIKE 'have_openssl';

• 資源限制的欄位

1. 以“max_”開頭的欄位，保存對用戶可使用的服務器資源的限制，
2. 用來防止用戶登錄MySQL服務器后的不法或不合規范的操作浪費服務器的資源，
3. 用戶資源限制欄位默認值均為0，表示對此用戶沒有任何的資源限制，

• 權限欄位

以“priv”結尾的欄位一共有29個，這些欄位保存了用戶的全域權限，如Select_ priv 查詢權限、Insert_ priv插入權限，Update_ priv更 新權限等，
user表對應的權限欄位的資料型別都是ENUM列舉型別，取值只有N或Y兩種，

1. N表示該用戶沒有對應權限，默認值都為N，
2. Y表示該用戶有對應權限，

• 賬戶是否鎖定的欄位

account_ locked字 段用于保存當前用戶是鎖定、還是解鎖狀態，

1. 該欄位是一個列舉型別，當其值為N時表示解鎖，此用戶可以用于連接服務器，
2. 當其值為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個字符，且區分大小寫，但是主機地址不區分大小寫，

• 用戶身份驗證選項的設定僅適用于其前面的用戶名，可將其理解為某個用戶的私有屬性，
• 其余的選項對宣告中的所有用戶都有效，可以將其理解為全域屬性，

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_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.權限管理

1.授予權限

根據權限的操作內容可將權限大致分為資料權限、結構權限以及管理權限，

權限級別指的就是權限可以被應用在哪些資料庫的內容中，

例如，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