MySQL 8用户及权限管理

文章目录

参考文档

官方链接：
https://dev.mysql.com/doc/refman/8.0/en/create-user.html

The optional WITH clause is used to enable a user to grant privileges to other users. The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level.

with grant option的意思是：权限赋予/取消是关联的，如将with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，如grant select on table with grant option to A,A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。

最好的学习文档，还是官方文档！！！

查看权限

用户账户复制权限时必须发布的GRANT语句：

SHOW GRANTS;

显示MySQL服务器支持的系统权限清单：

SHOW PRIVILEGES;

安装后，登录测试

先登录，

1. 
cd /usr/local/mysql8/bin
 ./mysql -u root -p
password:输入临时密码
 
2.无密码时登录
./mysql -u root --skip-password

然后执行修改密码与root用户，主机host ip，并刷新权限：

host ip解释：
1）%允许来自任何ip的连接
2）localhost允许本机的连接

# 1. 查询前，必须要修改密码，如新密码123456:
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY '你的新密码';
#刷新权限
flush privileges;
# 首次改密推荐使用本地密码插件 mysql_native_password
 
use mysql;
select user,host,plugin,authentication_string from user;

# 创建用户任意远程访问
CREATE user 'root'@'%';
# 修改密码
alter user 'root'@'%' identified with mysql_native_password by '123456';
 #给用户授权
grant all privileges on *.* to "root"@"%";
#刷新权限
flush privileges;

# 2. 更改具体用户远程访问
# 创建'root'@'127.0.0.1'用户
CREATE USER 'root'@'127.0.0.1' IDENTIFIED with mysql_native_password BY '123456'; 
#===> 记住刷新权限
flush privileges; 

select user,host,plugin,authentication_string from user;
 
===============5.7以后===================
update user set authentication_string=password("test") where user='root';
update mysql.user set host='你要指定的主机ip' where user='root';

============以下5.7以前======================
SET PASSWORD = PASSWORD('123456');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
flush privileges;
select user,host,plugin,authentication_string from user;
============================================

# 退出mysql>
quit;或者exit;

#关闭mysql
shutdown;

# 修改MySQL用户密码
mysqladmin -u用户名 -p旧密码 password 新密码
 
# 或进入mysql命令行
SET PASSWORD FOR '用户名'@'主机' = PASSWORD(‘密码');
flush privileges;

添加帐户，分配特权和删除帐户

use mysql;

#创建新用户及密码
CREATE USER 'root'@'localhost' IDENTIFIED with mysql_native_password  BY '新密码';
FLUSH PRIVILEGES;
CREATE USER 'root'@'%' IDENTIFIED with mysql_native_password  BY '123456';
FLUSH PRIVILEGES;
CREATE USER 'mysql'@'%' IDENTIFIED with mysql_native_password  BY '123456';
CREATE USER 'test'@'%' IDENTIFIED with mysql_native_password  BY '123456';

#给用户授权
GRANT ALL ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'mysql'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'test'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

select user,host,plugin,authentication_string from user;

#撤消test数据库级特权
REVOKE CREATE,DROP ON test.* FROM 'test'@'%';
FLUSH PRIVILEGES;

#删除帐户
DROP USER 'test'@'%';
FLUSH PRIVILEGES;

扩展

注意：
MySQL8.0.4开始，默认身份认证开始改变。
因为之前，MySQL的密码认证插件是“mysql_native_password”，而现在使用的是“caching_sha2_password”。
参考：

https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

当然也支持修改：

编辑my.cnf文件，更改默认的身份认证插件。

vi /etc/my.cnf

在[mysqld]中添加下边的代码
default_authentication_plugin=mysql_native_password

然后重启MySQL
service mysqld restart

创建远程访问新用户并授权

use mysql;
# mysql8 以前：
grant all privileges on *.* to '新用户名'@'%' identified by '密码' with grant option;
grant all privileges on *.* to '新用户名'@'指定ip' identified by '密码' with grant option;
例如:以前使用
grant all privileges on *.* to 'root'@'%' identified by "123456" with grant option;

#mysql8==>不允许<==授权并创建用户了,要分开使用
mysql8分开操作为:
CREATE USER 'root'@'%'; #创建角色CREATE USER 'root'@'%' IDENTIFIED BY '123456';
ALTER USER 'root'@'%' IDENTIFIED with mysql_native_password by '123456'; #修改密码
grant all privileges on *.* to "root"@"%"; #给角色授权
flush privileges; #刷新权限
 
#另外修改主机
update mysql.user set host='具体要指定的主机ip' where user='root';

#重命名
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1'; 

flush privileges; #刷新权限
select user,host,authentication_string from user;