核心查看命令速查表

需求	命令	說明
查看所有用戶	SELECT User, Host FROM mysql.user;	包含認證插件、密碼過期等信息
查看當前用戶	SELECT CURRENT_USER();	實際認證身份（可能與登錄用戶不同）
查看用戶權限	SHOW GRANTS FOR 'user'@'host';	顯示完整 GRANT 語句
查看當前權限	SHOW GRANTS;
查看所有數據庫	SHOW DATABASES;	受 show_db 權限控制
模糊匹配數據庫	SHOW DATABASES LIKE 'prefix%';	支持通配符 % _
查看當前庫	SELECT DATABASE();	返回 NULL 表示未選庫
查看指定庫表	SHOW TABLES FROM db_name;	不切換庫上下文
查看表結構	DESC table_name; / SHOW CREATE TABLE t\G	\G 垂直顯示更易讀

實戰演示場景

場景 1：審計所有用戶權限

-- 導出所有用戶權限（便於審計/遷移）
mysql -u root -p -N -e "SELECT CONCAT('SHOW GRANTS FOR ''', User, '''@''', Host, ''';') FROM mysql.user" | \
mysql -u root -p | sed 's/$/;/' > all_grants_$(date +%F).sql

場景 2：查找擁有危險權限的用戶

-- SUPER 權限用戶
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';

-- DROP 權限用戶
SELECT User, Host, Db FROM mysql.db WHERE Drop_priv = 'Y';

-- FILE 權限用戶（可讀寫服務器文件）
SELECT User, Host FROM mysql.user WHERE File_priv = 'Y';

-- 匿名用戶（安全隱患）
SELECT User, Host FROM mysql.user WHERE User = '';

場景 3：清理無用/默認用戶

-- 刪除匿名用戶
DROP USER ''@'localhost';
DROP USER ''@'%';

-- 刪除無密碼用戶（5.7+ 需先設置密碼或直接刪除）
DELETE FROM mysql.user WHERE User='' OR (Password='' AND authentication_string='');
FLUSH PRIVILEGES;

場景 4：數據庫對象查看進階

-- 查看表大小、引擎、行數
SELECT 
  TABLE_SCHEMA AS `Database`,
  TABLE_NAME AS `Table`,
  ENGINE,
  TABLE_ROWS,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `Size_MB`,
  TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 查看視圖定義
SELECT TABLE_NAME, VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'db_name';

-- 查看存儲過程/函數
SELECT ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'db_name';

權限系統表結構速查

表	層級	關鍵列
mysql.user	全局	Host, User, * _priv 列, plugin, authentication_string
mysql.db	庫級	Host, Db, User, * _priv 列
mysql.tables_priv	表級	Host, Db, User, Table_name, Table_priv, Column_priv
mysql.columns_priv	列級	Host, Db, User, Table_name, Column_name, Column_priv
mysql.procs_priv	程序級	Host, Db, User, Routine_name, Routine_type, Proc_priv
mysql.proxies_priv	代理用戶	Host, User, Proxied_host, Proxied_user, With_grant

MySQL 8.0 新特性：信息模式表替換

8.0 將權限表移至數據字典，mysql.user 等成為視圖，查詢方式不變但性能更好。新增：

-- 查看角色
SELECT * FROM information_schema.APPLICABLE_ROLES;

-- 查看默認角色
SELECT * FROM information_schema.ENABLED_ROLES;

一鍵健康檢查腳本

#!/bin/bash
# mysql_user_audit.sh
echo "=== 匿名用戶 ==="
mysql -u root -p -e "SELECT User, Host FROM mysql.user WHERE User = '';"

echo -e "\n=== SUPER 權限用戶 ==="
mysql -u root -p -e "SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';"

echo -e "\n=== 無密碼/弱密碼用戶 ==="
mysql -u root -p -e "SELECT User, Host, plugin FROM mysql.user WHERE authentication_string = '' OR authentication_string IS NULL;"

echo -e "\n=== 所有用戶權限摘要 ==="
mysql -u root -p -e "
SELECT 
  u.User, u.Host,
  u.Super_priv, u.Shutdown_priv, u.File_priv,
  COUNT(d.Db) AS db_count
FROM mysql.user u
LEFT JOIN mysql.db d ON u.User = d.User AND u.Host = d.Host
GROUP BY u.User, u.Host
ORDER BY u.Super_priv DESC, db_count DESC;"