本例操作适合5.7版本
用户表中的信息
哪些用户可以登陆都是在这个表中查看
mysql> select * from user \G;
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
password_expired: N
password_last_changed: 2023-11-08 17:31:54
password_lifetime: NULL
account_locked: N
添加一个新用户
添加一个用户pyhui,密码pyhui
mysql> create user 'pyhui'@'localhost' identified by 'pyhui';
Query OK, 0 rows affected (0.00 sec)
添加的结果
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | pyhui | *5970AAA82D639CC7F0EE84413A3DF044328C3AA3 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
使用新帐户来登陆
可以登陆成功的
C:\Users\Administrator>mysql -upyhui -ppyhui
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.43-log MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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>
修改用户的密码
在超级管理员的权限下可以修改其他用户的密码
两个操做
1,修改密码
2,刷新权限
UPDATE USER SET authentication_string =PASSWORD('root') WHERE USER='pyhui';
FLUSH PRIVILEGES;
如果是版本5.0那么,authentication_string要变为password
参考笔记
在MySQL5.7版本中,可以使用以下步骤在超级管理员登陆情况下修改其它用户的密码:
- 登录MySQL服务:
mysql -u root -p
- 切换到MySQL系统库:
use mysql;
- 通过下面的命令修改用户密码:
UPDATE user SET authentication_string=PASSWORD('root123') WHERE user='pyhui';
- 刷新MySQL系统权限表:
FLUSH PRIVILEGES;
- 退出MySQL服务:
quit;
经过上述步骤,即可将用户pyhui的密码修改为root123。
删除用户
删除用户pyhui
mysql> drop user 'pyhui'@'localhost';
Query OK, 0 rows affected (0.00 sec)
验证结果
再次查看用户表
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)