how to allow remote connections via a user

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

wyatt12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

madwaxCommented:
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
0
madwaxCommented:
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
0
psadacCommented:
GRANT ALL PRIVILEGES ON data . * TO 'whyatt'@'%' IDENTIFIED BY 'wyatt_pass';
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

jonhubbardCommented:
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
0
madwaxCommented:
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...
0
wyatt12Author 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
0
jonhubbardCommented:
Just to make sure - you need to be in the mySQL command line to run these commands

If you are using Windows then, in a command prompt window, move to the directory which has the mySQL exe files in (usually something like c:\mysql\bin.  If you are using Linux or osX then you are fine at any command prompt.

Now enter

mysql mysql

and you should be taken into the mysql database command prompt.  If this fails login as roos as you usially would

now enter

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

There is no need to add wyatt as a user on the system as mySQL does not use the standard users for authentication, but instead maintains it's own list of users.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.