Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to allow remote connections via a user

Posted on 2004-04-09
7
Medium Priority
?
4,326 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 2000 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

578 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