目录

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语句时,请确保您指定了正确的用户、主机和权限,以避免意外地撤销了错误的权限或影响了错误的用户。

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部