687
edits
Changes
From SME Server
Jump to navigationJump to searchHow to create a mysql user with access from a computer other than localhost.
Keep in mind this enables access to your MySQL database for ANYONE, so make sure you have strong passwords on ALL your MySQL databases.
Keep in mind this enables access to your MySQL database for ANYONE, so make sure you have strong passwords on ALL your MySQL databases.
Alternatively it would be a more secure approach to require external (remote) users to establish a VPN connection and effectively become part of the local network. In that case do not change the mysql access to public status using the above command.
Alternatively it would be a more secure approach to require external (remote) users to establish a VPN connection and effectively become part of the local network. In that case do not change the mysql access to public status using the above command.
=== Create MySQL user(s) with access from other computers ===
SME Server's default mysql database users, and most of the database examples in the wiki, allow login only from ''localhost''.
If you want to access a mysql database on your SME server from other computers, you must not only make the configuration changes described above, you must also create a user who is allowed to login from those systems (see [http://dev.mysql.com/doc/refman/4.1/en/connection-access.html 5.5.4. Access Control, Stage 1: Connection Verification] for more detail).
==== Allow mysql login from any LAN workstation ====
Assuming your local network is 192.168.xx.0, you can create a user with mysql access from any LAN workstation (or VPN client) using the command shown below (couresy of [http://forums.contribs.org/index.php/topic,32270.msg136322.html#msg136322 DarkMirage]).
You probably want to change:
* the database name ('''MyDB''')
* the user name ('''MyUser''')
* the password ('''MyPW''') and
* the allowed computers ('''192.168.xx.%''')
<nowiki>## In the command below, \ escapes a linebreak.
## Either include them, or place the entire command on one line
mysql -e "\
create database MyDB; \
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER \
ON *.* \
TO 'MyUser'@'192.168.xx.%' \
IDENTIFIED BY 'MyPW'; \
FLUSH PRIVILEGES;"</nowiki>
==== Security Implications of allowing remote mysql login ====
It is technically possible to combine the above techniques to allow remote mysql login from any host on the Internet (allow network login, open the firewall, then set the network address to '%'). This would be a bad idea.
If you have remote users who need access to your mysql database(s), encourage them to use a VPN connection, or an SSH tunnel, or (at a minimum), restrict the allowed login hosts to their internet IP address.
=== Enable InnoDB engine ===
=== Enable InnoDB engine ===
