【MySQL】用户管理

目录

1.用户信息

2.用户的创建修改和删除

2.1.用户的创建

2.1.1.validate_password 插件

2.1.2.远程登录MySQL

 2.2.修改用户密码

2.3.删除用户

 3.MySQL权限

3.1.权限授权

3.2.权限回收

---

与Linux操作系统类似，MySQL中也有超级用户和普通用户之分。

如果一个用户只需要访问MySQL中的某一个数据库，甚至数据库中的某一个表，那么可以为其创建一个普通用户，并为该用户赋予对应的权限，而不让该用户看到数据库中的其他数据，防止该用户对其他数据进行误操作。

1.用户信息

MySQL当中默认有一个名为mysql的数据库。如下：

show databases;

查看该数据库中的表，可以看到其中有一个名为user的表。如下：

use mysql;
show tables;

user表中存储的就是MySQL中用户相关的信息。如下：

select * from user;

我们看到有四个 

部分字段说明：

1. user： 表示该用户的用户名。
2. host： 表示该用户可以从哪个主机登录，localhost表示只能从本机登录，%表示可以从任意地方登录。
3. authentication_string： 表示该用户的密码经过password()函数加密后的值。
4. xxx_priv： 表示该用户是否拥有对应权限。

 注意：

        在MySQL 8.0中，authentication_string字段的含义已经发生了变化，不再表示该用户的密码经过password()函数加密后的值。实际上，从MySQL 5.7版本开始，password()函数就已经被弃用，并且在MySQL 8.0中完全被移除。
        在MySQL 8.0中，用户的密码存储和认证机制得到了显著的改进。MySQL 8.0引入了新的默认密码认证插件caching_sha2_password，该插件使用SHA-256算法对密码进行加密，并且可以选择是否使用SSL/TLS加密认证过程。因此，authentication_string字段现在存储的是经过caching_sha2_password插件加密后的密码值。

上面的三个字段也是最重要的字段，所以以后我们进行查看用户相关的信息时，为了避免刷屏可以使用部分字段进行显示。如下：

select User,Host,authentication_string from user;

需要注意的是，MySQL中可以存在同名的用户，只要这些同名用户对应的登录主机不同即可，因为user表中的主键是复合主键，由表中的user列和host列共同承担。

如下：

desc user;

2.用户的创建修改和删除

2.1.用户的创建

创建用户的SQL如下：

CREATE USER '用户名'@'登录主机' IDENTIFIED BY '密码';

这条SQL语句用于在MySQL数据库中创建一个新的用户账户。下面是这条语句的详细解释：

CREATE USER：这是用于创建新用户的SQL命令。

'用户名'@'登录主机'：这里指定了新用户的用户名和可以从哪个主机连接到数据库服务器。

• 用户名：这是你想为新用户设置的用户名。用户名可以是字母、数字、下划线（_）或美元符号（$）的组合。
• 登录主机：这指定了用户可以从哪个主机连接到数据库服务器。

登录主机常见的值包括：

1. 'localhost'：表示用户只能从本地主机连接到数据库。
2. '%'：表示用户可以从任何主机连接到数据库。
3. 特定的IP地址或主机名：限制用户只能从特定的IP地址或主机名连接到数据库。

IDENTIFIED BY '密码'：这里指定了用户的密码。

• 密码：这是你想为新用户设置的密码。密码可以是字母、数字、特殊字符的组合，通常建议设置复杂且不易猜测的密码，以提高安全性。

---

以下是针对“登录主机”不同值的三个具体例子：

• 'localhost'：

当用户只能从本地主机连接到数据库时，你会在CREATE USER语句中使用'localhost'作为登录主机。这意味着，如果用户试图从网络上的另一台计算机连接到数据库，他们将被拒绝。

例子：

CREATE USER 'alice'@'localhost' IDENTIFIED BY 'strongPassword'; 

在这个例子中，用户alice只能从运行MySQL服务器的同一台计算机上连接到数据库。

• '%'：

当用户可以从任何主机连接到数据库时，你会在CREATE USER语句中使用'%'作为登录主机。这是一个通配符，表示接受来自任何IP地址的连接。

例子：

CREATE USER 'bob'@'%' IDENTIFIED BY 'anotherStrongPassword'; 

在这个例子中，用户bob可以从任何网络上的计算机连接到数据库，只要他们知道正确的用户名、密码和数据库服务器的地址。

• 特定的IP地址或主机名：

当用户只能从特定的IP地址或主机名连接到数据库时，你会在CREATE USER语句中指定那个特定的IP地址或主机名。这增加了安全性，因为它限制了可以连接到数据库的用户的位置。
例子（使用IP地址）：

CREATE USER 'charlie'@'192.168.1.100' IDENTIFIED BY 'yetAnotherStrongPassword'; 

在这个例子中，用户charlie只能从IP地址为192.168.1.100的计算机上连接到数据库。

例子（使用主机名）：

CREATE USER 'dave'@'database.example.com' IDENTIFIED BY 'secureDavePassword'; 

在这个例子中，用户dave只能从名为database.example.com的计算机上连接到数据库。这通常要求DNS解析正确，并且该主机名能够解析为数据库服务器可以访问的IP地址。

        请注意，在实际应用中，出于安全考虑，通常不建议使用'%'来允许从任何主机连接，除非有特定的需求，并且你已经采取了其他安全措施来限制对数据库的访问。 

---

 例如，如果你想创建一个名为john的用户，密码为securePassword123，并且允许他从任何主机连接到数据库服务器，你可以使用以下SQL语句：

CREATE USER 'john'@'%' IDENTIFIED BY 'securePassword123';

 创建用户成功后，该用户的相关信息也就被写入到刚才的user表中了。如下：

select User,Host,authentication_string from user;

---

注意：

• 创建用户的SQL当中包含用户的密码，为了安全性该SQL不会被历史记录下来，所以不能通过上下键进行追溯。
• MySQL本身的认证级别比较高，因此创建用户时设置的密码不能太简单，否则会出现报错，这时你可以选择将密码设置复杂一些，也可以对密码相关的设置进行调整。

注意密码不能设置的太简单，否则就会出现类似下面这种情况

但是我们初学者真的不想要搞一个太复杂的密码，那怎么办？我们可以修改 MySQL的密码验证策略的。

2.1.1.validate_password 插件

通过show命令查看全局变量，可以看到密码设置相关的要求。

show variables like 'validate_password%';

我的居然没有！！这个可能是我们没有安装

        在 MySQL 8.0 中，如果您执行 SHOW VARIABLES LIKE 'validate_password%'; 命令但没有看到任何与 validate_password 相关的变量，这通常意味着 validate_password 插件尚未被安装或启用。
 

       如果您想使用 validate_password 插件来设置密码策略，您需要首先确保该插件已被安装并启用。

以下是一些可能的步骤来安装和启用该插件：

• 检查插件状态：

在安装之前，你可以先检查validate_password插件是否已经安装并启用。使用以下命令查看：

SHOW PLUGINS; 

在结果中查找validate_password。如果它已经在列表中，并且状态是ACTIVE，那么插件已经安装并启用了。

• 安装插件

如果插件未安装，您可以使用 INSTALL PLUGIN 命令来安装 validate_password 插件。

INSTALL PLUGIN validate_password SONAME 'validate_password.so'; 

但是，你需要确保validate_password.so文件位于MySQL的插件目录中。这个目录通常是在MySQL的配置文件（my.cnf或my.ini）中通过plugin_dir变量指定的。
 

• 查看插件是否已安装：

安装插件后，您可以使用 SHOW PLUGINS; 命令来查看已安装的插件列表，并确认 validate_password 插件是否已列出。

SHOW PLUGINS;

看到我的最后一行了吗，这说明我安装成功了

• 配置密码策略：

一旦插件已安装并启用，您可以使用 SET GLOBAL 命令来配置密码策略参数。例如：

SET GLOBAL validate_password_policy = 'MEDIUM';
SET GLOBAL validate_password_length = 8;
SET GLOBAL validate_password_mixed_case_count = 1;
SET GLOBAL validate_password_number_count = 1;
SET GLOBAL validate_password_special_char_count = 1;

这些命令将设置密码策略为中等强度，并指定密码的最小长度、大小写字母、数字和特殊字符的要求。

• 验证配置：

最后，您可以使用 SHOW VARIABLES LIKE 'validate_password%'; 命令来验证配置是否已成功应用。

SHOW VARIABLES LIKE 'validate_password%';

通过修改validate_password_policy和validate_password_length参数，您可以放宽MySQL对密码复杂性的要求。

以下是对这些命令的详细解释：

• 修改validate_password_policy参数的值为低等级：

SET GLOBAL validate_password_policy=0; 

此命令将全局密码策略设置为最低等级（0）。MySQL的密码策略等级通常包括：

1. 0 或 LOW：只验证密码长度。
2. 1 或 MEDIUM：验证密码长度、数字、大小写字母和特殊字符。
3. 2 或 STRONG：验证密码长度、数字、大小写字母、特殊字符，以及字典文件检查（如果启用了字典文件）。

将策略设置为0意味着MySQL将只检查密码长度，而不考虑其他复杂性要求。

• 设置密码长度最小值改为1：

SET GLOBAL validate_password_length=1;

此命令将全局密码最小长度设置为1。这意味着您可以设置仅包含一个字符的密码（尽管这在实际应用中是非常不安全的）。

重要提示：

1. 修改这些设置会降低MySQL数据库的安全性。较短的密码和较低的复杂性要求更容易被破解。
2. 在生产环境中，强烈建议不要放宽密码策略。相反，应该实施更强的密码策略，并定期更新密码。
3. 如果您确实需要放宽密码策略（例如，用于测试环境），请确保您了解这样做的风险，并在适当的时候恢复更严格的策略。
4. 这些更改是全局的，将影响所有新创建的用户和现有用户的密码更改。但是，现有用户的当前密码不会因这些更改而失效，除非他们尝试更改密码并违反了新的策略（尽管在这种情况下，由于您已将策略设置为最低等级，这不太可能发生）。
5. 在执行这些更改之前，请确保您有足够的权限来修改全局变量，并且了解这些更改对数据库安全性的影响。

---

这时便可以用尝试使用新创建的普通用户来连接MySQL服务器了。

• 本地登录

首先我们来了解一个函数

select user();

        在MySQL中，SELECT USER(); 是一个SQL语句，用于返回当前连接到MySQL服务器的用户的名称以及该用户所连接的主机。这个语句通常用于诊断目的，以确保你以正确的用户身份连接到数据库。

        当你执行 SELECT USER(); 时，MySQL会返回一个结果集，其中包含一个列和一个行，显示类似于 'username@hostname' 的信息。这里的 username 是你的MySQL用户名，而 hostname 是你连接MySQL服务器时所用的主机名或IP地址。

        例如，如果你以用户名 root 从本地主机连接到MySQL服务器，那么执行 SELECT USER(); 可能会返回：

这个信息对于调试和审计目的非常有用，因为它可以帮助你确认哪个用户正在执行特定的SQL语句。

        请注意，即使你在MySQL客户端中使用了不同的别名或用户名连接到服务器（例如，通过 -u 选项指定了一个不同的用户名），USER() 函数仍然会返回实际连接到MySQL服务器的用户的名称和主机。这是因为MySQL服务器在建立连接时会验证用户的身份，并记录连接信息。

我们怎么进行本地登录呢？

我们退出MySQL

 

输入下面这个

mysql -u john -p

然后我们输入密码securePassword123

 

接下来我们输入

select user();

 

发现我们登录成功了。

2.1.2.远程登录MySQL

由于我们创建的这个用户可以从任意地方登录，所以我们换一台安装了MySQL的云服务器来登录一下这个john。

我的机器是ubuntu 22.04

        在Linux系统上远程登录MySQL数据库可以通过MySQL命令行客户端（如mysql）或图形化管理工具（如MySQL Workbench）来实现。以下是使用MySQL命令行客户端进行远程登录的步骤：
前提条件

1. MySQL服务器已开启远程访问：MySQL服务器的配置文件（ubuntu22.04里面的配置文件是/etc/mysql/mysql.conf.d/mysqld.cnf）中的bind-address需要设置为0.0.0.0或具体的服务器IP地址，以允许来自远程的连接。
2. MySQL用户有远程访问权限：MySQL用户需要被授权从远程主机连接。

步骤

• 在MySQL服务器上设置远程访问

编辑MySQL配置文件（/etc/mysql/mysql.conf.d/mysqld.cnf），找到[mysqld]部分，确保bind-address设置正确。

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

我们把下面这句加进去

bind-address=0.0.0.0

保存文件并重启MySQL服务：

sudo service mysql restart

• 创建或修改MySQL用户以允许远程访问

登录到MySQL服务器（可以使用mysql -u root -p），然后创建或修改用户以允许从远程IP地址连接。例如，允许用户john从任意IP地址连接：

CREATE USER 'john'@'%' IDENTIFIED BY 'mypassword';  

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' WITH GRANT OPTION;  

FLUSH PRIVILEGES; 

注意：第2句是赋予新用户对数据库的权限的，第3句是设置立即生效的意思，这两句我们可以先不深入理解

如果只想允许从特定IP地址（如121.36.254.82）连接，将%替换为具体的IP地址：

CREATE USER 'myuser'@'121.36.254.82' IDENTIFIED BY 'mypassword';

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'121.36.254.82' WITH GRANT OPTION;

FLUSH PRIVILEGES;

我们执行第二个

• 确保防火墙允许MySQL连接

配置防火墙以允许MySQL的默认端口（3306）的连接。例如，使用ufw（Uncomplicated Firewall）：

sudo ufw allow 3306/tcp

注意：上面这3步都是在要被外部访问的数据库所在的主机里面进行操作的，上面的所有操作我都是在117.72.80.239这台机器里面操作的！！！

• 从远程主机连接到MySQL服务器

 好，我们现在转到121.36.254.82这台机器上面，使用mysql命令行客户端连接到MySQL服务器：

mysql -h 117.72.80.239 -u myuser -p

输入密码后，即可登录到MySQL服务器。

 

到目前为止，我们完成了从121.36.254.82这台机器远程访问117.72.80.239这台机器里面的MYSQL数据库！！

注意事项

1. 被访问的云服务器需要去官网设置安全组——开放MYSQL的3306端口
2. 安全性：允许从任意IP地址连接MySQL服务器（'%'）可能会带来安全风险。建议只允许从特定的、受信任的IP地址连接。
3. 防火墙和SELinux：确保防火墙和SELinux（如果启用）允许MySQL的连接。
4. MySQL版本：不同版本的MySQL可能在配置和管理方面有所不同，请参考对应版本的官方文档。
5. 通过上述步骤，你应该能够在Linux系统上实现远程登录MySQL数据库。

如果你只想允许john从本地主机连接到数据库服务器，你可以使用以下SQL语句：

CREATE USER 'john'@'localhost' IDENTIFIED BY 'securePassword123'; 

 2.2.修改用户密码

• 修改自己密码

普通用户有修改自己密码的权力，但是没有修改其他用户密码的权力。

修改密码的SQL语句如下

对于已经存在的用户，并且你想要更新其密码，你应该使用如下的 ALTER USER 语句：

ALTER USER 'username'@'hostname' IDENTIFIED BY 'newpassword';

例如，我修改john的密码为123456

ALTER USER 'john'@'%' IDENTIFIED BY '123456';

我们退出MySQL

 

输入下面这个

mysql -u john -p

然后我们输入密码123456

 

接下来我们输入

select user();

 

发现我们登录成功了。这说明我们密码修改成功了

• root用户修改指定用户的密码

root有权利修改任何一个用户的密码

修改密码的SQL语句和上面的一模一样！！！！

ALTER USER 'username'@'hostname' IDENTIFIED BY 'newpassword';

这条语句对于 MySQL 数据库中的普通用户和 root 用户都是适用的。无论你是想更改一个普通用户的密码，还是 root 用户的密码，都可以使用这条语句。

---

• 为了让修改的配置立即生效，我们可以使用下面的命令：

flush privileges;

        在MySQL数据库中，FLUSH PRIVILEGES语句被用于重新加载授权表，使对mysql数据库下的user、db、tables_priv、columns_priv以及procs_priv等表的更改立即生效。这些表存储了关于用户权限的信息。

        当你直接对这些权限表进行了INSERT、UPDATE或DELETE等DML（数据操作语言）操作后，MySQL并不会立即将这些更改应用到内存中。为了立即反映这些更改，你需要执行FLUSH PRIVILEGES语句。这样，MySQL会重新读取这些权限表，并将新的权限信息加载到内存中。

        然而，在大多数情况下，你并不需要手动执行FLUSH PRIVILEGES。当你通过GRANT、REVOKE或SET PASSWORD等语句更改用户权限时，MySQL会自动执行这个操作。这些语句内部已经包含了FLUSH PRIVILEGES的逻辑。

---

2.3.删除用户

删除用户的SQL如下：

DROP USER '用户名'@'登录地址';

例如下面我们要删除用户john

drop user 'john'@'%';

 3.MySQL权限

Mysql提供了非常的权限

一、数据库级别权限

• CREATE：创建新数据库的权限。
• DROP：删除数据库的权限。
• GRANT OPTION：赋予或撤销其他用户权限的权限，仅对数据库级别有效时。

二、表级别权限

• CREATE：在指定数据库中创建新表的权限。
• DROP：删除指定数据库的表的权限。
• ALTER：修改表结构的权限，如添加、删除列或索引等。
• DELETE：删除表中数据的权限。
• INDEX：创建或删除索引的权限。
• SELECT：查询表中数据的权限。
• UPDATE：更新表中数据的权限。
• GRANT OPTION：赋予或撤销其他用户对该表的权限。

三、视图级别权限

• CREATE VIEW：创建视图的权限。
• SHOW VIEW：查看视图定义的权限。

四、保存的程序（存储过程和函数）级别权限

• ALTER ROUTINE：修改存储过程或函数的权限。
• CREATE ROUTINE：创建存储过程或函数的权限。
• EXECUTE：执行存储过程或函数的权限。
• GRANT OPTION：赋予或撤销其他用户对该存储过程或函数的权限。

五、服务器管理权限

• FILE：在服务器主机上读写文件的权限。
• CREATE TEMPORARY TABLES：创建临时表的权限。
• LOCK TABLES：对表进行锁定的权限，以防止其他用户访问。
• CREATE USER：创建新用户的权限。
• PROCESS：查看或终止服务器上运行的线程的权限。
• RELOAD：重新加载服务器权限表的权限，或执行其他刷新操作。
• REPLICATION CLIENT：查询主服务器或从服务器状态的权限。
• REPLICATION SLAVE：在主服务器和从服务器之间设置复制的权限。
• SHOW DATABASES：查看服务器上所有数据库的权限。
• SHUTDOWN：关闭MySQL服务器的权限。
• SUPER：执行高级管理操作的权限，如设置全局变量或杀死其他用户的连接。

这些权限可以通过GRANT语句授予用户，通过REVOKE语句撤销。在授予或撤销权限时，需要指定具体的权限类型、上下文（如数据库名、表名等），以及用户的主机名和用户名。同时，MySQL还提供了SHOW GRANTS语句来查看用户的当前权限。

创建用户后该用户默认会有USAGE权限，该权限只能用于数据库登录，不能执行任何操作。

3.1.权限授权

新创建的用户没有任何权限，因此创建用户后需要给用户授权。

给用户授权的SQL如下：

GRANT 权限类型 [(列名)] [, 权限类型 [(列名)] ...]  
ON 库名.对象名  
TO '用户名'@'登录地址'  
[WITH GRANT OPTION];

1. 权限类型：这是您要授予的权限，如SELECT、INSERT、UPDATE、DELETE等。对于某些权限，如INDEX或ALTER，它们可能不适用于所有类型的对象（例如，您不能在列级别授予ALTER权限）。
2. (列名)：对于某些权限（如SELECT或INSERT），您可以指定特定的列。这是可选的，如果不指定，则权限适用于对象（如表）中的所有列。
3. 库名.对象名：这指定了权限将应用于哪个数据库和哪个对象（表、视图、存储过程等）。对象名可以是具体的对象，也可以是*来表示所有对象。
4. '用户名'@'登录地址'：这指定了将接收权限的用户。用户名是必需的，而登录地址可以是具体的IP地址、主机名或'%'（表示任何主机）。
5. [WITH GRANT OPTION]：这是一个可选部分，如果包含，它将允许用户将其拥有的权限授予其他用户。

注意

---

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

1. grant select on … // 赋予查询权限
2. grant select, delete, create on … // 赋予查询，删除和创建权限
3. grant all [privileges] on … // 表示赋予该用户在该对象上的所有权限

*.* : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）

库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)、

---

首先我们在root账户下面看看

 

我们可以看到很多库和很多表。

我们创建一个用户，john

CREATE USER 'john'@'%' IDENTIFIED BY 'securePassword123';

 我们可以登上这个账号，然后执行下面这些操作

嗯？为什么john显示结果的和root的不一样？这是因为john没有查看的权限

我们可以看一看用户拥有什么权限，可以使用下面的命令：

show grants for '用户名'@'登录地址'

创建用户后该用户默认会有USAGE权限，该权限只能用于数据库登录，不能执行任何操作。

上面就一个USAGE权限

例如，我们可以给john加一个SELECT权限（注意只能root来）

grant select on db1.* to 'john'@'%';

•  SELECT：查询表中数据的权限。

它用于在 MySQL 数据库中给用户 john 授予对数据库 db1 中所有表的 SELECT 权限，并且这个权限是从任何主机连接时都有效的（由 '%' 指定）。

我们回到john，重新查看

我们发现现在能查看到db1了！！！我们进去看看

都能正常查看了啊，但是我们不能对这些表进行修改

因为john没有权限！ 想要修改这张表，就需要root给john安排update权限！！

• UPDATE：更新表中数据的权限。

这个我就不演示了啊！

3.2.权限回收

在MySQL中，REVOKE语句用于撤销之前授予给用户的权限。

语法格式

REVOKE 权限列表 ON 库名.对象名 FROM '用户名'@'登录地址'; 

1. 权限列表：这是要撤销的权限，可以是单个权限（如SELECT），也可以是多个权限（如SELECT, INSERT），用逗号分隔。如果要撤销所有权限，可以使用ALL PRIVILEGES。
2. 库名.对象名：这指定了权限将被撤销的数据库和对象。库名是数据库的名称，对象名可以是表名、视图名等。如果要撤销数据库级别的权限（而不是特定对象的权限），可以使用库名.*。如果要撤销全局权限，则不使用ON子句。
3. '用户名'@'登录地址'：这指定了要撤销权限的用户。'用户名'是用户的名称，'@登录地址'指定了用户可以从哪个主机连接到MySQL服务器。如果用户可以从任何主机连接，则使用'%'作为登录地址。

例子

假设您想要撤销用户john从任何主机连接到MySQL服务器时，在db1数据库中对table1表的SELECT和INSERT权限，您可以使用以下语句：

REVOKE SELECT, INSERT ON db1.table1 FROM 'john'@'%'; 

如果您想要撤销用户john的全局权限（即所有数据库和对象的权限），您可以使用：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'john'@'%'; 

注意，在撤销全局权限时，通常还需要撤销GRANT OPTION，这是授予其他用户权限的权限。

 如果您想要撤销用户john在db1数据库中的所有权限，您可以使用：

REVOKE ALL PRIVILEGES ON db1.* FROM 'john'@'%';

 

我们发现db1又不见了！！

注意事项

1. 撤销权限后，用户将不再能够执行被撤销的操作，直到再次被授予这些权限。
2. 撤销权限可能需要管理员权限（如root用户）或具有足够权限的其他用户来执行。
3. 使用REVOKE语句时，请确保您指定了正确的用户、主机和权限，以避免意外地撤销了错误的权限或影响了错误的用户。