CODEWITHSUNDEEP
mysqlsecurity
Alex
Alex

Reputation: 44719

Connecting to MySQL DB from various hosts

How to add a host to MySQL's list of hosts it can accept connections from.

Upvotes: 1

Views: 1687

Answers (2)

Pascal Thivent
Pascal Thivent

Reputation: 570335

From http://www.linuxhelp.net/guides/mysql/:

To give localhost permission to access all databases, enter this:

mysql> insert into 
         -> host(host,db,Select_priv, Insert_priv, Update_priv, 
         -> Delete_priv, Create_priv, Drop_priv)
         -> values('localhost','%','Y','Y','Y','Y','Y','Y');

Note, the '%' can be replaced with a database name. The '%' is a wildcard.

Following the previous format, to allow access from another hostname (in this case "windowsbox") add this:

mysql> insert into 
         -> host(host,db,Select_priv, Insert_priv, Update_priv, 
         -> Delete_priv, Create_priv, Drop_priv)
         -> values('windowsbox','%','Y','Y','Y','Y','Y','Y');

Again, '%' is used as a Wild-Card.

To create a user 'djg' who can access the MySQL server from localhost, type this:

mysql> insert into 
         -> user (host, user, password)
         -> values('localhost','djg',password('mypassword'));

To give the user access from another hostname, domain, etc... add other entries accordingly. For example, to give user djg access from windowsbox:

mysql> insert into 
         -> user (host, user, password)
         -> values('windowsbox','djg',password('mypassword'));

Now... to give the user permissions to access a database from localhost, add this entry and change with your appropriate information:

mysql> insert into
      -> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv,  Create_priv, Drop_priv)
      -> values ('localhost','mydatabase','djg','Y','Y','Y','Y','Y','Y');

To give the user permissions from windowsbox, add this:

mysql> insert into
      -> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
      -> values ('windowsbox','mydatabase','djg','Y','Y','Y','Y','Y','Y');

PS: don't forget to flush privileges

Upvotes: 1

Simon Groenewolt
Simon Groenewolt

Reputation: 10665

The information on the host a user is allowed to connect from is in the user table, see adding users. Also make sure that you are not blocking connections on a firewall/os level.

Upvotes: 1

Related Questions