MySQL 中的用户管理

MySQL 用户管理涉及创建用户、授予权限、修改密码、撤销权限等操作是数据库安全管理的重要组成部分。MySQL 的用户信息存储在mysql.user系统表中。一、用户管理概念说明用户名登录 MySQL 时使用的账号名主机名允许用户从哪个主机登录localhost、%、192.168.1.%密码用户身份验证的凭证权限用户对数据库/表/列的操作权限角色一组权限的集合MySQL 8.0二、查看用户查看所有用户-- 查看用户列表 SELECT User, Host, authentication_string FROM mysql.user; -- 查看当前用户 SELECT CURRENT_USER(); -- 查看当前会话的用户 SELECT USER();三、创建用户1. 基本创建语法CREATE USER 用户名主机名 IDENTIFIED BY 密码;2. 示例-- 创建本地用户 CREATE USER app_userlocalhost IDENTIFIED BY SecurePass123!; -- 创建远程用户允许任意主机 CREATE USER app_user% IDENTIFIED BY SecurePass123!; -- 创建指定网段的用户 CREATE USER app_user192.168.1.% IDENTIFIED BY SecurePass123!; -- 创建无密码用户不推荐 CREATE USER guestlocalhost; -- 创建时指定加密插件MySQL 8.0 CREATE USER app_userlocalhost IDENTIFIED WITH mysql_native_password BY SecurePass123!; -- 创建多个用户 CREATE USER user1localhost IDENTIFIED BY pass1, user2% IDENTIFIED BY pass2;四、修改用户1. 修改密码-- 方法1使用 ALTER USER推荐MySQL 5.7.6 ALTER USER app_userlocalhost IDENTIFIED BY NewPass456!; -- 方法2使用 SET PASSWORD旧方式 SET PASSWORD FOR app_userlocalhost NewPass456!; -- 方法3修改当前用户密码 ALTER USER USER() IDENTIFIED BY NewPass456!;2. 修改用户名或主机-- 重命名用户 RENAME USER old_namelocalhost TO new_namelocalhost; -- 修改主机 RENAME USER app_user% TO app_user192.168.1.%;3. 锁定/解锁用户-- 锁定用户禁止登录 ALTER USER app_userlocalhost ACCOUNT LOCK; -- 解锁用户 ALTER USER app_userlocalhost ACCOUNT UNLOCK;4. 修改密码过期策略-- 设置密码永不过期 ALTER USER app_userlocalhost PASSWORD EXPIRE NEVER; -- 设置密码立即过期 ALTER USER app_userlocalhost PASSWORD EXPIRE; -- 设置密码90天后过期 ALTER USER app_userlocalhost PASSWORD EXPIRE INTERVAL 90 DAY;五、授予权限GRANT1. 基本语法GRANT 权限列表 ON 对象 TO 用户主机 [WITH GRANT OPTION];2. 权限类型常用权限-- 数据操作权限 GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_userlocalhost; -- 所有权限 GRANT ALL PRIVILEGES ON mydb.* TO app_userlocalhost; -- 创建相关权限 GRANT CREATE, ALTER, DROP, INDEX ON mydb.* TO app_userlocalhost; -- 数据库管理权限 GRANT CREATE USER, RELOAD, PROCESS ON *.* TO adminlocalhost;权限对象对象语法说明全局*.*所有数据库的所有表数据库级mydb.*指定数据库的所有表表级mydb.users指定表列级mydb.users(name, email)指定列3. 示例-- 创建应用用户 CREATE USER applocalhost IDENTIFIED BY AppPass123!; -- 授予应用用户对 mydb 的完整操作权限 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON mydb.* TO applocalhost; -- 授予只读用户 CREATE USER readonly% IDENTIFIED BY ReadOnly123!; GRANT SELECT ON mydb.* TO readonly%; -- 授予管理员用户全部权限 CREATE USER adminlocalhost IDENTIFIED BY AdminPass123!; GRANT ALL PRIVILEGES ON *.* TO adminlocalhost WITH GRANT OPTION; -- 授予列级权限 GRANT SELECT(name, email) ON mydb.users TO applocalhost; -- 授予存储过程执行权限 GRANT EXECUTE ON PROCEDURE mydb.sp_cleanup TO applocalhost;六、查看权限SHOW GRANTS-- 查看当前用户权限 SHOW GRANTS; -- 查看指定用户权限 SHOW GRANTS FOR app_userlocalhost; -- 查看所有用户的权限需查询系统表 SELECT * FROM mysql.user\G;七、撤销权限REVOKE-- 撤销指定权限 REVOKE INSERT, UPDATE ON mydb.* FROM app_userlocalhost; -- 撤销所有权限 REVOKE ALL PRIVILEGES ON mydb.* FROM app_userlocalhost; -- 撤销所有全局权限 REVOKE ALL PRIVILEGES, GRANT OPTION FROM app_userlocalhost;八、删除用户-- 删除用户 DROP USER app_userlocalhost; -- 删除多个用户 DROP USER user1localhost, user2%;九、刷新权限修改权限后需要刷新权限才能生效FLUSH PRIVILEGES;十、主机名Host的写法写法含义localhost只允许本机连接127.0.0.1只允许本机连接通过 IPv4%允许任意主机连接192.168.1.%允许 192.168.1.x 网段192.168.1.100只允许指定 IP 连接*.example.com允许 example.com 域的主机十一、角色管理MySQL 8.0角色是权限的集合可以方便地管理多个用户的权限。-- 创建角色 CREATE ROLE app_role, readonly_role; -- 授予角色权限 GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_role; GRANT SELECT ON mydb.* TO readonly_role; -- 授予角色给用户 GRANT app_role TO app_userlocalhost; GRANT readonly_role TO readonly_user%; -- 查看用户拥有的角色 SELECT * FROM mysql.role_edges;