Remote Access/Account Creation Q

[RhythmAddict]

Hey eveyone - kind of new to MySQL, so please excuse my ignorance.
Alright, so I created a DB on a RH8 box. Works fine, the Web App is local to the machine and that works fine too.

The DB name is branches - right now it is just on a test box and I use the root user to access it (as does the CFM webapp). I need to create a user account that will have basically full permissions (insert/update/etc) to the branches DB. Lets just call this accout webuser for now - FYI im gonna use this account to connect to the *nix mysql DB from my Win machine. I've tried creating an account numerous times on the *nix mysql DB...the actual account creation seems to work fine, but in MySQLyog I just get "Lost connetion to MySQL server during query" BTW, this happens even if I try to connect using the root MySQL account...same exact error.

Furthermore, something is fundamentally wrong with the way I'm creating accounts because if I make the webapp access the MySQL Branches db using the 'webuser' account, it does not work.

The syntax I am using to create the user under MySQL is:

mysql> set password for 'webuser'@'localhost' = password('mypassword');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'webuser'@'%' = password('mypassword');

Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Any help would be greatly appreciated, thank you in advance.

 

[hvass]

Permissions are controlled by a number of tables in a special database called mysql

You can insert records into these tables and update existing records to control permissions but better is to use the GRANT and REVOKE statements

http://www.mysql.com/doc/en/GRANT.html

grant all on branches.* to webuser@'%' identified by 'mypassword';
flush privileges;

grant all privileges except the ability to grant further privileges on the database branches * and any of its tables to webuser coming in from any host identified by mypassword

If you want to check this try

use mysql;
select host,user,password from user where user='webuser';
select host,user,db from db where user='webuser';

 

[RhythmAddict]

hvass - thank you for your prompt resonse. I actually pasted the incorrect portion (pasted the password setting part instead of the user account creation)

In any case, it seems like I have the account creation syntax right. I was doing..
GRANT ALL PRIVILEGES ON *.* TO webuser@localhost
IDENTIFIED BY 'password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO webuser@'%'
IDENTIFIED BY 'password' WITH GRANT OPTION;

Mysql.com states you must grant local as well as wildcard privs...so I did that. I tried changing the syntax to "...on branches.*" instead, however it yielded the same results. When I check the mysql.users table, the user I created is in there, and it does have 2 rows, one for the local and one for the wildcard host field....I have tried every combo with these...all with no luck. Any other ideas?? I dont suppose I have to enable SSL in order to access the DB remotely, right? Thanks for your help!!

 

[hvass]

No you don't necessarily need ssl.

Your grant statment looks OK. You need to restart the server or issue flush privileges to get mysql to use the new privilege tables.
Assuming you have done that then difficult to see what might be wrong. You will have to verify that each bit works starting with standard client rather than a GUI

It might be worth testing mysql using the standard mysql client to rule out any other problems.
On your linux box try
mysql -u webuser -p
Password: *******
mysql> use branches;

What you are doing here is testing the connection as webuser password localhost

Next you need to test the connection from another box. Again use the standard mysql client - either on another linux box or you might get the windows client downloaded from

http://www.mysql.com

(I think you probably have to download the whole mysql application server and client to get the client) and running it in an old fashion dos window check that the mysql client can connect to the same database from a different box.

mysql -h linuxbox -u webuser -p

Tell us how far you get...

 

[RhythmAddict]

I did some research...and everything seems to point to this:

http://bugzilla.redhat.com/bugzilla/long_list.cgi?buglist=75290

My logs, symptoms and versions all fall in line with this bug. curiously...the bug issue has been closed so if anyone has a solution
(tried to edit /etc/my.cnf mysqld options without success)
please let me know...seems like my only other option is downgrading the libraries...

:?

 

[hvass]

Sorry cannot help on this one - I am on Mandrake 9.0-9.2 and Windows2000