mysql数据库的用户与权限

作者

MySQL 用户与权限完全指南

本文档专门讲 用户（User） 和 权限（Privilege）：如何创建用户、授权、收权、改密码、删除用户，以及权限级别和常见用法。每一步都配有详细说明和大量示例，适合零基础新手跟着做。

目录

  1. 为什么需要用户与权限
  2. 用户与主机（user@host）
  3. 操作前：用 root 登录
  4. 创建用户
  5. 查看用户
  6. 授权（GRANT）
  7. 查看权限（SHOW GRANTS）
  8. 收回权限（REVOKE）
  9. 修改密码
  10. 删除用户
  11. 权限级别与常用权限说明
  12. 使权限生效：FLUSH PRIVILEGES
  13. 安全建议与常见错误
  14. 综合示例与速查表

1. 为什么需要用户与权限

1.1 多用户与安全

  • 一台 MySQL 服务器可能给多个应用或多个人用，若大家都用同一个 root 账号，谁都能改库、删表、看所有数据，风险很大。
  • 通过创建不同用户，并给每个用户只分配需要的权限（例如只能查某几个库、不能删表），可以做到：
    • 最小权限：每人/每个应用只用得到必要的权限。
    • 责任清晰：出问题能追溯到是哪个账号。
    • 安全：即使某个账号泄露，影响范围也有限。

1.2 用户与权限的关系

  • 用户：用来登录 MySQL 的“账号”，由 用户名 和 允许登录的主机 一起决定（见下一节）。
  • 权限：规定该用户能做什么，例如：能否 SELECT（查）、INSERT（插）、UPDATE（改）、DELETE（删）、CREATE（建表）等。
    权限可以按全局、数据库、表、列等不同级别来给。

下面从“创建用户 → 授权 → 查看/收回权限 → 改密码 → 删用户”的顺序说明，并配合示例。

2. 用户与主机（user@host）

2.1 用户 = 用户名 + 主机

在 MySQL 里，一个“用户”是由 用户名 和 允许连接的主机 共同决定的，写成：‘用户名’@’主机’。

  • 用户名：登录时用的名字，如 root、app_user、readonly。
  • 主机：规定该用户从哪台机器可以连上来，例如：
    • ‘localhost’ 或 ‘127.0.0.1’：只允许从本机连接。
    • ‘%’：允许从任意主机连接（生产环境慎用）。
    • ‘192.168.1.100’：只允许从该 IP 连接。
    • ‘192.168.1.%’：允许从 192.168.1.0～255 的网段连接。

所以：‘xiaoming’@’localhost’ 和 ‘xiaoming’@’%’ 是两个不同的用户，可以有不同的密码和权限。

2.2 示例理解

  • ‘root’@’localhost’：本机用 root 登录。
  • ‘app’@’192.168.1.10’：只允许从 192.168.1.10 这台机器用 app 登录。
  • ‘readonly’@’%’：任意机器都可以用 readonly 登录（一般只给只读权限）。

3. 操作前：用 root 登录

创建用户、授权、删用户等操作通常需要高权限，所以要先用 root（或具有相应权限的管理员账号）登录：

mysql -u root -p

输入 root 密码后，在 mysql> 下执行后面的 SQL。
以下示例都假设你已经用 root 连上 MySQL。

4. 创建用户

4.1 基本语法

CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
  • 用户名、主机 用单引号；密码 用单引号。
  • 密码会按 MySQL 默认方式加密存储（如 caching_sha2_password、mysql_native_password，取决于版本和配置）。

4.2 示例：只允许本机登录的用户

CREATE USER 'xiaoming'@'localhost' IDENTIFIED BY 'MyPass123';

表示：用户名为 xiaoming，只允许从本机连接，密码为 MyPass123。
创建后该用户还没有任何权限（除登录外），需要单独 GRANT 授权。

4.3 示例：允许从任意主机登录（慎用）

CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass456';

‘%’ 表示任意主机。生产环境尽量不用 ‘%’，改为具体 IP 或网段更安全。

4.4 示例：只允许从指定 IP 登录

CREATE USER 'web'@'192.168.1.100' IDENTIFIED BY 'WebPass789';

只有从 192.168.1.100 连接时，才能用 web 这个用户名和对应密码登录。

4.5 示例：同一用户名、不同主机（两个用户）

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'LocalPass';
CREATE USER 'admin'@'192.168.1.1' IDENTIFIED BY 'RemotePass';

这样 ‘admin’@’localhost’ 和 ‘admin’@’192.168.1.1’ 是两个独立用户，密码和权限都可以不同。

4.6 若用户已存在

若 ‘用户名’@’主机’ 已存在，再执行 CREATE USER 会报错。可以：

  • 先 DROP USER 再 CREATE USER，或
  • 用 CREATE USER … IDENTIFIED BY ‘新密码’ 相当于只改密码（若只打算改密码，更规范的是用 ALTER USER，见后文）。

5. 查看用户

5.1 查看有哪些用户（mysql.user 表）

用户信息存在系统库 mysql 的 user 表里，可以用 SELECT 查看（不要随便改这张表，用 SQL 命令操作用户更安全）：

SELECT user, host FROM mysql.user;

会列出所有 用户名 和 主机 的组合。
例如能看到 root@localhost、xiaoming@localhost、app_user@% 等。

5.2 只看用户名或主机

SELECT DISTINCT user FROM mysql.user;
SELECT user, host FROM mysql.user WHERE user = 'xiaoming';

6. 授权（GRANT）

6.1 基本语法

GRANT 权限1, 权限2, ... ON 库名.表名 TO '用户名'@'主机';
  • 权限：如 SELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES 等（见后文）。
  • ON 库名.表名：权限作用范围，可以是 *.*（所有库表）、库名.*（某库下所有表）、库名.表名（某张表）。
  • TO ‘用户名’@’主机’：和 CREATE USER 时一致，主机必须写对，否则授权不生效。

授权后通常要执行 FLUSH PRIVILEGES;（见后文）使权限生效（部分版本在 GRANT 后会自动 flush）。

6.2 示例：给用户某库的“查”权限（只读）

GRANT SELECT ON school.* TO 'xiaoming'@'localhost';

表示：xiaoming 从本机登录后，对 school 库下所有表只有 SELECT（查）权限，不能 INSERT/UPDATE/DELETE。

6.3 示例：给用户某库的增删改查

GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO 'app_user'@'%';

对 school 库下所有表可以查、插、改、删，但不能建表、删表等。

6.4 示例：给用户某库的“全部权限”

GRANT ALL PRIVILEGES ON school.* TO 'admin'@'localhost';

ALL PRIVILEGES 表示该库下的所有常见权限（SELECT、INSERT、UPDATE、DELETE、CREATE、DROP 等），一般不包括“给别人授权”的 GRANT OPTION，除非单独加。

6.5 示例：给用户所有库的所有权限（类似 root，慎用）

GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' WITH GRANT OPTION;
  • *.* 表示所有库、所有表。
  • WITH GRANT OPTION 表示该用户还可以把权限再授予别人。
    这种权限很大，只给极少数管理员用。

6.6 示例：只给某张表的权限

GRANT SELECT, INSERT ON school.students TO 'xiaoming'@'localhost';

只对 school.students 这张表有查和插，其他表没有权限。

6.7 授权后执行 FLUSH PRIVILEGES

执行完 GRANT 后，建议执行：

FLUSH PRIVILEGES;

使权限表重新加载，新权限立即生效（有的版本 GRANT 后自动 flush，执行一次也无妨）。

7. 查看权限（SHOW GRANTS）

7.1 查看当前登录用户的权限

SHOW GRANTS;

或：

SHOW GRANTS FOR CURRENT_USER();

7.2 查看指定用户的权限

SHOW GRANTS FOR 'xiaoming'@'localhost';

会列出该用户被授予的权限，例如：

GRANT USAGE ON *.* TO `xiaoming`@`localhost`
GRANT SELECT ON `school`.* TO `xiaoming`@`localhost`

表示：全局只有 USAGE（仅能登录），对 school 库有 SELECT。

8. 收回权限（REVOKE）

8.1 基本语法

REVOKE 权限1, 权限2, ... ON 库名.表名 FROM '用户名'@'主机';

REVOKE 的“权限、ON 范围”要和之前 GRANT 的对应，否则可能收不干净。

8.2 示例：收回某库的 SELECT

REVOKE SELECT ON school.* FROM 'xiaoming'@'localhost';

8.3 示例：收回某库所有权限

REVOKE ALL PRIVILEGES ON school.* FROM 'app_user'@'%';

8.4 示例：收回所有库的所有权限（包括 GRANT OPTION）

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'super'@'localhost';

执行后建议再执行 FLUSH PRIVILEGES;。

9. 修改密码

9.1 用 ALTER USER（推荐，MySQL 5.7+）

ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';

示例：

ALTER USER 'xiaoming'@'localhost' IDENTIFIED BY 'NewPass123';

修改后该用户用新密码登录即可，无需 FLUSH PRIVILEGES 来改密码（权限变更才需要 flush）。

9.2 用 SET PASSWORD（传统写法）

SET PASSWORD FOR '用户名'@'主机' = '新密码';

或使用 PASSWORD() 加密（MySQL 5.7 及以前，8.0 已废弃 PASSWORD()）：

SET PASSWORD FOR 'xiaoming'@'localhost' = PASSWORD('NewPass123');

8.0 下一般直接用明文：SET PASSWORD FOR ‘xiaoming’@’localhost’ = ‘NewPass123’; 或改用 ALTER USER。

9.3 修改当前登录用户的密码

ALTER USER USER() IDENTIFIED BY '新密码';

10. 删除用户

10.1 基本语法

DROP USER '用户名'@'主机';

示例：

DROP USER 'xiaoming'@'localhost';

删除后，该用户无法再登录，其权限也随之消失。不会删除该用户创建过的数据库或表（数据仍在，只是谁有权限访问由其他用户决定）。

10.2 若用户不存在

若用户不存在会报错，可加 IF EXISTS（MySQL 5.7+）：

DROP USER IF EXISTS 'xiaoming'@'localhost';

10.3 一次删多个用户

DROP USER IF EXISTS 'user1'@'localhost', 'user2'@'%';

10.4 注意

不要对 ‘root’@’localhost’ 等系统必需账号执行 DROP USER，否则可能无法再以 root 登录。

11. 权限级别与常用权限说明

11.1 权限作用范围（ON 的写法）

写法 含义 示例
. 所有库、所有表 GRANT … ON . TO …
库名.* 某库下所有表 GRANT … ON school.* TO …
库名.表名 某张表 GRANT … ON school.students TO …

11.2 常用权限列表

权限 含义 典型用途
SELECT 查询数据 只读账号
INSERT 插入数据 可写账号
UPDATE 更新数据 可写账号
DELETE 删除数据 可写账号
CREATE 建表、建库等 开发/管理
DROP 删表、删库等 管理（慎授）
ALTER 改表结构 管理/迁移
INDEX 建/删索引 开发/管理
ALL PRIVILEGES 除 GRANT 外全部 库/表级全权
GRANT OPTION 把权限授予他人 仅管理员

USAGE：仅能连接，没有其他权限，新建用户默认就是 USAGE。

11.3 示例：只读用户

只给 SELECT，不给 INSERT/UPDATE/DELETE：

CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'ReadOnly123';
GRANT SELECT ON school.* TO 'readonly'@'localhost';
FLUSH PRIVILEGES;

11.4 示例：应用用户（可读可写，不可建表删表）

CREATE USER 'app'@'192.168.1.10' IDENTIFIED BY 'AppPass';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'192.168.1.10';
FLUSH PRIVILEGES;

12. 使权限生效：FLUSH PRIVILEGES

12.1 何时需要执行

  • 执行 GRANT 或 REVOKE 后，建议执行一次 FLUSH PRIVILEGES;，让权限表重新加载。
  • 部分版本在 GRANT/REVOKE 后会自动 flush，但多执行一次没有副作用。
  • 若直接改动了 mysql.user 等表（不推荐），必须执行 FLUSH PRIVILEGES; 才会生效。

12.2 命令

FLUSH PRIVILEGES;

13. 安全建议与常见错误

13.1 安全建议

  • root 设强密码，且不要用 root 跑应用，单独建应用账号并只给必要权限。
  • 尽量不用 ‘用户’@’%’，改为具体 IP 或网段。
  • 遵循最小权限：只给该用户需要的库、表、权限。
  • 密码避免过于简单，并定期更换。
  • 生产环境慎用 GRANT ALL ON . 和 WITH GRANT OPTION。

13.2 常见错误

  • 主机写错：CREATE USER 和 GRANT 的 ‘用户’@’主机’ 必须一致，例如创建的是 ‘xiaoming’@’localhost’，授权也要 TO ‘xiaoming’@’localhost’，写 ‘xiaoming’@’%’ 是另一个用户。
  • 忘记 FLUSH PRIVILEGES：授权或收权后没执行 FLUSH，可能不生效。
  • 授权范围写错：**ON school.* 表示 school 库下所有表，不要写成 ON school**（无效）。
  • 删错用户：DROP USER 前用 SHOW GRANTS FOR ‘用户’@’主机’; 确认一次。

14. 综合示例与速查表

14.1 综合示例（用 root 执行）

-- 1. 创建用户（本机）
CREATE USER 'xiaoming'@'localhost' IDENTIFIED BY 'Pass123';

-- 2. 授权：school 库只读
GRANT SELECT ON school.* TO 'xiaoming'@'localhost';
FLUSH PRIVILEGES;

-- 3. 查看权限
SHOW GRANTS FOR 'xiaoming'@'localhost';

-- 4. 创建应用用户（允许从 192.168.1.10 连接）
CREATE USER 'app'@'192.168.1.10' IDENTIFIED BY 'AppPass456';
GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO 'app'@'192.168.1.10';
FLUSH PRIVILEGES;

-- 5. 修改密码
ALTER USER 'xiaoming'@'localhost' IDENTIFIED BY 'NewPass123';

-- 6. 收回 school 的 SELECT
REVOKE SELECT ON school.* FROM 'xiaoming'@'localhost';
FLUSH PRIVILEGES;

-- 7. 删除用户（演示用，慎用）
-- DROP USER IF EXISTS 'xiaoming'@'localhost', 'app'@'192.168.1.10';

14.2 用户与权限速查表

操作 命令
创建用户 CREATE USER '用户'@'主机' IDENTIFIED BY '密码';
查看用户 SELECT user, host FROM mysql.user;
授权 GRANT 权限 ON 库.表 TO '用户'@'主机';
查看权限 SHOW GRANTS FOR '用户'@'主机';
收权 REVOKE 权限 ON 库.表 FROM '用户'@'主机';
改密码 ALTER USER '用户'@'主机' IDENTIFIED BY '新密码';
删用户 DROP USER '用户'@'主机'; 或 DROP USER IF EXISTS '用户'@'主机';
刷新权限 FLUSH PRIVILEGES;

常用权限：SELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES。
ON 范围：.（所有）、库名.*（某库）、库名.表名（某表）。
主机：localhost（本机）、%（任意，慎用）、具体 IP。

把“创建用户 → 授权 → 查看/收回权限 → 改密码 → 删除用户”和“权限级别、FLUSH PRIVILEGES”过一遍后，你就能在本地或测试环境里安全地分配账号和权限。生产环境务必遵循最小权限、强密码、限制主机。遇到问题可先 SHOW GRANTS FOR ‘用户’@’主机’; 确认权限是否给对。

访问数: 161

发表评论