596 lines
16 KiB
Markdown
596 lines
16 KiB
Markdown
<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
|
||
```
|
||
|