MySQL 安全管理

許可權表

MySQL 伺服器通過許可權表來控制用戶對資料庫的訪問，由 mysql_install_db 腳本初始化，MySQL 會根據這些許可權表的內容為每個用戶賦予相應的許可權

1. user 表

user 表是 MySQL 最重要的一個許可權表，有 49 個欄位，這些欄位可以分成四類：

• 範圍列：包括 Host、User，分別表示主機名、用戶名，Host 指明允許訪問的 IP 或主機範圍，User 指明允許訪問的用戶名
• 許可權列：許可權列欄位描述用戶在全局範圍內允許進行的操作，該列的欄位值類型為 ENUM，只能取 Y 和 N
• 安全列：安全列有 12 個欄位，其中兩個和 ssl 相關、兩個和 x509 相關、其他八個是授權插件和密碼相關
• 資源控制列：用於限制用戶使用的資源，一個小時內用戶查詢或連接數量超過資源控制限制將被鎖定，知道下一個小時才可以再次執行

2. db 表

db 表存儲用戶對某個資料庫的操作許可權，決定用戶能從哪個主機存取哪個資料庫，大致可以分為兩類欄位：

• 用戶列：用戶列有三個欄位，分別是 Host、Db 和 User，分別表示主機名、資料庫名和用戶名
• 許可權列：決定用戶是否具有創建和修改存儲過程的許可權

3. tables_priv 表

tables_priv 表用來對錶設置操作許可權，有八個欄位：

• Host、Db、User 和 Table_name 四個欄位分別表示主機名、資料庫名、用戶名和表名
• Grantor 表示修改記錄的用戶
• Timestamp 表示修改該記錄的時間
• Table_priv 表示對象操作許可權。包括 Select、Insert、Delete 等
• Column_priv 表示對錶中的列的操作許可權，包括 Select、Insert、Update 和 References

4. columns_priv 表

columns_priv 表用來對錶的某一列設置許可權，欄位 Column_name 用來指定對哪些數據列具有操作許可權

賬戶管理

1. 新建普通用戶

在 MySQL8 以前可以使用 GRANT 語句新建用戶，MySQL8 以後需要先創建用戶才能執行 GRANT 語句

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

user 參數表示新建用戶的賬戶，由用戶（User）和主機名（Host）構成，形式如 Justin@localhost，IDENTIFIED BY 關鍵字用來設置用戶的密碼，password 參數表示用戶密碼，可以同時創建多個用戶，新用戶可以沒有初始密碼

2. 刪除普通用戶

DROP USER user[,user]...

user 參數表示新建用戶的賬戶，由用戶（User）和主機名（Host）構成，可以同時刪除多個用戶

也可以使用 DELETE 語句直接將用戶信息從 mysql.user 表刪除，前提是擁有對 mysql.user 表的刪除許可權

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

3. Root 用戶修改自己的密碼

root 用戶可以使用 ALTER 命令修改密碼

ALTER USER USER() IDENTIFIED BY 'new_password'

也可以使用 SET 語句修改密碼

SET PASSWORD='new_password'

4. Root 用戶修改普通用戶的密碼

root 用戶可以使用 ALTER 命令修改普通用戶的密碼

ALTER USER user [IDENTIFIED BY 'new_password'][,user [IDENTIFIED BY 'new_password']]...

也可以使用 SET 語句修改普通用戶的密碼

SET PASSWORD FOR 'username'@'hostname'='new_password'

普通用戶也可對自己的密碼進行管理，方式與 Root 用戶相同

密碼管理

1. 密碼過期策略

資料庫管理員可以手動設置賬號密碼過期，也可以建立一個自動密碼過期策略。過期策略可以是全局的，也可以為每個賬號設置單獨的過期策略

手動設置賬號密碼過期：

ALTER USER user PASSWORD EXPIRE

密碼過期策略基於最後修改密碼的時間自動將密碼設置為過期，MySQL 使用 default_password_lifetime 系統變數建立全局密碼過期策略，預設值為 0 表示不使用自動過期策略。它允許的值是正整數 N，表示密碼必須每隔 N 天進行修改。該值可在伺服器的配置文件設置，也可以使用 SQL 語句設置，使用 SQL 語句方式如下：

SET PERSIST default_password_lifetime=180

每個賬號既可沿用全局密碼過期策略，也可單獨設置策略

# 設置賬號密碼90天過期
CREATE USER 'Justin@localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'Justin@localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
# 設置賬號密碼永不過期
CREATE USER 'Justin@localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'Justin@localhost' PASSWORD EXPIRE NEVER;
# 沿用全局密碼過期策略
CREATE USER 'Justin@localhost' PASSWORD EXPIRE DEFAULT;

2. 密碼重用策略

MySQL 限制使用已用過的密碼，重用限制策略基於密碼更改時間和使用時間，可以是全局的，也可以為每個賬號設置單獨的策略

MySQL 基於以下規則來限制密碼重用：

1. 如果賬戶密碼限制基於密碼更改的數量，那麼新密碼不能從最近限制的密碼數量中選擇，例如，如果密碼更改的最小值為3，那麼新密碼不能與最近3個密碼中任何一個相同
2. 如果賬戶密碼限制基於時間，那麼新密碼不能從規定時間內選擇，例如，如果重用周期為60天，那麼新密碼不能從最近60天內使用的密碼中選擇

可以在配置文件設置密碼重用策略，也可以使用 SQL 語句

# 密碼重用數量
SET PERSIST password_history=6;
# 密碼重用周期
SET PERSIST password_reuse_interval=365;

每個賬號既可沿用全局密碼重用策略，也可單獨設置策略

# 不能使用最近5個密碼
CREATE USER 'Justin@localhost' PASSWORD PASSWORD HISTORY 5;
ALTER USER 'Justin@localhost' PASSWORD PASSWORD HISTORY 5;
# 不能使用最近365天內的密碼
CREATE USER 'Justin@localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'Justin@localhost' PASSWORD REUSE INTERVAL 365 DAY;

角色

在 MySQL 中，角色是許可權的集合，可以為角色添加或移除許可權。用戶可以被賦予角色，同時也被賦予角色包含的許可權

1. 創建角色並授權

創建角色語句如下：

CREATE ROLE 'role_name'[@'host_name'][,'role_name'[@'host_name']]...

為角色授權語句如下：

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

privileges 代表許可權的名稱，多個許可權以逗號1隔開，可以使用 SHOW 語句查詢許可權名稱

SHOW PRIVILEGES\G;

2. 給用戶添加角色

GTANT role[,role2,...] TO user[,user2,...];

role 代表角色，user 代表用戶，添加之後如果角色處於未激活狀態，需要先將用戶對應的角色激活

SET ROLE DEFAULT

3. 編輯角色或許可權

撤銷用戶角色的 SQL 語句如下：

REVOKE role FROM user;

撤銷角色許可權的 SQL 語句如下：

REVOKE privileges ON tablename FROM 'rolename';

4. 刪除角色

DROP ROLE role[,role2]...