查看 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导出导入。可本地连接LINUX里的MYSQL，简单便捷。可以大大地提高工作效率喔。
