Mysql8.0 --- properties of create user
01mysql version 8.0.27 introduction
The latest version of MySQL is 8.0.27. I downloaded one today. Have a try. This version is more like a bug fix version, which fixes more than 200 bugs.
Seeing this result, if you want to select 8.0 series for online version, it is recommended to avoid 8.0.26 and 8.0.27. The reasons are as follows:
First, there are too many bug s in version 8.0.26, which is not recommended as an online version;
Second, 8.0.27 fixes these bug s, so there are many code changes. It is not recommended to use this version as an online version.
I am mainly interested in the syntax of Create User. Here are two changes:
First, create user and alter user syntax support the definition of multiple authentication methods;
Second, the user program can log in to MySQL with -- password1, - password2, - password3
However, this content will be explained in a special article later. Today, we mainly look at the lengthy and complex attributes in create user
02create user syntax
I simply wrote a create user syntax, but I still gained a lot.
mysql> create user yeyz@'10.%'; ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. No connection. Trying to reconnect... Connection id: 1606616 Current database: *** NONE *** Query OK, 0 rows affected (0.01 sec) mysql> mysql> show create user yeyz@'10.%'; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER for yeyz@10.% | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER `yeyz`@`10.%` IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
1. The red prompt directly prompts us that MySQL Server is disconnected because the connection is not active. We need to consider whether it is a wait_timeout and interactive_timeout parameter, which is more humanized.
2. In MySQL 8.0.27, an account has many attributes, such as:
- IDENTIFIED WITH 'mysql_native_password ': authentication method
You can choose the following three types,
mysql_native_password
sha256_password
caching_sha2_password
The default value is caching_sha2_password, from a security point of view, is more secure than other methods, but in order to be compatible with lower version 5.7, MySQL is preferred_ native_ Password, because mysql_native_password is the default encryption method for MySQL 5.7
- REQUIRE NONE: whether to encrypt the client connection
If your MySQL needs to encrypt the current client connection and connect through ssl mode, you need to configure -- ssl mode = required on the client to support encrypted connection. If the Require option is None, it means that the current connection is not encrypted.
- PASSWORD EXPIRE DEFAULT: password expiration policy
The default password expiration policy is adopted here. Under the default policy, password expiration depends on the parameter default_password_lifetime, and the value of this parameter can be 0, which means no expiration, or a number N, which means that the password must be changed after N days.
mysql> show variables like '%default_password_lifetime%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | default_password_lifetime | 0 | +---------------------------+-------+ 1 row in set (0.00 sec)
In addition, other values can be taken, such as
password expire never: the password will never expire;
password expire INTERVAL N DAY: the password expires in N days
password expire: the password expires immediately (I don't know what this function wants to do)
- Account Unlock: whether the account is locked
Generally, when creating an account, you can specify the locking status of the account. In the online environment, an account is generally not locked. If an account is locked and you try to connect to the account, the following error will appear:
Access denied for user 'user_name'@'host_name'. Account is locked.
In some special scenarios, we can temporarily lock some accounts to intercept some business requests outside the database.
- PASSWORD HISTORY default: password usage policy
This attribute represents whether the current database supports password reuse. It can be followed by two values, default and integer N.
If it is an integer N, it means that the password can be the same as the previous nth password; For example, we set the password aaa for the first time, bbb for the second time, and ccc for the third time. If N=3, we can set the password aaa for the fourth time after the ccc password expires;
If n > 3, the fourth password cannot be set to aaa, because aaa is the latest password in history
If default is used, it means that the password usage history policy depends on password_ The history system variable is as follows:
mysql> show variables like '%password_history%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | password_history | 0 | +------------------+-------+ 1 row in set (0.00 sec)
0 here represents no limit, and other numbers are consistent with the above description.
Note: after setting the specific N, the system variable will be ignored.
- PASSWORD REUSE INTERVAL: password usage time policy
This attribute, similar to the above attributes, is only a time dimension, which can be followed by a specific number of days N or the default value default.
If it is a specific number of days, N means that this account can be used within N days. After that, you must change your password
CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 360 DAY;
The above case represents that the password can be used for 360 days.
If it is default, the specific number of days depends on the system variable password_reuse_interval, as follows:
mysql> show variables like '%password_reuse_interval%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | password_reuse_interval | 0 | +-------------------------+-------+ 1 row in set (0.01 sec)
Note: after setting the specific N, the system setting will be ignored.
- PASSWORD REQUIRE CURRENT: whether the original password authentication is required
This attribute controls whether the original password authentication is required when changing the password. It is not required by default. Its values can be blank, optional or default
If nothing is followed, that is, empty, it means that the original password needs to be specified before other user passwords can be modified
If optional is selected, the passwords of other users can be modified without specifying the original password
If default is selected, the specific behavior depends on password_require_current parameter: if it is off, it means the original password is not required; if it is on, it means the original password is required;
mysql> show variables like '%password_require_current%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | password_require_current | OFF | +--------------------------+-------+ 1 row in set (0.00 sec)
Note that if optional or null is defined, the system variable password_require_current will be ignored.
In addition, if an account has create user permission or update permission of mysql.user table, you can directly modify the password of other accounts without the original password.
To summarize briefly:
When creating an account with Create user syntax, the following 7 attributes can be defined for the password of the account:
1. Password encryption method
2. Encrypt client connections
3. Does the account and password have expiration policy
4. Is the account locked
5. Password history policy
6. Password usage days policy
7. Do you need the original password policy to modify the password
Of course, these properties can be set as default, which is usually the same. However, these features introduced in MySQL 8.0 can improve password security and availability in some specific scenarios.