Solved

Access to MySql

Posted on 2012-03-27
4
494 Views
Last Modified: 2012-03-31
How do I restrict access to MySql.
I have full access but I want my college to have access only to certain tables.
I would like to use NaviCat to define this.
Also how can I allow my college to connect to the MySql from the internet. Now I can only connect from the localhost.
0
Comment
Question by:soffcec
[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
  • 2
4 Comments
 
LVL 11

Assisted Solution

by:SANDY_SK
SANDY_SK earned 400 total points
ID: 37771774
0
 

Author Comment

by:soffcec
ID: 37771853
How can I tell MySql to allow connection from the Internet ?

I need example for the user MyUser who allow him to use only the datbase MyTable but no other databases and he may connect from the Internet.
0
 
LVL 7

Assisted Solution

by:designatedinitializer
designatedinitializer earned 100 total points
ID: 37771939
You'll have to change some settings in the mysql config file.
This is usually located in /etc/mysql/my.cnf

find that file and then find the line which reads:
skip-networking

Open in new window


this is preventing mysql from accepting remote connections.
Comment out that line by placing a '#' sign like this:
#skip-networking

Open in new window

(or remove that entire line altogether)

Do the same to a line that says:
bind-address = 127.0.0.1

Open in new window


This is your localhost, and this line forces mysql to accept only connections from localhost.

Then, you will need to grant remote access to that specific host ("your college").
This is done with a GRANT mysql statement, like this:
GRANT ALL PRIVILEGES ON *.* TO username@remotehost IDENTIFIED BY “password”;

Open in new window


You have to revise that and put in the privileges, username and remotehost (and password) you want.
0
 
LVL 11

Accepted Solution

by:
SANDY_SK earned 400 total points
ID: 37771942
if from the internet you are going to use, are you planning to allow it from a specific IP[This is recommended] ??  then you can do this

CREATE USER 'MyUser'@'<IP ADDRESS>' identified by 'myuser123';

how ever it is possible to allow access to the db from any IP [This is NOT Recommended]

CREATE USER 'MyUser'@'%' identified by 'myuser123';

Then to grant privileges to that user use this

grant ALL on MyTable.* to 'MyUser'@'<IP ADDRESS>'

Even in the above statement, the ALL can be changed to specific like only select or only insert ,etc.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

There are two basic ways to configure a static route for Cisco IOS devices. I've written this article to highlight a case study comparing the configuration of a static route using the next-hop IP and the configuration of a static route using an outg…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

738 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