如何正确设置和管理MySQL数据库的权限？

MySQL数据库权限管理详解

MySQL数据库权限

MySQL数据库的权限管理是确保数据安全和完整性的关键，它允许数据库管理员对用户访问和操作数据库的能力进行细粒度的控制，MySQL中的权限可以分为全局权限、数据库级权限和表级权限，这些权限分别适用于不同的操作范围和对象。

权限级别	作用范围
全局权限	作用于整个MySQL实例
数据库级权限	作用于指定的某个数据库
表级权限	作用于指定的某个表或视图

MySQL权限类型

MySQL支持多种类型的权限，每种权限对应不同的操作能力，以下是一些常见的MySQL权限及其说明：

权限名称	权限说明
ALL PRIVILEGES	所有全局、数据库和表级别的权限
ALTER	修改表结构，需配合CREATE和INSERT权限
ALTER ROUTINE	修改或删除存储过程和函数
CREATE	创建数据库和表
CREATE ROUTINE	创建存储过程和函数
CREATE TABLESPACE	创建、修改、删除表空间和日志文件
CREATE TEMPORARY TABLES	创建临时表
CREATE USER	创建、修改、删除用户账号
CREATE VIEW	创建视图
DELETE	删除行数据
DROP	删除数据库、表、视图，包括TRUNCATE TABLE命令
EXECUTE	执行存储过程和函数
FILE	在服务器上读写文件，如LOAD DATA INFILE
GRANT OPTION	授权或收回其他用户的权限
INDEX	创建和删除索引
INSERT	插入数据行
LOCK TABLES	锁定表以防止其他会话读取或写入
PROCESS	查看MySQL进程信息，如SHOW PROCESSLIST
REFERENCES	创建外键（5.7.6版本之后引入）
RELOAD	重新加载权限表到内存中
REPLICATION CLIENT	查看复制状态信息
REPLICATION SLAVE	允许从服务器连接主服务器以建立复制关系
SELECT	查询表中的数据
SHOW DATABASES	查看所有数据库名称
SHOW VIEW	查看视图定义
SHUTDOWN	关闭MySQL服务
SUPER	执行高级数据库管理命令，如KILL、CHANGE MASTER TO等
TRIGGER	创建、删除、显示触发器
UPDATE	更新表中的数据
USAGE	默认权限，无实际权限

MySQL权限存储

MySQL的权限信息存储在mysql系统数据库的几张表中，这些表在MySQL实例启动时加载到内存中，主要的权限表及其作用如下：

表名	作用
user	存放用户账户信息及全局级（所有数据库）权限
db	存放数据库级权限，决定哪些用户可以访问哪些数据库
tables_priv	存放表级权限，决定哪些用户可以访问哪些表
columns_priv	存放列级权限，决定哪些用户可以访问哪些列
procs_priv	存放存储过程和函数的权限

字段user, password, authentication_string, db, table_name是大小写敏感的，而字段host, column_name, routine_name则不区分大小写。

用户权限管理

MySQL用户权限的管理主要包括创建用户、授权、撤销权限以及查看用户权限等操作，以下是相关的SQL语句示例：

1、创建用户：

“`sql

CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;

“`

创建一个名为dayi123的用户，允许其从本地登录：

“`sql

CREATE USER ‘dayi123’@’localhost’ IDENTIFIED BY ‘dayi123’;

“`

2、授予权限：

“`sql

GRANT privilege ON database.table TO ‘username’@’host’;

“`

授予dayi123用户对所有库的所有表的所有权限：

“`sql

GRANT ALL PRIVILEGES ON *.* TO ‘dayi123’@’localhost’ WITH GRANT OPTION;

“`

3、撤销权限：

“`sql

REVOKE privilege ON database.table FROM ‘username’@’host’;

“`

撤销dayi123用户的INSERT权限：

“`sql

REVOKE INSERT ON testdb.* FROM ‘dayi123’@’localhost’;

“`

4、查看用户权限：

“`sql

SHOW GRANTS FOR ‘username’@’host’;

“`

查看root用户的权限：

“`sql

SHOW GRANTS FOR ‘root’@’localhost’;

“`

常见问题解答（FAQs）

Q1: 如何查看当前MySQL服务器中的所有用户？

可以使用以下SQL语句查看所有用户：

SELECT user, host FROM mysql.user;

该命令将列出mysql系统数据库中user表的所有记录，显示每个用户及其允许的主机。

Q2: 如何撤销一个用户的所有权限？

要撤销一个用户的所有权限，可以使用以下SQL语句：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

撤销dayi123用户的所有权限：

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