Creating a New User in MySQL 8
Creating a new user in MySQL is a common task for every admin and developer using MySQL database. If you are creating a new user in MySQL 8, you may not be aware of some new features relating to the CREATE USER and ALTER USER statements—in MySql 8, you may create a user that requires multiple authentication methods, uses a random password, with comments and attributes annotated with the user, and configured failed logins.
Grant Tables Are Transactional
Grant tables are the tables in the mysql system database that hold information about user accounts and the privileges held by the users. The grant tables in MySQL 8.0 are stored using the InnoDB storage engine instead of the MyISAM storage engine in earlier versions. What does that mean for a common user? The InnoDB tables are transactional tables, whereas the MyISAM tables are non-transactional. With transactional tables, each statement is transactional, implying that it runs within a single transaction. Account management DDL statements such as CREATE USER and DROP USER that specify multiple users in a single statement will either succeed or fail for all users. If the statement fails for even one of the 10 users, as an example, the complete statement is rolled back and no changes are made.
Multifactor Authentication Is Supported
Multifactor Authentication (MFA) is authentication involving multiple factors or methods. Prior to 8.0.27, only a single factor authentication using a username and password was supported. With the new version up to 3 factor authentication (3FA) may be used. The additional authentication methods could be email, phone number, or one of the pluggable methods provided by the authentication plugins. The default authentication option makes use of the Native Pluggable Authentication provided by the mysql_native_password plugin, which is built-into the MySQL server. Just about any authentication method you are familiar with is supported. Some of the other plugins are Caching SHA-2 Pluggable Authentication, SHA-256 Pluggable Authentication, Client-Side Cleartext Pluggable Authentication, Windows Pluggable Authentication, LDAP Pluggable Authentication, and Kerberos Pluggable Authentication. The authentication methods, or factors, that could be used may be configured in the authentication_policy system variable. Up to three authentication methods may be set; the default being any authentication method with two optional, additional authentication methods. Client programs support the --password1, --password2, and --password3 command-line options.
Support for Random Password
A new user account may be created with a random password. Use the IDENTIFIED BY RANDOM PASSWORD clause to generate a random password. A random password may be created only if user credentials are stored internally to MySQL in the mysql.user system table, which is supported only when using one of the authentication plugins: mysql_native_password, caching_sha2_password, and sha256_password. A random password may also be set with the ALTER_USER, and SET_PASSWORD statements.
User Comments and Attributes
If you want to annotate the CREATE USER or ALTER USER statements with any comments, you may do so with the COMMENT clause. An example could be: CREATE USER 'John'@'localhost' COMMENT 'This is John Smith\'s account';
To add an attribute, which is a key-value pair as a JSON object, use the ATTRIBUTE clause. As an example:
ALTER USER 'John'@'localhost' ATTRIBUTE '{"email":"[email protected]"}';. A single CREATE_USER, or ALTER_USER may specify only a single instance of one of the COMMENT and ATTRIBUTE clauses.
Failed Logins
Some new password_option values to the CREATE_USER are added to be able to configure failed logins. The number of failed login attempts after which an account is locked, and the number of days for which to lock an account may be set with the clauses FAILED_LOGIN_ATTEMPTS N and PASSWORD_LOCK_TIME {N | UNBOUNDED} respectively.
