mysql用户管理

查询用户信息

查询用户哪些设备可以登录

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | wjdb             |
| %         | wordpress        |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | wjdb             |
| localhost | wordpress        |
+-----------+------------------+

% 表示允许所有的ip远程访问，如果需要指定具体的某个ip就写上具体的ip即可
localhost 表示只允许本地登录

use mysql
select * from user;
等同于 select * from mysql.user;

show grants for 'root'@'localhost';
show grants for 'wjcs'@'%'

创建用户

-- 创建用户wjcs，同时允许所有地址都可以访问
create user 'wjyonghu'@'%' identified by 'Wj_20252025';
create user 'wordpress'@'%' identified by 'Wj_20252025';
-- 表示只允许本地登录
create user 'wjyonghu'@'localhost' identified by 'Wj_20252025';
--表示只有192.168.1.1 可以登录数据库
create user 'wjyonghu'@'192.168.1.1' identified by 'Wj_20252025';
-- 表示此用户没有密码，同时可以从任何地方可以登录
create user 'wjyonghu'@'%'
flush privileges;

删除用户

删除特定主机的用户

DROP USER 'wjyonghu'@'localhost';  -- 删除本地用户
DROP USER 'wjyonghu'@'192.168.1.100';  -- 删除指定 IP 的用户
DROP USER 'wjyonghu'@'%';  -- 删除所有 IP 可访问的用户
flush privileges;

验证用户是否已删除

SELECT User, Host FROM mysql.user WHERE User = 'wjyonghu';

授权用户登陆

1、登录数据库

[root@iZuf ~]# mysql -uroot -p****

2、切换数据库

mysql> use mysql;
Database changed

3、查看用户权限

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | wjdb             |
| %         | wordpress        |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | wjdb             |
| localhost | wordpress        |
+-----------+------------------+

4、修改用户登录权限

UPDATE user SET Host= '%' WHERE User= 'root' LIMIT 1;
flush privileges;

% 表示允许所有的ip远程访问，如果需要指定具体的某个ip就写上具体的ip即可

5、刷新权限

flush privileges;

授权数据库给用户

-- 全部授权 这里wjdb.*表示wjdb数据库中的所有对象。ALL PRIVILEGES包含了如SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）、CREATE（创建）、DROP（删除）等多种权限。表示wjcs用户可以对wjdb库进行操作
grant all privileges ON wjshujuku.* TO 'wjyonghu'@'%' WITH GRANT OPTION;
grant all privileges ON  wordpress.* TO 'wordpress'@'%' WITH GRANT OPTION;

--授予部分权限 只想授予用户new_user对mydb数据库中的表进行查询和插入数据的权限
GRANT SELECT, INSERT ON mydb.* TO 'new_user'@'localhost';
GRANT Update ON wjdb.* TO 'wjcs'@'%';

--可以根据需要组合不同的权限，如
GRANT SELECT, UPDATE, DELETE ON mydb.customers TO 'new_user'@'localhost';，这里是授予对mydb数据库中的customers表进行查询、更新和删除的权限

--最后刷新授权才能生效
FLUSH PRIVILEGES;

撤销授权

-- 撤销权限使用REVOKE语句。例如，撤销new_user对mydb数据库的插入权限，语句如下
REVOKE INSERT ON mydb.* FROM 'new_user'@'localhost';
REVOKE Index ON wjdb.* FROM 'wjcs'@'%';
REVOKE Update ON wjdb.* FROM 'wjcs'@'%';

--同样，撤销全部权限可以使用REVOKE ALL PRIVILEGES ON database_name.* FROM 'user_name'@'host';语句。

--撤销权限后，也需要使用FLUSH PRIVILEGES;来刷新权限，使撤销操作生效。

用户连接异常

案例1：Public Key Retrieval is not allowed
当你在连接 MySQL 数据库时碰到 “Public Key Retrieval is not allowed” 错误，这一般是因为 MySQL 8.0 及后续版本默认运用了 caching_sha2_password 认证插件，该插件需要进行公钥检索

解决办法一：在连接 URL 里添加参数
在你的数据库连接 URL 中增加 allowPublicKeyRetrieval=true 参数，同时要保证使用的是 SSL 连接。示例如下：
jdbc:mysql://localhost:3306/yourdatabase?allowPublicKeyRetrieval=true&useSSL=false

修改用户密码

在修改密码之前先确认下host信息，根据host的值修改对应的信息

mysql> use mysql;
Database changed

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.01 sec)

以下修改的root密码是host为%的

mysql> alter user 'root'@'%' identified by 'Wj_20252025';
Query OK, 0 rows affected (0.02 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)