Remote access and setting up user

[Drongo_III]

Hi Guys

 

I need to setup a MySQL user account so I can connect (using PHP) both on the server where MySQL resides and remotely from another server (these are load balanced servers).

 

I read a MySQL article here: http://dev.mysql.com/doc/refman/5.1/en/adding-users.html - this seems to suggest in order to do this I would need to setup two user accounts as follows:

 

CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON MyDB.sometable TO 'monty'@'localhost'
   
CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';

 

 

I am not overly familiar with creating users so apologies for the noob questions about to follow:

 

1. Is it correct that I need to setup two users with the same username only one as localhost and one with wildcard to be able to use the database locally and remotely ?
    
2. Rather than use a wildcard would it be more secure to use the remote server's IP - i.e. 'monty'@'SomeIPaddress'?
    
3. Do I need to grant privileges on the second user - i.e. 'monty'@'%' . Or does this account inherit the same privileges from 'monty'@'localhost'?

 

I would obviously not grant all privileges either but the above is just for simplicity.

 

 

Is that all I need to do?

 

Thanks

 

Drongo

Edited March 20, 2013 by Drongo_III

[fenway]

1. Well, '%' includes localhost, but typically localhost permissions are less restrictive.

2. Yes, IP would be the best way to go -- usually, web users connect from your web server to your DB server -- and the former almost always has a static/virtual IP (or small range).

3. There is no such inheritance -- one sets of privs for each user/host.  More restrictive first, IIRC, in terms of host.