We help IT Professionals succeed at work.

how to allow remote connections via a user

wyatt12
wyatt12 asked
on
4,357 Views
Last Modified: 2007-12-19
Hi,

I have a default mysql installation.  I can login as root user via localhost.  How do I create a user "wyatt", and grant this user permission to access a database named "data" from everywhere remotely?.  ....    Sorry, I'm a newbie to mysql, and need this setup ASAP.

Regards,

Wyatt

Comment
Watch Question

Commented:
You download the MySQL Command Center:

http://dev.mysql.com/downloads/mysqlcc.html

Then you install it, connect to your db and then take user administration, rightclick add new user enter properties and then under the option host you enter % which symbolizes all possible hosts.

It works fine for me so I hope it works out for you as well..

//jan
http://www.ivtaco.com

Commented:
Antoher option is to, in some way (e.g. command prompt, phpAdmin) modify the mysql-user tabel that resides in the mysql-system database (called mysql). In the user table you simply modify or add a new user and similarily as above change the value in the host-column.

//jan
http://www.ivtaco.com

Commented:
GRANT ALL PRIVILEGES ON data . * TO 'whyatt'@'%' IDENTIFIED BY 'wyatt_pass';
You can use the MySQL GRANT command to acheive this quickly and easily.

Assuming that you have created the 'data' database already then use the GRANT command like this

GRANT ALL PRIVILEGES ON data.* TO wyatt IDENTIFIED BY 'password';

where password is whatever password you want assigned to the user wyatt

You can be more specific about what data you allow access to by specifying tables within the database that the user can use. For instance

GRANT ALL PRIVILEGES ON data.mytable TO wyatt IDENTIFIED BY 'password';

You can also be quite picky about what the user is permitted to do. ie

GRANT SELECT, INSERT, UPDATE ON data.* TO wyatt IDENTIFIED BY 'password';

Or, if you are a total control freak (or just want some really good security) then specify the fields that the user can play with

GRANT SELECT (userName, userHost) ON data.users TO wyatt IDENTIFIED BY 'password';

Hope this helps

Commented:
And all these GRANT - commands maybe set by the commandline prompt or a GUI like phpAdmin or the control center. The advantage with the MySQL Command Center is that you may choose all the options graphically in checkboxes or lists etc...

Author

Commented:

I tried this, but it didn't work.  

GRANT ALL PRIVILEGES ON data.* TO wyatt IDENTIFIED BY 'password';

Do I have to add the user "wyatt" first?  If so, how do I add the user wyatt?  Will this command allow "wyatt to connect" from remote location, not just localhost?

Wyatt
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.