當(dāng)前位置 主頁(yè) > 技術(shù)大全 >
查看 MySQL 用戶權(quán)限是數(shù)據(jù)庫(kù)管理和安全審計(jì)的核心操作。下面為您提供一份從基礎(chǔ)到高級(jí)的完整指南。
一、基礎(chǔ)權(quán)限查看方法
1. 查看當(dāng)前用戶權(quán)限
```sql
-- 查看當(dāng)前登錄用戶的權(quán)限
SHOW GRANTS;
-- 或者使用 CURRENT_USER()
SHOW GRANTS FOR CURRENT_USER();
```
2. 查看特定用戶權(quán)限
```sql
-- 查看指定用戶的權(quán)限（必須指定host）
SHOW GRANTS FOR 'username'@'host';
-- 實(shí)際示例
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 |
+---------------------------------------------------+
```
二、詳細(xì)權(quán)限分析
1. 查看所有用戶及其權(quán)限概覽
```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. 分析全局權(quán)限（數(shù)據(jù)庫(kù)級(jí)別）
```sql
-- 查看用戶對(duì)特定數(shù)據(jù)庫(kù)的權(quán)限
SELECT * FROM mysql.db
WHERE User='username' AND Host='host';
-- 查看所有用戶的數(shù)據(jù)庫(kù)權(quán)限
SELECT * FROM mysql.db;
```
3. 查看表級(jí)權(quán)限
```sql
SELECT * FROM mysql.tables_priv
WHERE User='username' AND Host='host';
```
4. 查看列級(jí)權(quán)限
```sql
SELECT * FROM mysql.columns_priv
WHERE User='username' AND Host='host';
```
三、高級(jí)權(quán)限查詢技巧
1. 權(quán)限匯總查詢
```sql
SELECT
u.User,
u.Host,
CASE
WHEN u.Select_priv = 'Y' THEN '全局權(quán)限'
WHEN EXISTS (SELECT 1 FROM mysql.db WHERE db.User = u.User AND db.Host = u.Host) THEN '數(shù)據(jù)庫(kù)權(quán)限'
ELSE '受限權(quán)限'
END AS 權(quán)限級(jí)別,
GROUP_CONCAT(
CASE
WHEN u.Select_priv = 'Y' THEN 'SELECT'
ELSE NULL
END,
CASE
WHEN u.Insert_priv = 'Y' THEN ',INSERT'
ELSE NULL
END
-- 可以繼續(xù)添加其他權(quán)限字段
) AS 全局權(quán)限列表
FROM mysql.user u
GROUP BY u.User, u.Host;
```
2. 查找具有特定權(quán)限的用戶
```sql
-- 查找有 SUPER 權(quán)限的用戶
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';
-- 查找有創(chuàng)建用戶權(quán)限的用戶
SELECT User, Host FROM mysql.user WHERE Create_user_priv = 'Y';
-- 查找有文件操作權(quán)限的用戶
SELECT User, Host FROM mysql.user WHERE File_priv = 'Y';
```
3. 查看用戶可訪問(wèn)的數(shù)據(jù)庫(kù)
```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;
```
四、權(quán)限類型詳解
1. 數(shù)據(jù)操作權(quán)限
```sql
-- 查看數(shù)據(jù)操作權(quán)限
SELECT
User,
Host,
Select_priv as 'SELECT',
Insert_priv as 'INSERT',
Update_priv as 'UPDATE',
Delete_priv as 'DELETE'
FROM mysql.user;
```
2. 結(jié)構(gòu)操作權(quán)限
```sql
-- 查看數(shù)據(jù)庫(kù)結(jié)構(gòu)權(quán)限
SELECT
User,
Host,
Create_priv as 'CREATE',
Drop_priv as 'DROP',
Alter_priv as 'ALTER',
Index_priv as 'INDEX'
FROM mysql.user;
```
3. 管理權(quán)限
```sql
-- 查看管理權(quán)限
SELECT
User,
Host,
Grant_priv as 'GRANT',
Super_priv as 'SUPER',
Process_priv as 'PROCESS',
Reload_priv as 'RELOAD'
FROM mysql.user;
```
五、實(shí)用權(quán)限檢查腳本
1. 完整權(quán)限審計(jì)腳本
```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
-- 查找有危險(xiǎn)權(quán)限的用戶
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. 權(quán)限導(dǎo)出腳本
```sql
-- 生成權(quán)限重建語(yǔ)句
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'";
```
七、權(quán)限分析與優(yōu)化
1. 權(quán)限使用情況分析
```sql
-- 分析權(quán)限分配情況
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. 查找權(quán)限過(guò)多的用戶
```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;
```
八、安全最佳實(shí)踐
1. 定期權(quán)限審計(jì)
```sql
-- 創(chuàng)建權(quán)限審計(jì)視圖
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. 查找弱權(quán)限配置
```sql
-- 查找沒有密碼的用戶
SELECT User, Host FROM mysql.user
WHERE authentication_string = '' OR authentication_string IS NULL;
-- 查找可以從任意主機(jī)連接的用戶
SELECT User, Host FROM mysql.user WHERE Host = '%';
-- 查找權(quán)限過(guò)多的應(yīng)用程序用戶
SELECT User, Host FROM mysql.user
WHERE User NOT IN ('root', 'mysql.sys', 'mysql.session')
AND Super_priv = 'Y';
```
3. 權(quán)限變更監(jiān)控
```sql
-- 檢查最近權(quán)限變更（需要啟用general log）
SELECT * FROM mysql.general_log
WHERE argument_text LIKE '%GRANT%' OR argument_text LIKE '%REVOKE%'
ORDER BY event_time DESC;
```
九、實(shí)用命令總結(jié)
| 場(chǎng)景 | 推薦命令 |
|------|----------|
| **快速查看用戶權(quán)限** | `SHOW GRANTS FOR 'user'@'host';` |
| **查看所有用戶列表** | `SELECT User, Host FROM mysql.user;` |
| **安全檢查** | `SELECT User, Host FROM mysql.user WHERE Host = '%';` |
| **權(quán)限詳情分析** | 查詢 `mysql.user`, `mysql.db`, `mysql.tables_priv` 表 |
| **生成權(quán)限報(bào)告** | 使用權(quán)限匯總查詢腳本 |
最重要的5個(gè)命令：
1. `SHOW GRANTS;` - 查看當(dāng)前用戶權(quán)限
2. `SHOW GRANTS FOR 'user'@'host';` - 查看指定用戶權(quán)限
3. `SELECT User, Host FROM mysql.user;` - 查看所有用戶
4. `SELECT * FROM mysql.db WHERE User='user';` - 查看數(shù)據(jù)庫(kù)權(quán)限
5. `SELECT * FROM mysql.user WHERE Super_priv='Y';` - 查找超級(jí)用戶
掌握這些權(quán)限查看方法，您就能全面掌控 MySQL 的權(quán)限體系，有效進(jìn)行安全審計(jì)和權(quán)限管理。
另外搭配便捷的80kmMYSQL備份工具，可定時(shí)備份、異地備份，MYSQL導(dǎo)出導(dǎo)入�？杀镜剡B接LINUX里的MYSQL，簡(jiǎn)單便捷�？梢源蟠蟮靥岣吖ぷ餍枢浮�
