CODEWITHSUNDEEP
mysql
mviswa
mviswa

Reputation: 145

Mysql logging in even without providing password

mysql logging in the prompt even not providing password , If provided like mysql -u root -p its prompting for password if provided like mysql -u root its logging in without asking for password i have run the query 

select user,hosts from mysql.user;

and came to know there are 3 root users with different hosts as shown 

+------+-------------+
| user | host        |
+------+-------------+
| root | 127.0.0.1   |
| root | db-busindia |
| root | localhost   |
+------+-------------+

And interesting thing is that mysql server name is also db-busindia , is root@'db-busindia' the culprit ?

Upvotes: 7

Views: 30171

Answers (5)

Ankit Kumar Rajpoot
Ankit Kumar Rajpoot

Reputation: 5610

you can use this command to access your MySQL command.

sudo mysql

Upvotes: 0

user2629240
user2629240

Reputation: 51

Yes, even I've encountered this issue long ago. Usually we encounter them while installing MySQL server where all your client connections establish connection using socket. You can simply check its Plugin under mysql.user table.

Ubuntu@localhost:~# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | auth_socket           |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

mysql> update mysql.user set plugin='mysql_native_password' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

You can disable that option by updating plugin value to 'mysql_native_password '.

Ubuntu@localhost:~# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Ubuntu@localhost:~# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Upvotes: 2

Barb H.
Barb H.

Reputation: 61

I had this issue after running mysql_secure_installation on a Debian 9 running MariaDB. Gazzar's answer on Oct 14, 2017 to the 'can't log in as mysql user root...' post 1 led me to look at the plugin field on the mysql.users table with this sql statement:

SELECT User, Host, plugin FROM mysql.user;

If you see 'auth.socket' (on mysqldb) or 'unix.socket' (mariadb), you can try modifying the entry to 'mysql_native_password' with an update statement like this:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'root';

After I made this change, I could login in from any account with the command:

mysql -u root -p

as long as I entered the correct password. And if I used the command:

sudo mysql

I was still asked for the root password.

Upvotes: 1

ninjascorner
ninjascorner

Reputation: 299

MySQL allow us to login without passing the user and password by putting the user and password in my.cnf. 

$ mysql

Please check the my.cnf which usually in /etc/mysql/ or if you have overrides you can find it in ~/.my.cnf. Just remove the user and password.

See something like this:

user=root
password=35620fhgf8025ec31ffd2c1
host=127.0.0.1

Upvotes: 1

user4035
user4035

Reputation: 23759

Check the [client] section of my.cnf file for the lines:

user=root 
password=some text.

These enable you to log in without a username or password. Delete password line, if you want to be prompted for password.

Upvotes: 3

Related Questions