MySQL 8.0 UN-securing root
by Mark Nielsen
Copyright July 2023
Why insecure root in MySQL 8.0 and how is root secured? In 8.0 root is password less, but it is authorized by the plugin auth_socket, which means
in Linux you must login as user root under Linux in order to login into the socket file of mysql as root for mysql. Basically, if you are root
on Linux you can do anything anyways.
So why unsecure it? Because of mysql history file. I have to login into Linux and sudo to root and then the mysql history file can't be used,
which can be annoying. Also, if I su -l root after, the arrows don't work in the mysql history. Perhaps I should solve the mysql history file
directly. But this is for non-production, so I don't really have an issue. I still keep root only logging in locally.
- Links
- How to do it
- Saving passwords
- Expect and automation
Links
- https://www.oreilly.com/library/view/exploring-expect/9781565920903/
- https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html
- https://dev.mysql.com/doc/refman/8.0/en/option-files.html
- https://dev.mysql.com/doc/refman/8.0/en/mysql-secure-installation.html
How to do it
Change the password. Here is a record of commands I used.
mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | auth_socket |
+------------------+-----------+-----------------------+
mysql> alter user root@localhost IDENTIFIED WITH caching_sha2_password;
Query OK, 0 rows affected (0.10 sec)
mysql> alter user root@localhost identified by 'BAD_PASSWORD';
Query OK, 0 rows affected (0.11 sec)
mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
Saving password
Also, with the mysql client, you configure the password automatically in two ways:
Expect and automation
To automate installation do one of the two things:
- Create .mylogin.cnf with mysql_config once and transfer it to destination servers for automation. Change permissions and ownership also.
- Use expect. NOTE: there have been problems where the expect script wouldn't work. It might be a timing issue.
For me, for automation I would rather use .mylogin.cnf and not have the password in an expect script. Otherwise you can do the stuff
below with expect. If you choose to use expect, figure out the timing problem, and make a more robust professional script. This is just a sample script.
Expect
- Put in that file change_mysql_password.expect
#!/usr/bin/expect
spawn mysql_config_editor --verbose set --user=root --password --host=localhost
expect "Enter password:"
send "BAD_PASSWORD\n"
- Execute : chmod 755 change_mysql_password.expect
- Execute : rm -f .mylogin.cnf
- Execute: ./change_mysql_password.expect
- Expected output : NOTE: It must say "Successfully written encrypted data to the login file."
File does not exist.
Creating login file.
Login file created.
Opening the file.
Generating a new key.
Key successfully written to the file.
Executing set command.
Enter password:
Key successfully written to the file.
Successfully written encrypted data to the login file.
- If you don't get successful response, it probably didn't work. Run it again. Sometimes the expect script doesn't work. It might
be a timing issue, I didn't bother to look into it.
- Test mysql : mysql -e "user()"
- Expected result :
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)