MySQL 8.0 finally allows you to use roles to grant privileges. Most companies are now required to fulfill stricter security requirements, one of those requirements is each user must login using their own username. Roles allow you to grant individual users permissions in one go, rather than trying to maintain permissions per user.

Sidenote: If your users are coming in with a shared user, understand that in the future that kind of access is going away. If you get audited for security, shared users are red flag. The reason being is, if something goes wrong or the wrong activity occurs, it can’t be determined easily who did the wrong thing.

This may seem like a mundane topic over trying to crack password hashes, or discovering the mysql_native_password plugin can be broken easily using rainbox tables, but roles will make managing privileges much much easier. Think of roles as removing a layer of redundancy (having to grant all users all the required permissions). Spending the time to get roles setup means less work controlling user privileges in the future.

Task 7: Identify groups of permissions, create a role, grant the role to individual usernames.

This is a multi stage task.

1. Get a list of current usernames and host and their privileges.
2. Review this list and generate a list of privileges which are required or are in common for the current usernames.
3. Create roles to cover all groups of privileges.
4. Grant this role the privileges identified in step 2.
5. Revoke individual privileges from the user so the user has permissions only via the roles granted to them.

For more details, MySQL roles and MariaDB roles

Until next time.

If you are still running the old MySQL authentication plugin commonly called the “mysql_native_password” authentication plugin in MySQL 5.7 and older, then you need to migrate to something better.

The mysql_native_password plugin (used in MariaDB as well) is based on SHA1. This hash function simply isn’t good enough anymore unless minimum password length is much longer than the current default of 8 characters.

Why this paranoia you ask? Rainbow tables and the technology driving cheaper and bigger disk storage and machine learning/deep learning driving GPU performance.

Rainbow tables are used once you realize storing pre-computed hashes of all plain text passwords for all combinations quickly consumes a lot of disk space.

• This site https://www.freerainbowtables.com/ used donated time to built huge rainbow tables.
• This site http://project-rainbowcrack.com/table.htm shows how to build your own tables.

Task 6: Use a stronger authentication plugin than the mysql_native_password.

Check SQL: 

select plugin,count(*) from mysql.user group by plugin

The Fix:

• In MySQL land, upgrade to MySQL 8.0 and the caching_sha2_password authentication plugin.
• In MariaDB land, change to use the ed25519 plugin.

Until next time.