MySQL 8.0 User Management, Password Policies, and Role Management

MySQL 8.0 introduces many enhancements for user management, such as built‑in role support, a default authentication plugin caching_sha2_password with SHA‑2 password hashing, and the ability to persist configuration changes using SET PERSIST (saved in mysqld-auto.cnf as JSON).

1. Authentication plugin and password hashing changes

在MySQL 8.0中，caching_sha2_password是默认的身份验证插件而不是之前版本的mysql_native_password，默认的密码加密方式是sha2。
如果需要保持之前的验证方式并保持之前版本的密码加密方式需要在配置文件中修改，暂不支持动态修改，需要重启生效：default_authentication_plugin = mysql_native_password。

To revert to the old method you must edit the configuration file and restart the server.

ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'passowrd' PASSWORD EXPIRE NEVER;  # password never expires
ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'password';  # use old authentication plugin
FLUSH PRIVILEGES;

2. User creation, granting privileges and password expiration

MySQL 8.0 separates user creation from privilege granting. The old GRANT ... IDENTIFIED BY syntax now fails.

GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd' WITH GRANT OPTION;  # old syntax – error in 8.0

Correct approach:

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

Creating users with password expiration:

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;

Global password‑lifetime policy can be set with the default_password_lifetime system variable, either in my.cnf or dynamically with SET PERSIST :

[mysqld]
default_password_lifetime=180   # passwords expire after 180 days

SET PERSIST default_password_lifetime = 180;
SET PERSIST default_password_lifetime = 0;   # disable expiration

Examples for forcing password change every 90 days or disabling expiration:

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

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

3. Password reuse policy

MySQL can limit reuse of recent passwords using password_history and password_reuse_interval variables.

[mysqld]
password_history=6
password_reuse_interval=365   # disallow reuse of passwords used in the last 365 days

SET PERSIST password_history = 6;
SET PERSIST password_reuse_interval = 365;

4. Role management

Roles are named collections of privileges that can be granted to users. Core commands include:

CREATE ROLE / DROP ROLE

GRANT … TO role

GRANT role TO user

SHOW GRANTS (optionally WITH USING to expand role privileges)

REVOKE … FROM role

SET DEFAULT ROLE, SET ROLE, CURRENT_ROLE()

Creating roles and assigning them:

CREATE ROLE 'app_developer', 'app_read', 'app_write';
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';

Creating users and granting roles instead of individual privileges:

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';

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';

Viewing role privileges with SHOW GRANTS ... USING :

SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';

Revoking privileges from a role (affects all users that have the role):

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

Dropping a role removes it from every account that was granted it:

DROP ROLE 'app_read', 'app_write';

Practical scenario: legacy applications may have users with direct privileges; converting them to role‑based access involves locking the old account, creating new accounts, and granting the old account as a role.

ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;
CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';
GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';

MySQL 8.0’s user and role features bring it closer to Oracle’s model, requiring DBAs to continuously learn and test the new capabilities as the platform evolves toward autonomous and intelligent database management.