用户管理与权限控制

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

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

12.1 用户管理概述

12.1.1 MySQL 用户体系

12.1.2 MySQL vs SQL Server 用户管理

12.2 创建用户

12.2.1 CREATE USER 语句

-- 基本语法
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 主机名说明

12.2.3 查看用户

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

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

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

12.3 修改用户

12.3.1 修改用户名

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

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

12.3.2 修改用户密码

-- 方法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 删除用户

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

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

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

12.5 权限管理

12.5.1 MySQL 权限类型

常用权限列表：

12.5.2 授予权限（GRANT）

-- 基本语法
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）

-- 基本语法
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 查看权限

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

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

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

12.6 角色管理（MySQL 8.0+）

12.6.1 什么是角色

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

12.6.2 创建和使用角色

-- 创建角色
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 创建只读用户

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

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

-- 刷新权限
FLUSH PRIVILEGES;

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

12.7.2 创建应用程序用户

-- 创建用户
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用户

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

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

-- 刷新权限
FLUSH PRIVILEGES;

12.8 快速参考

-- 用户管理
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 '角色名';

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