Access to MySql

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.
soffcecManagerAsked:
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.

soffcecManagerAuthor Commented:
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
designatedinitializerCommented:
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
SANDY_SKCommented:
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

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
Routers

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.