DELETE FROM mysql.user WHERE User='';ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongPass#2024'; (MySQL 5.7+)UPDATE mysql.user SET Host='localhost' WHERE User='root'; FLUSH PRIVILEGES;| OS | 啟動 | 停止 |
|---|---|---|
| Windows | net start mysql | net stop mysql |
| Linux (Systemd) | systemctl start mysqld | systemctl stop mysqld |
| Linux (SysV) | /etc/init.d/mysqld start | /etc/init.d/mysqld stop |
SHOW GRANTS FOR 'app_user'@'%';| 角色 | 授權範例 | 說明 |
|---|---|---|
| 唯讀報表 | GRANT SELECT ON reporting_db.* TO 'report'@'10.0.1.%' IDENTIFIED BY 'pass'; | 僅 SELECT,限定內網 IP 段 |
| 應用寫入 | GRANT SELECT,INSERT,UPDATE,DELETE ON biz_db.* TO 'app'@'10.0.2.%' IDENTIFIED BY 'pass'; | 核心 CRUD,無 DDL/DCL |
| 開發人員 | GRANT CREATE,ALTER,DROP,INDEX,CREATE VIEW,SHOW VIEW ON dev_db.* TO 'dev'@'10.0.3.%' IDENTIFIED BY 'pass'; | 結構變更權限,無資料刪除 |
| DBA | GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.10' IDENTIFIED BY 'pass' WITH GRANT OPTION; | 全權限,僅限堡壘機 IP |
| 存儲過程執行 | GRANT EXECUTE ON PROCEDURE biz_db.proc_name TO 'worker'@'%'; | 精確到程序級 |
-- 撤銷特定權限
REVOKE DELETE ON biz_db.* FROM 'app'@'%';
-- 撤銷所有權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'%';
-- 刪除用戶
DROP USER 'old_user'@'%';
validate_password 元件default_authentication_plugin=mysql_native_password 僅相容舊版,新專案用 caching_sha2_passwordbind-address = 10.0.0.10 僅監聽內網 IPREQUIRE SSL 在 GRANT 中指定audit_log 外掛記錄所有查詢/登入/失敗