MySQL 的权限管理

MySQL 的权限管理是数据库安全的核心，通过精细控制用户对数据库对象的访问，确保数据安全性。以下是详细的权限管理指南：

---

​​一、用户管理​​

1. 创建用户

sql

复制

-- MySQL 8.0+ 推荐方式

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

-- 示例：允许从任意主机连接

CREATE USER 'app_user'@'%' IDENTIFIED BY 'SecurePass123!';

2. 修改密码

sql

复制

ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

-- 强制立即生效（部分版本需手动刷新）

FLUSH PRIVILEGES;

3. 删除用户

sql

复制

DROP USER 'username'@'hostname';

---

​​二、权限类型​​

1. 权限级别

• ​​全局权限​​：影响所有数据库（*.*）

  sql

  复制

• GRANT SELECT, INSERT ON *.* TO 'user'@'host';

• ​​数据库权限​​：作用于特定数据库（db.*）

  sql

  复制

• GRANT CREATE, DROP ON mydb.* TO 'user'@'host';

• ​​表权限​​：针对具体表（db.table）

  sql

  复制

• GRANT UPDATE, DELETE ON mydb.orders TO 'user'@'host';

• ​​列权限​​：限制特定列的操作（需显式指定）

  sql

  复制

• GRANT SELECT(id, name) ON mydb.users TO 'user'@'host';

• ​​存储过程/函数权限​​：执行权限

  sql

  复制

• GRANT EXECUTE ON PROCEDURE mydb.procedure_name TO 'user'@'host';

2. 特殊权限

• ALL：授予所有权限（慎用）。
• USAGE：无权限，仅用于占位。
• WITH GRANT OPTION：允许用户转授权限。

---

​​三、授权与撤销​​

1. 授予权限

sql

复制

-- 授予全局权限

GRANT SELECT, SHOW DATABASES ON *.* TO 'readonly'@'localhost';

-- 授予数据库级权限并允许转授

GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'%' WITH GRANT OPTION;

2. 撤销权限

sql

复制

REVOKE SELECT, INSERT ON mydb.* FROM 'user'@'host';

-- 撤销所有权限

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';

---

​​四、查看权限​​

1. 查看用户权限

sql

复制

SHOW GRANTS FOR 'username'@'hostname';

-- 示例输出：

GRANT SELECT ON mydb.* TO 'user'@'localhost'

2. 直接查询系统表

sql

复制

SELECT * FROM mysql.user WHERE User = 'username';

---

​​五、高级特性​​

1. 角色（Roles，MySQL 8.0+）

• ​​创建角色​​：

  sql

  复制

• CREATE ROLE 'read_role', 'write_role';

• ​​分配权限给角色​​：

  sql

  复制

• GRANT SELECT ON mydb.* TO 'read_role';

• ​​启用角色​​：

  sql

  复制

• SET DEFAULT ROLE 'read_role' TO 'user'@'host';

• ​​删除角色​​：

  sql

  复制

• DROP ROLE 'role_name';

2. 密码策略

• 强制密码复杂度：

  sql

  复制

  SET GLOBAL validate_password.policy = STRONG;

• 设置密码有效期：

  sql

  复制

• ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;

---

​​六、安全建议​​

1. ​​最小权限原则​​：仅授予必要权限，避免使用 GRANT ALL。
2. ​​主机限制​​：尽量用具体 IP 或域名代替 %，防止远程滥用。
3. ​​定期审计​​：使用 SHOW GRANTS 或脚本定期检查权限。
4. ​​避免 root 远程登录​​：禁用 root@%，改用具有受限权限的账户。
5. ​​密码安全​​：启用 SSL 加密连接，使用强密码并定期更换。

---

​​七、常见问题​​

• ​​权限未生效​​：修改权限后执行 FLUSH PRIVILEGES;。
• ​​权限冲突​​：更高权限（如全局）会覆盖低级权限。
• ​​直接修改系统表风险​​：始终通过 SQL 语句（如 GRANT）修改权限，避免手动编辑 mysql.user。

---

通过合理运用上述策略，可以有效保障 MySQL 数据库的安全性，平衡灵活性与风险控制。