# MySQL 用户权限设置

> MySQL 用户创建、授权、管理

---

## 连接 MySQL

```bash
mysql -u root -p
```

---

## 1. 创建新用户

```sql
-- 创建用户（本地访问）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

-- 创建用户（远程访问）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';

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

---

## 2. 设置数据库访问权限

```sql
-- 只能访问指定数据库
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'localhost' IDENTIFIED BY 'password';

-- 访问所有数据库
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

-- 只能访问指定表的某列
GRANT SELECT ON mydb.users TO 'username'@'localhost';
```

---

## 3. 设置操作权限

| 权限 | 说明 |
|------|------|
| ALL PRIVILEGES | 所有权限 |
| SELECT | 查询 |
| INSERT | 插入 |
| UPDATE | 更新 |
| DELETE | 删除 |
| CREATE | 创建 |
| DROP | 删除 |
| WITH GRANT OPTION | 可授权 |

```sql
-- 授予所有权限（管理员）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

-- 只授予查询权限
GRANT SELECT ON *.* TO 'username'@'localhost';

-- 授予查询和插入权限
GRANT SELECT, INSERT ON *.* TO 'username'@'localhost';

-- 授予增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'username'@'localhost';
```

---

## 4. 撤销权限

```sql
REVOKE SELECT, INSERT ON *.* FROM 'username'@'localhost';
FLUSH PRIVILEGES;
```

---

## 5. 设置远程访问权限

```sql
-- 允许指定 IP 访问
GRANT ALL PRIVILEGES ON *.* TO 'username'@'192.168.1.100' IDENTIFIED BY 'password';

-- 允许所有 IP 访问
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
```

### 配置文件修改

```bash
sudo vim /etc/mysql/my.cnf
```

注释掉绑定地址：
```ini
# bind-address = 127.0.0.1
```

重启 MySQL：
```bash
sudo systemctl restart mysql
```

---

## 6. Root 用户管理

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

-- 允许 root 远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

-- 禁止 root 远程访问
DELETE FROM mysql.user WHERE user='root' AND host='%';
FLUSH PRIVILEGES;
```

---

## 7. 删除用户

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

---

## 8. 查看用户权限

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

---

## 常用示例

```sql
-- 创建开发用户
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'dev'@'localhost';
FLUSH PRIVILEGES;

-- 创建只读用户
CREATE USER 'reader'@'%' IDENTIFIED BY 'read_password';
GRANT SELECT ON myapp.* TO 'reader'@'%';
FLUSH PRIVILEGES;

-- 修改用户密码
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');
FLUSH PRIVILEGES;
```

---

> 参考：[MySQL 用户权限管理](https://www.cnblogs.com/candle806/p/4048651.html)