深入解析：《MySQL基础——用户管理》

一、MySQL 用户的 “藏身之处”：user 表解析

MySQL 的所有用户信息都存储在系统数据库mysql的user表中，这是用户管理的 “源头”。

1. 查看用户信息

通过以下 SQL 可查看已存在的用户及核心属性：

-- 切换到系统数据库mysql
use mysql;
-- 查看用户的主机、用户名、加密后的密码
select host, user, authentication_string from user;
+-----------+------------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+------------------+-------------------------------------------+
3 rows in set (0.00 sec)

2. 核心字段含义

字段说明
host用户允许登录的主机 / IP，localhost表示仅允许本机登录，%表示允许任意主机登录
user用户名
authentication_string用户密码通过password()函数加密后的结果（MySQL 5.7+，旧版本为password字段）
*_priv权限相关字段（如Select_priv控制查询权限，Drop_priv控制删除权限）

二、用户基础操作：创建、删除与密码修改

1. 创建用户

语法

create user '用户名'@'登录主机/IP' identified by '密码';
  • 登录主机 / IP：localhost（本机）、192.168.1.100（指定 IP）、%（任意主机，不推荐，风险高）。
  • 密码限制：MySQL 默认密码策略较严格，简单密码（如123456）会报错，可通过SHOW VARIABLES LIKE ‘validate_password%’;查看策略，调整方法参考解决密码策略报错。

案例：创建本地登录用户whb:

-- 创建用户whb，仅允许本机登录，密码12345678
create user 'whb'@'localhost' identified by '12345678';
-- 验证用户是否创建成功
select user, host, authentication_string from user;
+-----------+------------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+-------------------------------------------+
| % | root | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | whb | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
+-----------+------------------+-------------------------------------------+
4 rows in set (0.00 sec)

此时可使用whb/12345678从本机登录 MySQL。

2. 删除用户

语法

drop user '用户名'@'登录主机/IP';

注意：必须指定host，仅写用户名（如drop user whb;）会默认按’whb’@'%'删除，若用户实际host为localhost，会报错ERROR 1396 (HY000)。

案例：删除用户whb

-- 错误示例：未指定host，删除失败
drop user whb;
-- 正确示例：指定host为localhost
drop user 'whb'@'localhost';
-- 验证删除结果
select user, host, authentication_string from user;
-- 错误示例执行结果
ERROR 1396 (HY000): Operation DROP USER failed for 'whb'@'%'
-- 正确示例执行结果
Query OK, 0 rows affected (0.00 sec)
-- 验证结果
+-----------+------------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+-------------------------------------------+
| % | root | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+------------------+-------------------------------------------+
3 rows in set (0.00 sec)

3. 修改用户密码

普通用户修改自身密码

-- 登录普通用户后执行
set password = password('新密码');

root 用户修改其他用户密码

set password for '用户名'@'登录主机/IP' = password('新密码');

案例：root 修改whb的密码

-- 先重新创建用户whb（用于测试）
create user 'whb'@'localhost' identified by '12345678';
-- root用户将whb的密码改为87654321
set password for 'whb'@'localhost' = password('87654321');
-- 验证密码是否修改成功
select host, user, authentication_string from user where user='whb';
+-----------+------+-------------------------------------------+
| host | user | authentication_string |
+-----------+------+-------------------------------------------+
| localhost | whb | *5D24C4D94238E65A6407DFAB95AA4EA97CA2B199 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

（注：密码加密后的字符串已改变，说明修改成功）

三、权限管理：赋予与回收（核心实战）

新创建的用户默认无任何权限，需通过grant赋予权限；若权限分配错误，可通过revoke回收。

1. MySQL 核心权限列表

权限对应字段作用范围说明
SELECTSelect_priv表、视图允许查询数据
INSERTInsert_priv表允许插入数据
UPDATEUpdate_priv表允许更新数据
DELETEDelete_priv表允许删除数据
CREATECreate_priv数据库、表、视图允许创建数据库 / 表 / 视图
DROPDrop_priv数据库、表允许删除数据库 / 表
ALL PRIVILEGES-任意赋予所有权限（除GRANT OPTION外）
GRANT OPTIONGrant_priv任意允许用户将自身权限赋予其他用户

2. 赋予权限（grant）

语法

grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码'];
  • 权限列表：多个权限用逗号分隔（如select, insert），all privileges表示所有权限。
  • 库: 对象名：
    *.*：所有数据库的所有对象（表、视图等），仅 root 可用。
    test.*：test数据库的所有对象。
    test.account：test数据库的account表。
  • identified by（可选）：若用户已存在，可同时修改密码；若用户不存在，自动创建用户并设置密码。

案例：给whb赋予test库查询权限
root 用户操作（终端 A）：

-- 查看当前数据库
show databases;
-- 切换到test库，查看表
use test;
show tables;
-- 给whb赋予test库所有表的select权限
grant select on test.* to 'whb'@'localhost';
-- 查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| bit_index |
| ccdata_pro |
| innodb_test |
| musicserver |
| myisam_test |
| mysql |
| order_sys |
| performance_schema |
| scott |
| sys |
| test |
| vod_system |
+--------------------+
13 rows in set (0.00 sec)
-- 查看test库的表
+----------------+
| Tables_in_test |
+----------------+
| account |
| student |
| user |
+----------------+
3 rows in set (0.01 sec)
-- 赋予权限
Query OK, 0 rows affected (0.01 sec)

whb用户验证（终端 B）：

-- 登录whb用户后，查看数据库（未授权前仅能看到information_schema）
show databases;
-- 授权后再次查看数据库（可看到test库）
show databases;
-- 切换到test库，查看表
use test;
show tables;
-- 执行查询操作（有权限，成功）
select * from account;
-- 尝试删除数据（无权限，失败）
delete from account;
-- 未授权前查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
-- 授权后查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.01 sec)
-- 查看test库的表
+----------------+
| Tables_in_test |
+----------------+
| account |
| student |
| user |
+----------------+
3 rows in set (0.00 sec)
-- 查询account表（成功）
+----+--------+---------+
| id | name | blance |
+----+--------+---------+
| 2 | 李四 | 321.00 |
| 3 | 王五 | 5432.00 |
| 4 | 赵六 | 543.90 |
| 5 | 赵六 | 543.90 |
+----+--------+---------+
4 rows in set (0.00 sec)
-- 尝试删除数据（失败）
ERROR 1142 (42000): DELETE command denied to user 'whb'@'localhost' for table 'account'

3. 回收权限（revoke）

语法

revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

案例：回收whb的test库所有权限
root 用户操作（终端 A）：

-- 回收whb对test库所有表的所有权限
revoke all on test.* from 'whb'@'localhost';

whb用户验证（终端 B）：

-- 查看数据库（test库消失）
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

4. 查看用户权限

通过show grants可查看指定用户的权限：

-- 查看whb的权限
show grants for 'whb'@'localhost';
-- 查看root的权限
show grants for 'root'@'%';
-- whb的权限（回收后）
+-------------------------------------------+
| Grants for whb@localhost |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'whb'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
-- root的权限
+------------------------------------------------+
| Grants for root@% |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+------------------------------------------------+
1 row in set (0.00 sec)

（注：USAGE表示无实际权限，仅保留登录权限）

5. 权限生效问题

若赋予 / 回收权限后未立即生效，执行以下命令刷新权限：

flush privileges;

四、用户管理最佳实践

  • 遵循最小权限原则：如开发人员仅赋予select, insert, update权限，避免赋予drop, create等高危权限；
  • 限制登录主机：尽量使用localhost或指定 IP（如192.168.1.100），避免使用%（允许任意主机登录）；
  • 强密码策略：要求用户密码包含字母、数字、特殊字符，定期更换；
  • 定期审计权限：通过select user, host, * from mysql.user;查看用户权限，回收冗余权限；
  • 避免共享账号：为每个开发者 / 运维人员创建独立账号，便于追溯操作日志。
posted @ 2025-09-08 14:53  yfceshi  阅读(16)  评论(0)    收藏  举报