Shulou(Shulou.com)06/01 Report--

This article mainly shows you how to use MySQL 8.0 users and role management, the content is easy to understand, I hope you can learn, after learning there will be gains, the following let the editor to take a look at it.

MySQL8.0 adds a lot of new functions, including role management in user management.

The default password encryption method has also been adjusted from the previous sha1 to sha2, with 5.7 disabled users and user expiration settings

In this way, the management of users and permissions also increases the security of users.

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

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

This is the good news for DBA students. You don't have to worry about the problem that you forget to save it in the configuration file after setting it up and it will be restored after restart.

Check the official documentation of MySQL8.0 to see the new management style through official examples.

1. MySQL user management

1.1.Authentication plug-ins and password encryption changes in MySQL 8.0, caching_sha2_password is the default authentication plug-in rather than the previous version of mysql_native_password

The default password encryption method is sha2.

If you need to maintain the previous verification method and the password encryption method of the previous version, you need to modify it in the configuration file. Dynamic modification is not supported for now. Reboot is required to take effect: default_authentication_plugin = mysql_native_password.

Change the 8.0 existing sha2 password to the mode of sha1:

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 password encryption method of the user to the previous version

FLUSH PRIVILEGES; # Refresh permissions

1.2 user authorization and password modification

The user authorization of MySQL8.0 is different from that before, and the commonly used authorization statements in the old version will report an error in 8.0:

Previous versions of MySQL8.0:

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

MySQL8.0 version:

# create account password CREATE USER `wangwei` @ `127.0.0.1` IDENTIFIED BY 'passowrd';# grant permission GRANT ALL ON *. * TO `wangwei` @ `127.0.0.1` WITH GRANT OPTION;# delete permission REVOKE all privileges ON databasename.tablename FROM' username'@'host';# modify password ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY' your password'

Creation of users with expiration time in MySQL8.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 changes the user's password:

1.2. Password expiration time 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 default_password_lifetime is a positive integer N, it represents the allowable password lifetime so that the password must change N every day. Can be added to the configuration file:

1: to establish a global policy, the password is about six months old, 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 expired passwords, example of account-specific expiration time settings:

The password is required to change 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 Settings

MySQL allows you to restrict the reuse of previous passwords. Reuse limits can be established based on the number of password changes, elapsed time, or both. The password history of an account consists of passwords assigned in the past.

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

1. If the account is limited according to the number of password changes, you cannot select a new password 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 last three passwords.

2. If the account is restricted due to time constraints, you cannot select a new password from the new password in the history, which 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 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 the values of variables when the server starts, define them in the server my.cnf file.

Example:

To prohibit the reuse of the last 6 passwords or any passwords that are longer than 365 days, put these lines in your server my.cnf file:

[mysqld] password_history=6password_reuse_interval=365

To set and save the configuration dynamically, use the following statement:

SET PERSIST password_history = 6

SET PERSIST password_reuse_interval = 365, role Management of MySQL8.0

The MySQL role is the specified set of permissions. Like user accounts, roles can have permissions that are granted and revoked.

You can grant a user account role and grant that account permissions related to each role. If a user is granted permission to a role, the user has permissions for that role.

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

CREATE ROLE and DROP ROLE role creation and deletion, GRANT and REVOKE assign and revoke permissions 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, and 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, it can be used for the developer and administrator accounts who 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 the role as the name of the desired permission set. By authorizing the appropriate roles, you can easily grant the required permissions to the user account.

To create a role, use CREATE ROLE:

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

The role name is very similar to the user account name and consists of the user part and the host part of the format. The host part, if omitted, defaults to%. 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 roles, using the same syntax as assigning permissions to users:

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 suppose you initially need a 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 permissions he or she needs, you can use the statement GRANT in the same form as the one just shown, but this requires enumerating each user's personal permissions.

Instead, use an alternative syntax that allows you to authorize 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'@'localhost'

A role that authorizes rw_user1 users to read and write in GRANT with the read and write permissions required by the role.

The syntax of authorized roles in GRANT is different from that of authorized users: there is an ON to distinguish between roles and user authorization, there is ON to authorize users, and there is no ON to assign roles.

Because the syntax is different, you cannot mix user permissions and roles in the same statement. (permissions and roles are allowed to be assigned to users, but separate GRANT statements must be used, and the syntax of each statement matches the authorized content. )

2.2 check role permissions

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

Mysql > SHOW GRANTS FOR 'dev1'@'localhost'

However, it displays each granted role instead of 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'

Also validate other types of users:

Mysql > SHOW GRANTS FOR 'read_user1'@'localhost' USING' app_read'

2.3 revoke roles or role permissions

Just as you can authorize a user's roles, you can revoke these roles from the account:

REVOKE role FROM user

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

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

As it happens, a role does not have any permissions at all, as you can see SHOW GRANTS (this statement can be used with the role, not just to query the user permissions available):

Mysql > SHOW GRANTS FOR 'app_write'

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

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

In fact, rw_user1 read / write users have become read-only users. This can also happen to any other user who is granted the app_write role, indicating that you do not have to modify the permissions of the individual account to modify the role.

To restore modify permissions for roles, simply re-grant them:

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

Now rw_user1 once again has permission to modify, just like any other account that authorizes the app_write role.

2.4 deleting roles

To delete a role, use DROP ROLE:

DROP ROLE 'app_read',' app_write'

Deleting a role removes the role from each account that authorizes it.

2.5 Application of roles and users in practice

Assume that the legacy application development project starts before the role in MySQL appears, so all users associated with the project grant permissions directly (rather than role permissions). One of the accounts is the developer user who was originally granted permission, as shown below:

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 there are more project participants, multiple users may be required. Here are some ways to solve this 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 roles: lock the account to prevent anyone from using it to connect to the server:

ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK

Then treat the account as a role. For developers of each 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 is to assign the original developer account permissions to the new account.

The user and role management of MySQL8.0 is more and more like Oracle. There are many new features in 8.0, which still change a lot. It is necessary for DBA to constantly learn and test, update the understanding of the new version of MySQL, and better operate and maintain the MySQL database. In the future, MySQL database autonomy and intelligent database is an inevitable development trend, which is not only a liberation but also a challenge for DBA.

The above is about how to use MySQL 8.0 user and role management, if you have learned knowledge or skills, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.