MySQL doesn't like passwords

legendaryfox · 04-11-2004, 09:31 PM

I've set user accounts on my MySQL database using mysql_setpermission, but in my PHP programs, when i do a mysql_connect and use the username and password, it gives me a denial. However, when I omit the password (leave it ""), i get a resource id #2 (a good thing, right?) What went wrong?

thanks!

dhbiker · 04-12-2004, 04:13 AM

sounds like you've set up the new accounts wrong, the reason that you can log in without a password is that when you install mysql it adds two accounts %@localhost and %@what_ever_you_called_your_pc that allow connections without passwords. As you can imagine this isn't ideal (security). Here's how to get rid of them (and set up the accounts you want), oh have you set the root password as well?

here's how to do everything (open up a terminal and do everything from there)

set root password: (not the old_password is 'password' when you install mysql!)

Code:

mysqladmin -uroot -pold_password password your_password

to get rid of the unsecure %@localhost accounts log in to mysql as root:

Code:

mysql -uroot -p
(it will ask for password)
use mysql
select * from user;
delete from user where Host="localhost" and Password="";
delete from user where Host="what_ever_your_pc_is_called" and Password="";
flush privileges;

if you now do "select * from user;" you should have nothing in there other than the root account, now to set up your new accounts;

Code:

grant usage on *.* to new_user identified by 'users_password';

this should let that user log into mysql with their password, but it doesn't allow them to do anything useful!

decide what privileges you want the user to have on which databases and then grant them to him/her

Code:

grant insert,select,update,delete on database_name.* to user_name identified by 'users_password';

you can also grant them all privileges on a database by using "grant all on database.* to ...."

hope this helps a bit!

legendaryfox · 04-12-2004, 05:48 PM

THANKS! It worked ^.^

Just a check question - I do this everytiime (except for the admin part and the deleting part) i make a new user, right?

dhbiker · 04-13-2004, 02:01 AM

yeah, although you don't actually need the grant usage bit, you can go straight to the grant, select etc. on database.* to user blah identified by 'blah', mysql will automatically grant usage on *.*

dhbiker · 04-13-2004, 02:02 AM

oh yeah, you might want to know that the "file" privilege can be used to attack your system and its recommended that you only grant it to people who really need it.

legendaryfox · 04-13-2004, 02:30 PM

Many thanks dude!

PhooD · 03-08-2009, 01:10 PM

I am using a remote Mac running Apple Mac OS X Leopard Server, but also have access via command line, VNC and ftp. I initially set up MySQL using the remote ServerAdmin program and it worked OK (even though it has the
"Cannot load mcrypt extension. Please check your PHP configuration." error displayed.

I opted for no login passwords initially because I intended to use .htaccess instead. I set a password for root and another for me with all privileges. I logged in as me and found I did not have permission to create a database. So I logged in as root and created 3 databases. But when I logged in as me via phpMyAdmin it would not allow me to see these new databases. After 2 days of frustration I decided to do away with passwords on these MySQL accounts and changed them all to "No Password" status and logged out.

Now phpmyadmin will not let me log in as root. I get:
"#1045 - Access denied for user 'root'@'localhost' (using password: YES)".
Even though I told it No password.

I tried moving the MySQL folder, shutting down all services (web, webojects, MySQL) and restarting via ServerAdmin in OSX. Still no go.

So at this point I am screwed and revert to command line. Couldn't get access to anything so logged in as root. Read tons of stuff on line. The site http://dev.mysql.com/doc/mysql-macos...tallation.html says use mysqladmin shutdown. But even as root I get the error "root# mysqladmin shutdown
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
sh:~ root# "
mysqld doesn't exist. I think it is renamed mysqladmin
It will not let me run mysql or access or move, rename or delete certain folders associated with mysql, even logged in as root. I thought I was god as root but I have been put in my place!

I deleted the MySQL folder that is supposed to contain the database and some other files and restarted MySL via ServerAdmin. Each time I restart Apache (web, including php5) and Webobjects and MySQL. There is also a "WebObjects Monitor" used on-line. I found that it said MySQL was running with no instances. I clicked Delete and it disappeared from the list. But still I cannot log in via phpMyAdmin. Thanks for you patience in reading all this but I thought you might prefer a full explanation.

PhooD · 03-08-2009, 02:09 PM

Hey I dont believe this! In the time it took to write this everything fixed itself! Something must have timed out. I went back to phpmyadmin and logged in as root, created databases and assigned them to new users no problem! Logged in as new user and I can see the databases! Still the question remains for others. How DO you get rid of unwanted passwords in MySQL?

norobro · 03-08-2009, 02:27 PM

Quote:

Originally Posted by PhooD

How DO you get rid of unwanted passwords in MySQL?

set password for user = password('');

From the mysql docs: link

PhooD · 03-08-2009, 11:46 PM

Thanks for the tip, but even logged in as root it would not let me use mysql commands. There may also be problems with having the right privileges to change passwords so this technique may not solve all problems.

norobro · 03-09-2009, 01:24 AM

Works like a charm here either from the command line or through phpmyadmin.

Quote:

Originally Posted by PhooD

I set a password for root and another for me with all privileges.

If you set up your users like so:

Code:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname'  IDENTIFIED BY 'some_pass'

either user should be able to change any other user's password.

sureshsujatha · 03-09-2009, 02:22 PM

Correct me if I am wrong ... but isnt it an incorrect practise to fire commands like "GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' IDENTIFIED BY 'some_pass'" from inside your application code.

Shouldnt such things be abstracted to the DBA and DBA only per se?

norobro · 03-09-2009, 04:36 PM

Not sure that I understand your point. The way that I read PhooD's posts, he is the DBA.