Why is mysql automatically logging me in without .my.cnf or my.cnf sections?

alunduil · 05-16-2010, 05:54 PM

I've checked and double checked that there is no user and password directive in /etc/my.cnf and ~/.my.cnf but it seems that every time I issue mysql it will drop me into the command line without prompting for a password or giving any pause. Of course, the only table I have access to when doing this is information_schema with read only on most of it and no access on the rest, but I was just wondering where else an auto-login style authentication could be coming from.

Thanks in advance,

Alunduil

paulsm4 · 05-16-2010, 06:05 PM

Uh - maybe from not having defined a password for all users (most especially "root")?

Strong recommendation:

Quote:

mysql -uroot mysql
select User,Host,Password from user;
<= Create passwords, or delete, all users with "blank" passwords!

alunduil · 05-16-2010, 06:42 PM

All users have a password set and just for good measure I removed some users that I don't use any longer. Any other ideas?

Regards,

Alunduil

paulsm4 · 05-16-2010, 11:35 PM

Quote:

Q: Any other ideas?

A: Yes.
Please run this command, and cut/paste the results ("X" out any passwords from the post):

Quote:

mysql -uroot -pYOUR_ROOT_PASSWORD mysql
select User,Host,Password from user;

alunduil · 05-18-2010, 11:33 PM

Here you go. Sorry it took so long it's been a hectic week.

Code:

alunduil@giskard ~ $ mysql -uroot -p mysql
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1871
Server version: 5.0.90-log Gentoo Linux mysql-5.0.90-r2

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

mysql> select User,Host,Password from user;
+-----------+-----------+-------------------------------------------+
| User      | Host      | Password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *XXXXX | 
| root      | giskard   | *XXXXX | 
| snort     | 127.0.0.1 | *XXXXX | 
| snort     | localhost | *XXXXX | 
| wordpress | localhost | *XXXXX | 
+-----------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql>

Regards,

Alunduil

paulsm4 · 05-18-2010, 11:56 PM

Hi -

I'm confused

It looks like it *did* prompt you for a password (it says "Enter Password:").

Do you think maybe you somehow inadvertantly made "<Enter>" a valid password? What happens if you *change* the root password?

For whatever it's worth, here's how I usually go into the MySQL monitor:

Quote:

$ mysql -uroot -pROOT_PASSWORD mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1278
Server version: 5.0.77 Source distribution

Note that there's *no space* between "-u" and "root", or "-p" and the password.

'Hope that helps .. PSM

alunduil · 05-19-2010, 12:01 AM

Yes, but what I'm saying is as my user I'm able to issue the command: mysql (no options) and it will drop me at a prompt. That's the issue. When I specify a user and tell mysql (through the -p switch) to prompt me for the password of course it's going to.

I do understand the command you provided in your last post and the -uroot can have a space. You are able to pass -u root and mysql will parse it correctly. The same does not go for the password because if -p is found alone mysql prompts for the password (making it so shoulder surfers don't get what they are there for).

I was thinking it might be a stray .my.cnf that was getting pulled in from my environment but I don't see anything like that.

Regards,

Alunduil

paulsm4 · 05-19-2010, 12:37 AM

Hi -

Actually, my understanding of the "-p" options was primarily to allow you to enter it in-line (and prevent wasting time responding to some silly "Enter Password:" prompt).

Neither here nor there:
a) if you entered "-p" with a valid password, you should get in (without being challenged, and without seeing an "Enter Password" prompt).

b) I completely understand that your problem is that you don't seem to need a password in the first place (despite the fact that all users - including root - appear to have valid passwords).

I'm still suggesting that maybe the root password somehow got set to "\n". Couldn't hurt to change it...

You've absolutely confirmed that no MySql users have a null password - thank you.

And you're absolutely correct: now that we seem to have ruled out just about everything else, some ".my.cnf" file somewhere in the environment is just about the only thing left that makes any sense.

Q: have you tried all of the following commands:

Quote:

ls -la /root/.my.cnf

ls -la ~/.my.cnf

find / -name .my.cnf -print 2>/devnull

find / -name my.cnf -print 2>/devnull

Please keep us posted what you find - and good luck!

Blue_Ice · 05-19-2010, 03:07 AM

Quote:

Originally Posted by alunduil

Yes, but what I'm saying is as my user I'm able to issue the command: mysql (no options) and it will drop me at a prompt. That's the issue. When I specify a user and tell mysql (through the -p switch) to prompt me for the password of course it's going to.

That's default behaviour. As you will see when you test it, you will not be able to see your databases, nor will you be able to make changes.

Edit: This will not be possible from any other system, then localhost.

alunduil · 05-19-2010, 11:34 AM

Sorry about my earlier frustrations. It seems after removing the extra users that you pointed out from your first post and then finally restarting mysql provided the desired effect. If this issue surfaces again I hope to get a better answer.

Thanks for all the patience and help everyone.

Regards,

Alunduil

alunduil · 05-19-2010, 11:36 AM

Also, Blue_Ice, here's the default behaviour on two separate boxes:

Code:

alunduil@dornick ~ $ mysql
ERROR 1045 (28000): Access denied for user 'alunduil'@'localhost' (using password: NO)

Code:

alunduil@giskard ~ $ mysql 
ERROR 1045 (28000): Access denied for user 'alunduil'@'localhost' (using password: NO)

Regards,

Alunduil

Blue_Ice · 05-19-2010, 11:52 AM

Quote:

Originally Posted by alunduil

Also, Blue_Ice, here's the default behaviour on two separate boxes:

Code:

alunduil@dornick ~ $ mysql
ERROR 1045 (28000): Access denied for user 'alunduil'@'localhost' (using password: NO)

Code:

alunduil@giskard ~ $ mysql 
ERROR 1045 (28000): Access denied for user 'alunduil'@'localhost' (using password: NO)

Regards,

Alunduil

Sorry, forgot to mention that MySQL tries to use the linux user you logged in without password. I am not sure if MySQL tries to log in with an empty account if that doesn't work. Still default behavior as you can see in the response you get from the server.