I want to secure mysql by setting root password. I reset root password successfully:
MariaDB [(none)]> select Host, User, Password from mysql.user; +-----------+------+-------------------------------------------+ | Host | User | Password | +-----------+------+-------------------------------------------+ | localhost | root | *58319282EAB9E38D49CA25844B73DA62C80C2ABC | +-----------+------+-------------------------------------------+ 1 row in set (0.00 sec)
But, after flush privileges, restart Mysql, I can still login mysql (on local host) without typing password.
root@myhost:/# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 10.0.29-MariaDB SLE 12 SP1 package Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
How can I force mysql asking password when connect ? Thanks !
If the root account has an empty password, your MySQL installation is unprotected: Anyone can connect to the MySQL server as root without a password and be granted all privileges. The 'root'@'localhost' account also has a row in the mysql.
my. cnf file in your user's home directory, MySQL would allow you to login without prompting you for a password. Make sure to update your MySQL credentials accordingly, then save the file and exit. After that, if you run just mysql you will be authenticated directly with the credentials that you've specified in the ~/.
Configuring a default root password for MySQL/MariaDB Use the following procedure to set a root password. To change the root password, type the following at the MySQL/MariaDB command prompt: ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd'; flush privileges; exit; Store the new password in a secure location.
Method 1: Use sudo By default, the local root user can log in to MySQL or MariaDB without password, so you can just use sudo mysql instead of mysql , and expect everything to work. Of course, this depends on your sudo to not ask you for a password, or you'll still have to enter one for the root privilege.
On MySQL table you have a column called plugin:
MariaDB [(none)]> SELECT host, user, password, plugin FROM mysql.user LIMIT 0,1; +-----------+------+-------------------------------------------+--------+ | host | user | password | plugin | +-----------+------+-------------------------------------------+--------+ | localhost | root | * | | +-----------+------+-------------------------------------------+--------+ 1 row in set (0.00 sec)
If I remember correctly the default plugin for mariaDB installations are 'console' or 'unix_socket', and this plugin allows you to enter without password, from console. But also disable authentication with password, and you cannot connect from another clients.
Simply update the plugin field with empty ('') value, and then, use FLUSH PRIVILEGES;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass'); UPDATE mysql.user SET plugin = '' WHERE user = 'root' AND host = 'localhost'; FLUSH PRIVILEGES;
With this, you have the problem solved.
I found I needed to change this:
UPDATE mysql.user SET plugin = 'auth_socket' WHERE user = 'root' AND host = 'localhost';
To
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND host = 'localhost';
That is the plugin for a password AFAICT.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With