您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL中如何修改用户各种信息

时间:2022-03-22 15:49:42  来源:  作者:程序猿集锦
  • IF EXISTS的含义
  • user的指定
  • auth_option选项
  • IDENTIFIED BY 'auth_string'
  • IDENTIFIED WITH auth_plugin
  • IDENTIFIED WITH auth_plugin BY 'auth_string'
  • IDENTIFIED WITH auth_plugin AS 'auth_string'
  • tls_option选项
  • resource_option选项
  • password_option选项
  • lock_option选项

修改用户的语法

MySQL中修改用户的语法如下:

ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

ALTER USER [IF EXISTS]
    USER() IDENTIFIED BY 'auth_string'

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

看了这么一大坨东西,感觉啥也没有告诉我,一脸懵逼。下面我们一起来解读一下具体的每一个选项的含义。

IF EXISTS的含义

对于这个大家应该有所了解。

[IF EXISTS]

首先它是被中括号包裹起来的,表示是可选的不是必须的,也就是说在我们的alter user语句中,这个IF EXISTS可以不出现不是必须要写的。但是为什么还要有这个呢,这个选项出现的目的是为了让你在执行一个alter user语句的时候,如果语句中指定的数据库用户不存在(或者你写用户名称的是写错了),而不至于出现一个错误的信息,如果指定了IF EXISTS这个词,他就会以一个警告的信息提示你而不是直接给你一个错误信息。

更直白一下就是:如果你的alter user的语句中指定的用户确实存在,那么就执行你的语句,如果不存在呢,就不执行你的语句,跳过这个alter user的指令。看示例:

mysql> alter user 'zhangsanfeng' identified by 'zhangsanfeng';
ERROR 1396 (HY000): Operation ALTER USER fAIled for 'zhangsanfeng'@'%'
mysql>
mysql> alter user if exists 'zhangsanfeng' identified by 'zhangsanfeng';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 3162 | User 'zhangsanfeng'@'%' does not exist. |
+-------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

在上面的例子中,第一个修改用户的语句,没有使用if exists关键词直接修改了一个不存在的用户的信息,结果直接出现了错误信息,接着我们使用了if exists关键词,结果SQL语句执行成功,只是出现了一个warning警告,然后我们通过show warnings命令查看警告信息的内容,提示我们用户'zhangsanfeng'@'%'不存在。这就是if exists关键词的存在的意义。

注意:当我们指定MySQL数据库用户的时候,如果我们不显示的指定用户的host网段,则默认使用%来代替。所以,我们看到我们输入的是'zhangsanfeng'但是提示的却是'zhangsanfeng'@'%'。

user的指定

当我们修改一个用户的信息的时候,我们需要指定用户的名称。这个名称,不仅仅使我们平时登录数据库的时候,输入的用户的名字,还有一个隐含的部分就是用户登录的host网段,这个网段表示只允许该用户通过这个网段登录,如果用户不在这个定义的网段内,即便是用户名和密码正确,也不能争取的登录。

下面的创建用户是两个不同的用户。

create user 'zhangsanfeng'@'10.10.10.11' identified by '123456';
create user 'zhangsanfeng'@'192.168.1.100' identified by 'zhangsanfeng';

上面的两个用户中,分别表示如下:

  • zhangsanfeng只允许在IP地址为10.10.10.11的主机上用密码123456登录。如果它在IP地址为192.168.1.100的主机上用密码123456是不能登录成功的。
  • zhangsanfeng只允许在IP地址为192.168.1.100的主机上用密码zhangsanfeng登录。如果它在IP地址为10.10.10.11的主机上用密码zhangsanfeng是不能登录成功的。

当然,我们可以使用模糊匹配的方式来限定用户登录的网段,比如我们创建下面第一个用户表示只要这个用户登录的时候,使用的主机的IP地址是在10.10.0.0~10.10.255.255这个网段质检的任何一个IP地址都可以登录。

create user 'zhangsanfeng'@'10.10.%' identified by '123456';

特殊的网段localhost。如果我们创建如下的用户,则表示该用户只能在MySQL数据库所在的服务器本机上登录。

create user 'zhangsanfeng'@'localhost' identified by '123456';

还有另外一个特殊的网段%。它表示用户可以通过任何一个IP地址来登录,不对用户登录的主机所在的网段做任何限制。如下用户就是不对它登录的主机IP地址做任何限制。

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

auth_option选项

auth_option选项的含义表示指定用户登录数据库的时候使用的验证插件和密码。它的选项有如下几种,下面我们分别展开分析一下各个选项的使用场景和示例。

auth_option: {
    IDENTIFIED BY 'auth_string'  /*使用默认的密码认证方式*/
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

IDENTIFIED BY 'auth_string'

如果在IDENTIFIED关键字后面没有使用WITH关键字指定使用的密码插件名称,则会使用MySQL默认的密码插件。对于MySQL默认的密码插件是什么,可以使用下面的命令进行查看:

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)

mysql>

通过上面的输出,我们可以看出默认的密码插件采用的是mysql_native_password插件,参数
default_authentication_plugin的值是可以在MySQL的配置文件my.cnf中指定的,它可以有以下两种取值:

  • mysql_native_password:使用MySQL本地密码策略,这个是默认值。更多信息请参考:Native Pluggable Authentication
  • sha256_password:使用SHA-256密码策略。更多信息请参考:SHA-256 Pluggable Authentication

MySQL的配置文件my.cnf中配置默认的密码认证插件的方式如下:

[mysqld]
default_authentication_plugin=mysql_native_password
# 或者下面的使用sha256_password作为默认的密码认证插件
# default_authentication_plugin=sha256_password

IDENTIFIED WITH auth_plugin

如果我们要修改某一个用户的密码认证插件,就可以使用到IDENTIFIED WITH auth_plugin这个语句了。如果一个用户它的密码认证插件使用的是默认的mysql_native_password,我们想把它的密码认证插件修改为sha256_password,此时我们就可以使用下面的命令来修改:

alter user xyz identified with sha256_password;

执行完成上述命令后,xyz这个用户的密码就会设置为空,并更新它的密码为已经过期,同时更新了它的密码认证插件为sha256_password,结果如下所示:

mysql> select user, host, plugin, authentication_string, password_expired from mysql.user where user ='xyz';
+------+------+-----------------------+-------------------------------------------+------------------+
| user | host | plugin                | authentication_string                     | password_expired |
+------+------+-----------------------+-------------------------------------------+------------------+
| xyz  | %    | mysql_native_password | *39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2 | N                |
+------+------+-----------------------+-------------------------------------------+------------------+
1 row in set (0.03 sec)

mysql> alter user xyz identified with sha256_password;
Query OK, 0 rows affected (0.02 sec)

mysql> select user, host, plugin, authentication_string, password_expired from mysql.user where user ='xyz';
+------+------+-----------------+-----------------------+------------------+
| user | host | plugin          | authentication_string | password_expired |
+------+------+-----------------+-----------------------+------------------+
| xyz  | %    | sha256_password |                       | Y                |
+------+------+-----------------+-----------------------+------------------+
1 row in set (0.02 sec)

mysql>

更改用户的密码认知方式之后,当xyz再次尝试登录MySQL数据库的时候,输入空密码登录成功后,会要求其修改一下自己的密码,然后才可以执行其他SQL语句的操作,这个要求和我们刚安装MySQL数据库后,第一次使用root登录的时候要求修改root的密码是一样的。下面是修改完成用户xyz的密码认证插件之后,尝试使用空密码登录后的操作示例:

➜  Downloads mysql -uxyz -p -h10.2.1.7
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 45197703
Server version: 5.7.25-log

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. # 这里提示需要修改完成密码之后才可以执行其他SQL语句命令。
mysql> alter user xyz identified by 'xyz';
Query OK, 0 rows affected (0.10 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.01 sec)

mysql> select user, host, plugin, authentication_string from mysql.user where user ='xyz';
+------+------+-----------------+---------------------------------------------------------------------+
| user | host | plugin          | authentication_string                                               |
+------+------+-----------------+---------------------------------------------------------------------+
| xyz  | %    | sha256_password | $5$Jmq<rS]t?F]~WUfR]$LVplZGG4HlLuHcoXEnXXFtBHARd8vkeWoLUu/uEN0ZC |
+------+------+-----------------+---------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql>

注意:当我们尝试使用sha256_password作为密码认证插件的时候,则必须使用SSL加密连接的方式连接到MySQL数据库,也就是需要为MySQL服务器端配置OpenSSL证书之后才可以正常连接到MySQL数据库。

IDENTIFIED WITH auth_plugin BY 'auth_string'

指定用户的密码认证插件,并设置密码。

当我们想给用户指定密码的认证插件,并且想为其设置密码的时候,可以使用这个命令,示例如下:

alter user xyz identified with mysql_native_password by 'xyz';

这样用户xyz的密码认证方式修改为了mysql_native_password,并且修改它的密码为xyz。需要我们主要的是by关键字后面跟的是密码的明文,也就是我们尝试登录的时候,输入的密码的值。

IDENTIFIED WITH auth_plugin AS 'auth_string'

指定用户的密码认证插件,并设置密码。

当我们想给用户指定密码的认证插件,并且想为其设置密码的时候,可以使用这个命令,示例如下:

alter user xyz identified with mysql_native_password as '*39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2';

这样用户xyz的密码认证方式修改为了mysql_native_password,并且修改它的密码为xyz。需要我们主要的是as关键字后面跟的是加密后的密码,而不是我们的明文的密码。如果我们想知道得到一个加密后的密码,则可以使用下面password()函数来得到加密后的密码。

mysql> select password('xyz');
+-------------------------------------------+
| password('xyz')                           |
+-------------------------------------------+
| *39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2 |
+-------------------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql>

tls_option选项

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

这里主要是指定用户在连接到MySQL数据库的时候,是否需要使用加密的方式,如果使用加密的方式,则需要启用SSL加密协议,同时也需要对MySQL进行证书的配置。

考虑到性能的问题,使用TLS加密的方式连接MySQL数据库会对MySQL数据库的性能有一定的影响。非必要情况下,一般不建议启用TLS加密连接到数据库,因为大家的MySQL数据库一般都是针对内网开放的。所以,启用TLS加密协议连接没有必要。

resource_option选项

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

resource_option选项中,主要是为每一个用户设置它所能使用到资源,做资源的使用限制。示例如下:

mysql> alter user xyz with max_queries_per_hour 100; /*限制每小时最多发起100个查询语句*/
Query OK, 0 rows affected (0.02 sec)

mysql> alter user xyz with max_updates_per_hour 50; /*限制每小时最多发起50个更新语句*/
Query OK, 0 rows affected (0.01 sec)

mysql> alter user xyz with max_connections_per_hour 70; /*限制每小时最多建立70个连接*/
Query OK, 0 rows affected (0.01 sec)

mysql> alter user xyz with max_user_connections 80; /*限制用户最多同时建立80个连接,超过80个同时在线的连接后,在请求建立新连接将会失败。*/
Query OK, 0 rows affected (0.01 sec)

mysql>

上面四个参数的值如果为0,则表示对应的资源限制不做任何限制。

password_option选项

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

password_option选项用来配置用户的密码,指定其密码的效期。下面看几个示例:

  • 设置用户xyz的密码立即过期:
alter user 'xyz' password expire; /*设置密码马上过期*/
  • 设置用户xyz的密码有效期为系统默认有效期。
alter user 'xyz' password expire default; /*设置密码过期时间为MySQL系统默认的时间*/

对于MySQL系统默认的有效期是多少,可以通过如下命令查看,下面的值为0表示密码有效期为永久有效。

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+
1 row in set (0.02 sec)

mysql>
  • 设置用户xyz的密码有效期为永不过期。
alter user 'xyz' password expire never; /*设置密码永不过期*/
  • 设置用户xyz的密码有效期为90天。
alter user 'xyz' password expire interval 90 day; /*设置密码有效期为90天*/

lock_option选项

lock_option选择是用来锁定和解锁用户的。示例如下:

  • 锁定用户xyz的账号
alter user 'xyz' account lock;
  • 解锁用户xyz的账号
alter user 'xyz' account unlock;


Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: MySQL  点击:(10)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: MySQL  点击:(17)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  Search: MySQL  点击:(14)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: MySQL  点击:(29)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10  Search: MySQL  点击:(14)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  Search: MySQL  点击:(32)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  Search: MySQL  点击:(53)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  Search: MySQL  点击:(54)  评论:(0)  加入收藏
为什么高性能场景选用Postgres SQL 而不是 MySQL
一、 数据库简介 TLDR;1.1 MySQL MySQL声称自己是最流行的开源数据库,它属于最流行的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。LAMP...【详细内容】
2024-02-19  Search: MySQL  点击:(39)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  Search: MySQL  点击:(55)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(10)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(17)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(14)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(29)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(14)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(32)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(53)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(54)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(55)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(87)  评论:(0)  加入收藏
站内最新
站内热门
站内头条