LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 10-09-2009, 09:13 AM   #1
stabu
Member
 
Registered: Mar 2004
Location: dublin IRL
Distribution: Slackv12.1, Slamd64v12.1,Xubuntu v8.10_64, FC8_64
Posts: 438
Blog Entries: 5

Rep: Reputation: 32
mysql fracas


Just a note to mention a mishap I had with MySQL I had on a running server. It left me stunned and bruised.

First of all, I'm not a big MySQL user. I can do the very basic things. That's all. So, one day, I'm on my gentoo server as root because I'm creating up a database for a certain user. I use the command-line "mysql", and do the operation as mysqlroot user. That's something I've done several times.

This time however, I decide to test it from the command-line. SO I issue
Code:
mysql -u <notrootuser> -p
and duly type in the password, but the password of the database I just created. Note however that I have not specified the database, which was clumsy, I admit.

Now with the "mysql>" prompt, "show databases;" gives me only the databases that that user can see. Then I leave.

Ok, but do you know what actually happened? That command I issued effectively changed the password to the user's _other_ databases as well! So the websites, which those databases fed, led to access errors and failure.

I still can't believe it. Now this may all due to some .mysqlrc config file in the the background (which I don't know about) that allows changing of passwords that easily, but somehow, a command like that, it doesn't seem conventional that it should change passwords, without looking for verification.

Any how, I just wanted to to get that off my chest. Another thing that I notice "mysql" has is a very trendy "man" page. It actually has the options listed out as a table very carefully. There's an artist at work there at MySQL for sure.

Last edited by stabu; 10-09-2009 at 09:17 AM.
 
Old 10-09-2009, 09:41 AM   #2
stabu
Member
 
Registered: Mar 2004
Location: dublin IRL
Distribution: Slackv12.1, Slamd64v12.1,Xubuntu v8.10_64, FC8_64
Posts: 438

Original Poster
Blog Entries: 5

Rep: Reputation: 32
I can start to feel that it will only be myself on this thread.

Anyhow, I have started to do a bit of reading, and that command I issued is probably not the change of password command at all.

I burrow my way into the section of their ref book:
MySQL 5.1 Reference Manual :: 5 MySQL Server Administration :: 5.4 The MySQL Access Privilege System

And half way down it says
"A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine."

Nicely hidden away, that one.

So, my thinking has been always wrong on mysql. You don't set up a database for a particular user at all. You set up a database and separately you organise access to it.

This means that if you set up an additional database for a user, you need to know that user's mysql account password beforehand. Is that the way it is? That's a pain.

Anyhow, I'm going to read up some more.
 
Old 10-09-2009, 09:48 AM   #3
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296
Hello,

As far as I know, and as stated in your second post, a password is assigned to a user not to a database.

Did you use the grant command to identify the database, user, host, permissions and password to use for identification?

After creating the database you should execute something like this in order to give permissions:
Code:
grant all on databasename to 'username'@'localhost_or_hostname' identified by 'the_users_password';
flush privileges;
The all means that all priviliges are granted. Check the MySQL manual for all options.

Kind regards,

Eric
 
Old 10-09-2009, 09:50 AM   #4
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296
Also you could use some tool like phpmyadmin that gives you a webGUI that is pretty handy.

Kind regards,

Eric
 
Old 10-10-2009, 09:41 AM   #5
stabu
Member
 
Registered: Mar 2004
Location: dublin IRL
Distribution: Slackv12.1, Slamd64v12.1,Xubuntu v8.10_64, FC8_64
Posts: 438

Original Poster
Blog Entries: 5

Rep: Reputation: 32
Hi EricTRA,

Thanks for your reply ... yes, I'm aware (especially now) of these things.

I've had to explain this error of mine to a number of people, and here is an extract of my email to them.

Quote:
The upshot with the <URL> failure was that mySQL databases do not actually have passwords. It's the mysql-user accounts have the passwords, and any database they have access to will use their account password.
If I could underline that five times, I would!

Cheers.
 
Old 10-10-2009, 09:45 AM   #6
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296Reputation: 1296
No worries, glad you figured it out. Or is it still failing?

Kind regards,

Eric
 
Old 10-10-2009, 01:15 PM   #7
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Ubuntu 12.04, Antix19.3
Posts: 3,794

Rep: Reputation: 282Reputation: 282Reputation: 282
To add to Eric's grant example, you only need to specify the password for the user when you issue the command the first time for that user. So you don't have to know the password when granting permissions on other databases.

Further you don't need to be system root to create databases (but you might have figured that one out by now).

Lastly I like to suggest that you start reading the MySql reference manual so you can understand how it exactly works; you should have done that long ago when you did set up your first database.


PS
I found your story very funny
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Can MySQL log on via SSH/bash? mysql:x:27:101:MySQL Server:/var/lib/mysql:/bin/bash Ujjain Linux - Newbie 2 04-24-2009 02:21 PM
yum install php-mysql fails with mysql 5.1 - "Error: mysql conflicts with MySQL" rebelde Linux - Software 2 03-13-2009 10:32 AM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. SpellChainz Linux - Newbie 1 06-23-2007 03:35 PM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 08:44 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 07:17 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration