MySQL 8.0 user and role management principles and usage details

This article describes MySQL 8.0 user and role management with examples. Share with you for your reference, the details are as follows:

MySQL 8.0 adds many new features, including role management in user management.

The default password encryption method has also been adjusted from sha1 to sha2, and the 5.7 settings for disabling users and user expiration have been added.

This improves user management and authority management, and also increases user security.

In MySQL 8.0, the files of the tables in the MySQL library are merged into mysql.ibd in the data root directory (MySQL 8.0 Innodb engine reconstruction).

At the same time, MySQL 8.0 can use SET PERSIST to dynamically modify parameters and save them in the configuration file (mysqld-auto.cnf, saved in JSON string format).

This is good news for DBAs. They don't have to worry about forgetting to save the settings in the configuration file and restoring the settings after restart.

I consulted the official documentation of MySQL 8.0 and used official examples to see the new management method.

1. MySQL user management

1.1. Changes in authentication plugins and password encryption methods In MySQL 8.0, caching_sha2_password is the default authentication plugin instead of mysql_native_password in previous versions.

The default password encryption method is sha2.

If you need to keep the previous authentication method and the password encryption method of the previous version, you need to modify it in the configuration file. Dynamic modification is not supported yet and requires a restart to take effect: default_authentication_plugin = mysql_native_password.

Change the existing sha2 password in 8.0 to sha1 mode:

ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'passowrd' PASSWORD EXPIRE NEVER;

#Modify the encryption rule to never expire

ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'password';

#Update the user's password encryption method to the previous version

FLUSH PRIVILEGES; #Refresh permissions

1.2 User authorization and password modification

User authorization in MySQL 8.0 is different from that in previous versions. Common authorization statements in old versions will report errors in 8.0:

MySQL versions prior to 8.0:

GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd' WITH GRANT OPTION;

MySQL 8.0 version:

# Create an account and password CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd';
# Grant permissions GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;
# Delete permissions REVOKE all privileges ON databasename.tablename FROM 'username'@'host';
# Change password ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password';

Creation of users with expiration time in MySQL 8.0:

  CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'wangwei' PASSWORD EXPIRE INTERVAL 90 DAY;

  GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;

MySQL8.0 change user password:

1.2. Password expiration management

To establish an automatic password expiration policy globally, use the default_password_lifetime system variable. Its default value is 0, which disables automatic password expiration.

If the value of default_password_lifetime is a positive integer N, then it indicates the allowed password lifetime, so that the password must be changed every N days. You can add it to the configuration file:

1: To establish a global policy that passwords expire after approximately six months, start the server with the following line in the server my.cnf file:

[mysqld]default_password_lifetime=180

2: To establish a global policy so that passwords never expire, set default_password_lifetime to 0:

[mysqld]default_password_lifetime=0

This parameter can be set and saved dynamically:

SET PERSIST default_password_lifetime = 180;SET PERSIST default_password_lifetime = 0;

Create and modify users with password expiration, account specific expiration time settings example:

Require password changes every 90 days:

CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

Disable password expiration:

CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE NEVER;

ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE NEVER;

Follow the global expiration policy:

CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT;

ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT; 1.3 MySQL user password reuse policy setting

MySQL allows you to restrict the reuse of previous passwords. Reuse restrictions can be established based on the number of password changes, the amount of time that has passed, or both. The password history of an account consists of passwords that have been assigned in the past.

MySQL can restrict the selection of new passwords from this history:

1. If you restrict an account based on the number of password changes, you cannot select a new password from the specified number of most recent passwords. For example, if the minimum number of password changes is set to 3, then the new password cannot be the same as any of the most recent 3 passwords.

2. If the account is restricted due to time restrictions, it is not possible to select a new password from the new password in the history that is no older than the specified number of days. For example, if the password reuse interval is set to 60, the new password must not be between passwords selected in the last 60 days.

Note: Empty passwords are 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 disallow reuse of the last 6 passwords or any password older than 365 days, put these lines in your server my.cnf file:

[mysqld]password_history=6password_reuse_interval=365

To dynamically set and save the configuration, use a statement like this:

SET PERSIST password_history = 6;

SET PERSIST password_reuse_interval = 365;2. Role management in MySQL 8.0

A MySQL role is a named collection of privileges. Like user accounts, roles can have permissions granted and revoked.

A user account can be granted roles, granting the account the permissions associated with each role. If a user is granted role permissions, the user has the permissions of the role.

The following list summarizes the role management capabilities provided by MySQL:

• CREATE ROLE and DROP ROLE role creation and deletion;
• GRANT and REVOKE assign and revoke privileges to users and roles;
• SHOW GRANTS displays permissions and role assignments for users and roles;
• SET DEFAULT ROLE specifies which account roles are active by default;
• SET ROLE changes the active role in the current session.
• The CURRENT_ROLE() function 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 called app_db.
• Associated with the application can be the developer and administrator accounts that create and maintain the application.
• Developers need full access to the database. Some users only need read permissions, while others need read/write permissions.

To clearly distinguish the permissions of a role, create a role with the name of the required permission set. By granting the appropriate roles, you can easily grant the required permissions to user accounts.

To create a role, use CREATE ROLE:

CREATE ROLE 'app_developer', 'app_read', 'app_write';

Role names are very similar to user account names, consisting of a user portion and a host portion in the format. The host part, if omitted, defaults to %. The user and host parts may be unquoted unless they contain special characters. Unlike account names, the user portion of a role name cannot be empty. To assign permissions to a role, use the same syntax as assigning permissions to a 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';CREATE ROLE 'app_developer', 'app_read', 'app_write';

Now assume that you initially need one developer account, two users who need read-only access, and one user who needs read/write access.

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 privileges they need, you can use a GRANT statement of the same form as just shown, but this requires listing each user's individual privileges.

Instead, use the alternative syntax of GRANT that allows granting roles rather than privileges:

GRANT 'app_developer' TO 'dev1'@'localhost';GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

Combine the read and write permissions required by the role and grant the rw_user1 user read and write permissions to the role in GRANT.

The syntax for GRANT to authorize roles is different from the syntax for authorizing users: there is an ON to distinguish between role and user authorization, with ON for user authorization, and no ON for assigning roles.

Because the syntax is different, you cannot mix assigning user permissions and roles in the same statement. (Assigning privileges and roles to users is allowed, but separate GRANT statements must be used, with the syntax of each statement matching that of the grant.)

2.2 Check role permissions

To verify the privileges assigned to a user, use SHOW GRANTS. For example:

mysql> SHOW GRANTS FOR 'dev1'@'localhost';

However, it displays each granted role without showing 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';

Same goes for authenticating other types of users:

mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';

2.3 Revoking a role or role permissions

Just as roles can be granted to a user, they can be revoked from an account:

REVOKE role FROM user;

REVOKE can be used to modify role permissions. This affects not only the permissions of the role itself, but also the permissions of any users granted to that role. Suppose you want to temporarily make all users read-only, use REVOKE to revoke the modification permission from the app_write role:

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';

As it happens, a certain role has no privileges at all, as can be seen with SHOW GRANTS (this statement can be used with roles, not just to query the privileges available to a user):

mysql> SHOW GRANTS FOR 'app_write';

Revoking privileges from a role affects the privileges of any user in that role, so rw_user1 now has no table modification privileges (INSERT, UPDATE, and DELETE privileges are gone):

mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';

In effect, the rw_user1 read/write user has become a read-only user. This will also happen for any other user who is granted the app_write role, illustrating the ability to modify permissions using roles without having to modify individual accounts.

To restore the role's edit permissions, simply regrant them:

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

Now rw_user1 has modify permissions again, just like any other account granted the app_write role.

2.4 Deleting a Role

To remove a role, use DROP ROLE:

DROP ROLE 'app_read', 'app_write';

Deleting a role revokes the role from every account that granted it.

2.5 Practical Application of Roles and Users

Assume that a legacy application development project began before roles existed in MySQL, so all users associated with the project had privileges granted directly (rather than granted role privileges). One of the accounts is the developer user who was initially granted permissions 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 may be necessary to assign permissions to other users, or if more people are involved in the project, multiple users may be needed. Here are some ways to solve this problem:

• Without using roles: Change the account password so the original developer cannot use it, and have the new developer use that account:

ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';

• Use Roles: Lock the account to prevent anyone from using it to connect to the server:

ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;

The account is then treated as a role. For each new developer developing a new project, create a new account and grant it to their 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 is to assign the original developer account permissions to the new account.

The user and role management of MySQL 8.0 is becoming more and more like Oracle. There are many new features in 8.0, and the changes are still very large. DBAs need to continue to learn and test, update their understanding of the new version of MySQL, and better operate and maintain the MySQL database. In the future, MySQL database autonomy and intelligent database are inevitable development trends, which will bring both liberation and challenge to DBAs.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.