MySQL 5.7 security-related features

MySQL 5.7 security-related features

1. Account security-related features

1.1: create a user

Mysql. user in analyticdb 5.7 requires that the plugin field be non-empty. The default value is mysql_native_password, And the mysql_old_password authentication plug-in is no longer supported. 5.7 The maximum length of a USER is 32 bytes, and the previous maximum length is 16 bytes. The IF [NOT] EXISTS condition is determined in the create user and drop user commands. After 5.7, the user creates a user and reports a warning through grant. For example:

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 prompt "grant Account creation Syntax" is deleted and replaced by "cerate user". Two steps are taken to create and authorize a user.

Create a user using create user:

# Create user 'dxy' @ 'localhost' identified by '000000' for plaintext passwords '; equivalent to create user 'dxy' @ 'localhost' identified with 'mysql _ native_password 'BY '123 '; # create user 'dxy' @ 'localhost' identified by password' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 'For the encrypted PASSWORD '; -- will be removed in a future release equivalent to create user 'dxy' @ 'localhost' identified with 'mysql _ native_password 'AS' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ';

Grant:

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

Note: When authorizing a user to manage a user, not only the all permission, but also the with grant option and proxy permissions. The proxy permission must be used for proxy users.

View default Management User Permissions: show grants for root @ localhost; ---- 2 records + allow + | + --------------------------------------------------------------------- + | grant all privileges on *. * TO 'root' @ 'localhost' with grant option | grant proxy on ''@'' TO 'root' @ 'localhost' with grant option | + users + new management account: create user dba@127.0.0.1 identified by '20140901'; authorization: grant all privileges on *. * TO 'root' @ '127. 0.0.1 'with grant option; GRANT proxy permission: grant proxy on ''@'' TO 'dba' @ '127 when creating a proxy user. 0.0.1 'with grant option; 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 period of time, force the user to change the password. You can set it directly when creating a user, or you can set it by alter user:

PASSWORD EXPIRE DEFAULT	By default, the expiration time is subject to global variables.Default_password_lifetimeControl
PASSWORD EXPIRE NEVER	Never expire
PASSWORD EXPIRE INTERVAL N DAY	Expired in N days
PASSWORD EXPIRE	Expired

When creating a user directly, set:

Create user dxy @ localhost identified by '000000' password expire interval 10 day; ---- expire in 10 days

Set existing users

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

Note: After you set a user to expire, you will be prompted to change the password upon login. You cannot perform any operations: this applies to preventing the program from accessing the database.

Set User Password Expiration:

alter user dxy@localhost password expire;

If you run any command, the following error occurs:

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

Solution: reset the password alter user dxy @ localhost identified by '20140901 ';

1.3: Lock disabled user alter user

When users need to be "locked" in some scenarios, temporarily disable a user: this applies to preventing the program from accessing the database.

Set to lock the user:

alter user dxy@localhost account lock;

Logon error:

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

Solution: unlock a user

alter user dxy@localhost account unlock;

1.4 proxy user

The authentication plug-in based on mysql_native_password comes with the proxy User Function. The proxy user is equivalent to the permissions of other users, so that the permissions of one account can be easily granted to other accounts, without the need for each account to perform authorization operations. Parameters: check_proxy_users and mysql_native_password_proxy_users must be enabled to enable the proxy User Function.

Create original account:

create user dxy@127.0.0.1 identified by '123456';

Authorization:

grant all on test.* to dxy@127.0.0.1;

Create a proxy account:

create user dxy_proxy@127.0.0.1 identified by '123456';

Authorize proxy 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' |+-------------------------------------------------------------+

Log on to the test using a proxy account:

View Logon 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 permissions of the proxy account are displayed as the permissions of the original account.

show grants;+-------------------------------------------------------+ +-------------------------------------------------------+| GRANT USAGE ON *.* TO 'dxy'@'127.0.0.1' || GRANT ALL PRIVILEGES ON `test`.* TO 'dxy'@'127.0.0.1' |+-------------------------------------------------------+

Verify that the agent account has the test database permission:

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || test |+--------------------+mysql> use testmysql> 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) has the permissions of the original account (dxy.

1.5: Other Options: SSL, MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR, and MAX_USER_CONNECTIONS. To restrict account login through ssl, you need to add require. To restrict resources, you need to add:

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 Security

2.1: MySQL5.7 has deleted the test database. By default, there is no test database after installation. In the past, any user can access the test database, increasing security risks.

2.2: MySQL5.7 provides simpler SSL secure access configuration, and the default connection uses SSL encryption. Before MySQL 5.7, you must manually create an SSL-related file. You can view this article. After MySQL 5.7

Mysql_ssl_rsa_setup can be directly generated:

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 some files ending with pem in the data directory, and these files are the files required to enable SSL connections (pay attention to file permissions), and then use the account

Default Logon:

root@t20:/var/lib/mysql# mysql -udba -p -h127.0.0.1Enter password: mysql> \s--------------mysql Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using EditLine wrapperConnection id: 4Current database: Current user: dba@localhostSSL: Cipher in use is DHE-RSA-AES256-SHA......

Force 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 wrapperConnection id: 10Current database: Current user: dba@localhostSSL: Cipher in use is DHE-RSA-AES256-SHA......

We can see from the above that all users have been logged on with ssl. If you want this user to be created through SSL each time, you need to set it through require ssl when creating the user, as described above. According to the test case in the article, the performance overhead of enabling SSL is about 25%: the SSL encrypted connection and performance overhead of MySQL

2.3: at the beginning of MySQL5.7, we recommend that you use mysqld -- initialize to initialize the database and discard the previous mysql_install_db method. In the new method, only one root @ localhost user is created, and the random password is stored in ~ /. In the mysql_secret file, the reset password is required for the first use.

Initialize Database: Create an instance.

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

2.4: Change of 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

Default SQL _mode before 5.7

Select @ SQL _mode;
NO_ENGINE_SUBSTITUTION

We can see that SQL _mode is stricter in 5.7. Explain the meaning of each mode:

ONLY_FULL_GROUP_BY	Do not point the query in the group by section to an unselected column.
STRICT_TRANS_TABLES	Enable strict mode for the transaction storage engine, or enable strict mode for the non-transaction storage engine
NO_ZERO_IN_DATE	In strict mode, the day or month is not accepted.
NO_ZERO_DATE	In strict mode, '2017-00-00 'is not used as the legal date
ERROR_FOR_DIVISION_BY_ZERO	In the strict mode, if the INSERT or UPDATE process is divided by zero (or MOD (X, 0), an error is generated.
NO_AUTO_CREATE_USER	Prevent GRANT from automatically creating new users unless a password is specified
NO_ENGINE_SUBSTITUTION	If the required storage engine is disabled or not compiled, the storage engine cannot be replaced automatically.

In the default 5.7 case:

---- For the datetime type <NO_ZERO_DATE>: insert the "0000-00-00 00:00:00" value, an error is returned: Incorrect datetime value ---- for the varchar/char type <STRICT_TRANS_TABLES>: The inserted string exceeds the length, data too long for column... ---- for columns not null <STRICT_TRANS_TABLES>: If a column not null is inserted, the following error occurs: Field 'xxx' doesn' t have a default value' ---- for grant <NO_AUTO_CREATE_USER>: if a user is authorized and no password is specified, the following error occurs: Can't find any matching row in the user table ---- for the engine storage engine <NO_ENGINE_SUBSTITUTION>: Create an unsupported storage engine, it will not be converted to the default storage engine, and an error is reported: Unknown storage engine... using storage engine InnoDB for table '...'

Note: In a master-slave environment, to ensure data consistency, you must set the same SQL _mode as the master-slave environment. During data migration, ensure that the SQL _mode is consistent, otherwise, the replication and migration will fail when the above restrictions are met, so use the standard SQL syntax as much as possible.

3. Conclusion:

In MySQL 5.7, there are many security-related improvements: create an account in two steps: use create user to create an account (increase the account length), and use grant to authorize; when the initial database is used, the password is not blank; the account can lock and set password expiration; the test database is deleted; ssl connections are provided by default; and SQL _mode enhancements are provided. This article introduces and tests these aspects to further deepen my understanding of MySQL.

The above section describes the security-related features of MySQL 5.7. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!