How do I allow remote mysql access to all users?

Question

I have setup a mysql server on unbuntu. The server hosts websites for my various clients. Hence, I want all of them(and any new clients) to have remote access to the mysql server. How do I configure mysql for the same?

I know about the GRANT ALL PRIVILEGES command, but that would require me to individually grant remote access to all the users.

Answer 1

I highly recommend that you do not run

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

This user has the SHUTDOWN privilege, which can allow the user shutdown mysql remotely with

mysqladmin -hIP_of_DB Server -uusername -p shutdown

You also do not want the SUPER privilege given remotely to just anyone. Why ?

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

If you have multiple users and want them to access specific databases, you need to create the user with database-level privileges. The SHUTDOWN and SUPER privileges are not part of database-level grants (See my post MySQL user without SUPER privilege as to why)

Suppose you have a database called mydb and you want to access it remotely. Do this:

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'%';

I have a better idea for you. You should restrict remote users by their netblocks

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.30.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.30.40';

Note that you will have to drop 'username'@'%' first before creating DB-level user access.

See my post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) for full clarification on MySQL's User Authentication Paradigm

After you have created the needed users, make sure your firewall has port 3306 open.

Answer 2

You can create a username and password also provide grant access to them. so that all clients can access the mysql from their local by using the username and password provided by you.

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'username'@'%';