04.Mysql-基础入门-用户与权限

# 1.MySQL用户介绍

**用户功能**

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

**用户的组成**

```bash
用户名@'白名单';比如  test@127.0.0.1  test@192.168.10.10是两个不一样的用户

#存在哪里？mysql.user即用户权限表
select user,host from mysql.user;

#用户分类，主要体现在白名单上
地址列表粒度:
- 所有网段:  'dbadmin'@%
- 指定网段:  'dbadmin'@'10.%' or '192.168.%' or '172.21.120.%' 
- 具体IP:  'dbadmin'@'192.168.120.21' 
- 主机名: 'dbamdin'@localhost
```

**用户创建**

- 通过create user语句创建用户
- 在执行CREATE USER或CRANT语句后，MySQL服务器会修改相应的用户权限表，添加或修改用户及权限。

```bash
#8.0版本
create user saos_damin@'%' identified by '3edfud';
grant all privileges on *.* to 'saos_damin'@'%' WITH GRANT OPTION; ;  #赋予超级权限

#5.7版本
grant all privileges on *.* to 'saos_damin'@'%' identified by '3edfud'  ;
```

# 2.MySQL的权限分类及存储

## 2.1**.MySQL用户权限层级**

- 全局权限:对单个MySQL实例有所有库表的访问权限，一般为dba所有

```cpp
# *.* 表示数据库库的所有库和表，ALL对应所有权限,存储在mysql.user表中
GRANT ALL ON *.* TO 'user'@'host'; 
```

- **数据库层级**：数据库权限适用于一个给定数据库中的所有目标，这些权限存储在mysql.db表中。

```bash
GRANT ALL ON test.* TO 'user'@'host';  #对 test库下的所有具有所有权限 
```

- **表层级:** 表权限适用于一个给定表中的所有列，存储 mysql.tables_priv表中。

```bash
GRANT ALL ON mydb.mytable TO 'user'@'host';   #对mydb库的mytable表具有所有权限
```

- **列层级：**列权限使用于一个给定表中的单一列，这些权限存储在mysql.columns_priv表中。

```bash
# test数据库下的mytable表的columne列col1, col2,  col3具有所有权限，很少使用
GRANT ALL (col1， col2， col3)  ON test.mytable TO 'dbmadin'@'127.0.0.1';

#指定权限赋予
grant select(id,name),update(age) on test.mytable to 'dbmadin'@'localhost';
```

## 2.2.**MySQL权限分类**

- **数据权限:** 库、表和字段三种级别
- **管理权限:DBA**使用权限,比如:开关机,创建用户,主从连接
- **结构与程序权限:** 主要是触发器、存储过程、函数以及对表结构更改权限。

![9](https://img.sunrisenan.com/img/2024/03/18/204102042.png)

- 注释
    - Global（Server administration）对应 mysql.user 表
    - Database 对应 mysql.db 表
    - Tables 对应 mysql.tables_priv 表
    - Columns 对应 mysql.columns_priv 表
    - Stored routines 对应 mysql.procs_priv 表

静态权限

![image-20240318204545103](https://img.sunrisenan.com/img/2024/03/18/204547761.png)
![image-20240318204621365](https://img.sunrisenan.com/img/2024/03/18/204623881.png)

## 2.3.**MySQL访问控制**

MySQL访问控制的两个阶段:

- MySQL权限控制主要是集中在5张表中,user,db,tables_priv,columns_priv,procs_priv每次启动会直接加载到内存中
- 用户连接检查阶段
    1. 用户连接时，MySQ server首先从user表里匹配host, user, password，匹配不到则拒绝Mysql client的连接
    2. check mysql.user表的max_connections和max_user_connections，如果超过上限则拒绝连接
    3. check user表的SSL安全连接，配置SSL，检查用户证书
    4. 三个检查通过,MySQL server端与MySQL client端建立connection，连接建立后，当用户执行SQL语句时，执行SQL语句检查。
- 执行SQL语句时的检查
    1. 从user表里检查max_questions和max_updates，超过则拒绝执行SQL。
    2. 检查user表，看是否具有相应的全局性权限，如果有，则执行，没有则继续下一步检查
    3. 接着到db表，看是否具有数据库级别的权限，如果有，则执行，没有则继续下一步检查
    4. 最后到tables_priv, columns_priv, procs_priv表里查看是否具有相应对象的权限

## 2.4.**权限表字段详解**

- **user表**
    - 基于服务器范围的所有权限，
        - 用户拥有服务器中所有数据库的select权限,user表中的Select_priv列为Y
        - 果用户单单只拥有某个一数据库的select权限那么user表中的Select_priv为N
        - 会在DB表中记录一条信息在DB表中的select_priv为Y。

```sql
#只对saos_test库有select权限
create user saos_test@'%' identified by 'test123';
grant  select on *.* to 'saos_test'@'%';

#看下权限，是否在user 中只Select_priv Y
select * from mysql.user where user='saos_test';

#库级别权限，user priv全为N
create user saos_db@'%' identified by 'test123';
grant  select on saos_db.* to 'saos_db'@'%';

select * from mysql.user where user='saos_db'\G
select * from mysql.db where user='saos_db'\G
```

![10](https://img.sunrisenan.com/img/2024/03/18/205314172.png)
![11](https://img.sunrisenan.com/img/2024/03/18/205355318.png)

- **db表:**如果授予一个用户单独某个数据库的权限，就会在db表中记录一条相关信息
- tables_priv表
- columns_priv表
- procs_priv表

# 3.**用户管理**

## 3.1**用户创建**

- 用户创建create user 语句 记录在响应权限表

```sql
#1.正常形式

create user saos_damin@'%' identified by '3edfud';
grant all privileges on *.* to 'saos_damin'@'%' WITH GRANT OPTION; ;  #赋予超级权限

#2.隐式创建
grant all privileges on *.* to 'saos_damin'@'%' identified by '3edfud'  ;

#3.查看用户权限
mysql> show grants for 'saos_damin'@'%';
```

- root 用户创建

```sql
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;

#user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中加载到内存里,不然其他session无法连接。
mysql> flush privileges; 
```

- DML用户创建

```sql
mysql> GRANT UPDATE, DELETE, INSERT, SELECT ON *.* TO 'test'@'%' identified by 'test' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; 
mysql> flush privileges;

# MAX_QUERIES_PER_HOUR， 
# MAX_CONNECTIONS_PER_HOUR 
# MAX_UPDATES_PER_HOUR  
# MAX_USER_CONNECTIONS count  单个用户过多连接数    
## 设置为0表示不限制，一般外包人员设置情况多
```

- 备份用户

```sql
mysql> GRANT SELECT,EVENT,SHOW DATABASES,LOCK TABLES,SUPER,REPLICATION CLIENT ON *.* TO 'backup'@'localhost' identified by 'backup';
mysql> flush privileges;
```

- 备份恢复用户权限

```sql
mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'restore'@'localhost' identified by '123456';
mysql> flush privileges;
```

- 复制用户

```sql
mysql> GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';
mysql> flush privileges;
```

## 3.2.**用户删除（生产环境注意事项）**

- 生产中非常非常需要注意的谨慎操作,极其容易造成失误,一定要准备好回滚语句和检查网段权限后在操作.
- 操作后一定要double check

```sql
#操作前检查用户是否有连接,分析连接池
select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

#删除普通用户
mysql> drop user 'USERNAME'@'HOST';

#操作后,看下用户删除是否正确,尤其是网段
select user,host from mysql.user;

# 删除MySQL默认账户;
mysql> drop user 'root'@'127.0.0.1';
```

## 3.3.**修改用户密码**

- 通过mysqladmin工具

```sql
# 给root@localhost用户登录mysql设置密码为"redhat";
shell> mysqladmin -uqianlong -ptest123 -h127.0.0.1 password "123456";
```

- 通过直接修改mysql.user表的用户记录，5.7版本

```sql
# MySQL 5.7
mysql> update mysql.user set authentication_string=PASSWORD('test123') where user='qianlong';
mysql> flush privileges;
```

- set password语句 5.7版本

```sql
#5.7
mysql> set password for 'qianlong'@'localhost'=PASSWORD('test123');
mysql> flush privileges;
```

- 8.0 密码修改

```sql
alter user 'qianlong'@'localhost'  identified with mysql_native_password by 'test123';
```

## 3.4.**密码找回(课件小练习)**

```sql
#1.修改配置文件
shell>cat /etc/my.cnf 
[mysqld]
--skip-networking
--skip-grant-tables

#2.重启MySQL Server

#3.设置root用户密码
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';

#4.重启MySQL Server
```

## 3.5.**用户权限收回**

> 使用REVOKE收回权限之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号依然在user表中保存。

```sql
# 移除saos_test用户对于saos_ab.*的权限;
Mysql> revoke all on saos_ab.* from 'saos_test'@'%';
# 刷新授权表;
Mysql> flush privileges;
```

## 3.6.权限练习实战

```bash
1. grant 所有权限
mysql> grant all privileges on *.* to 'db_test'@'192.168.%';
mysql>  flush privileges;

2. grant super权限在*.*上(super权限可以对全局变量更改)；
mysql> grant super on *.* to 'db_test'@'192.168.%';
mysql>  flush privileges;

3. grant某个库下所有表的所有权限
mysql> grant all privileges on DB_NAME.* to 'db_test'@'192.168.%';
mysql>  flush privileges;

4. grant某个库下所有表的select权限
mysql>grant select on DB_NAME.* to 'db_test'@'192.168.%';
mysql>  flush privileges;

5. grant某个库下某个表的insert权限
mysql> grant insert on  DB_NAME.TABLE_NAME to 'db_test'@'192.168.%'';
mysql>  flush privileges;

6. grant某个库下某个表的update权限
mysql>grant update on DB_NAME.TABLE_NAME to 'db_test'@'192.168.%';
mysql>  flush privileges;

7. grant某个库下某个表的某个字段update权限
mysql> grant update(COLUMN_NAME)  on DB_NAME.TABLE_NAME to 'db_test'@'192.168.%';
mysql>  flush privileges;

8.通过GRANT语句中的USAGE权限，可以创建账户而不授予任何权限
mysql> grant usage on *.* to 'db_test'@'192.168.%';
mysql> flush privileges;

9. grant创建、修改、删除MySQL数据表结构权限
mysql> grant create on testdb.* to developer@'192.168.0.%';
mysql> grant alter on testdb.* to developer@'192.168.0.%';
mysql> grant drop on testdb.* to developer@'192.168.0.%';
mysql>  flush privileges;

10. grant操作MySQL外键权限
mysql> grant references on testdb.* to developer@'192.168.0.%';
mysql>  flush privileges;

11. grant操作MySQL临时表权限
mysql> grant create temporary tables on testdb.* to developer@'192.168.0.%';
mysql>  flush privileges;

12. grant操作MySQL索引权限
mysql> grant index on testdb.* to developer@'192.168.0.%';
mysql>  flush privileges;

13.grant操作MySQL视图、查看视图源代码权限
mysql> grant create view on testdb.* to developer@'192.168.0.%';
mysql> grant show view on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;

14. grant操作MySQL存储过程、存储函数权限
mysql> grant create routine on testdb.* to developer@'192.168.0.%';
mysql> grant alter routine on testdb.* to developer@'192.168.0.%';
mysql> grant execute on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;

15.PROXY特殊权限如果想让某个用户具有给他人赋予权限的能力，那么就需要proxy权限了。当你给一个用户赋予all权限之后，你查看mysql.user表会发现Grant_priv字段还是为N，表示其没有给他人赋予权限的权限。查看一下系统默认的超级管理员权限：

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以看到其本身有PROXY权限，并且这个语句跟一般授权语句还不太一样。所以如果想让一个远程用户有给他人赋予权限的能力，就需要给此用户PROXY权限，如下：

mysql> grant all on *.* to 'test'@'%' identified by 'helloWORD';
mysql> GRANT PROXY ON ''@'' TO 'test'@'%' WITH GRANT OPTION;
mysql> flush privileges;

16.生命周期用户权限
ALTER USER 'qianlong'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;‘

17.用户锁定,锁定后登入错误 提示Account is locked.
ALTER USER 'qianlong'@'localhost' ACCOUNT LOCK; 
```