mysql使用系統庫mysql的user表來存儲用戶信息。mysql.user表詳細的記錄了用戶名，對應的允許連接的主機信息還有各種全局權限標識位。

用戶管理

創建用戶

CREATE USER '用戶名'@'host主機' IDENTIFIED BY '密碼';

上面是創建用的基本命令，指定了用戶名，密碼和允許連接的主機。其中host如果設置為localhost，則是只允許本機連接，不允許遠程連接。如果開放遠程連接可以使用’%‘（允許所有主機連接）。一般root用戶不開啟遠程連接為好。也可以使用通配符，如’192.168.10.%’

關于密碼，以前的版本可以使用PASSWORD(‘密碼’)函數來設置密碼，該函數已經被標記為過時，且在mysql8中被刪除了，盡量直接使用密碼明文即可，下面的auth plugin會自動進行密碼的加密。

除了基本信息外，還可以有其它選項進行賬號屬性設置

1、設置密碼校驗插件（auth plugin）

CREATE USER 'jeffrey'@'localhost'IDENTIFIED WITH caching_sha2_password BY 'password'

密碼的存儲都是密文，在密碼加密過程中可以指定密碼認證插件，使用上面的IDENTIFIED WITH語句來明確指定認證插件，其中caching_sha2_password就是一種認證方式。mysql有多個認證插件，在MySQL 8.0.27之前的版本，使用default_authentication_plugin變量來定義默認的認證插件

5.7

mysql> SELECT @@default_authentication_plugin;
+---------------------------------+
| @@default_authentication_plugin |
+---------------------------------+
| mysql_native_password           |
+---------------------------------+

8.0

mysql> SELECT @@default_authentication_plugin ;
+---------------------------------+
| @@default_authentication_plugin |
+---------------------------------+
| caching_sha2_password           |

對于mysql5.7默認的認證插件方式是mysql_native_password，對于8.0版本新增了一個caching_sha2_password作為默認認證插件。

2、設置密碼過期時間

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;

使用PASSWORD EXPIRE語句來指定密碼的有效期，像上面的INTERVAL 180 DAY就是180天后過期。

如果沒有明確指定密碼過期策略，默認使用default_password_lifetime指定值來確認密碼過期方式。

mysql> select @@default_password_lifetime;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
|                           0 |

default_password_lifetime默認值是0表示永遠不過期，如果指定一個大于0的值，代表多少天后過期。

密碼過期有以下常見格式：

1、PASSWORD EXPIRE

后面沒有任何參數，密碼立即過期。這樣可以創建一個不能登錄的賬號

2、PASSWORD EXPIRE DEFAULT

使用默認的過期策略，也就是default_password_lifetime指定的方式

3、PASSWORD EXPIRE NEVER

永不過期

4、PASSWORD EXPIRE INTERVAL N DAY

N天后過期

3、鎖定用戶

CREATE USER test@'%' ACCOUNT LOCK;

鎖定的用戶，客戶端登錄會報：Error number: 3118; Symbol: ER_ACCOUNT_HAS_BEEN_LOCKED; SQLSTATE: HY000 . Account is locked.錯誤。

4、訪問資源限制

CREATE USER 'jeffrey'@'localhost'WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100 MAX_USER_CONNECTIONS 10;

• MAX_USER_CONNECTIONS

當前用戶最大允許創建連接數，默認值使用max_user_connections變量，變量值為0表示不限制。

• MAX_CONNECTIONS_PER_HOUR ，MAX_QUERIES_PER_HOUR，MAX_UPDATES_PER_HOUR

這三個參數限制了每小時連接數、查詢數和更新數。

用戶修改

用戶的修改要素和create語句是一樣的，只不過語句變成了ALTER.如下：

ALTER USER 'jeffrey'@'localhost'IDENTIFIED WITH sha256_password BY 'new_password'PASSWORD EXPIRE INTERVAL 180 DAY;

修改用戶時：用戶名和host組成了條件信息。

用戶刪除

DROP USER 'jeffrey'@'localhost';

如果不指定host，默認刪除host值為’%'的記錄。

權限管理

權限分類

數據庫有很多中權限，按照作用范圍不同大致可以分為以下幾種權限:

全局權限

ALL [PRIVILEGES]：所有的權限

SHOW DATABASES：使用SHOW DATABASES 命令查看數據庫列表權限

PROCESS：允許使用SHOW PROCESSLIST查看所有進程權限

SHUTDOWN：允許使用mysqladmin shutdown命令關閉數據庫權限

FILE：允許文件操作權限，像select into outfile，load data infile等都需要該權限

CREATE USER：允許CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES操作

REPLICATION SLAVE：允許用戶讀取binlog進行主從復制

REPLICATION CLIENT：允許查看復制狀態，像show master status,show slave status,show binary logs命令

SUPER：超級權限，允許執行一系列數據庫管理命令，像CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin等命令

RELOAD：允許執行flush命令

CREATE TABLESPACE：表空間管理權限

GRANT OPTION：分配權限操作

數據庫對象操作權限：

CREATE VIEW：創建視圖權限

SHOW VIEW：使用SHOW CREATE VIEW查看視圖權限

TRIGGER：觸發器操作權限

CREATE ROUTINE：創建存儲過程和函數權限

ALTER ROUTINE：修改存儲過程和函數權限

EXECUTE：執行存儲過程和函數權限

INDEX：索引操作權限

CREATE TEMPORARY TABLES：創建臨時表權限

CREATE：創建數據庫，創建表權限

ALTER：修改表操作

DROP：刪除數據庫、表、視圖，truncate table等權限

LOCK TABLES：鎖表權限

表數據操作權限：

INSERT：插入數據權限，ANALYZE TABLE, OPTIMIZE TABLE操作也需要該權限

DELETE：刪除數據

SELECT：查詢權限

UPDATE：更新數據權限

更多權限可以查看官方網站 privileges-provided

權限分配

權限的分配使用GRANT語句

GRANT priv_type...  ON [object_type] priv_level TO user

priv_type是權限列表，多個用英文逗號相隔

object_type：對象范圍，可以是數據庫，表，存儲過程等。

#全局級別
GRANT ALL ON *.* TO 'someuser'@'somehost';
#數據庫級別
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
#表級別
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
#列級別
GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';
#ROUTINE
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';

被grant分配后的權限，根據權限類型的不同存儲在系統庫不同的表中：

全局權限存儲在：sys.user表中

數據庫級別權限存儲在：mysql.db表中

表級權限存儲在：mysql.tables_priv表

列級別存儲在：mysql.columns_priv表

ROUTINE類型存儲在mysql.procs_priv。

user需要用戶名和host兩部分表示，格式是 username@host。host同創建用戶時host，username可以是空表示給所有用戶賦通用權限。

例：

#將db1數據庫所有權限賦給用戶
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
#將db2庫invoice對象的查詢權限賦給用戶
GRANT SELECT,INSERT ON db2.invoice TO 'jeffrey'@'localhost';

權限回收

權限的回收使用REVOKE語句，revoke語法幾乎和grant一致，只是grant改成了revoke。

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

權限查詢

使用show grants可以查看分配的用戶權限，如果要查特定用戶的可以使用for user來指定

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS FOR 'jeffrey'@'localhost';

在grant或revoke修改了權限后，一般會使用FLUSH PRIVILEGES命令使權限生效。FLUSH PRIVILEGES會從新加載系統權限表數據到內存。