使用者管理
以下整理 MySQL 中常見的使用者管理操作。建議使用具備 `CREATE USER`、`ALTER USER`、`GRANT OPTION` 等權限的管理帳號執行。
> 注意：MySQL 使用者由「使用者名稱 + 來源主機」組成，例如 `'appuser'@'localhost'` 與 `'appuser'@'%'` 是不同帳號。
---
## 1. 登入 MySQL
```bash
mysql -u root -p
```
或指定主機：
```bash
mysql -h 127.0.0.1 -u root -p
```
---
## 2. 新增使用者
### 建立只能從本機登入的使用者
```sql
CREATE USER 'appuser'@'localhost'
IDENTIFIED BY 'StrongPassword_123!';
```
### 建立可從指定 IP 登入的使用者
```sql
CREATE USER 'appuser'@'192.168.1.100'
IDENTIFIED BY 'StrongPassword_123!';
```
### 允許從任意主機登入
```sql
CREATE USER 'appuser'@'%'
IDENTIFIED BY 'StrongPassword_123!';
```
不建議在正式環境中直接使用 `%`，最好限制來源 IP 或使用應用程式伺服器的固定網段。
### 如果使用者可能已存在
MySQL 8.0 可使用：
```sql
CREATE USER IF NOT EXISTS 'appuser'@'localhost'
IDENTIFIED BY 'StrongPassword_123!';
```
---
## 3. 修改使用者密碼
```sql
ALTER USER 'appuser'@'localhost'
IDENTIFIED BY 'NewStrongPassword_456!';
```
如果是其他主機來源的帳號，必須指定正確的 `Host`：
```sql
ALTER USER 'appuser'@'%'
IDENTIFIED BY 'NewStrongPassword_456!';
```
管理員也可以修改自己的密碼：
```sql
ALTER USER CURRENT_USER()
IDENTIFIED BY 'NewStrongPassword_456!';
```
### 強制使用者下次登入時修改密碼
```sql
ALTER USER 'appuser'@'localhost'
PASSWORD EXPIRE;
```
取消密碼過期：
```sql
ALTER USER 'appuser'@'localhost'
PASSWORD EXPIRE NEVER;
```
---
## 4. 查看使用者
### 查看目前登入帳號
```sql
SELECT USER(), CURRENT_USER();
```
- `USER()`：用戶端實際使用的登入資訊
- `CURRENT_USER()`：MySQL 實際套用權限的帳號
### 查看使用者清單
```sql
SELECT User, Host
FROM mysql.user;
```
在某些環境中，直接查詢系統表需要額外權限。也可以使用：
```sql
SELECT User, Host
FROM mysql.user
ORDER BY User, Host;
```
不要直接修改 `mysql.user`，應使用 `CREATE USER`、`ALTER USER`、`GRANT` 等管理指令。
---
## 5. 授予資料庫權限
### 授予某個資料庫的全部權限
```sql
GRANT ALL PRIVILEGES
ON mydb.*
TO 'appuser'@'localhost';
```
這代表使用者可操作 `mydb` 資料庫中的所有資料表。
### 授予常見的應用程式權限
```sql
GRANT SELECT, INSERT, UPDATE, DELETE
ON mydb.*
TO 'appuser'@'localhost';
```
### 只授予查詢權限
```sql
GRANT SELECT
ON mydb.*
TO 'reportuser'@'localhost';
```
### 授予特定資料表權限
```sql
GRANT SELECT, INSERT
ON mydb.orders
TO 'appuser'@'localhost';
```
### 授予特定欄位權限
```sql
GRANT SELECT (id, name, email)
ON mydb.customers
TO 'reportuser'@'localhost';
```
### 授予建立 Stored Procedure 或函式的權限
```sql
GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON mydb.*
TO 'developer'@'localhost';
```
---
## 6. 查看使用者權限
```sql
SHOW GRANTS FOR 'appuser'@'localhost';
```
查看目前登入帳號的權限：
```sql
SHOW GRANTS;
```
也可以指定：
```sql
SHOW GRANTS FOR CURRENT_USER();
```
---
## 7. 撤銷權限
### 撤銷某些資料庫權限
```sql
REVOKE INSERT, UPDATE, DELETE
ON mydb.*
FROM 'appuser'@'localhost';
```
### 撤銷全部資料庫權限
```sql
REVOKE ALL PRIVILEGES
ON mydb.*
FROM 'appuser'@'localhost';
```
### 撤銷授予他人權限的能力
若使用者具有 `GRANT OPTION`：
```sql
REVOKE GRANT OPTION
ON mydb.*
FROM 'appuser'@'localhost';
```
注意，撤銷資料庫權限不一定會撤銷全域權限，因此撤銷後應檢查：
```sql
SHOW GRANTS FOR 'appuser'@'localhost';
```
---
## 8. 限制或停用帳號
### 鎖定帳號
```sql
ALTER USER 'appuser'@'localhost'
ACCOUNT LOCK;
```
### 解鎖帳號
```sql
ALTER USER 'appuser'@'localhost'
ACCOUNT UNLOCK;
```
### 設定連線限制
限制同一帳號同時最多 10 個連線：
```sql
ALTER USER 'appuser'@'localhost'
WITH MAX_USER_CONNECTIONS 10;
```
限制每小時最多登入 100 次：
```sql
ALTER USER 'appuser'@'localhost'
WITH MAX_CONNECTIONS_PER_HOUR 100;
```
也可以在建立帳號時設定：
```sql
CREATE USER 'limiteduser'@'localhost'
IDENTIFIED BY 'StrongPassword_123!'
WITH MAX_USER_CONNECTIONS 5
MAX_QUERIES_PER_HOUR 1000;
```
---
## 9. 刪除使用者
```sql
DROP USER 'appuser'@'localhost';
```
如果帳號可能不存在：
```sql
DROP USER IF EXISTS 'appuser'@'localhost';
```
若同一名稱存在不同 Host，必須分別刪除：
```sql
DROP USER 'appuser'@'localhost';
DROP USER 'appuser'@'%';
```
刪除使用者通常也會刪除其權限，但不會刪除該使用者建立的資料庫或資料表。
---
## 10. 使用角色管理權限
在 MySQL 8.0 中，可以使用角色集中管理權限。
### 建立角色
```sql
CREATE ROLE 'app_readonly';
```
### 將權限授予角色
```sql
GRANT SELECT
ON mydb.*
TO 'app_readonly';
```
### 將角色授予使用者
```sql
GRANT 'app_readonly'
TO 'reportuser'@'localhost';
```
### 設定預設角色
```sql
SET DEFAULT ROLE 'app_readonly'
TO 'reportuser'@'localhost';
```
### 移除角色
```sql
REVOKE 'app_readonly'
FROM 'reportuser'@'localhost';
```
角色適合多個使用者共用同一套權限規則，後續只需修改角色權限。
---
## 11. 是否需要執行 `FLUSH PRIVILEGES`？
使用以下標準指令修改使用者和權限時：
```sql
CREATE USER
ALTER USER
GRANT
REVOKE
DROP USER
```
通常**不需要**執行：
```sql
FLUSH PRIVILEGES;
```
這些指令會自動使權限設定生效。
只有在直接修改 MySQL 系統表，例如 `mysql.user`、`mysql.db`，才可能需要重新載入權限；但不建議直接修改系統表。
---
## 12. 常見完整範例
建立一個只能操作 `shopdb` 的應用程式帳號：
```sql
CREATE USER 'shopapp'@'10.0.0.%'
IDENTIFIED BY 'VeryStrongPassword_2025!';
GRANT SELECT, INSERT, UPDATE, DELETE
ON shopdb.*
TO 'shopapp'@'10.0.0.%';
SHOW GRANTS FOR 'shopapp'@'10.0.0.%';
```
移除其刪除資料的權限：
```sql
REVOKE DELETE
ON shopdb.*
FROM 'shopapp'@'10.0.0.%';
```
停用帳號：
```sql
ALTER USER 'shopapp'@'10.0.0.%'
ACCOUNT LOCK;
```
刪除帳號：
```sql
DROP USER 'shopapp'@'10.0.0.%';
```
---
## 13. 實務安全建議
1. **遵循最小權限原則**
應用程式通常不需要 `GRANT ALL PRIVILEGES`。
2. **避免使用 `%` 作為來源主機**
優先使用固定 IP、網段或 `localhost`。
3. **不要讓應用程式使用 `root`**
為每個應用程式建立專用帳號。
4. **使用強密碼並定期輪替**。
5. **不要直接修改 `mysql.user` 系統表**。
6. **刪除帳號前先確認是否仍有服務使用**。
7. **確認權限變更結果**：
```sql
SHOW GRANTS FOR 'username'@'host';
```
8. **注意帳號的 Host 部分**
例如：
```sql
'appuser'@'localhost'
'appuser'@'127.0.0.1'
'appuser'@'%'
```
這三者可能是不同的 MySQL 帳號。
相關學習地圖、教學課程
Python 後端工程、資料庫