查看 MySQL 用戶權限是數據庫管理和安全審計的核心操作。下面為您提供一份從基礎到高級的完整指南。
一、基礎權限查看方法
1. 查看當前用戶權限
```sql
-- 查看當前登錄用戶的權限
SHOW GRANTS;
-- 或者使用 CURRENT_USER()
SHOW GRANTS FOR CURRENT_USER();
```
2. 查看特定用戶權限
```sql
-- 查看指定用戶的權限（必須指定host）
SHOW GRANTS FOR 'username'@'host';
-- 實際示例
SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'app_user'@'%';
```
**輸出示例：**
```sql
+---------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' |
| WITH GRANT OPTION |
+---------------------------------------------------+
```
二、詳細權限分析
1. 查看所有用戶及其權限概覽
```sql
SELECT
user,
host,
authentication_string,
Select_priv,
Insert_priv,
Update_priv,
Delete_priv,
Create_priv,
Drop_priv
FROM mysql.user
ORDER BY user, host;
```
2. 分析全局權限（數據庫級別）
```sql
-- 查看用戶對特定數據庫的權限
SELECT * FROM mysql.db
WHERE User='username' AND Host='host';
-- 查看所有用戶的數據庫權限
SELECT * FROM mysql.db;
```
3. 查看表級權限
```sql
SELECT * FROM mysql.tables_priv
WHERE User='username' AND Host='host';
```
4. 查看列級權限
```sql
SELECT * FROM mysql.columns_priv
WHERE User='username' AND Host='host';
```
三、高級權限查詢技巧
1. 權限匯總查詢
```sql
SELECT
u.User,
u.Host,
CASE
WHEN u.Select_priv = 'Y' THEN '全局權限'
WHEN EXISTS (SELECT 1 FROM mysql.db WHERE db.User = u.User AND db.Host = u.Host) THEN '數據庫權限'
ELSE '受限權限'
END AS 權限級別,
GROUP_CONCAT(
CASE
WHEN u.Select_priv = 'Y' THEN 'SELECT'
ELSE NULL
END,
CASE
WHEN u.Insert_priv = 'Y' THEN ',INSERT'
ELSE NULL
END
-- 可以繼續添加其他權限字段
) AS 全局權限列表
FROM mysql.user u
GROUP BY u.User, u.Host;
```
2. 查找具有特定權限的用戶
```sql
-- 查找有 SUPER 權限的用戶
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';
-- 查找有創建用戶權限的用戶
SELECT User, Host FROM mysql.user WHERE Create_user_priv = 'Y';
-- 查找有文件操作權限的用戶
SELECT User, Host FROM mysql.user WHERE File_priv = 'Y';
```
3. 查看用戶可訪問的數據庫
```sql
SELECT
u.User,
u.Host,
db.Db as Database,
db.Select_priv,
db.Insert_priv,
db.Update_priv,
db.Delete_priv
FROM mysql.user u
LEFT JOIN mysql.db db ON u.User = db.User AND u.Host = db.Host
WHERE u.User = 'username'
ORDER BY db.Db;
```
四、權限類型詳解
1. 數據操作權限
```sql
-- 查看數據操作權限
SELECT
User,
Host,
Select_priv as 'SELECT',
Insert_priv as 'INSERT',
Update_priv as 'UPDATE',
Delete_priv as 'DELETE'
FROM mysql.user;
```
2. 結構操作權限
```sql
-- 查看數據庫結構權限
SELECT
User,
Host,
Create_priv as 'CREATE',
Drop_priv as 'DROP',
Alter_priv as 'ALTER',
Index_priv as 'INDEX'
FROM mysql.user;
```
3. 管理權限
```sql
-- 查看管理權限
SELECT
User,
Host,
Grant_priv as 'GRANT',
Super_priv as 'SUPER',
Process_priv as 'PROCESS',
Reload_priv as 'RELOAD'
FROM mysql.user;
```
五、實用權限檢查腳本
1. 完整權限審計腳本
```sql
SELECT
CONCAT('\'', user, '\'@\'', host, '\'') as user_host,
IF(Select_priv = 'Y', 'SELECT', '') as select_priv,
IF(Insert_priv = 'Y', 'INSERT', '') as insert_priv,
IF(Update_priv = 'Y', 'UPDATE', '') as update_priv,
IF(Delete_priv = 'Y', 'DELETE', '') as delete_priv,
IF(Create_priv = 'Y', 'CREATE', '') as create_priv,
IF(Drop_priv = 'Y', 'DROP', '') as drop_priv,
IF(Grant_priv = 'Y', 'GRANT', '') as grant_priv,
IF(Super_priv = 'Y', 'SUPER', '') as super_priv
FROM mysql.user
ORDER BY user, host;
```
2. 安全檢查腳本
```sql
-- 查找有危險權限的用戶
SELECT
User,
Host,
CONCAT_WS(',',
IF(Super_priv = 'Y', 'SUPER', NULL),
IF(File_priv = 'Y', 'FILE', NULL),
IF(Process_priv = 'Y', 'PROCESS', NULL),
IF(Shutdown_priv = 'Y', 'SHUTDOWN', NULL)
) as dangerous_privileges
FROM mysql.user
WHERE Super_priv = 'Y'
OR File_priv = 'Y'
OR Process_priv = 'Y'
OR Shutdown_priv = 'Y';
```
3. 權限導出腳本
```sql
-- 生成權限重建語句
SELECT
CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') as grant_command
FROM mysql.user;
```
六、information_schema 查詢
1. 使用 SCHEMA_PRIVILEGES
```sql
SELECT * FROM information_schema.SCHEMA_PRIVILEGES
WHERE GRANTEE = "'username'@'host'";
```
2. 使用 TABLE_PRIVILEGES
```sql
SELECT * FROM information_schema.TABLE_PRIVILEGES
WHERE GRANTEE = "'username'@'host'";
```
3. 使用 USER_PRIVILEGES
```sql
SELECT * FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = "'username'@'host'";
```
七、權限分析與優化
1. 權限使用情況分析
```sql
-- 分析權限分配情況
SELECT
privilege_type,
COUNT(*) as user_count
FROM (
SELECT 'SELECT' as privilege_type FROM mysql.user WHERE Select_priv = 'Y'
UNION ALL SELECT 'INSERT' FROM mysql.user WHERE Insert_priv = 'Y'
UNION ALL SELECT 'UPDATE' FROM mysql.user WHERE Update_priv = 'Y'
UNION ALL SELECT 'DELETE' FROM mysql.user WHERE Delete_priv = 'Y'
UNION ALL SELECT 'CREATE' FROM mysql.user WHERE Create_priv = 'Y'
) privileges
GROUP BY privilege_type
ORDER BY user_count DESC;
```
2. 查找權限過多的用戶
```sql
SELECT
User,
Host,
(Select_priv = 'Y') + (Insert_priv = 'Y') + (Update_priv = 'Y') +
(Delete_priv = 'Y') + (Create_priv = 'Y') + (Drop_priv = 'Y') +
(Reload_priv = 'Y') + (Shutdown_priv = 'Y') + (Process_priv = 'Y') +
(File_priv = 'Y') + (Grant_priv = 'Y') + (References_priv = 'Y') +
(Index_priv = 'Y') + (Alter_priv = 'Y') + (Super_priv = 'Y') +
(Create_tmp_table_priv = 'Y') + (Lock_tables_priv = 'Y') +
(Execute_priv = 'Y') + (Repl_slave_priv = 'Y') + (Repl_client_priv = 'Y') +
(Create_view_priv = 'Y') + (Show_view_priv = 'Y') + (Create_routine_priv = 'Y') +
(Alter_routine_priv = 'Y') + (Create_user_priv = 'Y') + (Event_priv = 'Y') +
(Trigger_priv = 'Y') + (Create_tablespace_priv = 'Y') as total_privileges
FROM mysql.user
ORDER BY total_privileges DESC;
```
八、安全最佳實踐
1. 定期權限審計
```sql
-- 創建權限審計視圖
CREATE VIEW user_privileges_audit AS
SELECT
u.User,
u.Host,
u.authentication_string,
IF(u.Select_priv = 'Y', 'GLOBAL',
IF(EXISTS(SELECT 1 FROM mysql.db WHERE User = u.User AND Host = u.Host), 'DATABASE', 'RESTRICTED')
) as privilege_level,
DATE(u.password_last_changed) as password_last_changed
FROM mysql.user u;
```
2. 查找弱權限配置
```sql
-- 查找沒有密碼的用戶
SELECT User, Host FROM mysql.user
WHERE authentication_string = '' OR authentication_string IS NULL;
-- 查找可以從任意主機連接的用戶
SELECT User, Host FROM mysql.user WHERE Host = '%';
-- 查找權限過多的應用程序用戶
SELECT User, Host FROM mysql.user
WHERE User NOT IN ('root', 'mysql.sys', 'mysql.session')
AND Super_priv = 'Y';
```
3. 權限變更監控
```sql
-- 檢查最近權限變更（需要啟用general log）
SELECT * FROM mysql.general_log
WHERE argument_text LIKE '%GRANT%' OR argument_text LIKE '%REVOKE%'
ORDER BY event_time DESC;
```
九、實用命令總結
| 場景 | 推薦命令 |
|------|----------|
| **快速查看用戶權限** | `SHOW GRANTS FOR 'user'@'host';` |
| **查看所有用戶列表** | `SELECT User, Host FROM mysql.user;` |
| **安全檢查** | `SELECT User, Host FROM mysql.user WHERE Host = '%';` |
| **權限詳情分析** | 查詢 `mysql.user`, `mysql.db`, `mysql.tables_priv` 表 |
| **生成權限報告** | 使用權限匯總查詢腳本 |
最重要的5個命令：
1. `SHOW GRANTS;` - 查看當前用戶權限
2. `SHOW GRANTS FOR 'user'@'host';` - 查看指定用戶權限
3. `SELECT User, Host FROM mysql.user;` - 查看所有用戶
4. `SELECT * FROM mysql.db WHERE User='user';` - 查看數據庫權限
5. `SELECT * FROM mysql.user WHERE Super_priv='Y';` - 查找超級用戶
掌握這些權限查看方法，您就能全面掌控 MySQL 的權限體系，有效進行安全審計和權限管理。
另外搭配便捷的80kmMYSQL備份工具，可定時備份、異地備份，MYSQL導出導入�？杀镜剡B接LINUX里的MYSQL，簡單便捷�？梢源蟠蟮靥岣吖ぷ餍枢�。
