mysql my.ini、登录、用户相关操作、密码管理、权限管理、权限表，角色管理

my.ini 配置文件格式

登录mysql

mysql -h hostname | IP -P port -u username -p database -e “select 语句”；

创建用户、修改用户、删除用户

create user ‘zen’ identified by ‘密码’ ## host 默认是 %

create user ‘zen’@‘localhost’ identified by ‘密码’

update user set user=‘zen123’ where user=‘zen’; # 如何有多个用户，需要家 and host=‘xxx’ 条件, 最后别忘记 Flush privileges ;

删除用户

方式1
drop user user1[,…usern ] ;
此处的user1: 用用户名和主机名组成。 zen@localhost
支持同时删除多个用户，用逗号隔开。
drop user zen ； drop user ‘zen’ ； # 默认是删除 host=%的用户
drop user ‘zen‘@’localhost’
flush privileges； 把权限表 重新加载到内存，让不退出登录，权限便可生效。
方式2
delete from mysql.user where Host=‘xxxx’ and user=‘xxxx’; ### host 和user 两个字段联合主键。
flush privileges;

建议
不推荐通过delete from user 进行删除，系统会有残留信息保留
drop user 命令删除用户以及对应的权限，执行命令后你会发现 mysql.user 表和 mysql.db 表的相应记录都消失了

修改密码

alter 和 set

root用户修改指定普通用户密码

alter user u identified by ‘密码’；
u: 由 用户名和主机名构成 ‘zen’@‘localhost’

set password for u=‘密码’
u：由 用户名和主机名构成 ‘zen’@‘localhost’

修改当前用户密码

alter user user() idenfited by ‘密码’;
user(): 返回是 当前登录用户的 用户名@主机名

密码管理

密码管理功能只针对使用基于MySQL授权插件的账号
这些插件如下：
mysql_native_password
sha256_password
caching_sha2_password

密码过期策略
手动设置 账号密码过期，也可以建立一个 自动 密码过期策略
过期策略 可以是 全局 也可以为 每个账号 设置单独的过期策略

手动设置立马过期
alter user ’zen‘@‘localhost’ password expire；

>

手动设置指定时间过期方式1 全局
default_password_lifetime 系统变量建立全局密码过期策略
默认值 0 ： 禁用自动密码过期
允许正整数N，表示允许的密码生存期。密码必须 每隔N天 进行修改

set persist default_password_lifetime=180; ### 没写 global，默认是 session， 建立全局策略，设置密码每个180天过期

配置文件 my.ini 全局 ###重启才能生效
[mysqld]
default_password_lifetime=180

单独设置 指定用户 过期策略
create user ‘xxx’@‘%’ identified by ‘密码’ password expire interval 90 DAY；

修改指定用户 密码过期时间
alter user ‘xxx’@‘%’ password expire interval 100DAY；

###设置密码永不过期
create user ‘XXX’@‘localhost’ password expire never
alter ser ‘XXX’@‘localhost’ password expire never

延用全局密码过期策略

create user ‘XXX’@‘localhost’ password expire default
alter ser ‘XXX’@‘localhost’ password expire default

密码重用策略

mysql限制使用已用过的密码。 重用限制策略基于 密码更改的数量和使用的时间，
重用策略可以是 全局 的，也可以为每个账号设置 单独的策略

mysql 使用 password_history 和 password_reuse_interval 系统变量设置密码重用策略
password_history : 规定密码重用的数量
password_reuse_interval: 规定密码重用的周期
这两个值 可以通过 my.ini 中维护 也可以在运行期间 通过 sql语句更改 变量的值 并持久化 persist

方式1 使用sql
set persist password_hostiry=6; # 设置不能选择最近使用过的6个密码
set persist password_reuse_interval=365; # 设置不能选择最近一年内的密码

方式2 my.ini
[mysqld]
password_history=6 # 设置不能选择最近使用过的6个密码
password_reuse_interval=365 # 设置不能选择最近一年内的密码

手动设置密码重用方式2：单独设置 ，上面 设置 都是 全局的
create user ‘xx’@‘localhost’ password history 5;
alter user ‘xx’@‘localhost’ password history 5;

create user ‘xx’@‘localhost’ password resuse interval 365 day;
alter user ‘xx’@‘localhost’ password resuse interval 365 day;

既不能使用最近5个密码，也不能使用365天内的密码
create user ’XXX‘ @’localhost‘
identified by ’密码‘
password history 5
reuse interval 365；

alter user ’XXX‘ @’localhost‘
password history 5
reuse interval 365 day；

延后全局策略
create user ’XXX‘ @’localhost‘
identified by ’密码‘
password history default
reuse interval default；

alter user ’XXX‘ @’localhost‘
password history default
reuse interval 365 default；

授权

语法格式：
grant 权限列表 on 库名.表名 to 用户名@‘客户端主机’ ### 当未查询到用户名时会自动创建
[identified by ‘密码’] [with grant option];
grant all privileges on . to root@“IP地址” identified by ‘密码’ with grant option；
all privileges: 表示所有权限。
identified by：指定用户的登录密码
with grant option表示该用户可以将自己拥有的权限授权给别人。

grant 权限叠加
grant select on zen.employee to ‘xx’@’%‘ ；
grant update on zen.employee to ‘xx’@’%‘ ；
所以 zen 用户 拥有 select 和update 权限

– 查看当前用户（自己）权限
show grants;
show grants for current_user;
show grants for current_user();

查看指定用户的权限
show grants for dba@localhost;

权限收回

revoke 收回权限之后，用户账户的记录将从 db、host、tables_priv和 columns_priv 表中删除，但用户账户记录仍然存在 user表中
要删除user表中记录 需要 drop user语句

revoke all privileges on . from dba@localhost;
revoke 权限列表 on 数据库.表 from 用户名@localhost;
回收之后，对应用户需要重新进入。

权限表

1. user xxx_priv 是针对所有数据库的权限
   grant select on . to zen@localhost ; user表中 Select_priv 就变成 Y啦

2. db 是针对具体的数据库权限
   grant select on test.* to zen@localhost ; user表中 Select_priv 就变成 Y啦
   
   

3. tables_priv
   

4. columns_priv
   grant select(列1，列2) on test.表 to zen@localhost
   

mysql请求的过程

一般软件针对权限都分2个阶段

1. 登录权限 只可以登录，不能操作系统
2. 操作权限 可以操作授权的模块。

当mysql允许一个用户执行各种操作时，它将 ①首先 核实该用户向MySQL服务发送的连接请求 ②确认用户的操作请求是否被允许。
这个过程 mysql 称为 访问控制过程，
mysql 访问控制过程分2个阶段

1. 连接核实阶段 等价于 登录权限阶段
2. 请求核实阶段 等价于 操作权限阶段

连接核实阶段
客户端用户连接请求中 提供 用户名，主机地址 ，用户密码 =》 mysql -hlocalhost -uroot -p
请求到达 mysql服务器时， 会于 mysql中的 user表 中的 user、host、authentication_string 三个字段匹配客户端信息

当连接核实阶段，通过，则进入 第二个阶段，即 请求阶段，否则 拒绝

请求核实阶段

连接请求核实通过后， 对此连接上进来的每个请求，服务器检查该请求要执行什么操作，是否有足够的权限来执行。这正是需要权限表中的权限列发挥作用的地方。这里的权限可以来自 user,db,tables_priv,columns_priv 等

确认权限时，是有顺序的， 从大权限到小权限
①mysql首先检查user表，如果没有指定的权限
②那么MySQL就会继续检查db表 db表是下一层安全层级，其中的权限限定于数据库层级，在该层级的select权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，
③那么mysql就会继续 检查tables_priv表 ，tables_priv表是下一层安全层级，其中的权限限定于表的层级，在该层级的select权限允许用户查看指定表中的（所有列）数据；如果在该层级没有找到限定的权限，
④那么mysql就会继续 检查columns_priv表 ，

如果所有权限表都检查完毕，但还是没有找到允许的权限操作，mysql 将 返回错误信息 ，用户请求的操作不能执行，操作失败

角色管理

创建角色

create [IF NOT EXISTS] role ‘role1’[@‘xxx’, role2@‘xxx’, …rolen@‘xxx’] ;
create role admin ; ### 如果不写主机名，mysql默认是 %，意思这个角色对应的账号，可以在任何一台主机上登录数据库
多个角色中间用 逗号 隔开
create role ’app_read’@‘IP地址’ ，‘app_check’ ;
给角色授权，查看角色权限
show privileges\G 查看权限清单

GRANT role [, role] 数据库.表 TO user_or_role [, user_or_role] …[WITH ADMIN OPTION]

grant 权限列表 on 数据库.表 to ‘role_name’[@‘host_name’] ;
grant select on . to ‘devp’;
show grants for ’角色‘；

删除角色

删除了角色，那么用户也失去了通过这个角色获得的所有权限
drop role ‘role1’[@host_name’ ,…,‘rolen’@‘host_name’];

用户赋予角色

grant role [, role2,…] TO user1[, role2…] ;

激活角色

你用赋予角色的用户去登录，发现没有对应的权限，这是因为mysql 中创建的角色之后，默认都是没有激活的，也不能用，
必须手动激活，才能获取对应权限

激活
激活可以自己激活，不一定需要用root激活
方式1
– 设置默认角色
SET DEFAULT ROLE ‘devp’ TO ‘migo’@‘%’;
语法
SET DEFAULT ROLE {NONE | ALL | role [, role ] …} TO user [, user ] …
如果已经登录，需要退出，再登录才生效

方式2
将 active_all_roles_on_login 设置 on
查看 show variables like ’active_all_roles_on_login‘;

设置
set global active_all_roles_on_login=ON; # 对所有角色永久激活

方式3 my.ini
#设置角色激活开启
[mysqld]
activate-all-roles-on-login=ON

撤销用户的角色、撤销用户的权限,撤销角色的权限

撤销用户的角色
revoke [IF EXISTS] 角色[,角色n] from 用户[@host_name, 用户n@host_name];
撤销用户的权限
revoke [IF EXISTS] 权限列表 ON 数据库.表 from 用户[@host_name, 用户n@host_name];

撤销角色的权限
revoke 权限列表 on 数据库.表 from role1[@‘host_name’,…rolen@host_name];

设置强制角色（mandatory role）

强制（mandatory）角色 是给每个创建的用户默认的角色，不需要手动设置的，
强制角色 无法被回收 revoke 和 删除 drop

方式1 服务启动前设置
mg.ini
[mysqld]
mandatory_roles=‘role1,role2@localhost,role3@%’;
方式2 运行时设置 persist 持久性 重启服务 仍然生效
set persist mandatory_roles=‘role1,role2@localhost,role3@%’;
重新生效
set global mandatory_roles=‘role1,role2@localhost,role3@%’;