【MySQL 保姆级教学】用户管理和数据库权限（16）

数据库账户管理是指对数据库用户进行创建、修改和删除等操作，以控制用户对数据库的访问权限。通过账户管理，可以设置用户名、密码、主机地址等信息，确保数据库的安全性和可控性。例如，使用 CREATE USER 创建用户，ALTER USER 修改用户密码。数据库权限是指数据库为用户授予的操作权限，包括对数据库、表或字段的访问和操作权限，例如 SELECT（查询）、INSERT（插入）、UPDATE（更新）和 DELETE（删除）。权限的分配通过 GRANT 命令进行，撤销权限则使用 REVOKE 命令。合理的权限设置可以限制用户操作，保护数据的安全和完整性。

💬 欢迎讨论：如对文章内容有疑问或见解，欢迎在评论区留言，我需要您的帮助！

👍 点赞、收藏与分享：如果这篇文章对您有所帮助，请不吝点赞、收藏或分享，谢谢您的支持！

🚀 传播技术之美：期待您将这篇文章推荐给更多对需要学习数据库用户管理和数据库权限、低代码开发感兴趣的朋友，让我们共同学习、成长！

1. 用户管理

1.1 什么是用户管理？

一般，下载好MySQL后直接使用的是root账户，root具有对数据库操作的所有权限。

用户管理 指的是对数据库用户的创建、删除、授权、修改权限以及密码管理等一系列操作。这些管理操作用于控制谁可以访问数据库、可以执行哪些操作，确保数据库的安全性和灵活性。

在MySQL中可以创建多个用户：

如果我们只能使用root用户，这样存在安全隐患。这时，就需要使用MySQL的用户管理。

在命令行中可以使用命令来查询当前使用的用户：

select user();

1.2 用户信息

MySQL以表结构的形式进行管理用户。安装MySQL后，默认有一个mysqll数据库
命令：show databases;

然后使用mysql数据库，查询user表：
命令：

use mysql;# 查询mysql数据库中的表
show tables;# 查询user表
select * from user\G#\G 将查询结果按 纵向显示 而不是默认的 横向表格显示。
#这对于结果数据内容较多、列较多时，能够使查询结果更易于阅读。

可以值查看用户的账号、登录主机和密码

select User, Host, authentication_string from user;

字段解释：
user：用户名
host：表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆
authentication_string： 用户密码通过password函数加密后的
*_priv：用户拥有的权限

1.3 创建用户

可以直接向user表中插入数据,但是这样不太符合用户的创建，创建用户的命令:

create user 'user_name'@'登录主机/ip' identified by 'user_name';

登录主机必须填写；创建的密码会被加密，在user表中的密码是经过加密后的密码。

示例：

1. 创建一个可以在任意主机登录该MySQL的账户：

   create user 'test_user'@'%' identified by 'Dhj123456.';# 然后使用刷新命令
   flush privileges
   

   %表示可以在任意主机登录。

   注意：不要忘记使用刷新命令：flush privileges；privilege 单词的意思就是：权限

2. 创建一个只能在本机登录的MySQL账户

   create user 'test_user'@'localhost' identified by 'Dhj123456.';
   

查询创建好的用户：

# 进入mysql数据库
use mysql# 查询user表
select User,Host,authentication_string from user;

前三个表时数据库默认有的。

创建用户时报错的解决方法：

1. 创建账号时发生报错
   
   这是因为你在/etc/my.cnf文件中设置了--skip-grant-tables（登录MySQL时不需要输入密码）
   先在MySQL窗口找中修改root账号的密码：

   UPDATE user SET authentication_string=PASSWORD('密码') WHERERE User='root';
   FLUSH PRIVILEGES;
   

   再在Linux命令行中输入：vim /etc/my.cnf找到--skip-grant-tables这一行，在开头加入#进行注解，或者直接删除。
   然后再进入MySQL修改root账户的密码：

   ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
   

   最后再创建新账户：

   create user 'test_user'@'%' identified by '123456';
   

2. 在设置密码的时候，因为mysql本身的认证等级比较高，一些简单的密码无法设置，会爆出
   如下报错：

   – ERROR 1819 (HY000): Your password does not satisfy the current policy
   requirements

   查看密码设置相关要求：

   SHOW VARIABLES LIKE 'validate_password%`;
   

1.4 删除用户

语法：

drop user '用户名'@'主机/ip';

示例：

删除test_user用户：

drop user 'test_user'@'%';

注意：删除用户的时候必须带上 ‘用户名’@‘主机名’。

1.5 修改用户的密码

1.5.1 语法和password函数

语法：

1. 自己改自己的密码：

   set password = password('新密码');
   

2. root用户修改指定用户的密码

   set password for '用户名'@'主机名' = password('新密码');
   

最规范的语法：

alter user '用户名'@'用户名' identified by '新密码'; 

password函数：

• PASSWORD() 函数会对输入的字符串进行加密，返回一个加密后的字符串。
• 这种加密方式曾用于存储 MySQL 用户账户的密码。
• 注意：从 MySQL 5.7.6 开始，PASSWORD() 函数已经被弃用，8.0 版本中已被移除。

1.5.2 示例

创建一个用户：

create user 'test_usr'@'localhost' identified by 'Dhj123456.';flush privileges;

修改用户的密码：

alter user 'test_user'@'localhost' identified by 'DHJ123456.';

再创建一个Linux命令窗口，用修改后的密码连接：

mysql -u test_user -p 

注意：在 MySQL 8.0 及之后的版本中，ALTER USER 和 CREATE USER 等语句会自动刷新权限，无需再手动执行 FLUSH PRIVILEGES 命令。

在 MySQL 8.0 及之前的版本中，在未使用 flush privileges之前，密码不会被修改，使用 flush privileges 后密码才会被修改。

2. 数据库的权限

2.1 数据库的权限列表

权限	列	上下文
CREATE	Create_priv	数据库、表或索引
DROP	Drop_priv	数据库或表
GRANT OPTION	Grant_priv	数据库、表或保存的程序
REFERENCES	References_priv	数据库或表
ALTER	Alter_priv	表
DELETE	Delete_priv	表
INDEX	Index_priv	表
INSERT	Insert_priv	表
SELECT	Select_priv	表
UPDATE	Update_priv	表
CREATE VIEW	Create_view_priv	视图
SHOW VIEW	Show_view_priv	视图
ALTER ROUTINE	Alter_routine_priv	保存的程序
CREATE ROUTINE	Create_routine_priv	保存的程序
EXECUTE	Execute_priv	保存的程序
FILE	File_priv	服务器主机上的文件访问
CREATE TEMPORARY TABLES	Create_tmp_table_priv	服务器管理
LOCK TABLES	Lock_tables_priv	服务器管理
CREATE USER	Create_user_priv	服务器管理
PROCESS	Process_priv	服务器管理
RELOAD	Reload_priv	服务器管理
REPLICATION CLIENT	Repl_client_priv	服务器管理
REPLICATION SLAVE	Repl_slave_priv	服务器管理
SHOW DATABASES	Show_db_priv	服务器管理
SHUTDOWN	Shutdown_priv	服务器管理
SUPER	Super_priv	服务器管理

2.2 给用户权限

2.2.1

刚创建的用户没有任何权限。需要给用户授权。

语法：

grant 权限列表 on 库.对象名 to '用户名'@'主机名' [identified by '密码']; 

说明：

权限列表，多个权限用逗号分开：

grant select on …
grant select, delete, create on …
grant all [privileges] on … – 表示赋予该用户在该对象上的所有权限

. ： 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
库.* ： 表示某个数据库中的所有数据对象(表，视图，存储过程等)
identified by可选。 如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户

2.2.2 示例

用root用户和新建的test_user用户查询数据库：

root用户:

show databases;

test_user用户:

show databases;

很明显，root用户查询出来的数据库比test_user用户多。

使用root用户给test_usr用户在 dm数据库上所有的权限：

grant all privileges on dm.* to 'test_user'@'localhost';flush privileges;

使用test_user用户再次查询数据库：

show databases;

2.3 回收权限

语法：

revoke 权限列表 on 库.对象名 from '用户名'@'主机名';

示例：

使用root用户回收test_user用户对数据库dm插入的权限：

revoke insert on dm.* from 'test_user'@'localhost';flush privileges;

使用test_user用户：

use dm;create table test_table(
id int;
name varchar(10)
);

然后向表中插入数据：

insert into test_user (id,name) values(1, '李明');