Solved

how to allow remote connections via a user

Posted on 2004-04-09
7
4,307 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

0
Comment
Question by:wyatt12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Expert Comment

by:madwax
ID: 10796197
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
 
LVL 7

Expert Comment

by:madwax
ID: 10796206
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
 
LVL 14

Expert Comment

by:psadac
ID: 10796529
GRANT ALL PRIVILEGES ON data . * TO 'whyatt'@'%' IDENTIFIED BY 'wyatt_pass';
0
Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
LVL 1

Expert Comment

by:jonhubbard
ID: 10796935
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
 
LVL 7

Expert Comment

by:madwax
ID: 10796944
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
 

Author Comment

by:wyatt12
ID: 10808358

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
 
LVL 1

Accepted Solution

by:
jonhubbard earned 500 total points
ID: 10808893
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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question