-- 查看當前用戶
SHOW GRANTS;
-- 查看指定用戶(需 SELECT 權限於 mysql.user)
SHOW GRANTS FOR 'app_user'@'10.0.%';
+---------------------------------------------------------------+
| Grants for app_user@10.0.% |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`10.0.%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `app_user`@`10.0.%` |
+---------------------------------------------------------------+
SELECT User, Host,
Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Alter_priv, Index_priv,
Create_tmp_table_priv, Lock_tables_priv,
Create_view_priv, Show_view_priv,
Create_routine_priv, Alter_routine_priv,
Event_priv, Trigger_priv,
Grant_priv, Super_priv, Repl_slave_priv, Repl_client_priv,
File_priv, Process_priv, Reload_priv, Shutdown_priv,
Create_user_priv, Create_tablespace_priv,
max_questions, max_updates, max_connections, max_user_connections,
plugin, authentication_string, password_expired
FROM mysql.user
WHERE User = 'app_user' AND Host = '10.0.%'\G
\G 垂直顯示,適合寬表閱讀。
| 層級 | GRANT 語法 | 適用對象 | 典型權限 |
|---|---|---|---|
| 全局 | GRANT ... ON *.* | 整個服務器 | SUPER, FILE, PROCESS, RELOAD, SHUTDOWN, CREATE USER |
| 資料庫 | GRANT ... ON db.* | 單一資料庫所有表 | SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX |
| 表 | GRANT ... ON db.table | 單一表 | SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, TRIGGER |
| 欄位 | GRANT SELECT (col1), UPDATE (col2) ON db.table | 單一表特定列 | SELECT, UPDATE, REFERENCES |
CREATE USER 'readonly'@'10.0.%' IDENTIFIED BY 'ReadOnlyStr0ng!';
GRANT SELECT ON `report_db`.* TO 'readonly'@'10.0.%';
GRANT SELECT ON `prod_db`.* TO 'readonly'@'10.0.%';
FLUSH PRIVILEGES;
CREATE USER 'dev'@'10.0.%' IDENTIFIED BY 'DevPass2024!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, EXECUTE
ON `dev_db`.* TO 'dev'@'10.0.%';
FLUSH PRIVILEGES;
CREATE USER 'dba_migrate'@'10.0.%' IDENTIFIED BY 'Migrat3Str0ng!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,
LOCK TABLES, RELOAD, PROCESS, REPLICATION CLIENT
ON *.* TO 'dba_migrate'@'10.0.%';
FLUSH PRIVILEGES;
REVOKE INSERT, UPDATE ON `app_db`.* FROM 'app_user'@'10.0.%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'%';
FLUSH PRIVILEGES;
SHOW DATABASES; -- 可見資料庫(受權限影響)
SHOW TABLES FROM db_name;
SHOW CREATE DATABASE db_name; -- 字符集、校對規則
SHOW CREATE TABLE tbl_name; -- 完整 DDL、索引、外鍵
SHOW INDEX FROM tbl_name; -- 索引詳情
SHOW TABLE STATUS FROM db_name; -- 行數、大小、引擎、自增值
SHOW PROCESSLIST; -- 當前線程
SHOW ENGINE INNODB STATUS; -- InnoDB 內部狀態(死鎖、鎖等待、緩衝池)
SHOW VARIABLES LIKE 'char%'; -- 字符集參數
SHOW STATUS LIKE 'Threads_%'; -- 連接統計
-- 建立角色
CREATE ROLE 'app_readonly', 'app_readwrite';
-- 授權給角色
GRANT SELECT ON `app_db`.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO 'app_readwrite';
-- 用戶授予角色
CREATE USER 'svc_user'@'10.0.%' IDENTIFIED BY 'pwd';
GRANT 'app_readwrite' TO 'svc_user'@'10.0.%';
-- 設定預設角色(登入自動生效)
SET DEFAULT ROLE 'app_readwrite' TO 'svc_user'@'10.0.%';
-- 查看角色權限
SHOW GRANTS FOR 'svc_user'@'10.0.%' USING 'app_readwrite';
SHOW GRANTS 適合人工快速核對;SELECT FROM mysql.user 適合自動化巡檢、CI/CD 門禁。%、定期輪換密碼、審計 GRANT OPTION 持有者。