MySQL doesn't like passwords
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! |
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 Code:
mysql -uroot -p Code:
grant usage on *.* to new_user identified by 'users_password'; 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'; hope this helps a bit!:jawa: |
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? |
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 *.*
|
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.
|
Many thanks dude!:D
|
How can I delete all old passwords for MySQL and start again?
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. |
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?
|
Quote:
From the mysql docs: link |
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.
|
Works like a charm here either from the command line or through phpmyadmin.
Quote:
Code:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' IDENTIFIED BY 'some_pass' |
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? |
Not sure that I understand your point. The way that I read PhooD's posts, he is the DBA.
|
All times are GMT -5. The time now is 01:30 PM. |