权限管理
grant权限管理
向用户开放全局，某个库及库内的表，特定的某个库内的某个表的权限时，使用grant
格式
grant 权限 on 库.表 to ‘用户’@‘允许登录的主机’ identified by ‘密码’;
实例
-
grant all (privileges) on . to ‘root’@‘%’ identified by ‘123’;
向root用户开放所有库的所有表的所有权限，并允许使用所有主机登录
-
grant select on school.* to school@‘192.168.98.12’ identified by ‘123’;
向school用户开放school库以及该库内所有的表的查看的权限，只允许192.168.98.12主机登录
-
grant select,insert on school.xueke to xueke@‘192.168.98.%’ identified by ‘123’;
向xueke用户开放school下的xueke表查看，增加权限，只允许192.168.98.0/24网段内的主机登录
注意
-
在使用grant进行授权时，如果授权的用户不存在，将会同时创建该用户
-
库.表 ：*.*表示所有库的所有表
-
权限种类：所有权限all (privileges)；增insert；删drop；删delete；改alter；查select…
使用all privileges时，privileges可以省略
-
insert，drop，delete，alter，select等权限可以在同一条授权语句中使用逗号隔开同时授权给同一个用户
-
用grant给普通用户所有权限（all privileges）时，并不会给普通用户授权权限（Grant_priv）
-
完成授权后，需要使用 flush privileges ，使授权生效
-
所有的用户创建后都会在全局授权表（mysql.user）中留下记录，其次会在对应授权等级的授权表中留下记录
-
允许登录的主机
localhost：仅允许本机（本地）登录，root用户默认为localhost，仅允许从本地登录
%：允许所有的主机登录
192.168.98.12：仅允许192.168.98.12登录
192.168.98.%：仅允许192.168.98.0/24网段内的主机登录
insert权限管理
向用户开放特定的某个库内的某个表的某个字段的权限时，使用insert
格式
insert into mysql.columns_priv(host,user,db,table_name,column_name,column_priv) values(‘允许登录主机’,‘用户名’,‘库名’,‘表名’,‘字段名’,‘权限’);
权限级别
Global level：系统级，所有库，所有表的权限，对应权限表 mysql.user
Database level：某个数据库中的所有表的权限，对应权限表 mysql.db
Table level：库中的某个表的权限，对应权限表 mysql.tables_priv
Column level：表中的某个字段的权限，对应权限表 mysql.columns_priv
procs level：某个存储过程的权限
proxies level：代理服务器的权限
查看权限记录表
因为超级管理员默认已经设置；所以直接查询权限即可
Global level
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: *B1DD4ADE47888D9AEC4D705C85230F1B52D2A817
password_expired: N
password_last_changed: 2022-09-25 14:44:38
password_lifetime: NULL
account_locked: N
字段介绍：
用户字段：root
权限字段：Select_priv
安全字段：*B1DD4ADE47888D9AEC4D705C85230F1B52D2A817
Select_priv：查询权限
Insert_priv：插入权限
Update_priv：更新权限
Delete_priv：删除权限
......
Database level
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
测试库权限：
mysql> create database t1;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on t1.* to 't1'@'localhost' identified by 'QianFeng@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看：
mysql> select * from mysql.db\G
*************************** 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)
验证：
[root@xingdian ~]# mysql -u t1 -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| t1 |
+--------------------+
2 rows in set (0.00 sec)
Table level
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: 2022-09-25 14:40:58
Table_priv: Select
Column_priv:
2 rows in set (0.00 sec)
创建库表验证：
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 'QianFeng@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table u2(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+--------------+
| Tables_in_t2 |
+--------------+
| u1 |
| u2 |
+--------------+
2 rows in set (0.00 sec)
权限查看：
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代表权限成功）
[root@xingdian ~]# mysql -u t2 -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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
[root@xingdian ~]# mysql -uroot -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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: 2022-09-25 15:34:05
Column_priv: Select
1 row in set (0.00 sec)
注意：
前提是有库，有表，有权限
用户管理
登录和退出
mysql -h 192.168.18.160 -P 30042 -u root -pmysql -e "show databases;"
mysql -h 192.168.18.160 -P 30042 -u root -pmysql mysql -e "show tables;"
-h 指定主机名 【默认为localhost】
-P MySQL服务器端口 【默认3306】
-u 指定用户名 【默认root】
-p 指定登录密码 【默认为空密码】
此处mysql为指定登录的数据库
-e 接SQL语句 (在脚本中使用)
创建用户
方式一：
使用此种方法创建普通用户，若不指定登录主机，默认所有主机均可登录（%）
mysql> create user user01;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
注意：
该报错是因为密码强度问题，取消密码强度即可创建用户
mysql> create user 'user01'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
方式二：
mysql> grant all on *.* to 'user01'@'localhost' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
删除用户
方式一：
MySQL [(none)]> Drop user user01@'%';
Query OK, 0 rows affected (0.00 sec)
方法二：
MySQL [(none)]> delete from mysql.user where user='user01' AND Host='%';
Query OK, 1 row affected (0.00 sec)
修改密码
方式一：
[root@xingdian ~]# mysqladmin -uroot -p'123' password 'new_password'
//123为旧密码
方式二：
如果普通用户拥有足够的权限，可以通过该方式修改root用户的密码
MySQL [(none)]> update mysql.user set authentication_string=password(123456) where user='user01' And Host='%';
注意：
刷新授权表后生效：flush privileges
自己设置自己密码：
MySQL [(none)]> set password='123';
Query OK, 0 rows affected (0.00 sec)
root用户修改其他用户密码：
方法一：
mysql> SET PASSWORD FOR user3@'localhost'='new_password';
方法二：
UPDATE mysql.user SET authentication_string=password('new_password') WHERE user='user01' AND host='localhost';
查看密码策略
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.00 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
0/LOW：只检查长度
1/MEDIUM：检查长度、数字、大小写、特殊字符
2/STRONG：检查长度、数字、大小写、特殊字符字典文件
validate_password_special_char_count密码至少要包含的特殊字符数
修改密码策略：
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.00 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)
关闭密码策略：
修改配置文件/etc/my.cnf，添加以下参数：
validate_password=off
