第3章：MySQL之权限连接
第3章：MySQL之权限连接
目录
修订日期：2020-10-23
一、 MySQL的连接登录
1. Linux下MySQL登录方式
- 方式一 Unix Socket
mysql -p该方法默认使用root@localhost用户, 可使用select user();查看当前用户mysql -S /tmp/mysql.sock -u root -p密码 ,该方法适用于在安装MySQL主机上进行Unix 域套接字连接
-
方式二 TCP/IP模式
mysql -h 127.0.0.1 -u root -p密码,使用'root'@'127.0.0.1'这个用户登录mysql -h ali01 -u root -p密码,该方式等价与上面，使用可以域名连接，属于两个不同的“用户”
2. 免密码登录
- 方式一
my.cnf增加[client]标签
[client]
user="root"
password="你的密码"
#单对定义不同的客户端
[mysql] # 这个是给/usr/loca/mysql/bin/mysql 使用的
user=root
password="你的密码"
[mysqladmin] # 这个是给/usr/local/mysql/bin/mysqladmin使用的
user=root
password="你的密码"
每个不同的客户端需要定义不同的标签，使用[client]可以统一
- 方式二
login-path
shell> mysql_config_editor set -G vm1 -u root -p
Enter password [输入root的密码]
shell> mysql_config_editor print --all
[vm1]
user=root
password=*****
socket=/tmp/mysql.sock
#login
shell> mysql --login-path=vm1 # 这样登录就不需要密码，且文件二进制存储 ,位置是 ~/.mylogin.cnf
该方式相对安全。如果server被黑了，该二进制文件还是会被破解
- 方式三
~/.my.cnf, 自己当前家目录
#Filename: ~/.my.cnf
[client]
user="root"
password="你的密码"
二、权限管理
1.USER + IP的概念
MySQL中同一个用户名，比如Bob,能否登录，以及用什么密码登录，可以访问什么库等等，都需要加上IP，才可以表示一个完整的用户标识
db@127.0.0.1和db@loalhost以及db@10.0.1.88这三个其实是不同的 用户标识
2. 用户权限管理
-
系统表权限信息:
- a) 用户名和IP是否允许
- b) 查看mysql.user表
// 查看全局所有库的权限 - c) 查看mysql.db表
// 查看指定库的权限 - d) 查看mysql.table_priv表
// 查看指定表的权限 - e) 查看mysql.column_priv表
// 查看指定列的权限
备注: mysql> desc [tablename]; 可以查看表的结构信息； -
常用权限：
- SQL语句：SELECT、INSERT、UPDATE、DELETE、INDEX
- 存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
- 管理权限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN、
-
可选资源:
- MAX_QUERIES_PER_HOUR count
- MAX_UPDATES_PER_HOUR count
- MAX_CONNECTIONS_PER_HOUR count
-
MAX_USER_CONNECTIONS count
备注:只能精确到小时，对于部分场景不适用，可以考虑中间件方式*
- 显示当前用户的权限
#这三个是同一个意思
mysql> show grants;
mysql> show grants for current_user;
mysql> show grants for current_user();
3. 基本操作
mysql> create user 'db'@'127.0.0.1' identified by '123456';
#创建一个认证用户为'db'@'127.0.0.1',密码是123456
mysql> grant all on sysbench.* to 'test'@'127.0.0.1';
#授予他sysbench库下面所有表的所有访问权限; *.*表示所有库的所有表
mysql> grant all on sysbench.* to 'test'@'127.0.0.1' identified by '123456';
#这个grant语句会搜索用户，如果用户不存在，则自动创建用户，
#如果不带identified by, 则该用户名密码为空
mysql> grant all on *.* to 'gcdb'@'192.168.1.%' identified by 'iforgot@123' with grant option;
#表示这个用户'gcdb'@'127.0.0.1'可以访问所有库的所有表，
#同时，他还可以给其他用户授予权限(with grant option)，
#注意如果，*.*改成了某一个指定的非USER库，
#则gcdb没法去新建其他用户了，因为User库没有权限了
#192.168.24.% 表示属于192.168.1.0/24网段的用户可以访问
4. 撤销权限
revoke关键字，该关键字只删除用户权限，不删除用户revoke语法同grant一致, 从grant ... to变为revoke ... from
三、授权实战
1.常规授权
- GRANT与创建用户
mysql> grant select on sysbench.* to 'fanghao'@'localhost' identified by 'haoshijiwudi';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
-- 这里有2个warning
mysql> show warnings;
--输入warning的Message如下：
| Warning | 1285 | MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work |
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release.Create new user with CREATE USER statement. |
--第一条的意思，跳过域名解析模式，暂时不管
--第二条的意思是使用GRANT赋权限的同时创建'fanghao'@'localhost'这个用户，但是出现了warning，从给出的提示看来，以后的MySQL版本会废弃掉这种方式
--正确的创建用户并赋权的方式:
mysql> drop user 'fanghao'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'fanghao'@'localhost' identified by 'haoshijiwudi';
Query OK, 0 rows affected (0.00 sec)
- 查看某一个用户的权限
mysql> show grants for 'fanghao'@'localhost';
+-------------------------------------------------------+
| Grants for fanghao@localhost |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fanghao'@'localhost' | --USAGE表示用户可以登录
| GRANT SELECT ON `sysbench`.* TO 'fanghao'@'localhost' | --对sysbench库的所有表有select权限
+-------------------------------------------------------+
2 rows in set (0.00 sec)
- 删除某一个用户
mysql> drop user 'fanghao'@'localhost';
Query OK, 0 rows affected (0.00 sec)
- MySQL权限信息*
mysql> select * from mysql.user where user='fanghao' \G;
*************************** 1. row ***************************
Host: localhost
User: fanghao ---由于fanghao用户是对sysbench库有权限，所以这里（USER）全是N
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: *1A7B1D37BD4148AD15497C3803F812C91047EC4F
password_expired: N
password_last_changed: 2017-11-22 16:31:57
password_lifetime: NULL
account_locked: N -- 如果这里为Y表示被锁定，该用户就无法使用了
1 row in set (0.01 sec)
mysql> select * from mysql.db where user='fanghao' \G;
*************************** 1. row ***************************
Host: localhost --授权主机
Db: sysbench --sysbench数据库
User: fanghao --用户名
Select_priv: Y --授权的select权限
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 --注意： 不建议使用INSERT或者GRANT对元数据表进行修改，来达到修改权限的目的
1 row in set (0.01 sec)
--使用fanghao账号登陆
mysql> select user();
+-------------------+
| user() |
+-------------------+
| fanghao@localhost |
+-------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | -- 这是一个统计信息的数据库，use可以进去，部分表没有权限查询
| sysbench |
+--------------------+
2 rows in set (0.00 sec)
mysql> select * from views;
Empty set (0.00 sec)
mysql> select * from innodb_cmp;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
2.授权表和列的访问方式
(root@localhost) [8.0.21] 22:29:57 [mysql]> create database ttt;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [8.0.21] 22:30:40 [mysql]> create table ttt.t2(id int(4)primary key not null auto_increment,nums int(20) not null); --创建t2表，两个字段（id，nums）
insert into ttt.t2(nums) values(1),(2),(3),(4),(5);
update ttt.t2 set nums=100 where id =3;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
(root@localhost) [8.0.21] 22:30:40 [mysql]> insert into ttt.t2(nums) values(1),(2),(3),(4),(5);
delete from ttt.t2 where id =4;
select * from ttt.t2;Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
(root@localhost) [8.0.21] 22:30:40 [mysql]> update ttt.t2 set nums=100 where id =3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(root@localhost) [8.0.21] 22:30:40 [mysql]> delete from ttt.t2 where id =4;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [8.0.21] 22:30:40 [mysql]> select * from ttt.t2;
+----+------+
| id | nums |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 100 |
| 5 | 5 |
+----+------+
4 rows in set (0.00 sec)
(root@localhost) [8.0.21] 22:35:52 [mysql]> use ttt
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
(root@localhost) [8.0.21] 22:36:15 [ttt]> create table ttt.t1(id int(4)primary key not null auto_increment,product_id int(20) not null); --创建t1表，两个字段（id，product_id）
insert into ttt.t1(product_id) values(1),(2),(3),(4),(5);
Query OK, 0 rows affected, 2 warnings (0.03 sec)
(root@localhost) [8.0.21] 22:36:16 [ttt]> insert into ttt.t1(product_id) values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
(root@localhost) [8.0.21] 22:36:16 [ttt]> update ttt.t1 set product_id=200 where id =3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(root@localhost) [8.0.21] 22:36:16 [ttt]> delete from ttt.t1 where id =4;
Query OK, 1 row affected (0.01 sec)
(root@localhost) [8.0.21] 22:36:16 [ttt]> select * from ttt.t1;
+----+------------+
| id | product_id |
+----+------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 200 |
| 5 | 5 |
+----+------------+
4 rows in set (0.00 sec)
(root@localhost) [8.0.21] 22:36:21 [ttt]> create user tempuser@'%' IDENTIFIED BY 'iforgot'; --创建tempuser@'%'用户
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [8.0.21] 22:38:44 [ttt]> grant all on ttt.t1 to tempuser@'%'; --授权访问t1表
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [8.0.21] 22:39:15 [ttt]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [8.0.21] 22:46:45 [ttt]> grant select(nums) on ttt.t2 to tempuser@'%'; --只授权访问t2表的nums字段
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [8.0.21] 22:52:51 [ttt]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [8.0.21] 22:52:57 [ttt]>
(tempuser@172.31.207.169) [8.0.21] 22:47:28 [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ttt |
+--------------------+
2 rows in set (0.00 sec)
(tempuser@172.31.207.169) [8.0.21] 22:47:37 [(none)]> use ttt;
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
(tempuser@172.31.207.169) [8.0.21] 22:47:41 [ttt]> show tables;
+---------------+
| Tables_in_ttt |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
(tempuser@172.31.207.169) [8.0.21] 22:47:45 [ttt]> select * from t1;
+----+------------+
| id | product_id |
+----+------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 200 |
| 5 | 5 |
+----+------------+
4 rows in set (0.00 sec)
(tempuser@172.31.207.169) [8.0.21] 22:47:52 [ttt]> show tables;
+---------------+
| Tables_in_ttt |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows in set (0.00 sec)
(tempuser@172.31.207.169) [8.0.21] 22:53:10 [ttt]> select * from t2;
ERROR 1143 (42000): SELECT command denied to user 'tempuser'@'172.31.207.169' for column 'id' in table 't2' --id列显示没权限查看
(tempuser@172.31.207.169) [8.0.21] 22:53:18 [ttt]> select nums from t2;
+------+
| nums |
+------+
| 1 |
| 2 |
| 100 |
| 5 |
+------+
4 rows in set (0.00 sec)
(tempuser@172.31.207.169) [8.0.21] 22:53:28 [ttt]>
- 既然可以为单独的列授予select权限，那么DML操作呢？例如insert能不能用以下语法对单独的列授权呢？
grant insert(id) on test.priv_test to sam@‘localhost’;
(root@localhost) [8.0.21] 22:52:57 [ttt]> show grants for tempuser@'%';
+-------------------------------------------------------+
| Grants for tempuser@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `tempuser`@`%` |
| GRANT ALL PRIVILEGES ON `ttt`.`t1` TO `tempuser`@`%` |
| GRANT SELECT (`nums`) ON `ttt`.`t2` TO `tempuser`@`%` |
+-------------------------------------------------------+
3 rows in set (0.00 sec)
(root@localhost) [8.0.21] 23:06:34 [ttt]> revoke SELECT (`nums`) ON `ttt`.`t2` from `tempuser`@`%`;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [8.0.21] 23:08:46 [ttt]> show grants for tempuser@'%';
+------------------------------------------------------+
| Grants for tempuser@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `tempuser`@`%` |
| GRANT ALL PRIVILEGES ON `ttt`.`t1` TO `tempuser`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)
(root@localhost) [8.0.21] 23:08:47 [ttt]> grant insert(nums) on ttt.t2 to tempuser@'%';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [8.0.21] 23:09:01 [ttt]> show grants for tempuser@'%';
+-------------------------------------------------------+
| Grants for tempuser@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `tempuser`@`%` |
| GRANT ALL PRIVILEGES ON `ttt`.`t1` TO `tempuser`@`%` |
| GRANT INSERT (`nums`) ON `ttt`.`t2` TO `tempuser`@`%` |
+-------------------------------------------------------+
3 rows in set (0.00 sec)
(root@localhost) [8.0.21] 23:09:07 [ttt]>
-- tmpuser账号访问
(tempuser@172.31.207.169) [8.0.21] 22:53:28 [ttt]> insert into ttt.t2(nums) values(6);
Query OK, 1 row affected (0.00 sec)
(tempuser@172.31.207.169) [8.0.21] 23:10:29 [ttt]> select nums from t2;
ERROR 1142 (42000): SELECT command denied to user 'tempuser'@'172.31.207.169' for table 't2' --有insert权限没select权限，就会出现能插入，但无法查看数据。在权限授予上需要注意。
(root@localhost) [8.0.21] 23:09:07 [ttt]> select * from ttt.t2; --root@localhost权限可以看到插入行
+----+------+
| id | nums |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 100 |
| 5 | 5 |
| 6 | 6 | --root@localhost权限可以看到插入行
+----+------+
5 rows in set (0.00 sec)
(root@localhost) [8.0.21] 23:12:28 [ttt]>
四、MySQL模拟角色
- 角色的定义：
角色(Role)可以用来批量管理用户，同一个角色下的用户，拥有相同的权限。 MySQL5.7.X以后可以模拟角色(Role)的功能，通过mysql.proxies_priv模拟实现。- mysql.proxies_priv在
5.5.X和5.6.X的时候就存在，但是无法模拟角色(Role)功能。
- mysql.proxies_priv在
- 模拟角色操作：
mysql> create user 'shiji_dba'@'localhost'; -- 相当于定于一个老司机角色(Role), -- 但这只是个普通的用户，名字比较有(Role)的感觉 -- 有点类似用户组
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'bangwen'@‘localhost’; --测试用户（邦文袍哥）
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'xuge'@'localhost'; --测试用户（许哥小司机）
Query OK, 0 rows affected (0.00 sec)
mysql> grant proxy on 'shiji_dba'@'localhost' to 'bangwen'@‘localhost’; -- 将shiji_dba的权限映射(map)给bangwen
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> grant proxy on 'shiji_dba'@'localhost' to 'xuge'@‘localhost’; -- 将shiji_dba的权限映射(map)给xuge
Query OK, 0 rows affected, 3 warnings (0.00 sec)
mysql> show grants for 'shiji_dba'@'localhost'; -- 查看shiji_dba角色赋予的实际权限
+---------------------------------------------------------+
| Grants for shiji_dba@localhost |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'shiji_dba'@'localhost' |
| GRANT SELECT ON `sysbench`.* TO 'shiji_dba'@'localhost' |
+---------------------------------------------------------+
mysql> show grants for 'bangwen'@‘localhost’; -- 查看bangwen老司机的权限
+-----------------------------------------------------------------------+
| Grants for bangwen@‘localhost’ |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bangwen'@'‘localhost’' |
| GRANT PROXY ON 'shiji_dba'@'localhost' TO 'bangwen'@'‘localhost’' |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.proxies_priv; -- 查看 proxies_priv的权限
+-----------------+---------+--------------+--------------+------------+----------------------+---------------------+
| Host | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp |
+-----------------+---------+--------------+--------------+------------+----------------------+---------------------+
| localhost | root | | | 1 | boot@connecting host | 0000-00-00 00:00:00 |
| ‘localhost’ | bangwen | localhost | shiji_dba | 0 | root@localhost | 0000-00-00 00:00:00 |
| ‘localhost’ | xuge | localhost | shiji_dba | 0 | root@localhost | 0000-00-00 00:00:00 |
+-----------------+---------+--------------+--------------+------------+----------------------+---------------------+
3 rows in set (0.00 sec)
--总结mysql.proxies_priv仅仅是对Role的模拟，和Oracle的角色还是有所不同.官方称呼为Role like
五、SSL连接
SSL（Secure Socket Layer）是维护Client - Server之间加密通讯的一套安全协议；
--默认ssl未开启
mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
+---------------+----------+
9 rows in set (0.00 sec)
1. 开启SSL (5.7.18)
- 环境说明
- 服务端A：MySQLserver； IP：192.168.1.168；
- 客户端B：MySQLserver； IP：192.168.2.138；
-- 服务端A：MySQLserver； IP：192.168.1.168；
[root@localhost-m(252) /r2/soft/dbtest/mysql-5.7.18]# bin/mysql_ssl_rsa_setup --datadir=/r2/soft/dbtest/mysql-5.7.18/mysqldata --user=mysql --uid=mysql --使用--uid后，就不需要chown mysql.mysql *.pem
Generating a 2048 bit RSA private key
..+++
......+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
..............................................................+++
...........................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.............+++
................+++
writing new private key to 'client-key.pem'
-----
[root@localhost-m(252) /r2/soft/dbtest/mysql-5.7.18]# ll mysqldata/|grep pem
-rw------- 1 mysql mysql 1675 11月 28 10:21 ca-key.pem
-rw-r--r-- 1 mysql mysql 1074 11月 28 10:21 ca.pem
-rw-r--r-- 1 mysql mysql 1078 11月 28 10:21 client-cert.pem #客户端证书文件
-rw------- 1 mysql mysql 1679 11月 28 10:21 client-key.pem #客户端私钥文件
-rw------- 1 mysql mysql 1675 11月 28 10:21 private_key.pem #用于密钥交换的公钥
-rw-r--r-- 1 mysql mysql 451 11月 28 10:21 public_key.pem #用户密钥交换的私钥
-rw-r--r-- 1 mysql mysql 1078 11月 28 10:21 server-cert.pem #服务器端证书文件
-rw------- 1 mysql mysql 1675 11月 28 10:21 server-key.pem #服务器端私钥文件
[root@localhost-m(252) /r2/soft/dbtest/mysql-5.7.18]# mysqladmin -uroot -piforgot --socket=/r2/soft/dbtest/mysql-5.7.18/mysqldata/mysql.sock shutdown
2017-11-28T02:21:55.829485Z mysqld_safe mysqld from pid file /r2/soft/dbtest/mysql-5.7.18/mysqldata/mysqldb.pid ended
[1]+ 完成 /r2/soft/dbtest/mysql-5.7.18/bin/mysqld_safe --defaults-file=/r2/soft/dbtest/mysql-5.7.18/my.cnf
[root@localhost-m(252) /r2/soft/dbtest/mysql-5.7.18]# /r2/soft/dbtest/mysql-5.7.18/bin/mysqld_safe --defaults-file=/r2/soft/dbtest/mysql-5.7.18/my.cnf &
[1] 159680
关于几个pem文件的用途说面，见官方文档，并搜索关键字
private/public key-pair
- 开始测试
- 服务端A：MySQLserver； IP：192.168.1.168；
mysql> show variables like "%ssl%";
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | YES | -- 已经支持SSL
| have_ssl | YES |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem | -- 公钥文件
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem | -- 私钥文件
+---------------+-----------------+
9 rows in set (0.00 sec)
mysql> \s -- status
--------------
/r2/soft/dbtest/mysql-5.7.18/bin/mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 802
Current database:
Current user: root@localhost
SSL: Not in use --此时本地socket登录，不用SSL
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /r2/soft/dbtest/mysql-5.7.18/mysqldata/mysql.sock
Uptime: 15 min 41 sec
Threads: 1 Questions: 5694 Slow queries: 0 Opens: 3439 Flush tables: 1 Open tables: 729 Queries per second avg: 6.051
--------------
--创建测试账号
mysql> create user 'ssl'@'%' identified by 'ssltest';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'ssl'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'ssl'@'%';
+------------------------------------------+
| Grants for ssl@% |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'ssl'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select ssl_type from mysql.user where user='ssl';
+----------+
| ssl_type |
+----------+
| | --看到ssl_还没有配置
+----------+
1 row in set (0.00 sec)
- 客户端B：MySQLserver； IP：192.168.2.138；默认使用ssl登录
[root@node2 ~]# mysql -h192.168.1.168 -ussl -pssltest
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 2264
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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.
(ssl@192.168.1.168) 11:06:57 [(none)]> \s status;
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 2264
Current database:
Current user: ssl@192.168.2.138
SSL: Cipher in use is DHE-RSA-AES256-SHA --已经使用了ssl登录了
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.1.168 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 44 min 55 sec
Threads: 2 Questions: 16275 Slow queries: 0 Opens: 8527 Flush tables: 1 Open tables: 1024 Queries per second avg: 6.038
--------------
- 客户端B：MySQLserver； IP：192.168.2.138;使用skip ssl登录
[root@node2 ~]# mysql -h192.168.1.168 -ussl -pssltest --skip-ssl
mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2601
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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.
(ssl@192.168.1.168) 11:11:55 [(none)]> \s status;
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 2601
Current database:
Current user: ssl@192.168.2.138
SSL: Not in use --表示为只用ssl
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.1.168 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 49 min 56 sec
Threads: 2 Questions: 18098 Slow queries: 0 Opens: 9366 Flush tables: 1 Open tables: 1024 Queries per second avg: 6.040
- 强制用户使用ssl登录
--
-- 服务端A：MySQLserver； IP：192.168.1.168；
--
mysql> alter user 'ssl'@'%' require ssl;
Query OK, 0 rows affected (0.00 sec)
-
- 客户端B：MySQLserver； IP：192.168.2.138;
-
[root@node2 ~]# mysql -h192.168.1.168 -ussl -pssltest --skip-ssl
mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.
ERROR 1045 (28000): Access denied for user 'ssl'@'192.168.2.138' (using password: YES) --禁用了SSL就无法登录了
[root@node2 ~]# mysql -h192.168.1.168 -ussl -pssltest
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 3023
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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.
(ssl@192.168.1.168) 11:20:00 [(none)]> \s status;
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3023
Current database:
Current user: ssl@192.168.2.138
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.1.168 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 58 min 10 sec
Threads: 2 Questions: 21080 Slow queries: 0 Opens: 10700 Flush tables: 1 Open tables: 1024Queries per second avg: 6.040
--------------
2. 开启证书认证(5.7.18)
--
-- 服务端A：MySQLserver； IP：192.168.1.168；
--
mysql> create user 'sslcatti'@'%' identified by 'sslcatti';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'sslcatti'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'sslcatti'@'%' require x509; -- 启用证书认证
Query OK, 0 rows affected (0.00 sec)
mysql> select ssl_type from mysql.user where user='sslcatti';
+----------+
| ssl_type |
+----------+
| X509 |
+----------+
1 row in set (0.00 sec)
-
- 客户端B：MySQLserver； IP：192.168.2.138;
-
[root@node2 ~]# mysql -h192.168.1.168 -usslcatti -psslcatti
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'sslcatti'@'192.168.2.138' (using password: YES)
-- 即使默认开启了ssl，也是无法登录的
- 把pem文件拷贝到客服端B
--
-- 服务端A：MySQLserver； IP：192.168.1.168；
--
[root@localhost-m(252) /r2/soft/dbtest/mysql-5.7.18/mysqldata]# pwd
/r2/soft/dbtest/mysql-5.7.18/mysqldata
[root@localhost-m(252) /r2/soft/dbtest/mysql-5.7.18/mysqldata]# scp client-cert.pem client-key.pem root@192.168.2.138:~/
The authenticity of host '192.168.2.138 (192.168.2.138)' can't be established.
ECDSA key fingerprint is 06:c0:78:4d:99:10:db:76:9f:78:92:ac:ab:cb:a7:cc.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.138' (ECDSA) to the list of known hosts.
root@192.168.2.138's password:
client-cert.pem 100% 1078 1.1KB/s 00:00
client-key.pem 100% 1679 1.6KB/s 00:00
- 客户端用证书登录
-
- 客户端B：MySQLserver； IP：192.168.2.138;
-
[root@node2 ~]# ll |grep pem
-rw-r--r-- 1 root root 1078 Nov 28 11:34 client-cert.pem
-rw------- 1 root root 1679 Nov 28 11:34 client-key.pem
[root@node2 ~]# mysql -h192.168.1.168 -usslcatti -psslcatti --ssl-cert=./client-cert.pem --ssl-key=./client-key.pem
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 3868
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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.
(sslcatti@192.168.1.168) 11:36:28 [(none)]> \s;
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3868
Current database:
Current user: sslcatti@192.168.2.138
SSL: Cipher in use is DHE-RSA-AES256-SHA --使用加密方式登录，且通过证书，因为这个用户
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.1.168 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 1 hour 14 min 31 sec
Threads: 1 Questions: 27036 Slow queries: 0 Opens: 13349 Flush tables: 1 Open tables: 1024Queries per second avg: 6.046
--------------
