MySQL 5.7 The security-related characteristics of learning experience _mysql

1, account security-related characteristics

1.1: Create User

The 5.7 version of the user table Mysql.user requires the plugin field to be non-null and the default value is the Mysql_native_password authentication plug-in and no longer supports the Mysql_old_password authentication plug-in. 5.7 The maximum user length is 32 bytes, preceded by a maximum length of 16 bytes, and the IF [not] EXISTS condition is implemented in the Create USER and DROP USER commands. After 5.7 The user creates a user report warning via grant. Such as:

Grant all on *.* to dxy@localhost identified by ' DXY ';
Query OK, 0 rows affected, 1 warnings (0.00 sec) Show
warnings +---------+------+---------------------------------- -----------------------------+
| Level | Code | message |
+---------+------+---------------------------------------------------------------+
| Warning | 1287 | The Using GRANT for creating the new user is deprecated and would removed in future. Create new user with Create USER statement. |
+---------+------+---------------------------------------------------------------+
2 rows in Set (0.01 sec)

The syntax to prompt grant to create an account will be deleted, with cerate user in place of creating users in 2 steps: Create and authorize.

Create user first by:

#明文密码创建
Create user ' DXY ' @ ' localhost ' identified by ' 123456 ', equivalent to
create user ' DXY ' @ ' localhost ' identified with ' Mysql_native_password ' by ' 123456 ';
#加密密码创建
CREATE USER ' dxy ' @ ' localhost ' identified by PASSWORD ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ';--will is removed in a future release equivalent to
CREATE USER ' dxy ' @ ' localhost ' identified with ' Mysql_native_password ' as ' *6bb4837eb7 4329105ee4568dda7dc67ed2ca2ad9 ';

Authorized by Grant:

Grant Select,insert,update,delete on dba_test.* to Dxy@localhost;

Note: when authorizing the administration of a user, there is more than just all permissions, but also the permission to include with GRANT option and proxy. Proxy permissions need to be used at the time of proxy user.

To view User rights:

Show grants for Dxy@localhost;
+---------------------------------------------------------------------------+
| Grants for Dxy@localhost |
+---------------------------------------------------------------------------+
| GRANT USAGE on *.* to ' DXY ' @ ' localhost ' |
| GRANT SELECT, INSERT, UPDATE, DELETE on ' dba_test '. * to ' DXY ' @ ' localhost ' |
+---------------------------------------------------------------------------+

View User password:

Show create user dxy@localhost;
+----------------------------------------------------------------------------------+
| CREATE USER ' DXY ' @ ' localhost ' identified with ' Mysql_native_password ' as ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' REQUIRE NONE PASSWORD EXPIRE DEFAULT Account UNLOCK |
+----------------------------------------------------------------------------------+

1.2: Password Expiration policy

Set the password expiration time for the user, and after a certain time, force the user to modify the password. Can be set directly at the time of create user, or alter user settings:

PASSWORD EXPIRE DEFAULT	Default, expiration time is controlled by global variable default_password_lifetime
PASSWORD EXPIRE NEVER	Never expires
PASSWORD EXPIRE INTERVAL N Day	N Days after expiration
PASSWORD EXPIRE	Expired

Set up when creating a user directly:

Create user Dxy@localhost identified by ' 123456 ' password expire interval; ----Expires after 10 days

Set for existing user

Alter user zjy@localhost password expire never; ----Never Expires

Note: After setting a user expiration, login will be prompted to modify the password, can not do anything: applicable to allow the program can not access the database.

Set User password expiration:

Alter user dxy@localhost password expire;

To perform any command error:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before the this executing.

WORKAROUND: Reset password alter user dxy@localhost identified by ' 123456 ';

1.3: Lock disabled user ALTER user

When some scenarios require a user to "lock", temporarily disable a user: Apply to allow the program to not access the database.

To set lock users:

Alter user Dxy@localhost account lock;

Login Error:

ERROR 3118 (HY000): Access denied for user ' DXY ' @ ' localhost '. The account is locked.

Workaround: Unlock User

Alter user dxy@localhost account unlock;

1.4 Proxy Users

The authentication plug-in based on Mysql_native_password has the function of proxy user. Proxy user is equivalent to "agent" other user's permissions, so it is convenient to give an account of the permissions to other accounts, and do not need each account to perform authorization operations. Open the function of the agent user need to open parameters: Check_proxy_users and Mysql_native_password_proxy_users

Create original account:

Create user dxy@127.0.0.1 identified by ' 123456 ';

Authorized:

Grant all on test.* to dxy@127.0.0.1;

To create a proxy account:

Create user dxy_proxy@127.0.0.1 identified by ' 123456 ';

Authorization Agent Permissions:

Grant Proxy on dxy@127.0.0.1 to dxy_proxy@127.0.0.1;

View:

Show grants for dxy_proxy@127.0.0.1;
+-------------------------------------------------------------+
| GRANT USAGE on *.* to ' dxy_proxy ' @ ' 127.0.0.1 ' |
| GRANT PROXY on ' DXY ' @ ' 127.0.0.1 ' to ' dxy_proxy ' @ ' 127.0.0.1 ' |
+-------------------------------------------------------------+

Login test with proxy account:

View login account: Proxy account Current_User (), original account user ()

Select User (), current_user ();
+---------------------+----------------+
| user () | current_user ()
| +---------------------+----------------+
| dxy_proxy@127.0.0.1 | dxy@127.0.0.1
| +---------------------+----------------+

View permissions: The right to discover the proxy account is the original account permissions displayed

Show grants;+-------------------------------------------------------+ +----------------------------------------- --------------+
| GRANT USAGE on *.* to ' DXY ' @ ' 127.0.0.1 ' |
| GRANT all privileges in ' test '. * to ' DXY ' @ ' 127.0.0.1 ' |
+-------------------------------------------------------+

Verify that the proxy account has permissions for the test library:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Test |
+--------------------+
mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tttt |
+----------------+
mysql> select * from TTTT; +------+
| id |
+------+
| 1 |
| |
+------+
mysql> insert INTO TTTT values (2), (MB);
Mysql> select * from TTTT;
+------+
| id |
+------+
| 1 |
| |
| 2 |
| |
+------+

Verify that the agent account (Dxy_proxy) agent of the original account (DXY) permissions.

1.5: Other options: SSL, Max_queries_per_hour, Max_updates_per_hour, Max_connections_per_hour, max_user_connections. When you need to limit your account access via SSL, you need to add require, and when you need to limit your resources, you need to add with:

Create user Dxy@localhost identified by ' 123456 ' require SSL with Max_queries_per_hour max_user_connections RD expire never account unlock;

2, external related security

2.1:mysql5.7 has deleted the test database, the default installation is not the test database, any previous users can access the test database, increase security risks.

2.2:mysql5.7 provides a simpler SSL security access configuration, and the default connection uses SSL encryption. Prior to 5.7, the generation of SSL-related files needed to be created manually, you can view this article, and after 5.7 MySQL passed

Mysql_ssl_rsa_setup can be directly generated by:

root@t20:~# Mysql_ssl_rsa_setup 
Generating a 2048 bit RSA private key ............ ... +++... ...
.... ...
+++
writing the new private key to ' Ca-key.pem '
-----
generating a 2048 bit RSA private with the ... "." Key ... +++ .... .... .... .... .... ......
+++
writing new private key to ' Server-key.pem ',
and so on ......
generating a 2048 bit RSA private key
.........................................................................................+++
.. +++
writing new private key to ' CLIENT-KEY.PEM '
-----

You can see some files at the bottom of the data directory that end with a PEM, which is the file you need to open the SSL connection (note file permissions), and then use the account

Default login:

root@t20:/var/lib/mysql# mysql-udba-p-h127.0.0.1
Enter Password: 
mysql> \s
--------------
MySQL Ver 14.14 distrib 5.7.12, for Linux (x86_64) using Editline wrapper Connection the current
database: 
C Urrent user:dba@localhost Ssl:cipher in the use is
dhe-rsa-aes256-sha
...
...

Force SSL to log on:

root@t20:~# mysql-udba-p-h127.0.0.1--ssl=1 WARNING:--ssl is deprecated and would be removed in 
a future version. Use--ssl-mode instead.
Enter Password: 
mysql> \s
--------------
mysql Ver 14.14 distrib 5.7.12, for Linux (x86_64) using EDITL INE wrapper
Connection id:10 current
database: Current 
user:dba@localhost
Ssl:cipher in the use is DHE -rsa-aes256-sha ...
...

See from above all SSL login, if you want the user to pass the SSL method each time when creating the user, you need to set up the user through require SSL, which is described above. The test case in Kang article shows that the cost of opening SSL is around 25%: MySQL's SSL encryption connection and performance overhead

2.3:MYSQL5.7 began to recommend that users use Mysqld--initialize to initialize the database, discard the previous mysql_install_db, and create a new way of creating only one root@localhost user, with a random password saved in ~/. Mysql_secret file, the first use must be reset password.

Initialize database: new instance.

Mysqld--initialize--datadir=/var/lib/mysql3309/

The change of 2.4:mysql5.7 Sql_mode,

5.7 Default Sql_mode

SELECT @ @sql_mode;
Only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_ User,no_engine_substitution

The default Sql_mode before 5.7

SELECT @ @sql_mode;
No_engine_substitution

See the Sql_mode more stringent in 5.7. Explain the meaning of each mode:

Only_full_group_by	Do not let queries in the group by section point to columns that are not selected
Strict_trans_tables	Enable strict mode for the transaction storage engine, or it may enable strict mode for non-transaction storage engines
No_zero_in_date	In strict mode, do not accept the month or day part is 0 of the date
No_zero_date	In strict mode, do not make ' 0000-00-00 ' a legal date
Error_for_division_by_zero	In strict mode, in the INSERT or update process, if 0 is removed (or mod (x,0)), an error is generated
No_auto_create_user	Prevent grant from automatically creating new users unless a password is also specified
No_engine_substitution	Prevents the storage engine from being replaced automatically if the storage engine you want is disabled or not compiled

In the case of default 5.7:

----for datetime type <NO_ZERO_DATE>:
insert 0000-00-00 00:00:00 value, Error: Incorrect datetime value
----for varchar /char type <strict_trans_tables>:
insert string exceeds length, error: Data too long for column ...
----column <strict_trans_tables> for NOT null:
inserting a column that does not specify NOT NULL will cause an error: Field ' xxx ' doesn ' t have a default value ' 
---- For Grant<no_auto_create_user>:
authorize a user, do not specify the password will be an error: can ' t find no matching row in the user table '
---- For ENGINE storage engine <no_engine_substitution>:
Create an unsupported storage engine that will not be converted to the default storage engine, directly error: Unknown storage ENGINE ... Using Storage Engine InnoDB for table ' ... '

Note: In a master-slave environment, in order to ensure the consistency of data, it is important to set the principal and subordinate Sql_mode the same, in the data migration to ensure the consistency of the sql_mode, or replication and migration encountered above limitations will fail, so use standard SQL syntax as much as possible.

3, Summary:

In MySQL 5.7, there are a number of security-related improvements: Create the account is divided into 2 steps: Creating user to build account (account length increase), with grant to authorize; The initial database time password is not null; The account can be locked and the password can be set to expire; The test library is deleted ; SSL connections are provided by default; Sql_mode enhancements, etc. This paper introduces and tests these aspects, and further deepens the understanding of MySQL5.7.

The above is a small set to introduce the MySQL 5.7 learning experience of the security-related characteristics, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!