mysql安全机制
- 登录
- 授权(创建)用户
- 回收权限
- 删除用户
- 修改密码
登录
发现远程访问时无法登录mysql
[root@VM_0_15_centos ~]# mysql -h106.54.3.104 -uroot -p'qingdao@123'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '118.24.193.33' is not allowed to connect to this MySQL server
登录参数
- mysql -h192.168.5.240 -P 3306 -u root -p'123' mysql -e 'show table'
| 参数 | 描述 | 默认 |
|---|---|---|
| -h192.168.5.240 | 指定主机名 | 默认localhost |
| -P 3306 | 指定端口 | 默认3306 |
| -u root | 用户 | 默认root |
| -p'123' | 密码 | 默认空密码 |
| mysql | 进入的数据库 | / |
| -e 'show table' | 指定的sql命令 | / |
授权(创建)用户
- mysql库下面存放授权信息
- user表存放----全局权限
- db表存放----库级别权限
- tables_priv表存放----表级别授权
- columns_priv表存放----列级别授权
| user表字段 | 描述 |
|---|---|
| Host | 主机 |
| User | 用户 |
| max_questions | 最大请求数 |
| max_updates | 最大更新数 |
| max_connections | 最大连接数 |
| max_user_connections | 最大用户连接数 |
| authentication_string | 密码 |
| password_expired | 密码过期情况 |
| password_last_changed | 密码上次更新 |
| password_lifetime | 密码生存周期 |
| account_locked | 账户锁 |
给某个用户授权时,如果该用户不存在,则会自动创建该用户.要指定密码
mysql> grant all on *.* to 'yang1'@'118.24.193.33' IDENTIFIED BY 'mypass.123A';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on company.* to 'zhuzhu1'@'118.24.193.33' IDENTIFIED BY 'mypass.123B';
Query OK, 0 rows affected, 1 warning (0.00 sec)
那么上面创建用户的命令都是在会把创建痕迹放在哪里呢?
- mysql> select * from mysql.user\G
- 发现yang1用户的权限是全局级别权限可以在mysql.user表中查看到信息
- mysql> select * from mysql.db\G
- 发现zhuzhu1用户的权限是库级别权限可以在mysql.db表中查看到信息
- 表中Grant_priv: N字段不允许将该权限授权给其他用户
现在通过刚刚建立的两张表以及可以在另一台服务器上访问数据库了
设置的权限不同,能操作的范围也不同
[root@VM_0_15_centos ~]# mysql -h106.54.3.104 -uyang1 -p'mysql.123A'
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| mysql |
| performance_schema |
| school |
| sumyum |
| sys |
| test1 |
+--------------------+
8 rows in set (0.03 sec)
mysql>
[root@VM_0_15_centos ~]# mysql -h106.54.3.104 -uzhuzhu1 -p'mypass.123B'
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
+--------------------+
2 rows in set (0.04 sec)
mysql>
权限管理
grant设置权限
- grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by'密码' with option参数]
| 权限列表 | 描述 |
|---|---|
| all | 所有权限 (不包括授权权限) |
| select,update | 指定权限 |
| 表名库名选项 | 描述 |
|---|---|
| * . * | 所有库下的所有表 |
| web.* | web库下的所有表 |
| web.stu_info | web库下的stu_info表 |
| select(col1),insert(col1,col2) ON web.stu_info | 对列赋予不同的权限 |
| 客户端主机 | 描述 |
|---|---|
| % | 所有主机 |
| 192.168.1.% | 指定网段 |
| 192.168.1.22 | 指定主机 |
| localhost | 指定主机 |
对列赋予不同的权限
mysql> grant select(dept_name,dept_comment),insert(dept_name) on company.department1 to 'admin'@'%' identified by 'qwQW123@#!!';
Query OK, 0 rows affected, 1 warning (0.03 sec)
[root@VM_0_15_centos ~]# mysql -h106.54.3.104 -uadmin -p'qwQW123@#!!'
mysql> use company;
mysql> insert into department1(dept_name, dept_comment) values('tomp','tompfdfreger');
ERROR 1143 (42000): INSERT command denied to user 'admin'@'118.24.193.33' for column 'dept_comment' in table 'department1'
mysql> insert into department1(dept_name) values('tomp');
Query OK, 1 row affected (0.07 sec)
查看权限show grants for
查看当前权限
mysql> show grants\G
Grants for admin@%: GRANT SELECT (dept_comment, dept_name), INSERT (dept_name) ON `company`.`department1` TO 'admin'@'%'
2 rows in set (0.03 sec)
查看指定用户权限
mysql> show grants for 'admin'@'%'\G
Grants for admin@%: GRANT SELECT (dept_comment, dept_name), INSERT (dept_name) ON `company`.`department1` TO 'admin'@'%'
2 rows in set (0.03 sec)
回收权限
回收某一个用户的权限时,可以先查看权限,然后复制,将to改为from
回收全部权限
回收admin
mysql> revoke SELECT (dept_comment, dept_name), INSERT (dept_name) ON `company`.`department1` from 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'admin'@'%'\G
*************************** 1. row ***************************
Grants for admin@%: GRANT USAGE ON *.* TO 'admin'@'%'
1 row in set (0.00 sec)
回收yang1
mysql> show grants for 'yang1'@'118.24.193.33'\G
*************************** 1. row ***************************
Grants for yang1@118.24.193.33: GRANT ALL PRIVILEGES ON *.* TO 'yang1'@'118.24.193.33'
1 row in set (0.00 sec)
mysql> revoke ALL PRIVILEGES ON *.* from 'yang1'@'118.24.193.33';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'yang1'@'118.24.193.33'\G
*************************** 1. row ***************************
Grants for yang1@118.24.193.33: GRANT USAGE ON *.* TO 'yang1'@'118.24.193.33'
1 row in set (0.00 sec)
回收部分权限
mysql> grant all on school.* to 'admin2'@'%' identified by'1q2w3eQWE!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for 'admin2'@'%'\G
*************************** 1. row ***************************
Grants for admin2@%: GRANT USAGE ON *.* TO 'admin2'@'%'
*************************** 2. row ***************************
Grants for admin2@%: GRANT ALL PRIVILEGES ON `school`.* TO 'admin2'@'%'
2 rows in set (0.00 sec)
mysql> revoke insert ON `school`.* from 'admin2'@'%';
Query OK, 0 rows affected (0.00 sec)
删除用户drop user
- 5.6版本删除用户前需要先拿掉权限
- 如果直接删除用户,权限还会存在
- 会造成如果下次创建同名用户,会继续使用删除之前的权限
- 5.7则不会
drop user
mysql> show grants for 'admin2'@'%'\G
*************************** 1. row ***************************
Grants for admin2@%: GRANT USAGE ON *.* TO 'admin2'@'%'
*************************** 2. row ***************************
Grants for admin2@%: GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `school`.* TO 'admin2'@'%'
2 rows in set (0.00 sec)
mysql> drop user 'admin2'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'admin2'@'%'\G
ERROR 1141 (42000): There is no such grant defined for user 'admin2' on host '%'
删除用户
DELETE语句
修改用户密码
root用户修改root用户密码
方法1
[root@VM_0_7_centos ~]# mysqladmin -uroot -p'qingdao@123' password 'qingdao@123NEW'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
方法二
mysql> update mysql.user set authentication_string=password('qingdao@123NEW2') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
使用这种方法需要刷新授权
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
方法三
mysql> set password=password('qingdao@123NEW3');
Query OK, 0 rows affected, 1 warning (0.00 sec)
root用户修改普通用户密码
方法一
mysql> set password for 'yang1'@'118.24.193.33'=password('yang1!@#NEW1');
Query OK, 0 rows affected, 1 warning (0.00 sec)
方法二
参考上面方法二
普通用户修改普通用户密码
set命令修改当前用户密码
忘记root密码
5.7.6之后的版本
在/etc/my.cnf配置文件中添加字段
[root@VM_0_7_centos ~]# tail -n 1 /etc/my.cnf
skip-grant-tables
重启服务
[root@VM_0_7_centos ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
现在可以无密码登录
[root@VM_0_7_centos ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
查看密码表
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *A32A774CFD115F70CC83AB40D7A4F9E4AA9E8DD3 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
重置密码
mysql> update mysql.user set authentication_string=password('qingdao@123') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
退出mysql
mysql> \q
Bye
注释skip-grant-tables字段
[root@VM_0_7_centos ~]# tail -n 1 /etc/my.cnf
# skip-grant-tables
重启mysql服务
[root@VM_0_7_centos ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
登录mysql
[root@VM_0_7_centos ~]# mysql -uroot -p'qingdao@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 2
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
5.7.6之前的怎么操作呢
只有两个区别
5.7.6之前的可以设置简单密码
5.7.6之前的版本需要将authentication_string字段改为password字段
