Detections
Analytics

7.9 Ensure 'activate_all_roles_on_login' is disabled and mandatory role activation is explicitly controlled

Information

MySQL roles are not effective until they are activated. When activate_all_roles_on_login is enabled, MySQL automatically activates all roles granted to an account at login, including explicitly granted roles and roles listed in mandatory_roles . This behavior takes precedence over roles configured with SET DEFAULT ROLE . MySQL documents activate_all_roles_on_login as a global, dynamic Boolean variable with a default value of OFF.

For MySQL 9.7.0 and later, activate_mandatory_roles provides more granular control over automatic role activation. If activate_all_roles_on_login is disabled and activate_mandatory_roles is enabled, only mandatory roles are activated in addition to the account's default roles. If activate_all_roles_on_login is enabled, activate_mandatory_roles is ignored and both mandatory and explicitly granted roles are activated. MySQL 9.5 introduced activate_mandatory_roles, and it is enabled by default.

Enabling activate_all_roles_on_login can increase a user's effective privileges beyond the minimum required for normal operation because every granted role becomes active at login. This may bypass the intended security boundary established by SET DEFAULT ROLE, where only approved default roles are activated automatically. MySQL states that activate_all_roles_on_login takes precedence over default roles specified with SET DEFAULT ROLE.

In MySQL, activate_mandatory_roles should be used instead of activate_all_roles_on_login when organization-wide baseline roles must be active automatically. This allows mandatory roles to be activated without automatically activating every role granted to each user. Mandatory roles should still be reviewed for least privilege, because MySQL treats a mandatory role as granted to all users.

NOTE: Nessus has provided the target output to assist in reviewing the benchmark to ensure target compliance.

Solution

Disable automatic activation of all granted roles:

SET PERSIST activate_all_roles_on_login = OFF;

Enable activation of mandatory roles when mandatory baseline roles are intentionally used:

SET PERSIST activate_mandatory_roles = ON;

If mandatory roles are not intended, clear the mandatory role list:

SET PERSIST mandatory_roles = '';

After remediation, re-run the audit query to confirm the active global values. SET PERSIST applies the value to the running instance and persists it across restarts. Changing mandatory_roles requires ROLE_ADMIN plus SYSTEM_VARIABLES_ADMIN, or the deprecated SUPER privilege.

Impact:

Disabling activate_all_roles_on_login may cause users or applications that rely on all granted roles being active at login to lose access until required roles are configured as default roles or explicitly activated with SET ROLE.

Enabling activate_mandatory_roles may cause privileges from mandatory_roles to become active automatically for all users. Any mandatory role must therefore be reviewed to ensure it contains only approved baseline privileges. MySQL prevents roles with the SYSTEM_USER privilege from being listed in mandatory_roles, but MySQL also recommends avoiding SYSTEM_USER through roles to guard against privilege escalation.

See Also

https://workbench.cisecurity.org/benchmarks/22687

Item Details

Category: ACCESS CONTROL, AUDIT AND ACCOUNTABILITY

References: 800-53|AC-2, 800-53|AC-3, 800-53|AC-6, 800-53|AC-6(1), 800-53|AC-6(7), 800-53|AU-9(4), CSCv7|14

Plugin: MySQLDB

Control ID: a5874e157aea4731c23fca8879bdccc105ad7b4597e98d329b770415ac4a7057