mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

17.2 权限授予：GRANT语句

创建用户账户后，默认情况下该用户几乎没有任何操作权限——他们甚至无法查看任何数据库。要让用户能够访问和操作数据库，就需要使用 GRANT 语句来授予权限。权限决定了用户能做什么、在哪里做。

一、什么是权限？

在 MySQL 中，权限定义了用户可以对数据库对象（如表、视图、存储过程等）执行的操作。权限可以按范围分为几个级别：

全局权限：适用于 MySQL 服务器上的所有数据库，例如 CREATE USER、SHUTDOWN。
数据库权限：适用于特定数据库中的所有对象，例如对 mydb 的 SELECT、INSERT 权限。
表权限：适用于特定表中的所有列，例如对 mydb.users 的 UPDATE 权限。
列权限：适用于特定表中的特定列（很少使用）。
存储过程/函数权限：适用于执行存储过程和函数。

二、GRANT 语句的基本语法

GRANT 权限1 [, 权限2] ...
ON 权限级别
TO '用户名'@'主机名'
[IDENTIFIED BY '密码']   -- 可选，同时创建用户或修改密码（不推荐）
[WITH GRANT OPTION];

权限级别

权限级别通过 ON 子句指定：

权限级别	语法示例	说明
全局	ON .	所有数据库所有对象
数据库	ON 数据库名.*	指定数据库的所有对象
表	ON 数据库名.表名	指定数据库中的特定表
列	ON 数据库名.表名(列名)	特定表的特定列（需指定列名）
存储过程	ON PROCEDURE 数据库名.过程名	存储过程
函数	ON FUNCTION 数据库名.函数名	存储函数

常用权限列表

权限	说明
ALL PRIVILEGES 或 ALL	授予指定级别所有可用的权限（不包括 GRANT OPTION ）
SELECT	查询数据
INSERT	插入数据
UPDATE	更新数据
DELETE	删除数据
CREATE	创建数据库或表
DROP	删除数据库或表
ALTER	修改表结构
INDEX	创建和删除索引
REFERENCES	创建外键约束
CREATE VIEW	创建视图
SHOW VIEW	查看视图定义
CREATE ROUTINE	创建存储过程和函数
ALTER ROUTINE	修改或删除存储过程和函数
EXECUTE	执行存储过程和函数
TRIGGER	创建和删除触发器
CREATE USER	创建、修改、删除用户
PROCESS	查看所有进程
RELOAD	刷新日志、表缓存等
REPLICATION CLIENT	可以查询主从服务器状态
REPLICATION SLAVE	复制所需的权限
SHUTDOWN	关闭 MySQL 服务
SUPER	超级权限，可以执行大多数管理操作

基础示例

假设我们有一个用户 'zhangsan'@'localhost'，我们需要授予他一些权限。

1. 授予查询权限（全局级别）

授予对所有数据库的所有表的 SELECT 权限：

GRANT SELECT ON *.* TO 'zhangsan'@'localhost';

2. 授予特定数据库的所有权限

授予对 school 数据库的所有操作权限（但不包括对其他数据库的操作）：

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

3. 授予表级别的特定权限

授予对 school.students 表的 INSERT、UPDATE、DELETE 权限：

GRANT INSERT, UPDATE, DELETE ON school.students TO 'zhangsan'@'localhost';

4. 授予列级别权限（较少用）

授予对 school.students 表的 name 列的 UPDATE 权限：

GRANT UPDATE(name) ON school.students TO 'zhangsan'@'localhost';

5. 授予创建用户的权限（全局）

GRANT CREATE USER ON *.* TO 'zhangsan'@'localhost';

6. 授予并允许用户将权限转授给其他人

使用 WITH GRANT OPTION：

GRANT SELECT ON school.* TO 'zhangsan'@'localhost' WITH GRANT OPTION;

这样 zhangsan 就可以把自己拥有的 school 库的 SELECT 权限授予其他用户。

三、在 GRANT 中同时修改密码（不推荐）

旧版 MySQL 中可以在 GRANT 中使用 IDENTIFIED BY 来创建用户或修改密码，但这种方式在 MySQL 8.0 中已不再推荐（且许多版本已移除）。应使用 CREATE USER 和 ALTER USER 管理用户，用 GRANT 只授予权限。

查看用户权限

查看当前用户自己的权限

SHOW GRANTS;

查看指定用户的权限

SHOW GRANTS FOR 'zhangsan'@'localhost';

注意事项

权限生效时机：GRANT 语句执行后立即生效，无需重启 MySQL。
权限叠加：多次 GRANT 是累加的，不会覆盖之前授予的权限。
最小权限原则：只授予用户完成工作所必需的最小权限，避免授予过多权限带来的安全风险。
主机名要完整：授权时指定的 '用户名'@'主机名' 必须与创建用户时一致，包括主机名部分。
ALL PRIVILEGES 不包括 GRANT OPTION：需要单独用 WITH GRANT OPTION 赋予。
不要轻易授予 SUPER、PROCESS 等管理权限，这些权限可能被滥用来干扰服务器或窃取信息。
撤销权限：使用 REVOKE 语句（下一节讲解）。

四、综合示例

假设我们有一个新的应用账号 'app_user'@'192.168.1.%'，需要连接数据库 appdb，并对所有表执行 SELECT、INSERT、UPDATE、DELETE 操作，同时能执行存储过程。

-- 创建用户（如果尚未创建）
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppUser2025!';

-- 授予数据库级别的基本增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_user'@'192.168.1.%';

-- 授予执行存储过程的权限
GRANT EXECUTE ON appdb.* TO 'app_user'@'192.168.1.%';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

小结

GRANT 语句用于向用户授予操作数据库的权限。
权限范围可以是全局、数据库、表、列或程序级别。
根据实际需求授予最小必要权限，遵循安全原则。
使用 WITH GRANT OPTION 允许用户转授自己拥有的权限。
通过 SHOW GRANTS 查看用户权限。