More Account Reports

[isotopp]

Cadfael should do more reporting on accounts:

• Accounts that use (for the server version outdated) authentication methods.

  • 5.6 should alert on mysql_old_password, 8.0 on mysql_native_password.

• Locked and expired accounts should be reported.

• Accounts with excessive privileges should be reported

  • Accounts with SUPER/rootlike/global privs.
  • Accounts that can execute DDL.

[xsist10]

This will be broken down into 3 checks:

• Account with outdated authentication methods
  • Check
  • Test
  • Documentation
• Locked or expired accounts
  • Check
  • Test
  • Documentation
• Accounts with excessive permissions
  • Check
  • Test
  • Documentation

@isotopp

1. For the first check:
   1.1 Does this only apply to MySQL 5.6 or all version of 5.* where * >= 6?
   1.2 What is the recommendation for MySQL 8.0 instead of using mysql_native_password. Using caching_sha2_pluggable?
2. For the last check, what would be the condition for warning? I suspect there will always be at least one account with DDL permissions and at least one SUPER user time account in normal operations. Or just make it always warn but not at a high warning level (just a CONCERN versus a CRITICAL issue)?

[xsist10]

Hey @isotopp. Are you still interested in the last set of checks "Accounts with excessive permissions"? If so I'd love your input on how best to structure it (see #85 (comment)).

If not I'll close up this issues with the other two checks since implemented.

[isotopp]

1.1 Applies to all versions past the one indicated.
1.2 correct, caching_sha2_pluggable
2. The default account with SUPER is "root". It can be renamed, and some obscure recommendations to do that exist. I have yet to see an installation that does, and I would not recommend to rename this. Other accounts with SUPER can exist, even legitimately, but should be reported. Do you have a mechanism to create exceptions ("I have seen this warning and I silence it, because I mark it as a false positive"?) That would be best. Otherwise it is a CoNCERN.

[isotopp]

https://www.percona.com/blog/a-quick-peek-at-mysql-8-0-34-and-mysql-8-1-0/

8.1.0 is based on 8.0.34, so along with the above changes, you get some additional material. The mysql_native_password authentication plugin is deprecated and subject to removal in a future version of MySQL.