萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 查看用戶權限的兩種標準方法與授權實戰

方法一:SHOW GRANTS(推薦,直觀)

-- 查看當前用戶
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.%` |
+---------------------------------------------------------------+

方法二:查詢 mysql 系統表(詳細、可程式化)

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;

開發賬號(無 DROP/TRUNCATE/ALTER)

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 系列實用命令速查

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_%';      -- 連接統計

8.0 新特性:角色

-- 建立角色
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 持有者。
  • MySQL 8.0 引入角色(Role),簡化多用戶同權限管理,建議新專案採用。
copyright © 萬盛學電腦網 all rights reserved