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

MySQL数据库权限管理涉及为用户分配对数据库对象的访问权，包括SELECT、INSERT、UPDATE等操作。

MySQL数据库权限管理是确保数据安全和控制用户对数据库资源访问的重要机制，它包括全局权限、数据库级别权限和对象级别权限，这些权限分别作用于整个MySQL实例、特定数据库以及特定的数据库对象如表或视图，以下是对这些权限的详细介绍：

1、MySQL 权限介绍

权限验证过程：MySQL通过user表的Host, User, Password字段进行身份验证，然后按照user, db, tables_priv, columns_priv的顺序分配权限。

权限级别：分为全局性管理权限、数据库级别权限和数据库对象级别权限，存储在mysql库的多个系统表中。

2、MySQL 权限详解

All/All Privileges：代表全局或者全数据库对象级别的所有权限。

Alter：允许修改表结构，但需要create和insert权限配合。

Alter routine：允许修改或删除存储过程和函数。

Create：允许创建新的数据库和表。

Create routine：允许创建存储过程和函数。

Create tablespace：允许创建、修改、删除表空间和日志组。

Create temporary tables：允许创建临时表。

Create user：允许创建、修改、删除用户。

Create view：允许创建视图。

Delete：允许删除行数据。

Drop：允许删除数据库、表、视图，包括truncate table命令。

Event：允许查询、创建、修改、删除MySQL事件。

Execute：允许执行存储过程和函数。

File：允许在MySQL可以访问的目录进行读写磁盘文件操作。

Grant option：允许授予或收回其他用户的权限。

Index：允许创建和删除索引。

Insert：允许在表中插入数据。

Lock tables：允许锁定拥有select权限的表。

Process：允许查看MySQL中的进程信息。

Reference：允许创建外键。

Reload：允许执行flush命令，重新加载权限表到系统内存中。

Replication client：允许执行show master status等命令。

Replication slave：允许slave主机连接master以便建立主从复制关系。

Select：允许查看表中的数据。

Show databases：允许查看所有数据库名。

Show view：允许查看视图创建语句。

Shutdown：允许关闭数据库实例。

Super：允许执行一系列数据库管理命令。

Trigger：允许创建、删除、执行显示触发器的权限。

Update：允许修改表中的数据。

Usage：创建一个用户后的默认权限，本身代表无权限。

3、用户权限管理

创建用户及授权：可以通过CREATE USER/GRANT命令创建用户并授权，也可以通过直接操作MySQL系统权限表来创建。

示例：创建用户dayi123并授予本地登录所有库所有权限，可以使用以下命令：

     mysql> create user 'dayi123'@'localhost' identified by 'dayi123';
     mysql> grant all privileges on *.* to 'dayi123'@'localhost' with grant option;

为dayi用户通过192.168.0.0段IP地址登录的增删改查新库web的权限，可以使用以下命令：

     mysql> grant select,insert,update,delete,create,drop on web.* to 'dayi'@'192.168.%.%' identified by 'dayi123';

给root用户授权可以通过192.168.0.0段地址登录，可以使用以下命令：

     mysql> grant all on *.* to root@'192.168.%.%' identified by 'root_password';

4、权限管理的注意事项

最小权限原则：只授予用户执行任务所需的最小权限，例如如果用户只需要查询数据，那么只应该授予SELECT权限。

GRANT OPTION：具有此权限的用户可以将权限授予其他用户，应谨慎使用此权限，防止权限滥用。

密码安全：确保使用强密码，并定期更换用户密码以提高安全性。

审计和日志：通过启用MySQL的审计功能或日志记录，监控用户的行为，确保系统安全。

FAQs

1、如何查看MySQL中的所有用户？

答：可以通过执行以下SQL命令来查看MySQL中的所有用户：

   select user,host from mysql.user;

这条命令会列出所有用户及其对应的主机信息。

2、如何为用户授予特定数据库的特定权限？

答：可以通过GRANT语句为用户授予特定数据库的特定权限，要为用户alice授予对testdb数据库的SELECT和INSERT权限，可以使用以下命令：

   GRANT SELECT, INSERT ON testdb.* TO 'alice'@'localhost';

这条命令将使alice用户在连接到localhost时能够对testdb数据库执行SELECT和INSERT操作。

权限	说明
SELECT	允许用户查询数据库中的数据。
INSERT	允许用户向数据库中插入新的数据。
UPDATE	允许用户修改数据库中的数据。
DELETE	允许用户从数据库中删除数据。
CREATE	允许用户创建新的数据库或表。
DROP	允许用户删除数据库或表。
INDEX	允许用户创建或删除索引。
ALTER	允许用户修改表结构。
CREATE TEMPORARY TABLES	允许用户在会话期间创建临时表。
LOCK TABLES	允许用户锁定表以防止其他用户修改它们。
REFERENCES	允许用户创建外键约束。
CREATE VIEW	允许用户创建视图。
SHOW VIEW	允许用户查看视图的定义。
CREATE ROUTINE	允许用户创建存储过程、函数或触发器。
ALTER ROUTINE	允许用户修改存储过程、函数或触发器。
CREATE USER	允许用户创建新用户。
DROP USER	允许用户删除用户。
GRANT OPTION	允许用户将权限授予其他用户。
EVENT	允许用户创建、修改或删除事件调度器的事件。
TRIGGER	允许用户创建或删除触发器。