Of Stack

MySQL 5.7 Safety related features of learning experience

  • 2021-01-25 08:02:24
  • OfStack

1. Account security-related features

1.1: Create users

The 5.7 version of user table mysql.user requires that the plugin field be non-empty, and the default value is mysql_native_password, and mysql_old_password is no longer supported. 5.7 The maximum user length is 32 bytes, the previous maximum length was 16 bytes, and the CREATE, USER and DROP, USER have implemented the IF [NOT] EXISTS condition judgment. 5.7 After that, user creates 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 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+---------------------------------------------------------------+
2 rows in set (0.01 sec)

The syntax for creating an account will be removed and replaced with cerate user. There are two steps to create a user: create and authorize.

Create user create user 


# Plaintext password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456'; equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';
# Encryption password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

grant authorizes: 


grant select,insert,update,delete on dba_test.* to dxy@localhost;

Note: When authorized to manage users, not only all permissions, but also with, grant, option and proxy permissions are included. proxy permissions need to be used when proxying users. 


 View the default administrative user permissions: 
show grants for root@localhost; ----2 records  +---------------------------------------------------------------------+ |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
 New management account: 
create user dba@127.0.0.1 identified by '123456';
 Authorization: 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
 grant proxy Rights: This is required when creating proxy users 
GRANT PROXY ON ''@'' TO 'dba'@'127.0.0.1' WITH GRANT OPTION;
 To view: 
show grants for 'dba'@'127.0.0.1';
+--------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dba'@'127.0.0.1' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'dba'@'127.0.0.1' WITH GRANT OPTION |
+--------------------------------------------------------------------+

View user permissions: 


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. After a certain time, force the user to change the password. create user can be set directly, or alter user can be set:

PASSWORD EXPIRE DEFAULT　　 默认，过期时间受全局变量default_password_lifetime控制
PASSWORD EXPIRE NEVER 永不过期
PASSWORD EXPIRE INTERVAL N DAY　　 N天后过期
PASSWORD EXPIRE 过期



Settings when creating users directly: 


create user dxy@localhost identified by '123456' password expire interval 10 day; ---- 10 Days after the date

Settings for existing users 


alter user zjy@localhost password expire never; ---- Never expire

Note: set 1 user expired, login will be prompted to change the password, can not carry out any operations: applicable to make the program can not access the database.

Set the user password to expire: 


alter user dxy@localhost password expire;

Execute any command to report an error: 


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

alter user dxy@localhost identified by '123456';

1.3: Lock disable user alter user

Temporarily disable a user when certain scenarios require the user to be "locked up" : applies so that the application cannot access the database.

Set the locked user: 


# Plaintext password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456'; equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';
# Encryption password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
0

Login error: 


ERROR 3118 (HY000): Access denied for user 'dxy'@'localhost'. Account is locked.

Solution: Unlock the user 


alter user dxy@localhost account unlock;

1.4 Agent User

The authentication plug-in based on mysql_native_password comes with the function of proxy user. Proxying users acts as a "proxy" for other users, making it easy to grant permissions from one account to other accounts without requiring each account to perform authorization operations. check_proxy_users and mysql_native_password_proxy_users to enable the function of proxy users

Create the original account: 


# Plaintext password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456'; equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';
# Encryption password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
3

Authorization: 


# Plaintext password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456'; equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';
# Encryption password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
4

Create a proxy account: 


# Plaintext password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456'; equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';
# Encryption password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
5

Authorized agency authority: 


# Plaintext password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456'; equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';
# Encryption password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
6

To 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:

Check the 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: found that the permissions of the proxy account show the permissions of the original account 


# Plaintext password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456'; equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';
# Encryption password creation 
CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release equivalent 
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
9

Verify that the proxy account has test library permissions: 


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tttt |
+----------------+
mysql> select * from tttt; +------+
| id |
+------+
| 1 |
| 100 |
+------+
mysql> insert into tttt values(2),(200);
mysql> select * from tttt;
+------+
| id |
+------+
| 1 |
| 100 |
| 2 |
| 200 |
+------+

Verify that the proxy account (dxy_proxy) represents the permissions of the original account (dxy).

1.5: Other options: SSL, MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR, MAX_USER_CONNECTIONS. If you want to restrict account logins via ssl, add require; if you want to restrict resources, add with: 


create user dxy@localhost identified by '123456' require SSL with MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 100 password expire never account unlock;

2. External related security

2.1: MySQL5.7 has deleted the test database. By default, there is no test database after the installation. Any user can access the test database originally, which increases the security risk.

2.2: MySQL5.7 provides a simpler SSL security access configuration, and the default connection is encrypted by SSL. Before 5.7, SSL related files need to be created manually. You can check this article. After 5.7, MySQL passed

mysql_ssl_rsa_setup can be generated directly: 


root@t20:~# mysql_ssl_rsa_setup 
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'
-----

You can see the files ending in pem under the data directory. These are the files you need to open the SSL connection (note the file permissions), and then use the account number

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 id: 4
Current database: 
Current user: dba@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
...
...

Forced ssl login: 


root@t20:~# mysql -udba -p -h127.0.0.1 --ssl=1 
WARNING: --ssl is deprecated and will 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 EditLine wrapper
Connection id: 10
Current database: 
Current user: dba@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
...
...

If you want to create a user that must use SSL each time, you need to create a user using REQUIRE SSL to set up, as described above. The test cases in Jiang's article show that the performance overhead of turning on SSL is around 25% : SSL encrypted connection and performance overhead for MySQL

2.3: MySQL5.7 recommends that users use mysqld --initialize to initialize the database. The previous mysql_install_db method is dropped. The new method creates only one root@localhost user, and the random password is stored in ~/.mysql_secret file.

Initialize the database: Create a new instance.

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

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

5.7 The default before sql_mode

select @@sql_mode;
NO_ENGINE_SUBSTITUTION

See sql_mode in 5.7 for more rigor. Explain the meaning of each mode:

ONLY_FULL_GROUP_BY　　
 不要让GROUP BY部分中的查询指向未选择的列　　
STRICT_TRANS_TABLES
 为事务存储引擎启用严格模式，也可能为非事务存储引擎启用严格模式
NO_ZERO_IN_DATE 在严格模式，不接受月或日部分为0的日期
NO_ZERO_DATE 在严格模式，不将 '0000-00-00'做为合法日期
ERROR_FOR_DIVISION_BY_ZERO 在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误　　
NO_AUTO_CREATE_USER 防止GRANT自动创建新用户，除非还指定了密码
NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译，可以防止自动替换存储引擎

By default, 5.7: 


---- for datetime type <NO_ZERO_DATE> : 
 insert "0000-00-00 00:00:00" Value will report an error: Incorrect datetime value
---- for varchar/char type <STRICT_TRANS_TABLES> : 
 Inserting a string beyond its length will cause an error:  Data too long for column...
---- for not null The columns of the <STRICT_TRANS_TABLES> : 
 Insert not specified not null The column of Field 'xxx' doesn't have a default value ' 
---- for grant<NO_AUTO_CREATE_USER> : 
 authorization 1 A user who does not specify a password will receive an error: Can't find any matching row in the user table '
---- for engine The storage engine <NO_ENGINE_SUBSTITUTION> : 
 create 1 If the storage engine is not supported, it will not be converted to the default storage engine. Unknown storage engine ... Using storage engine InnoDB for table '...'

Note: In one master-slave environment, in order to ensure the consistency of the data, it is necessary to set the sql_mode1 of the master-slave, and also ensure the sql_mode of the data during migration, otherwise the replication and migration will fail in the above restrictions, so try to use the standard SQL syntax.

3. Conclusion:

In MySQL 5.7, there are a number of secure-related improvements: creating an account is a 2 step process: using create to create an account (account length is increased), using grant to authorize; The password is not empty when initializing the database. Accounts can be locked and passwords can be set to expire; The test library has been removed; ssl connection is provided by default; sql_mode enhancement, etc. The article introduces and tests from these aspects, further deepen the understanding of MySQL5.7.

Related articles: