<h2><center>权限管理</center></h2>

------

## 一：权限管理

### 1. 权限级别

- Global level：系统级，所有库，所有表的权限
- Database level：某个数据库中的所有表的权限
- Table level：库中的某个表的权限
- Column level：表中的某个字段的权限
- procs level：某个存储过程的权限
- proxies level：代理服务器的权限

### 2. 查看权限记录表

因为超级管理员默认已经设置；所以直接查询权限即可

#### Global level

```SQL
mysql> select * from mysql.user\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *AD894C69730A54BD7AB4A411AC954632A005973A
      password_expired: N
 password_last_changed: 2025-03-09 17:33:44
     password_lifetime: NULL
        account_locked: N
*************************** 2. row ***************************
                  Host: localhost
                  User: mysql.session
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: Y
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2025-03-09 17:31:55
     password_lifetime: NULL
        account_locked: Y
*************************** 3. row ***************************
                  Host: localhost
                  User: mysql.sys
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2025-03-09 17:31:55
     password_lifetime: NULL
        account_locked: Y
3 rows in set (0.00 sec)
```

字段介绍：

```
用户字段：root
权限字段：Select_priv
安全字段：*B1DD4ADE47888D9AEC4D705C85230F1B52D2A817

Select_priv：查询权限
Insert_priv：插入权限
Update_priv：更新权限
Delete_priv：删除权限
......
```

#### Database level

```SQL
mysql> select * from mysql.db\G
*************************** 1. row ***************************
                 Host: localhost
                   Db: performance_schema
                 User: mysql.session
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: localhost
                   Db: sys
                 User: mysql.sys
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: Y
2 rows in set (0.00 sec)
```

测试库权限：

```SQL
mysql> create database t1;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on t1.* to 't1'@'localhost' identified by 'Wdsji@213';
Query OK, 0 rows affected, 1 warning (0.00 sec)
```

查看：

```SQL
*************************** 3. row ***************************
                 Host: localhost
                   Db: t1
                 User: t1
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
3 rows in set (0.00 sec)
```

验证：

```SQL
[root@mysql ~]# mysql -ut1 -p'Wdsji@213';
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| t1                 |
+--------------------+
2 rows in set (0.00 sec)
```

#### Table level

```SQL
mysql> select * from mysql.tables_priv\G
*************************** 1. row ***************************
       Host: localhost
         Db: mysql
       User: mysql.session
 Table_name: user
    Grantor: boot@connecting host
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select
Column_priv: 
*************************** 2. row ***************************
       Host: localhost
         Db: sys
       User: mysql.sys
 Table_name: sys_config
    Grantor: root@localhost
  Timestamp: 2025-03-09 17:31:55
 Table_priv: Select
Column_priv: 
2 rows in set (0.00 sec)
```

创建库表验证：

```SQL
mysql> create database t2;
Query OK, 1 row affected (0.00 sec)

mysql> use t2;
Database changed
mysql> create table u1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into u1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> grant all on t2.u1 to 't2'@'localhost' identified by 'Wxhfua@3414';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table u2(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------+
| Tables_in_t2 |
+--------------+
| u1           |
| u2           |
+--------------+
2 rows in set (0.00 sec)
```

权限查看：

```SQL
mysql> select * from mysql.tables_priv\G
*************************** 3. row ***************************
       Host: localhost
         Db: t2
       User: t2
 Table_name: u1
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger
Column_priv: 
3 rows in set (0.00 sec)
```

验证：（登录t2账户，看到u1表，看不到u2代表权限成功）

```SQL
[root@mysql ~]# mysql -ut2 -p'Wxhfua@3414'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| t2                 |
+--------------------+
2 rows in set (0.00 sec)

mysql> use t2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_t2 |
+--------------+
| u1           |
+--------------+
1 row in set (0.00 sec)
```

#### Column level

```SQL
mysql> select * from mysql.columns_priv\G
Empty set (0.00 sec)

mysql> insert into mysql.columns_priv(host,db,user,table_name,column_name,column_priv) values('%','t2','t2','u1','id','select');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql.columns_priv\G
*************************** 1. row ***************************
       Host: %
         Db: t2
       User: t2
 Table_name: u1
Column_name: id
  Timestamp: 2025-03-11 19:37:00
Column_priv: Select
1 row in set (0.00 sec)
```

注意：

​	前提是有库，有表，有权限

## 二：用户管理

### 1. 登录和退出

```bash
[root@mysql ~]# mysql -h 192.168.18.160 -P 3306 -u root -pmysql -e "show databases;"
[root@mysql ~]# mysql -h 192.168.18.160 -P 3306 -u root -pmysql mysql -e "show tables;"
```

### 2. 创建用户

方式一：

```SQL
mysql> create user wxin;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
注意：
	该报错是因为密码强度问题，取消密码强度即可创建用户
	
mysql> create user wxin@'%' identified by 'Wfsf@31241';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
```

方式二：

```SQL
mysql> grant all on *.* to 'Wxin'@'localhost' identified by 'Wfsf@31241';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
```

注意：

​	该方式采用授权的方式

​	ALL		所有权限		select		单独某一个权限（多个权限用逗号隔开）

```SQL
mysql> grant select on *.* to 'Wxin'@'localhost' identified by 'Wfsf@31241';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
```

```
*.*		所有的库所有的表   也可以单独某一个库某一个表
```

```
root@localhost  用户有则授权无则创建   localhost % 10.19.40.% 10.19.40.11
```

### 3. 删除用户

方式一：

```SQL
mysql> drop user wxin@'%';
Query OK, 0 rows affected (0.00 sec)
```

方式二：

```SQL
ysql> delete from mysql.user where user='Wxin' and Host='%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
```

### 4. 修改密码

方式一：

```SQL
[root@mysql ~]# mysqladmin -uroot -p'Wxin@123456' password 'Wxin@123'
```

方式二：

```SQL
mysql> update mysql.user set authentication_string=password(123456) where user='Wxin' and Host='%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1
mysql> flush privileges;
```

注意：

​	刷新授权表后生效：flush privileges

自己设置自己密码：

```SQL
mysql> set password='Wxin@123';
Query OK, 0 rows affected (0.00 sec)
```

root用户修改其他用户密码：

方法一：

```SQL
mysql> set password for wxin@'localhost'='Wxin@123456';
Query OK, 0 rows affected (0.00 sec)
```

方式二：

```SQL
mysql> update mysql.user set authentication_string=password('Wxin@123') where user='wxin' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
```

### 5. 查看密码策略

```SQL
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
```

参数解释：

- validate_password_dictionary_file 指定密码验证的文件路径
- validate_password_length  密码最小长度
- validate_password_mixed_case_count  密码至少要包含的小写字母个数和大写字母个数
- validate_password_number_count  密码至少要包含的数字个数
- validate_password_policy 密码强度检查等级，对应等级为：0/LOW、1/MEDIUM、2/STRONG,默认为1
  1. 0/LOW：只检查长度
  2. 1/MEDIUM：检查长度、数字、大小写、特殊字符
  3. 2/STRONG：检查长度、数字、大小写、特殊字符字典文件
- validate_password_special_char_count密码至少要包含的特殊字符数

修改密码策略：

```SQL
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 4      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
```

关闭密码策略：

```SQL
修改配置文件，添加以下参数：
[root@mysql ~]# vim /etc/my.cnf
validate_password=off
```