1 用户的功能

登录数据库
管理数据库对象

2 用户的组成

—2.1 组成方式

用户名@'白名单'
oldguo@'%'
oldguo@'10.0.0.1'
oldguo@'10.0.0.%' 24掩码 1-254
oldguo@'10.0.0.5%' 50-59
oldguo@'localhost' 数据库本地socket

—2.2 白名单格式

地址列表:
%
10.0.0.10
10.0.0.%
10.0.0.5%
10.0.0.0/255.255.254.0
oldguo.com
db01
127.0.0.1
localhost ----> socket

3 用户的管理

—3.1 创建用户

create user oldguo@'10.0.0.%' identified by '123';

—3.2 查询用户

mysql> select user,host,authentication_string,plugin from mysql.user;

mysql> desc mysql.user
| authentication_string    | text   | YES  |     | NULL   |       |

—3.3 删除用户(不代表生产操作)

drop user oldguo@'10.0.0.%';
drop user test@'10.0.0.%';
mysql> select user,host from mysql.user where (user='' or host='' or
authentication_string='') and user!='root';

+-------+-----------+

| user | host |

+-------+-----------+

| test1 | |

| | localhost |

+-------+-----------+
2 rows in set (0.01 sec)
mysql> drop user test1@'';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user ''@'localhost';
Query OK, 0 rows affected (0.01 sec)

—3.4 修改用户

alter user oldguo@'10.0.0.%' identified with mysql_native_password by '123456';

—3.5 用户资源管理

——3.5.1 密码过期时间

select @@default_password_lifetime;
SET PERSIST default_password_lifetime = 180;
SET PERSIST default_password_lifetime = 0;
CREATE USER 'oldguo'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'oldguo'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
CREATE USER 'oldguo'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'oldguo'@'localhost' PASSWORD EXPIRE NEVER;
FAILED_LOGIN_ATTEMPTS N

——3.5.2 密码重用

password_history=6
password_reuse_interval=365

——3.5.3 锁定用户

alter user 'old_app_dev'@'localhost' account lock; 

——3.5.4 查看用户是否被锁定

select user,host,authentication_string,plugin,account_locked from mysql.user;

——3.5.5 解锁用户

alter user 'old_app_dev'@'localhost' account unlock; 

——3.5.6 连接资源限制

with
MAX_QUERIES_PER_HOUR count
MAX_UPDATES_PER_HOUR count
MAX_CONNECTIONS_PER_HOUR 2000;
MAX_USER_CONNECTIONS count

4 忘记root管理密码

关闭数据库
/etc/init.d/mysqld stop

安全模式启动数据库
[root@db01 data]# mysqld_safe --skip-grant-tables --skip-networking &

登陆数据库
mysql

刷新授权表
flush privileges;

修改密码
mysql> alter user root@'localhost' identified with mysql_native_password by
'123';

重启数据库到正常模式
[root@db01 data]# /etc/init.d/mysqld restart

5 MySQL8.0用户方面的新特性

—5.1 在8.0中不支持grant直接创建用户并授权，必须先建用户后grant授权。

mysql> grant all on *.* to test1@'10.0.0.%' identified by '123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near 'identified by '123'' at line 1

mysql> create user test1@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.02 sec)
mysql>  grant all on *.* to test1@'10.0.0.%' ;
Query OK, 0 rows affected (0.00 sec)

—5.2 密码插件,在8.0中替换为了 aching_sha2_password模式

客户端工具，navicat 、 sqlyog工具不支持（无法连接）
主从复制，MGR高可用 ，不支持新的密码插件的用户，老的驱动无法连接数据库
mysql_native_passwor老的密码加密插件兼容老版本

解决方法：
create user test@'10.0.0.%' identified with mysql_native_password by '123';
create with mysql_native_password
alter with mysql_native_password
vim /etc/my.cnf
default_authentication_plugin=mysql_native_password

6 用户权限

—6.1 介绍

用户基础权限 ,grant命令
8.0后出现了角色：role ,即基础权限集合

—6.2 权限级别

GRANT 权限 ON 权限级别 TO 用户;

级别：
*.* : 全库级别,一般是管理员使用，在mysql.user表存储 
test.* : test单库级别，一般是业务层面使用，在mysql.db 表存储
test.t1 : 单表级别，在mysql.tables_priv 表存储
select(id,name) : 列级别 ---> 在mysql.columns_priv表存储

—6.3 权限列表

mysql> show privileges;

Alter
Alter routine
Create
Create routine
Create role
Create temporary tables
Create view
Create user
Delete
Drop
Drop role
Event
Execute
File
Grant option
Index
Insert
Lock tables
Process
Proxy
References
Reload
Replication client
Replication slave
Select
Show databases
Show view
Shutdown
Super
Trigger
Create tablespace
Update

—6.4 普通权限授权

mysql> grant all on *.* to test@'10.0.0.%' ;
mysql> grant select ,update ,delete ,insert on *.* to oldguo@'10.0.0.%' ;
mysql> grant select ,update ,delete ,insert on test.* to test@'10.0.0.%';
mysql> grant select(id) on test.t1 to user1@'10.0.0.%';

创建主从复制相关用户
mysql> create user repl@'10.0.0.%' identified with mysql_native_password by '123';
mysql> grant replication slave,replication client on *.* to repl@'10.0.0.%';

—6.5 角色创建及授权

mysql> create role dev@'10.0.0.%'; 创建角色
mysql> grant select on *.* to dev@'10.0.0.%'; 给角色授权
mysql> grant dev to user2@'10.0.0.%'; 把角色授权给用户
mysql> select * from mysql.role_edges; 查看角色信息
mysql> select * from information_schema.user_privileges; 查看角色信息

—6.6 生产中用户类型规范

开发用户 ---》 开发库
测试人员 ---》 测试库
开发人员---》 SQL 审核平台（管理员） ----》 生产库
管理员 : ALL (权限中不包含 Grant option)
开发:Create ,Create routine,Create temporary tables,Create view,

Show view,

Delete ,Event ,Execute,Insert ,
    References,Select,Trigger,Update
监控 : select , replication slave , client supper
备份 : Select 、SHOW DATABASES、 PROCESS LOCK TABLES 、RELOAD
主从 : replication slave，replication client
业务 : insert , update , delete ,select

—6.7 查看权限

show grants for test@'10.0.0.%' ;
查询权限表
mysql> select * from mysql.user where user='repl' \G

—6.8 回收权限

mysql> revoke delete on *.* from oldguo@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for oldguo@'10.0.0.%';
mysql> revoke select(id) on test.t1 from user1@'10.0.0.%';