Solved MariaDB accepts any password for root after su to root in shell?

F

freemicom

Hi,
I´m currently setting up a fresh FreeBSD 14.3 server with MariaDB 11.8. This morning I stumbled about some odd behavior: I was in the root shell for configuration and wanted to edit a database so I ran „mysql -p“. What I wasn´t aware of was that had opened the wrong entry in my password manager but mariadb let me in with wrong password! I noticed that later when I was looking for a database user but couldn´t find him in the pw manager. After some testing I found that
- when you su to root mariadb accept any input for „mysql -p“ even if you only press enter
- for any other database user you need to give the correct password
- when you run in a normal system account „mysql -u root -p“ you must give the correct password
So - is this a bug or a feature I`m not aware of?

Best regards,

Mike
 
You can check which auth plugin is set for your root user using
SELECT host,user,plugin FROM mysql.user;


For example MySQL is using caching_sha2_password for auth plugin. So if you try to connect with root to MySQL with password and you root user doesn't have authentication_string you get error: Access denied for user 'root'@'localhost' (using password: YES)

SELECT host,user FROM mysql.user WHERE authentication_string='';

 
VladiBG said:
SELECT host,user,plugin FROM mysql.user;
Click to expand...
Says mysql_native_password for all users. But to my understanding unix socket plugin wouldn´t make any difference because system passwords and database passwords are always different and random generated
VladiBG said:
SELECT host,user FROM mysql.user WHERE authentication_string='';
Click to expand...
Returns this:
+-----------+-------------+
| Host | User |
+-----------+-------------+
| localhost | mariadb.sys |
| | PUBLIC |
+-----------+-------------+
I can´t hardly see how this will give me root access to the database management of MariaDB when logging in without password even when I´m in the system root account
 
If your unix_socket plugin is enabled it allows you to login without password as the authentication is taken from already logged unix user.

A unix_socket authentication plugin is a passwordless security mechanism. Its security lies in the strength of the access to the Unix user, rather than the complexity and the secrecy of the password.
Click to expand...

For example if you are with some other than root user and try to login with mysql -u root it will fail as the unix user is not eq to root so it won't be authenticated via unix_socket and you need to provide the MySQL password which is stored in authentication_string.

It's same used in MSSQL when you enable Windows Authentication for the SQL. Then the user is authenticated using it's Windows user credential instead of the database password credential.

If you don't want to use your unix user authentication to login to the DB you can disabled it as it's described in the first link that i provide.
 
So the unix_socket plugin is activated by default in MariaDB? Because both
grep -r „unix_socket“ /usr/local/etc/*
Click to expand...
and
grep -r „plugin“ /usr/local/etc/*
Click to expand...
don´t gave me a hit.
Have tried this on some other servers and all MariaDB servers accept password less access the MySQL servers don´t which seems to be odd...
 
MySQL doesn't have that plugin installed by default (auth_socket.so)

Code: 
root@localhost [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS;
+---------------------------------+---------------+
| PLUGIN_NAME                     | PLUGIN_STATUS |
+---------------------------------+---------------+
| binlog                          | ACTIVE        |
| mysql_native_password           | ACTIVE        |
| sha256_password                 | ACTIVE        |
| caching_sha2_password           | ACTIVE        |
| sha2_cache_cleaner              | ACTIVE        |
| daemon_keyring_proxy_plugin     | ACTIVE        |
| CSV                             | ACTIVE        |
| MEMORY                          | ACTIVE        |
| InnoDB                          | ACTIVE        |
| INNODB_TRX                      | ACTIVE        |
| INNODB_CMP                      | ACTIVE        |
| INNODB_CMP_RESET                | ACTIVE        |
| INNODB_CMPMEM                   | ACTIVE        |
| INNODB_CMPMEM_RESET             | ACTIVE        |
| INNODB_CMP_PER_INDEX            | ACTIVE        |
| INNODB_CMP_PER_INDEX_RESET      | ACTIVE        |
| INNODB_BUFFER_PAGE              | ACTIVE        |
| INNODB_BUFFER_PAGE_LRU          | ACTIVE        |
| INNODB_BUFFER_POOL_STATS        | ACTIVE        |
| INNODB_TEMP_TABLE_INFO          | ACTIVE        |
| INNODB_METRICS                  | ACTIVE        |
| INNODB_FT_DEFAULT_STOPWORD      | ACTIVE        |
| INNODB_FT_DELETED               | ACTIVE        |
| INNODB_FT_BEING_DELETED         | ACTIVE        |
| INNODB_FT_CONFIG                | ACTIVE        |
| INNODB_FT_INDEX_CACHE           | ACTIVE        |
| INNODB_FT_INDEX_TABLE           | ACTIVE        |
| INNODB_TABLES                   | ACTIVE        |
| INNODB_TABLESTATS               | ACTIVE        |
| INNODB_INDEXES                  | ACTIVE        |
| INNODB_TABLESPACES              | ACTIVE        |
| INNODB_COLUMNS                  | ACTIVE        |
| INNODB_VIRTUAL                  | ACTIVE        |
| INNODB_CACHED_INDEXES           | ACTIVE        |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE        |
| MyISAM                          | ACTIVE        |
| MRG_MYISAM                      | ACTIVE        |
| PERFORMANCE_SCHEMA              | ACTIVE        |
| TempTable                       | ACTIVE        |
| ARCHIVE                         | ACTIVE        |
| BLACKHOLE                       | ACTIVE        |
| FEDERATED                       | DISABLED      |
| ngram                           | ACTIVE        |
| mysqlx_cache_cleaner            | ACTIVE        |
| mysqlx                          | ACTIVE        |
+---------------------------------+---------------+
45 rows in set (0.01 sec)

Server version: 8.0.42 Source distribution
Click to expand...
 
Espionage724 said:
Did you do mariadb-secure-installation? Iirc it has an option for the unix authentication thing (Y by default, but I'm thinking N/disabled would prevent any password from working while root)
Click to expand...
First thing after service mysql-server start
with default settings
VladiBG said:
change your MariaDB root password using
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Then try to login again.
Click to expand...
This did the trick! No more mysql and you´re in! Even after changing back to the first password the right password is always needed to log in to MariaDB when root. I can´t see any reason for that behavior but as long as it works I´m fine with that.
Thanks for all the help,

Mike
 
You must log in or register to reply here.
Back
Top