CertGrid CertGrid
Configuration·MySQL

MySQL Secure Installation and Password Validation

The four things `mysql_secure_installation` is famous for are already done on Ubuntu - the transcript shows no anonymous users, no test database and root bound to localhost. What is missing is password validation, which is a component you install yourself.

Foundations Guide 2 of 45 Beginner

Written against the versions above. Ubuntu's packaging does most of this at install time. On a tarball or Oracle APT install the same checks are worth running, and they will find work to do.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Look for anonymous accounts

    The first thing the script removes. An anonymous account has an empty user name and lets anyone who can reach the server connect without identifying themselves.

    The result is empty - not a table with no rows, but no output at all, which is what --table prints when nothing matches. Ubuntu's package does not create them.

    bash 
    sudo mysql --table -e "SELECT user, host FROM mysql.user WHERE user = ''"

    Expected resultNo rows. There are no anonymous accounts to remove.

    Success conditionYou have confirmed the first hardening step is already done.

  2. Look for the test database

    The second thing the script removes: a database called test that any account could write to, plus the mysql.db grants that made that possible.

    SHOW DATABASES lists four, and every one is a system schema - information_schema, mysql, performance_schema and sys. No test. The mysql.db table then shows only two rows, both internal accounts being granted their own schemas.

    Two of the four classic steps, and neither has anything to do.

    bash Example session 
    sudo mysql -e "SHOW DATABASES"Databaseinformation_schemamysqlperformance_schemasyssudo mysql --table -e "SELECT Host, Db, User FROM mysql.db"+-----------+--------------------+---------------+| Host      | Db                 | User          |+-----------+--------------------+---------------+| localhost | performance_schema | mysql.session || localhost | sys                | mysql.sys     |+-----------+--------------------+---------------+

    Expected resultFour system databases, no test, and two internal rows in mysql.db.

    Success conditionYou have confirmed there is no test database and no loose grant on one.

  3. Check whether root can reach the server from anywhere else

    The third step: disallowing remote root login.

    root exists once, with host localhost. The host column is part of the account identity in MySQL - root@localhost and root@% would be two different accounts with different passwords. There is no second root here, so there is no remote root to disable.

    bash Example session 
    sudo mysql --table -e "SELECT user, host FROM mysql.user WHERE user = 'root'"+------+-----------+| user | host      |+------+-----------+| root | localhost |+------+-----------+

    Expected resultOne row: root at localhost.

    Success conditionYou know root cannot be used from another machine.

  4. Find the step that IS missing

    Password validation is a loadable component, and mysql.component is the table that records which components are installed.

    It comes back empty. Nothing is enforcing password quality, so any account you create can have any password you like - including abc.

    This is the one part of the traditional hardening pass that Ubuntu leaves to you.

    bash 
    sudo mysql --table -e "SELECT component_urn FROM mysql.component"

    Expected resultNo rows. No components are installed.

    Success conditionYou have found the gap the packaging does not close.

  5. Install the validation component and read the policy it applies

    INSTALL COMPONENT is persistent - the component is recorded in mysql.component and loads again on every restart. There is nothing to add to a config file.

    The variables it registers are the policy. At MEDIUM, the default, a password must be at least 8 characters and contain at least one uppercase, one lowercase, one digit and one special character. check_user_name being ON also blocks a password that matches the account name.

    bash Example session 
    sudo mysql -e "INSTALL COMPONENT 'file://component_validate_password'"sudo mysql --table -e "SHOW VARIABLES LIKE 'validate_password%'"+-------------------------------------------------+--------+| Variable_name                                   | Value  |+-------------------------------------------------+--------+| validate_password.changed_characters_percentage | 0      || validate_password.check_user_name               | ON     || validate_password.dictionary_file               |        || validate_password.length                        | 8      || validate_password.mixed_case_count              | 1      || validate_password.number_count                  | 1      || validate_password.policy                        | MEDIUM || validate_password.special_char_count            | 1      |+-------------------------------------------------+--------+

    Expected resultThe install returns nothing, then eight validate_password.* variables with policy set to MEDIUM and length to 8.

    Success conditionPassword validation is active and you can see exactly what it demands.

  6. Prove it rejects a weak password

    The fastest way to confirm a policy is loaded is to violate it.

    ERROR 1819 and exit status 1. The account is not created - this is a refusal, not a warning. Note that the error names the policy rather than the specific rule broken; abc fails length, case, digit and special-character checks all at once.

    bash Example session 
    sudo mysql -e "CREATE USER 'weakling'@'localhost' IDENTIFIED BY 'abc'" ; echo "exit=$?"ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirementsexit=1

    Expected resultERROR 1819 (HY000): Your password does not satisfy the current policy requirements and exit=1.

    Success conditionYou have seen the policy refuse a real attempt.

  7. Create an account that satisfies it

    The same statement with a password that meets all four requirements. exit=0 and no output, which is SQL for success.

    Look at the plugin column in the account list afterwards. appuser is on caching_sha2_password, the 8.x default for password accounts - not auth_socket like root. This account has a real password and can therefore be used over the network, which root cannot.

    That difference is the subject of the connections guide later in this track.

    bash Example session 
    sudo mysql -e "CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'S7rong-Pass!2026'" ; echo "exit=$?"exit=0sudo mysql --table -e "SELECT user, host, plugin FROM mysql.user ORDER BY user"+------------------+-----------+-----------------------+| user             | host      | plugin                |+------------------+-----------+-----------------------+| appuser          | localhost | caching_sha2_password || debian-sys-maint | localhost | auth_socket           || mysql.infoschema | localhost | caching_sha2_password || mysql.session    | localhost | caching_sha2_password || mysql.sys        | localhost | caching_sha2_password || root             | localhost | auth_socket           |+------------------+-----------+-----------------------+

    Expected resultexit=0, then six accounts with appuser on caching_sha2_password.

    Success conditionYou have one working password account and know which plugin it uses.

Troubleshooting

Official sources

← All MySQL guides