MySQL permission connection

Keywords: MySQL

MySQL permission connection
1, MySQL connection login
1. MySQL login mode under Linux
2. Password free login
2, Authority management
1. Concept of user + IP
2. User authority management
3. Basic operation
4. Revoke authority
3, Authorization practice
1. General authorization
2. Access method of authorization table and column
4, MySQL simulation role
5, SSL connection
1. Enable SSL (5.7.18)
2. Open certificate certification (5.7.18)
​

1, MySQL connection login

1. MySQL login mode under Linux

  • Mode 1 Unix Socket
  • mysql -p this method is used by default root@localhost User, you can use select user(); View current user
    • mysql -S /tmp/mysql.sock -u root -p password. This method is applicable to Unix domain socket connection on the host where MySQL is installed

  • Mode 2: TCP/IP mode

    • mysql -h 127.0.0.1 -u root -p password, log in with the user 'root'@'127.0.0.1'
    • mysql -h ali01 -u root -p password. This method is equivalent to the above. It can be connected by domain name and belongs to two different "users"

2. Password free login

  • Method 1: add [client] tag to my.cnf
[client]   
user="root"  
password="Your password"  

#A single pair defines different clients
[mysql] # This is for / usr/loca/mysql/bin/mysql
user=root
password="Your password"

[mysqladmin] # This is for / usr/local/mysql/bin/mysqladmin
user=root 
password="Your password"

Each different client needs to define different labels, which can be unified by using [Client]

  • Mode 2: login path
shell> mysql_config_editor set -G vm1  -u root -p
Enter password [input root Password for]

shell> mysql_config_editor print --all
[vm1]
user=root
password=*****
socket=/tmp/mysql.sock
#login
shell> mysql --login-path=vm1 # In this way, no password is required for login, and the file is stored in binary, and the location is ~ /. mylogin.cnf

This method is relatively safe. If the server is hacked, the binary file will still be cracked

  • Method 3 ~ /. my.cnf, your current home directory
#Filename: ~/.my.cnf
[client]
user="root"
password="Your password"

2, Authority management

1. Concept of user + IP

The same user name in MySQL, such as Bob, whether he can log in, what password he can log in with, what database he can access, etc., needs to be added with IP to represent a complete user ID

db@127.0.0.1 And db@loalhost And db@10.0.1.88 These three are actually different user IDs

2. User authority management

  • System table permission information:

    • a) Is user name and IP allowed
    • b) View the mysql.user table. / / view the permissions of all global databases
    • c) View the mysql.db table. / / view the permissions of the specified database
    • d) View mysql.table_priv table / / view the permissions of the specified table
    • e) View mysql.column_priv table / / view the permissions of the specified column

    Note: MySQL > desc [tablename]; You can view the structure information of the table;

  • Common permissions:

    • SQL statements: SELECT, INSERT, UPDATE, DELETE, INDEX
    • Stored procedures: create route, alter route, EXECUTE, TRIGGER
    • Management permissions: SUPER, RELOAD, SHOW DATABASE, SHUTDOWN

    All permissions are stamped here

  • Optional resources:

    • MAX_QUERIES_PER_HOUR count
    • MAX_UPDATES_PER_HOUR count
    • MAX_CONNECTIONS_PER_HOUR count

  • MAX_USER_CONNECTIONS count

    Note: it can only be accurate to hours. It is not applicable to some scenarios. Middleware can be considered*

  • Displays the permissions of the current user
#These three mean the same thing
mysql> show grants;
mysql> show grants for current_user;
mysql> show grants for current_user();

3. Basic operation

mysql> create user 'db'@'127.0.0.1' identified by '123456'; 
       #Create an authenticated user with 'db'@'127.0.0.1' and password 123456
       
mysql> grant all on sysbench.* to 'test'@'127.0.0.1';
       #Grant him all access rights to all tables under the sysbench library; ** All tables representing all libraries

mysql> grant all on sysbench.* to 'test'@'127.0.0.1' identified by '123456';
       #This grant statement will search for the user. If the user does not exist, it will automatically create the user,
       #If there is no identified by, the user name and password are empty

mysql> grant all on *.* to 'gcdb'@'192.168.1.%' identified by 'iforgot@123' with grant option;
       #Indicates that this user 'gcdb'@'127.0.0.1' can access all tables of all libraries,
       #At the same time, he can also grant permissions (with grant option) to other users,
       #Note that if *. * is changed to a specified non USER library,
       #Then gcdb cannot create other users because the User library does not have permission
       #192.168.24.% indicates that users belonging to the 192.168.1.0/24 network segment can access

4. Revoke authority

  • revoke keyword, which only deletes user permissions and does not delete users
  • The syntax of revoke is consistent with grant, from Grant... To to revoke... From

3, Authorization practice

1. General authorization

  • GRANT and create users
mysql> grant select on sysbench.* to 'fanghao'@'localhost' identified by 'haoshijiwudi';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
-- Here are two warning
mysql> show warnings; 
--input warning of Message As follows: 
| 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. |
--The meaning of the first article is to skip the domain name resolution mode and ignore it for the time being
--Article 2 means to use GRANT Create while granting permission'fanghao'@'localhost'This user, but it appears warning，From the tips given, future MySQL The version will discard this method

--The right way to create and empower users: 
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)
  • View the permissions of a user
mysql> show grants for 'fanghao'@'localhost';
+-------------------------------------------------------+
| Grants for fanghao@localhost                          |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fanghao'@'localhost'           |  --USAGE Indicates that the user can log in
| GRANT SELECT ON `sysbench`.* TO 'fanghao'@'localhost' |  --yes sysbench All tables in the library have select jurisdiction
+-------------------------------------------------------+
2 rows in set (0.00 sec)
  • Delete a user
mysql> drop user 'fanghao'@'localhost';
Query OK, 0 rows affected (0.00 sec)
  • MySQL permission information*
mysql> select * from mysql.user where user='fanghao' \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: fanghao       ---because fanghao The user is right sysbench The library has permissions, so here( USER)All 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     -- If this is Y Indicates that the user cannot use it if it is locked
1 row in set (0.01 sec)


mysql> select * from mysql.db where user='fanghao' \G;
*************************** 1. row ***************************
                 Host: localhost    --Authorized host
                   Db: sysbench     --sysbench database
                 User: fanghao      --user name
          Select_priv: Y            --Authorized select jurisdiction
          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            --Note: not recommended INSERT perhaps GRANT Modify the metadata table to achieve the purpose of modifying permissions
1 row in set (0.01 sec)

--use fanghao Account login    
mysql> select user();
+-------------------+
| user()            |
+-------------------+
| fanghao@localhost |
+-------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |               -- This is a database of statistical information, use You can go in. Some tables do not have permission to query
| 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. Access method of authorization table and column

(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);  --establish t2 Table, two fields( 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); --establish t1 Table, two fields( 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';   --establish tempuser@'%'user
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [8.0.21] 22:38:44 [ttt]> grant all on ttt.t1 to tempuser@'%';    --Authorized access t1 surface
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@'%';    --Only authorized access t2 Tabular nums field 
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 Column display does not have permission to view
(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]>
  • Since you can grant select permission to individual columns, what about DML operations? For example, can insert authorize individual columns with the following syntax?
    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 Account access

(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'  --yes insert No authority select Permission, you can insert but can't view data. Pay attention to permission granting.

(root@localhost) [8.0.21] 23:09:07 [ttt]>  select * from ttt.t2;   --root@localhost Permission to see the inserted row
+----+------+
| id | nums |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |  100 |
|  5 |    5 |
|  6 |    6 |   --root@localhost Permission to see the inserted row
+----+------+
5 rows in set (0.00 sec)

(root@localhost) [8.0.21] 23:12:28 [ttt]>

4, MySQL simulation role

  • Definition of role:
  • Role can be used to manage users in batches. Users under the same role have the same permissions. After MySQL 5.7. X, the function of role can be simulated through mysql.proxies_priv simulation.
    • mysql.proxies_priv existed in 5.5.X and 5.6.X, but it cannot simulate the role function.
  • Simulate role actions:
mysql> create user 'shiji_dba'@'localhost'; -- Equivalent to an old driver(Role), -- But this is just an ordinary user with a different name(Role)Feeling of -- It's kind of like a user group
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'bangwen'@'localhost';   --Test user (Bangwen paoge)
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'xuge'@'localhost';      --Test user (Xu Ge small driver)
Query OK, 0 rows affected (0.00 sec)

mysql> grant proxy on 'shiji_dba'@'localhost' to 'bangwen'@'localhost';  -- take shiji_dba Permission mapping for(map)to bangwen
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant proxy on 'shiji_dba'@'localhost' to 'xuge'@'localhost';     -- take shiji_dba Permission mapping for(map)to xuge
Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> show grants for 'shiji_dba'@'localhost';                         -- see shiji_dba Actual permissions given by role
+---------------------------------------------------------+
| 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';                            -- see bangwen Authority of old driver
+-----------------------------------------------------------------------+
| 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;                                 -- see proxies_priv Permissions for
+-----------------+---------+--------------+--------------+------------+----------------------+---------------------+
| 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)
--summary mysql.proxies_priv Just right Role Simulation of, and Oracle Your role is still different.The official name is Role like

5, SSL connection

SSL (Secure Socket Layer) is a set of security protocol for maintaining encrypted communication between client and server;

--default ssl Not open
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. Enable SSL (5.7.18)

  • Environmental description
    • Server A: MySQL server; IP: 192.168.1.168;
    • Client B: MySQL server; IP: 192.168.2.138;
-- Server 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 / MySQL data -- user = MySQL -- uid = MySQL -- after using -- uid, chown mysql.mysql *.pem is not required
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 October 28:21 ca-key.pem
-rw-r--r-- 1 mysql mysql       1074 11 October 28:21 ca.pem
-rw-r--r-- 1 mysql mysql       1078 11 October 28:21 client-cert.pem   #Client certificate file
-rw------- 1 mysql mysql       1679 11 October 28:21 client-key.pem    #Client private key file
-rw------- 1 mysql mysql       1675 11 October 28:21 private_key.pem   #Public key for key exchange
-rw-r--r-- 1 mysql mysql        451 11 October 28:21 public_key.pem    #Private key for user key exchange
-rw-r--r-- 1 mysql mysql       1078 11 October 28:21 server-cert.pem   #Server side certificate file
-rw------- 1 mysql mysql       1675 11 October 28:21 server-key.pem    #Server side private key file

[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]+  complete                  /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

For the purpose of several pem files, see Official documents , and search for the keyword private / public key pair

  • Start test
    • Server A: MySQL server; IP: 192.168.1.168；
mysql> show variables like "%ssl%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |  -- Already supported SSL
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |  -- Public key file
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |  -- Private key file
+---------------+-----------------+
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       --Local at this time socket Login, No 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
--------------

--Create test account
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 |
+----------+
|          |    --notice ssl_Not configured yet
+----------+
1 row in set (0.00 sec)
  • Client B: MySQL server; IP: 192.168.2.138； ssl login is used by default
[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   --Already used ssl Logged in
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
--------------
  • Client B: MySQL server; IP: 192.168.2.138; Log in using 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                  --Expressed as only 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
  • Force users to log in using ssl
--
-- Server A: MySQLserver； IP: 192.168.1.168；
--

mysql> alter user 'ssl'@'%' require ssl;
Query OK, 0 rows affected (0.00 sec)



-
-  client 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)  --Disabled SSL You can't log in

[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. Open certificate certification (5.7.18)

--
-- Server 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; -- Enable certificate authentication
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)


-
-  client 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)
-- Even if it is on by default ssl，You can't log in
  • Copy the pem file to customer service terminal B
--
-- Server 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
  • The client logs in with a certificate
-
-  client 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   --Log in using encryption and pass the certificate because this user
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
--------------

Posted by Bob Norris on Wed, 13 Oct 2021 13:35:34 -0700

Hot Keywords

©2024 Programmer Group