Solved

how to allow remote connections via a user

Posted on 2004-04-09
7
4,298 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now