MySQL8用户密码及权限管理

MySQL用户和权限管理

https://juejin.cn/post/7198476152632000567

禁止mysql用户使用特定的密码列表

将不允许使用的密码存储在mysql.user_invalid_passwords表中。

MySQL提供了一个名为mysql.user_invalid_passwords的系统表，用于存储不允许使用的密码列表。您可以将不允许使用的密码添加到该表中，以防止用户使用这些密码。当用户尝试使用该列表中的密码时，MySQL将拒绝该密码并返回“无效的口令”的错误消息。
要将密码添加到mysql.user_invalid_passwords表中，您可以使用INSERT语句。

-- 如果没有该表可以创建---未验证
CREATE TABLE mysql.user_invalid_passwords (
  id INT NOT NULL AUTO_INCREMENT,
  password VARCHAR(1024) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX (password)
);

例如:

-- 禁止mysql用户使用"password123"密码
INSERT INTO mysql.user_invalid_passwords (password) VALUES ('password123');

-- 添加多个
INSERT INTO mysql.user_invalid_passwords (password) VALUES ('password1'), ('password2');

请注意，为了使用mysql.user_invalid_passwords表，您需要具有足够的权限。

在MySQL配置文件中设置validate_password_policy参数

[mysqld]
# 配置MySQL密码强度检查的级别
## LOW 策略要求密码至少长度为8个字符。
## MEDIUM 策略要求密码至少长度为8个字符，包含字母，数字和特殊字符。
## STRONG 策略要求密码至少长度为12个字符，包含大写字母、小写字母、数字和特殊字符。
## INVALID_PASSWORDS 策略要求MySQL不允许使用事先定义的无效口令列表中的任何一项作为密码（例如，123456等常见弱密码）。
# validate_password_policy=LOW, MEDIUM, STRONG, INVALID_PASSWORDS

# 配置MySQL密码强度检查的级别
## STRONG策略要求密码至少长度为12个字符，包含大写字母、小写字母、数字和特殊字符。
validate_password_policy=STRONG
# 密码长度至少为8位
validate_password_length= 8 
# 密码应包含至少一个数字
validate_password_number_count= 1 
# 密码应包含至少一个特殊字符
validate_password_special_char_count= 1 
# 密码应包含至少一个大写或小写字母
validate_password_mixed_case_count= 1 
# 指定了一个自定义的密码字典文件，用于检查密码是否出现在字典中。
validate_password_dictionary_file=dictionary.txt

修改初始化密码

mysql_native_password 是在MySQL5.5.3版本中引入的一种身份验证插件，可以使用原始密码方式来加密、验证密码.

-- 以初始化密码登录mysql
mysql -uroot -p

-- 进入mysql库
use mysql;

-- 修改初始化密码
-- 'localhost'表示本地登录--可以省略
-- IDENTIFIED BY 关键字用于指定用户的密码
-- IDENTIFIED WITH 指定密码插件
-- mysql_native_password 使用旧版密码认证，支持简单密码
alter user 'root'@'localhost' identfiled with mysql_native_password by '新密码';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

创建用户

https://www.cnblogs.com/chanshuyi/p/mysql_user_mng.html

创建本地用户

-- 创建用户不设定密码
-- IF NOT EXISTS若果不存在就创建
CREATE IF NOT EXISTS USER laozhang@'localhost' ;  

-- 创建zhangsan用户并指定密码
CREATE  USER zhangsan IDENTIFIED BY '密码';  

-- 创建zhangsan用户并指定密码--完整命令
-- 'localhost'表示本地登录--可以省略
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '密码';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

创建远程用户

-- 为用户设置远程登录密码

-- 创建远程用户zhangsan并指定密码
CREATE  USER zhangsan@'%' IDENTIFIED BY '密码';  

-- '%'表示从所有IP地址远程登录
-- WITH mysql_native_password 使用旧版密码认证插件--支持简单密码
create user 'root'@'%' identified with mysql_native_password by '新密码';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

查看用户的登录权限

-- 进入mysql库
use msyql;

-- 查看mysql中root用户的权限
select host,user from user where user='root';

创建从指定地址登录的用户

-- 创建一个用户，仅允许从指定IP登录
-- WITH mysql_native_password 使用旧版密码认证插件--支持简单密码
create user 'root'@'192.168.10.100' identified with mysql_native_password by '新密码';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

远程登录测试

# 远程登录测试
mysql -uroot -h 192.167.0.5 -p密码

修改用户密码

https://blog.csdn.net/omaidb/article/details/120022524

修改本地用户密码

-- 修改当前用户的密码--简化语法
alter user user() identified by '新密码';

-- 修改指定用户zhangsan的密码
alter user zhangsan identified by '新密码';

-- 修改指定用户zhangsan的密码--完整版
-- 'localhost'表示本地登录--可以省略
ALTER USER 'zhangsan'@'localhost' IDENTIFIED BY 'new_password';

-- WITH mysql_native_password 使用旧版密码认证插件--支持简单密码
-- 'localhost'表示本地登录--可以省略
ALTER USER 'zhangsan'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

修改密码永不过期

-- 设置root@localhost 的密码永不过期
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; 

修改远程用户密码

https://blog.csdn.net/omaidb/article/details/120022524

-- 进入指定数据库
USE <database_name>;

-- 查看该数据库下的所有用户
SELECT User FROM mysql.user;

-- 修改远程用户密码
ALTER USER '<username>'@'%' IDENTIFIED BY '<new_password>';

-- WITH mysql_native_password 使用旧版密码认证插件--支持简单密码
ALTER USER '<username>'@'%' IDENTIFIED WITH mysql_native_password BY '<new_password>';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

授权管理

授予本地用户权限

-- 为用户授权本地访问所有权限：
-- 'localhost'表示本地登录--可以省略
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- 为zhangsan用户授权所有权限
-- 'localhost'表示本地登录--可以省略
GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost' WITH GRANT OPTION;

-- 授予lisi用户对mydb库的查询权限
-- 'localhost'表示本地登录--可以省略
GRANT SELECT ON mydb.* TO 'lisi'@'localhost';

-- 授予wangwu用户对mydb库的插入和修改权限
-- 'localhost'表示本地登录--可以省略
GRANT INSERT, UPDATE ON mydb.* TO 'wangwu'@'localhost';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

撤销本地用户权限

-- 列出当前授权给本地用户的权限
-- 'localhost'表示本地用户
SHOW GRANTS FOR '<username>';
SHOW GRANTS FOR '<username>'@'localhost';

-- 撤销本地权限：
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

授予远程用户权限

-- 1.先创建好远程用户

-- 指定用户从指定IP登录，并赋予对该数据库的所有权限
-- ALL PRIVILEGES，此处可以换成指定权限
GRANT ALL PRIVILEGES ON <database_name>.* TO '<username>'@'<ip_address>';


-- 为zhangsan用户授权所有权限
-- '%'表示远程登录
GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'%' WITH GRANT OPTION;

-- 授予lisi用户对mydb库的查询权限
-- '%'表示远程登录
GRANT SELECT ON mydb.* TO 'lisi'@'%';

-- 授予wangwu用户对mydb库的插入和修改权限
-- '%'表示远程登录
GRANT INSERT, UPDATE ON mydb.* TO 'wangwu'@'%';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

为root远程用户授权所有权限

# 先创建root远程用户
CREATE USER root@'%' IDENTIFIED BY '密码';  

# 为root远程用户授权所有权限
## *.*表示所有库的所有表
GRANT ALL ON *.* TO 'root'@'%' ;

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

撤销远程用户的权限

-- 列出当前授权给远程用户的权限
-- '%'表示远程登录
SHOW GRANTS FOR '<username>'@'%';

-- 撤销权限语法
-- '%'表示远程登录
REVOKE 权限1,权限2 ON <database_name>.* FROM '<username>'@'%';

-- 撤销zhangsan用户对于mydb库的插入和修改权限
-- '%'表示远程登录
REVOKE INSERT, UPDATE ON mydb.* FROM 'zhangsan'@'%';

-- 撤销wangwu用户对于mydb库的查询权限
-- '%'表示远程登录
REVOKE SELECT ON mydb.* FROM 'wangwu'@'%';

-- 撤销zhangsan用户对于mydb库的所有权限
-- '%'表示远程登录
REVOKE ALL PRIVILEGES ON mydb.* FROM 'lisi'@'%';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

删除用户

删除本地用户

-- 删除本地用户zhangsan：
DROP USER 'zhangsan'@'localhost';

-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

删除远程用户

-- 查看所有用户 
SELECT User, Host FROM mysql.user;


-- 删除指定远程用户
-- '%' 表示从远程登录
DROP USER '<username>'@'%';


-- 应用配置--将内存中的配置写入磁盘
FLUSH PRIVILEGES;

锁定用户

-- 锁定数据库中的用户账号
ALTER USER baduser@hostname  ACCOUNT LOCK; 

将指定的MySQL用户设置为不可登录

-- 将指定的 MySQL 用户设置为不可登录
-- ALTER USER: 表示要修改一个 MySQL 用户的属性；
-- IDENTIFIED WITH mysql: 使用 "mysql_native_password" 身份验证插件来进行身份验证。
-- no login: 表示该用户不能登录到 MySQL 服务器。
ALTER USER baduser@hostname  IDENTIFIED WITH mysql  no login;

自动为所有用户授予所有角色

MySQL 8.0 的语句 SET PERSIST mandatory_roles=ALL; 用于将系统变量 mandatory_roles 的值设置为 ALL，并使更改在服务器重启时持久化。

mandatory_roles 系统变量指定了必须启用的角色列表，以便任何客户端成功连接。将其设置为 ALL，表示要求所有在服务器实例中定义的角色必须启用才能成功连接客户端。

请注意，只有具备 SUPER 特权的用户才能执行此语句。

-- 自动为所有用户授予所有角色
SET PERSIST mandatory_roles=ALL;

精细授权

-- 授予用户 'jsmith'@'%' 对于数据库 'world' 中的表 'country' 的 'name' 列进行更新的权限
-- grant:   用于授权用户或角色访问特定的数据库或表。
-- update(name):   授予对于 name 列进行更新的权限。
-- on 'world';country':   授权操作发生在名为 'world' 的数据库中的 'country' 表上。
-- to 'jsmith'@'%':   授权给用户名为 'jsmith' 而且允许从任何主机 (%) 访问数据库。
GRANT UPDATE (列名) ON 指定库名.表名 TO '用户名' @'%';

查看权限