20.2 用户和权限管理-MySQL从入门到精通(五)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(五)

### 20.2 用户和权限管理

在MySQL数据库中，用户和权限管理是维护数据库安全性的基石。它决定了哪些用户能够访问数据库、能够执行哪些操作以及这些操作可以针对哪些数据。本章节将深入讲解MySQL中的用户管理、权限分配、角色使用以及相关的安全最佳实践。

#### 20.2.1 用户管理基础

**1. 用户账户的概念**

MySQL中的用户账户由用户名和宿主（host）两部分组成，宿主指定了用户可以从哪些主机连接到数据库服务器。例如，用户`john`从`localhost`连接和从`192.168.1.100`连接会被视为两个不同的账户。

**2. 创建用户**

创建用户的基本语法如下：

```sql
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
```

例如，为`john`用户从`localhost`创建账户并设置密码：

```sql
CREATE USER 'john'@'localhost' IDENTIFIED BY 'securepassword';
```

注意，密码应该足够复杂，以避免被轻易破解。

**3. 查看用户**

要查看当前数据库中的所有用户，可以查询`mysql.user`表，但更常用的方法是使用`SELECT`语句结合`INFORMATION_SCHEMA.USER_PRIVILEGES`或`mysql.user`表进行过滤和格式化输出。

```sql
SELECT User, Host FROM mysql.user;
```

**4. 修改用户密码**

修改用户密码的语法如下：

```sql
ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';
```

例如，更新`john`的密码：

```sql
ALTER USER 'john'@'localhost' IDENTIFIED BY 'newsecurepassword';
```

**5. 删除用户**

删除用户的语法简单直接：

```sql
DROP USER 'username'@'host';
```

例如，删除`john`的账户：

```sql
DROP USER 'john'@'localhost';
```

#### 20.2.2 权限管理

**1. 权限类型**

MySQL支持多种权限，大致可以分为数据权限、管理权限等。数据权限如`SELECT`、`INSERT`、`UPDATE`、`DELETE`等，用于控制用户对数据的访问和修改能力；管理权限如`CREATE`、`DROP`、`GRANT OPTION`等，用于控制用户对数据库结构、用户及权限的管理能力。

**2. 授予权限**

授予权限的基本语法为：

```sql
GRANT 权限列表 ON 数据库.表 TO 'username'@'host';
```

例如，授予`john`在`sales`数据库的所有表上的`SELECT`和`INSERT`权限：

```sql
GRANT SELECT, INSERT ON sales.* TO 'john'@'localhost';
```

还可以授予全局权限，如授予用户创建数据库的权限：

```sql
GRANT CREATE ON *.* TO 'john'@'localhost';
```

**3. 查看权限**

查看用户权限的语法为：

```sql
SHOW GRANTS FOR 'username'@'host';
```

例如，查看`john`的权限：

```sql
SHOW GRANTS FOR 'john'@'localhost';
```

**4. 撤销权限**

撤销权限的语法与授予权限类似，但使用`REVOKE`关键字：

```sql
REVOKE 权限列表 ON 数据库.表 FROM 'username'@'host';
```

例如，撤销`john`在`sales`数据库上的所有表的`INSERT`权限：

```sql
REVOKE INSERT ON sales.* FROM 'john'@'localhost';
```

#### 20.2.3 角色与权限管理

MySQL 5.7及以上版本引入了角色的概念，用于简化权限管理。角色是权限的集合，可以将一组权限授予一个角色，然后将该角色授予一个或多个用户。

**1. 创建角色**

```sql
CREATE ROLE 'rolename';
```

**2. 给角色授权**

```sql
GRANT 权限列表 ON 数据库.表 TO 'rolename';
```

**3. 将角色授予用户**

```sql
GRANT 'rolename' TO 'username'@'host';
```

**4. 查看角色及其权限**

```sql
SHOW GRANTS FOR 'rolename';
```

**5. 撤销角色**

从用户撤销角色：

```sql
REVOKE 'rolename' FROM 'username'@'host';
```

删除角色（先撤销角色授予的所有权限和用户关联）：

```sql
DROP ROLE 'rolename';
```

#### 20.2.4 安全最佳实践

1. **使用强密码**：确保所有用户密码都足够复杂，难以通过暴力破解。
2. **限制用户权限**：仅授予用户完成其工作所必需的最低权限集。
3. **定期审查权限**：定期检查并更新用户权限，确保它们仍然符合当前的安全策略和业务需求。
4. **使用角色管理权限**：通过角色来管理权限可以简化权限管理过程，并减少错误。
5. **启用审计日志**：记录数据库活动，以便在发生安全事件时进行调查。
6. **使用加密连接**：通过SSL/TLS加密客户端与服务器之间的通信，防止数据在传输过程中被截获。
7. **定期更新MySQL**：及时安装安全补丁和更新，以修复已知的安全漏洞。
8. **限制远程访问**：尽可能限制远程访问MySQL服务器，仅允许从受信任的网络或主机连接。

#### 结语

用户和权限管理是MySQL数据库安全性的重要组成部分。通过合理规划和实施用户账户管理、精细控制权限分配、利用角色简化权限管理，并遵循安全最佳实践，可以显著提高数据库系统的安全性。随着MySQL版本的不断更新，用户和权限管理功能也在不断完善，建议定期关注官方文档，以获取最新的安全特性和最佳实践。

该分类下的相关小册推荐：

MySQL从入门到精通(四)

MySQL从入门到精通(二)

SQL零基础到熟练应用(增删改查)

MySQL从入门到精通(一)

MySQL从入门到精通(三)

MySQL8.0入门与实践

MySQL 实战 45 讲

MySQL必会核心问题

细说MySQL(零基础到高级应用)