MYSQL8的账户管理

最新推荐文章于 2024-08-11 00:05:10 发布

user2025

最新推荐文章于 2024-08-11 00:05:10 发布

阅读量1.3k

点赞数 3

分类专栏： MySQL数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/user2025/article/details/106885108

版权

MySQL数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

查询mysql的版本

SELECT @@version

查询用户、用户能登录的主机IP、用户当前使用的身份认证插件

SELECT user,host,plugin,authentication_string  FROM mysql.user
（1）localhost指本地才可连接
（2）%指任意ip都能连接
（3）可以使用户在指定的IP地址登录
----------------------------------------------------------------
用户名            可登录的IP地址    身份验证插件		       使用指定身份验证插件加密的密码
root	             %	         caching_sha2_password   $A$005$THISISACOMBINATI
mysql.infoschema	localhost	 caching_sha2_password   $A$005$THISISACOMBINATI
mysql.session	    localhost	 caching_sha2_password   $A$005$THISISACOMBINATI
mysql.sys	        localhost	 caching_sha2_password   $A$005$THISISACOMBINATI

登录数据库

mysql -u root -p

show databases;
use 数据库名;

#查询时间：
select now();
#查询当前用户：
select user();
#查询数据库版本：
select version();
#查询当前使用的数据库：
select database();
#查询当前使用的身份验证插件：
SHOW VARIABLES LIKE 'default_authentication_plugin'

创建、修改、删除用户

mysql8.0有两种身份认证插件：caching_sha2_password和mysql_native_password
MySQL8.0首选默认认证插件：caching_sha2_password，替代了5.7的mysql_navtive_password

#创建账号：
create user '用户名'@'%' identified with caching_sha2_password by '用户密码';
#注意：可以通过修改localhost进行访问控制，包括输入指定IP,其中当为 % 时达到不限IP访问

#修改密码：
Alter user '用户名'@'192.168.1.10' identified with caching_sha2_password by '新密码';

#修改可登录服务器的IP地址
update mysql.user set host="%" where user="root"; 
#修改数据库的身份认证插件：
UPDATE mysql.user SET PLUGIN='mysql_native_password' WHERE user='test'

#删除用户
方式1：
drop user '用户名'@'IP地址';
#如果drop user 用户名，则默认删除的是'用户名'@'%'，如果还有其他用户，例如'用户名'@'localhost','用户名'@'ip地址',则不会一起被删除

方式2：
delete from user where user='用户名' and host='IP地址'; 
FLUSH PRIVILEGES；
#使用delete删除用户后需要执行FLUSH PRIVILEGES;刷新权限，否则下次使用create语句创建用户时会报错。

二者的区别：
drop删除掉的用户不仅将user表中的数据删除，还会删除诸如db和其他权限表的内容。而delete只是删除了user表的内容，其他表不会被删除，后期如果命名一个和已删除用户相同的名字，权限就会被继承。

mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问

flush privileges;

用户授权

语法：
GRANT privileges ON databasename.tablename TO 'username'@'host' [WITH GRANT OPTION];

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
WITH GRANT OPTION 该用户是否可授权【一般使用，授权一般由dba统一进行管理】

#授予用户指定数据库的指定表的所有权限
grant all privileges on 数据库名.表名 to '用户名'@'主机IP';

#授予用户服务器中所有数据库所有表的所有权限
grant all privileges on *.* to '用户名'@'主机IP';

常见的权限有select,insert,update,delete,create,drop，分别是查、增、改、删、创建、清空，前四种是对表数据的操作，后两种是对表的操作

#授予用户指定权限
grant select,insert on test to 'tester'@'localhost';

#撤销用户所有权限
revoke all privileges on *.* from 'test1'@'localhost';
#撤销用户某些权限
REVOKE select,insert ON 数据库名.表名 FROM '用户名'@'192.168.204.1';

#用户授权完成后，别忘了刷新权限：
flush privileges;

#查询指定用户的权限
show grants for 'test';

如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 "grant option"

grant 权限 on 数据库名.表名 to 用户名@'%' with grant option;

mysql服务器中的mysql数据库中与权限相关的表

（1）user 保存了用户名、允许连接数据的IP地址、加密后的密码

（2）db 保存了哪些用户有哪些权限可以操作哪个数据库

（3）tables_priv 保存了哪些用户有哪些权限可以操作哪个表

（4）columns_priv 保存了哪些用户有哪些权限可以操作哪个列

（5）default_roles 保存了每个用户的默认角色

（6）role_edges 保存了角色授予用户的相关信息

角色管理

#创建角色
CREATE role 'only_select_role';
#查询的角色【查询方式与查询用户相同，但角色没有密码】
SELECT user,host,plugin,authentication_string  FROM mysql.user;
#授予角色权限
GRANT 权限名 ON 数据库名.角色名 TO '角色名'
GRANT SELECT ON saas.* TO 'only_select_role';
#创建用户
CREATE USER 'test' IDENTIFIED WITH caching_sha2_password by 'yyt@test'; 
#授予角色给用户
GRANT '角色名' TO '用户名';
GRANT 'only_select_role' TO 'test';
#当授予角色给用户后，用户的角色是没有激活的，该用户角色仍处于none状态，因为一个用户可以有多个角色，因此需要指定默认角色，不然就没有角色
#为用户指定默认角色
SET DEFAULT ROLE '角色名' TO '用户名'
#用户同时使用多个角色
SET DEFAULT ROLE ALL '角色名' TO '用户名'

#查询用户的默认角色
SELECT * FROM mysql.default_roles
#查询用户角色授予用户的相关信息
SELECT * FROM mysql.role_edges;

#查询当前用户的角色
SELECT CURRENT_ROLE();
#查询在某个角色下的指定用户用户的权限
SHOW GRANTS FOR 'test' USING 'only_select_role'

#撤销指定用户用户的角色
REVOKE '角色名' FROM '用户名';
#撤销指定角色的权限
REVOKE '权限名' FROM '角色名';
#删除角色
DROP ROLE '角色名1', '角色名2';
------------------------------------------------------------------------------
user               host    plugin                  authentication_string
only_select_role	%	   caching_sha2_password	

----------------------------------------------------
user               host    plugin                  authentication_string
only_select_role	%	   caching_sha2_password