MySQL 8.0 User and role management

MySQL8.0 new Add a lot of features, which in the user management to add the role of management, the default password encryption method has also been adjusted, from the previous SHA1 changed to SHA2, plus 5.7 of the disabled user and user expiration settings, such aspects of user management and authority management, but also increased user security. MySQL8.0, the files of the tables in the MySQL library are merged into the MYSQL.IBD (MySQL8.0 InnoDB engine refactoring) in the data root directory. At the same time MySQL8.0 can use set Persist dynamic modification parameters and saved in the configuration file (mysqld-auto.cnf, saved in the format of the JSON string), this is the Gospel of the DBA classmate, do not have to worry about the settings after forgetting to save in the configuration file after the restart is restored after the problem. Check out the official documentation for MySQL8.0 and see the new management method through the official sample.

1. mysql User management 1.1, verify the change of plug-in and password encryption mode

In MySQL 8.0, Caching_sha2_password is the default authentication plug-in instead of the previous version of Mysql_native_password, and the default password encryption method is SHA2. If you need to maintain the previous authentication method and maintain the previous version of the password encryption method needs to be modified in the configuration file, temporarily does not support dynamic modification, need to restart the effective: Default_authentication_plugin = Mysql_native_password.
Modify the 8.0 existing SHA2 password to the SHA1 mode:
ALTER USER ' root ' @ ' 127.0.0.1 ' identified by ' passowrd ' PASSWORD EXPIRE never; #修改加密规则为永不过期
ALTER USER ' root ' @ ' 127.0.0.1 ' identified with Mysql_native_password by ' password '; #更新一下用户的密码加密方式为之前版本的方式
FLUSH privileges; #刷新权限

1.2. User authorization and password change

MySQL8.0 user authorization and previous differences, the old version of the common authorization statement in 8.0 will be error:

MySQL8.0 Previous version:
GRANT all on . To wangwei @ 127.0.0.1 identified by ' passowrd ' with GRANT OPTION;
MySQL8.0 version:
CREATE USER wangwei @ 127.0.0.1 identified by ' PASSOWRD ';
GRANT all on . To wangwei @ with 127.0.0.1 GRANT OPTION;
Creation of users with expiration time in MySQL8.0:
CREATE USER wangwei @ 127.0.0.1 identified by ' Wangwei ' PASSWORD EXPIRE INTERVAL
GRANT all on . To wangwei @ with 127.0.0.1 GRANT OPTION;
MySQL8.0 Modify User password:
ALTER USER ' Wangwei ' @ ' 127.0.0.1 ' identified by ' Wangwei ';

1.3. Password Expiration Time management

To establish an automatic password expiration policy globally, use the DEFAULT_PASSWORD_LIFETIME system variable. Its default value is 0, and automatic password expiration is disabled. If the value default_password_lifetime positive integer n, the allowed password lifetime is indicated so that the password must change N per day. Can be added to the configuration file:
1: To establish a global policy with a password that is approximately six months old, start the server in the server my.cnf file by using the following line:
[mysqld]
Default_ password_lifetime=180
2: To establish a global policy so that the password never expires, set it default_password_lifetime to 0:
[mysqld]
Default_password_lifetime=0
This parameter can be dynamically set and saved:
set PERSIST default_password_lifetime =;
SET PERSIST default_password_lifetime = 0;
Create and modify a user with password expiration, an example of an account-specific expiration setting:
requires a password change every 90 days:
Create user ' wangwei ' @ ' localhost ' PASSWORD EXPIRE INTERVAL Day;
ALTER USER ' wangwei ' @ ' localhost ' PASSWORD EXPIRE INTERVAL;
Disable password expiration:
CREATE USER ' wangwei ' @ ' localhost ' PASSWORD EXPIRE never;
ALTER USER ' wangwei ' @ ' localhost ' PASSWORD EXPIRE never;
follows the global expiration policy:
CREATE USER ' wangwei ' @ ' localhost ' PASSWORD EXPIRE DEFAULT;
ALTER USER ' wangwei ' @ ' localhost ' PASSWORD EXPIRE DEFAULT;

1.4. mysql User password reuse policy settings

MySQL allows you to restrict the reuse of previous passwords. You can establish reuse limits based on the number of password changes, time spent, or both. The password history of the account consists of the passwords that were assigned in the past. MySQL can limit the selection of a new password from this history:
1: If the account is restricted based on the number of password changes, the new password cannot be selected from the specified number of latest passwords. For example, if the minimum number of password changes is set to 3, the new password cannot be the same as any of the most recent 3 passwords.
2: If the account is restricted due to time constraints, you cannot select a new password from the new password in the history, and the new password will not exceed the specified number of days. For example, if the password reuse interval is set to 60, the new password must not be between the passwords that were selected within the last 60 days.
Note: A blank password is not recorded in the password history and can be reused at any time.
To establish a password reuse policy globally, use the password_history and Password_reuse_interval system variables. To specify variable values at server startup, define them in the server my.cnf file.
Example:
To prevent the reuse of any passwords that last 6 passwords or passwords exceed 365 days, put these lines in your server my.cnf file:
[Mysqld]
Password_history=6
password_reuse_interval=365
To set up and save the configuration dynamically, use the following statement:
SET PERSIST password_history = 6;
SET PERSIST password_reuse_interval = 365;

2. MySQL8.0 Role Management

The MySQL role is a specified set of permissions. Like a user account, a role can have permissions to grant and revoke. You can grant the user account role to grant the account the permissions associated with each role. The user is granted the role permission, the user has permission to the role.
The following list summarizes the role management features provided by MySQL:
? The Create role and drop role roles are created and deleted.
? Grant and REVOKE assign and revoke permissions for users and roles.
? Show GRANTS displays permissions and role assignments for users and roles.
? The SET default role specifies which account roles are active by default.
? SET role changes the active role in the current session.
? The Current_role () feature displays the active roles in the current session.

2.1. Create roles and grant user role permissions

Consider the following scenarios:
? The application uses a database named app_db.
? Associated with an application, you can create and maintain application developers as well as administrator accounts.
? Developers need full access to the database. Some users only need to read permissions, and some users require read/write permissions.
To clearly differentiate the role's permissions, create the role as the name of the required permission set. By authorizing the appropriate roles, it is easy to grant the required permissions to the user account.
To create roles, use the Create role:
CREATE ROLE ' app_developer ', ' app_read ', ' app_write ';
The role name is very similar to the user account name, consisting of the user part and the host part in the format. The host section, if omitted, defaults to%. The user and host parts can be unquoted unless they contain special characters. Unlike the account name, the user portion of the role name cannot be empty. Assign permissions to a role, using the same syntax as assigning permissions to the User:
GRANT all on app_db.To ' App_developer ';
GRANT SELECT on app_db.To ' App_read ';
GRANT INSERT, UPDATE, DELETE on app_db.* to ' App_write ';
Now assume that you initially need a developer account, two users who need read-only access, and a user who needs read/write permissions. To create a user using CreateUser:
CREATE USER ' dev1 ' @ ' localhost ' identified by ' dev1pass ';
CREATE USER ' read_user1 ' @ ' localhost ' identified by ' read_user1pass ';
CREATE USER ' read_user2 ' @ ' localhost ' identified by ' read_user2pass ';
CREATE USER ' rw_user1 ' @ ' localhost ' identified by ' rw_user1pass ';
To assign each user the permissions they need, you can use the same statement as the one that you just displayed, but this needs to enumerate the individual permissions for each user. Instead, use grant to allow alternate syntax for authorizing roles rather than permissions:
GRANT ' app_developer ' to ' dev1 ' @ ' localhost ';
GRANT ' app_read ' to ' read_user1 ' @ ' localhost ', ' read_user2 ' @ ' localhost ';
GRANT ' App_read ', ' app_write ' to ' rw_user1 ' at ' localhost ';
A role that is authorized by the Rw_user1 user to read and write in Grant, combined with the read and write permissions required by the role.
The syntax of the grant authorization role differs from the syntax of the authorized user: There is an on to differentiate between roles and user authorization, there is on for user authorization, and no on for assigning roles. Because of different syntax, you cannot mix user rights and roles in the same statement. (Allows users to be assigned permissions and roles, but must use separate grant statements, each with a syntax that matches the content of the authorization.) ）

2.2. Check Role Permissions

To verify the permissions assigned to the user, use SHOW GRANTS. For example:
mysql> SHOW GRANTS for ' dev1 ' @ ' localhost ';
+-------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------+
| GRANT USAGE on.Todev1@localhost|
| GRANTapp_developer@%Todev1@localhost|
+-------------------------------------------------+
? However, it displays each assigned role without displaying it as the permissions that the role represents. If you want to display role permissions, add a using to display:
mysql> SHOW GRANTS for ' dev1 ' @ ' localhost ' USING ' app_developer ';
+----------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------+
| GRANT USAGE on.Todev1@localhost|
| GRANT all privileges onapp_db.To dev1 @ localhost |
| GRANT app_developer @ % to dev1 @ localhost |
+----------------------------------------------------------+
Also verify other types of users:
mysql> SHOW GRANTS for ' read_user1 ' @ ' localhost ' USING ' app_read ';
+--------------------------------------------------------+
| Grants for [email protected] |
+--------------------------------------------------------+
| GRANT USAGE on.to read_user1 @ localhost |
| GRANT SELECT on app_db .Toread_user1@localhost|
| GRANTapp_read@%Toread_user1@localhost|
+--------------------------------------------------------+
mysql> SHOW GRANTS for ' rw_user1 ' @ ' localhost ' USING ' app_read ', ' app_write ';
+------------------------------------------------------------------------------+
| Grants for [email protected] |
+------------------------------------------------------------------------------+
| GRANT USAGE on.Torw_user1@localhost|
| GRANT SELECT, INSERT, UPDATE, DELETE onapp_db. * Torw_user1@localhost|
| GRANTapp_read@%,app_write@%Torw_user1@localhost|
+------------------------------------------------------------------------------+

2.3. Revoke role or role permissions

Just as you can authorize a user's role, you can revoke these roles from your account:
REVOKE role from user;
Revoke can be used to modify role permissions for roles. This affects not only the role's own permissions, but also any user rights granted to the role. Assuming that you want to temporarily make all users read-only, use REVOKE to revoke the Modify permission from the App_write role:
REVOKE INSERT, UPDATE, DELETE on app_db.From ' App_write ';
It happens that a role does not have any permissions at all, as can be seen with show GRANTS (this statement can be used with the role, not just the query user right):
mysql> SHOW GRANTS for ' app_write ';
+---------------------------------------+
| Grants for [email protected]% |
+---------------------------------------+
| GRANT USAGE on.To app_write @ % |
+---------------------------------------+
Revoking permissions from a role affects the permissions of any user in the role, so rw_user1 now has no table modification permissions (INSERT, UPDATE, and delete permissions are gone):
mysql> SHOW GRANTS for ' rw_user1 ' @ ' localhost '
USING ' App_read ', ' app_write ';
+----------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------+
| GRANT USAGE on.to rw_user1 @ localhost |
| GRANT SELECT on app_db .Torw_user1@localhost|
| GRANTapp_read@%,app_write@%Torw_user1@localhost|
+----------------------------------------------------------------+
In fact, Rw_user1 read/write users have become read-only users. This also occurs for any other user who is granted the App_write role, which describes the ability to modify the role without having to modify the individual account.
To restore the role's modify permissions, simply re-grant them:
GRANT INSERT, UPDATE, DELETE on app_db.* to ' App_write ';
Now Rw_user1 has the Modify permission again, just like any other account that authorizes the App_write role.

2.4. Delete a role

To remove a role, use drop role:
drop role ' app_read ', ' app_write ';
Deleting a role revokes the role from each account that authorizes it.
2.5, roles and users in practice
assume that a legacy app development project starts before a role in MySQL occurs, so all users associated with the project are granted permissions directly (rather than granting role permissions). One of the accounts is the developer user who was initially granted permission, as follows:
CREATE user ' old_app_dev ' @ ' localhost ' identified by ' old_app_devpass ';
GRANT all on old_app.* to ' old_app_dev ' @ ' localhost ';
If this developer leaves the project, it is necessary to assign permissions to other users, or if the number of project participants increases, multiple users may be required. Here are some ways to fix the problem:
? Do not use roles: Change the account password so that the original developer cannot use it, and let the new developer use the account:
ALTER user ' old_app_dev ' @ ' localhost ' identified by ' New_password ';
? Use role: Lock the account to prevent anyone from using it to connect to the server:
ALTER USER ' old_app_dev ' @ ' localhost ' accounts lock;
The account is then treated as a role. For each developer of a new development project, create a new account and grant it the original developer account:
Create USER ' new_app_dev1 ' @ ' localhost ' identified by ' new_password ';
GRANT ' old_app_dev ' @ ' localhost ' to ' new_app_dev1 ' @ ' localhost '; The effect of the
is to assign the original developer account permissions to the new account.
MySQL8.0 's user and role management is becoming more and more like Oracle, 8.0 has a lot of new features, changes are still very large, need to continue to learn and test DBAs, update the MySQL new version of the knowledge, better operation MySQL database. The future MySQL database autonomy and intelligent database is the inevitable trend of development, the DBA is a liberation, but also a challenge.
At the same time also very grateful friends well-known MySQL database expert Wu Bingxi teacher in the busy schedule to proofread this article.

MySQL 8.0 User and role management