MySQL用户权限管理完全指南：从查看到授权的详细教程

MySQL用户权限管理完全指南：从查看到授权的详细教程

MySQL的用户权限管理是数据库安全的重要组成部分。本文将详细介绍MySQL的权限级别、查看用户权限的方法，以及如何进行权限的授予和回收。通过本文，读者将能够掌握MySQL用户权限管理的核心知识，确保数据库的安全性和可用性。

一、MySQL权限级别介绍

MySQL的权限可以分为以下几个级别：

• 全局：可以管理整个MySQL服务器
• 库：可以管理指定的数据库
• 表：可以管理指定数据库的指定表
• 字段：可以管理指定数据库的指定表的指定字段

这些权限信息存储在mysql库的以下系统表中：

• user
• db
• tables_priv
• columns_priv
• procs_priv

当MySQL实例启动时，这些权限信息会被加载到内存中。

二、查看用户权限

查看所有用户

要查看所有用户的用户名和主机信息，可以使用以下SQL命令：

SELECT user, host FROM mysql.user;

查看单个用户详细信息

要查看单个用户的详细信息，可以使用以下SQL命令：

SELECT * FROM mysql.user WHERE user='root'\G

\G相当于;，但会使每个字段打印到单独的行，便于阅读。

用户信息

• Host: %表示允许所有主机连接
• User: 用户名
• authentication_string: 用户密码（MD5加密）

授权信息

• Select_priv: 是否可以使用SELECT命令选择数据
• Insert_priv: 是否可以使用INSERT命令插入数据
• Update_priv: 是否可以使用UPDATE命令修改数据
• Delete_priv: 是否可以使用DELETE命令删除数据
• Create_priv: 是否可以创建新的数据库和表
• Drop_priv: 是否可以删除现有数据库和表
• Reload_priv: 是否可以执行刷新和重新加载MySQL缓存的命令
• Shutdown_priv: 是否可以关闭MySQL服务器
• Process_priv: 是否可以查看进程
• File_priv: 是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
• Grant_priv: 是否可以将权限授予其他用户
• References_priv: 目前没有实际作用
• Index_priv: 是否可以创建和删除表索引
• Alter_priv: 是否可以重命名和修改表结构
• Show_db_priv: 是否可以查看所有数据库的名字
• Super_priv: 是否可以执行某些强大的管理功能
• Create_tmp_table_priv: 是否可以创建临时表
• Lock_tables_priv: 是否可以使用LOCK命令锁定表
• Execute_priv: 是否可以执行存储过程
• Repl_slave_priv: 是否可以读取二进制日志文件
• Repl_client_priv: 是否可以确定复制从服务器和主服务器的位置
• Create_view_priv: 是否可以创建视图
• Show_view_priv: 是否可以查看视图
• Create_routine_priv: 是否可以更改或放弃存储过程和函数
• Alter_routine_priv: 是否可以修改或删除存储函数及函数
• Create_user_priv: 是否可以执行CREATE USER命令
• Event_priv: 是否可以创建、修改和删除事件
• Trigger_priv: 是否可以创建和删除触发器
• Create_tablespace_priv: 是否可以创建表的空间

三、授权

每次更新权限后，需要刷新权限：

FLUSH PRIVILEGES;

授权格式

GRANT
 [权限]
ON [库.表]
TO [用户名]@[IP]
IDENTIFIED BY [密码]
# WITH GRANT OPTION;

GRANT命令说明

• ALL PRIVILEGES: 表示所有权限
• ON: 指定权限针对的库和表
• *.*: 表示所有数据库和所有表
• TO: 表示将权限赋予某个用户
• @: 前面表示用户，后面接限制的主机
• IDENTIFIED BY: 指定用户的登录密码
• WITH GRANT OPTION: 表示该用户可以将自己拥有的权限授权给别人

全局授权

将root用户的主机限制改为任意主机：

UPDATE mysql.user SET Host='%' WHERE User='root';
FLUSH PRIVILEGES;

也可以新建一个用户并赋予全部权限：

GRANT
 ALL PRIVILEGES
ON *.*
TO admin@'175.155.59.133'
IDENTIFIED BY 'admin';

单个数据库授权

只给175.155.59.133这个IP赋予ctrip数据库的查询权限：

GRANT
 select
ON ctrip.*
TO ctrip@'175.155.59.133'
IDENTIFIED BY 'ctrip';

单个数据库单个表授权

只给175.155.59.133这个IP赋予ctrip数据库的t_plane表的查询权限：

GRANT
 select
ON ctrip.t_plane
TO ctrip@'175.155.59.133'
IDENTIFIED BY 'ctrip';

单个数据库单个表某些字段授权

只给175.155.59.133这个IP赋予ctrip数据库的t_plane表的id和EN字段的查询权限：

GRANT
 select(id,EN)
ON ctrip.t_plane
TO ctrip@'175.155.59.133'
IDENTIFIED BY 'ctrip';

四、收回权限、删除用户

收回权限

格式：

REVOKE
 [权限]
ON [库.表]
FROM [用户名]@[IP];

操作示例：

REVOKE
 select(id,EN)
ON ctrip.t_plane
FROM ctrip@'175.155.59.133';

删除用户

格式：

DROP USER [用户名]@[IP];

操作示例：

DROP USER ctrip@'175.155.59.133';

以上就是MySQL用户权限管理的详细教程，通过本文，读者可以全面掌握MySQL权限管理的核心知识，确保数据库的安全性和可用性。