---
title: "17-用户管理与权限控制"
created: 2026-01-08
tags:
  - 项目筑基
---

# 用户管理与权限控制

## **第十二章：用户管理与权限控制**

> 💡 数据库安全的核心是用户管理和权限控制

### **12.1 用户管理概述**

#### **12.1.1 MySQL 用户体系**

![[image-6421ed2e.png]]

#### **12.1.2 MySQL vs SQL Server 用户管理**

| **特性** | **SQL Server** | **MySQL** |
| --- | --- | --- |
| 认证层级 | 登录名 → 数据库用户 | 用户（包含主机信息） |
| 用户标识 | 登录名 | `用户名@主机名` |
| 默认管理员 | sa | root |

### **12.2 创建用户**

#### **12.2.1 CREATE USER 语句**

```sql
-- 基本语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 创建本地用户
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';

-- 创建可从任意主机连接的用户
CREATE USER 'lisi'@'%' IDENTIFIED BY '123456';

-- 创建可从指定IP连接的用户
CREATE USER 'wangwu'@'192.168.1.100' IDENTIFIED BY '123456';

-- 创建可从指定网段连接的用户
CREATE USER 'zhaoliu'@'192.168.1.%' IDENTIFIED BY '123456';
```

#### **12.2.2 主机名说明**

| **主机名** | **说明** |
| --- | --- |
| `localhost` | 仅允许本地连接 |
| `%` | 允许任意主机连接 |
| `192.168.1.100` | 仅允许指定IP连接 |
| `192.168.1.%` | 允许指定网段连接 |
| `%.example.com` | 允许指定域名连接 |

#### **12.2.3 查看用户**

```sql
-- 查看所有用户
SELECT User, Host FROM mysql.user;

-- 查看当前用户
SELECT USER();
SELECT CURRENT_USER();

-- 查看用户详细信息
SELECT * FROM mysql.user WHERE User = 'zhangsan'\G
```

### **12.3 修改用户**

#### **12.3.1 修改用户名**

```sql
-- RENAME USER 语句
RENAME USER '旧用户名'@'主机名' TO '新用户名'@'主机名';

-- 示例
RENAME USER 'zhangsan'@'localhost' TO 'zhang3'@'localhost';
```

#### **12.3.2 修改用户密码**

```sql
-- 方法1：ALTER USER（推荐）
ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';

-- 方法2：SET PASSWORD
SET PASSWORD FOR '用户名'@'主机名' = '新密码';

-- 方法3：修改当前用户密码
SET PASSWORD = '新密码';

-- 示例
ALTER USER 'zhangsan'@'localhost' IDENTIFIED BY 'newpassword123';
```

### **12.4 删除用户**

```sql
-- DROP USER 语句
DROP USER '用户名'@'主机名';

-- 安全删除
DROP USER IF EXISTS '用户名'@'主机名';

-- 删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'%';
```

### **12.5 权限管理**

#### **12.5.1 MySQL 权限类型**

| **权限类型** | **说明** | **权限示例** |
| --- | --- | --- |
| **数据权限** | 对数据的操作权限 | SELECT, INSERT, UPDATE, DELETE |
| **结构权限** | 对结构的操作权限 | CREATE, ALTER, DROP, INDEX |
| **管理权限** | 数据库管理权限 | GRANT OPTION, CREATE USER |

**常用权限列表：**

| **权限** | **说明** |
| --- | --- |
| `ALL PRIVILEGES` | 所有权限（不包括GRANT OPTION） |
| `SELECT` | 查询数据 |
| `INSERT` | 插入数据 |
| `UPDATE` | 更新数据 |
| `DELETE` | 删除数据 |
| `CREATE` | 创建数据库/表 |
| `DROP` | 删除数据库/表 |
| `ALTER` | 修改表结构 |
| `INDEX` | 创建/删除索引 |
| `EXECUTE` | 执行存储过程 |
| `GRANT OPTION` | 授予权限给其他用户 |

#### **12.5.2 授予权限（GRANT）**

```sql
-- 基本语法
GRANT 权限列表 ON 数据库.表 TO '用户名'@'主机名';

-- 授予单个权限
GRANT SELECT ON school.student TO 'zhangsan'@'localhost';

-- 授予多个权限
GRANT SELECT, INSERT, UPDATE ON school.student TO 'zhangsan'@'localhost';

-- 授予所有表的权限
GRANT SELECT ON school.* TO 'zhangsan'@'localhost';

-- 授予所有数据库的权限
GRANT SELECT ON *.* TO 'zhangsan'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON school.* TO 'zhangsan'@'localhost';

-- 授予权限并允许转授
GRANT ALL PRIVILEGES ON school.* TO 'zhangsan'@'localhost' WITH GRANT OPTION;

-- 刷新权限（使权限生效）
FLUSH PRIVILEGES;
```

#### **12.5.3 撤销权限（REVOKE）**

```sql
-- 基本语法
REVOKE 权限列表 ON 数据库.表 FROM '用户名'@'主机名';

-- 撤销单个权限
REVOKE INSERT ON school.student FROM 'zhangsan'@'localhost';

-- 撤销多个权限
REVOKE SELECT, UPDATE ON school.student FROM 'zhangsan'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON school.* FROM 'zhangsan'@'localhost';

-- 撤销授权权限
REVOKE GRANT OPTION ON school.* FROM 'zhangsan'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;
```

#### **12.5.4 查看权限**

```sql
-- 查看用户权限
SHOW GRANTS FOR '用户名'@'主机名';

-- 查看当前用户权限
SHOW GRANTS;

-- 示例
SHOW GRANTS FOR 'zhangsan'@'localhost';
```

### **12.6 角色管理（MySQL 8.0+）**

#### **12.6.1 什么是角色**

**定义**：角色是一组权限的集合，可以将角色授予用户，简化权限管理。

![[image-f20da447.png]]

#### **12.6.2 创建和使用角色**

```sql
-- 创建角色
CREATE ROLE 'readonly', 'readwrite', 'admin';

-- 给角色授权
GRANT SELECT ON school.* TO 'readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO 'readwrite';
GRANT ALL PRIVILEGES ON school.* TO 'admin';

-- 将角色授予用户
GRANT 'readonly' TO 'user1'@'localhost';
GRANT 'readwrite' TO 'user2'@'localhost';
GRANT 'admin' TO 'user3'@'localhost';

-- 激活角色（用户登录后需要激活）
SET DEFAULT ROLE 'readonly' TO 'user1'@'localhost';
-- 或者设置为全部角色
SET DEFAULT ROLE ALL TO 'user2'@'localhost';

-- 撤销角色
REVOKE 'readwrite' FROM 'user2'@'localhost';

-- 删除角色
DROP ROLE 'readonly';
```

### **12.7 完整示例**

#### **12.7.1 创建只读用户**

```sql
-- 创建用户
CREATE USER 'reader'@'%' IDENTIFIED BY 'reader123';

-- 授予只读权限
GRANT SELECT ON school.* TO 'reader'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 验证
SHOW GRANTS FOR 'reader'@'%';
```

#### **12.7.2 创建应用程序用户**

```sql
-- 创建用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'app123456';

-- 授予增删改查权限（不包括结构操作）
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'192.168.1.%';

-- 刷新权限
FLUSH PRIVILEGES;
```

#### **12.7.3 创建DBA用户**

```sql
-- 创建用户
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'dba123456';

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;
```

### **12.8 快速参考**

```sql
-- 用户管理
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
DROP USER IF EXISTS '用户名'@'主机名';
ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';
RENAME USER '旧用户'@'主机' TO '新用户'@'主机';

-- 权限管理
GRANT 权限 ON 数据库.表 TO '用户'@'主机';
REVOKE 权限 ON 数据库.表 FROM '用户'@'主机';
SHOW GRANTS FOR '用户'@'主机';
FLUSH PRIVILEGES;

-- 角色管理（MySQL 8.0+）
CREATE ROLE '角色名';
GRANT 权限 ON 数据库.表 TO '角色名';
GRANT '角色名' TO '用户'@'主机';
SET DEFAULT ROLE '角色名' TO '用户'@'主机';
DROP ROLE '角色名';
```

---
⬅️ [[16-存储过程与函数|存储过程与函数]] 🏠 [[00-数据库|00-数据库]] ➡️ [[18-事务与并发控制|事务与并发控制]]