Remote database connections available in all tools

January 30, 2020April 2, 2020 Peter SchulzWhat’s new?

WP Data Access version 3 is the largest update so far. The new version of the plugin supports remote database access for all tools and features. In this post I’ll explain how remote database access can be used and when you should not use it. Please read this post completely before you start using remote database connections.

Local database

A local database is a database (schema) located on the same physical server as your WordPress installation.

From the perspective of the plugin, a local database runs in the same database instance as your WordPress database. If your server runs multiple database instances, the connection to another local instance is regarded as a remote connection.

Remote database

A remote database is a database which is located on another physical server than your WordPress installation.

Connecting

Local databases are available from the listboxes. Only databases to which you have access are shown in the listbox. A remote database has to be added manually.

The plugin allows users to create and drop databases from the Data Explorer in the WordPress dashboard. The plugin user however, needs to have the necessary priveleges to perform these actions (CREATE and DROP).

If a local database is running in the same instance as your WordPress database, you can grant acccess to it to make it accessible within the plugin. Local databases running in other instances can be added as remote database connections.

Grant access to local database wpda_demo:

grant all privileges on wpda_demo.* To 'wordpress'@'localhost';

HINT On my development machine my WordPress user is allowed to create and drop databases. On my web server I use remote connections.

Remote databases have to be added manually. The database name has to start with the prefix rdb:. This is enforced by the plugin. Below is an example of a remote database connection named rdb:remote. The add remote database connection form allows you to test your connection. When you saved the connection it becomes available in all database listboxes (see previous image rdb:astroshare_astroonline and rdb:test).

Using remote connections

You can use remote connections just like you use your WordPress database. The plugin handles both types the same way. If you have the right privileges you can even create, alter and drop tables, views and so on in the Data Explorer or Data Designer.

Remote databases are available in all tools: Data Explorer, Data Designer, Data Projects, Data Publisher and Data Backup. If you want to create unscheduled data exports for remote database, yo need to select the remote database in the Data Explorer first and then click on button Data Backup.

Restrictions

A remote database must be a MySQL or MariaDB database! Other database management systems like Oracle or SQL Server are not supported.

Remote connection management

What to do if a remote database is temporarily unavailable?
What to do if a remote database is no longer available?
How to disable or modify an existing remote database connection?
How to remove an existing remote database connection?

The plugin settings page (now available from the WordPress settings menu) allows you to disable, modify or delete remote databases.

Risks

By default remote data is send over the network in plain text. So does your database username. Only your password is encrypted. If you are publicly showing data you retrieve from a remote database, sending your data in plain text does not seem to be a issue. You should however not use an unsecured remote database connection to manage sensitive data!

Restrict access

Make sure your remote database user is only allowed to connect from your webserver. Since your webserver has a public IP address, you can use that address to improve security:

grant all privileges on remote_schema.* To 'remote_user'@'211.180.78.111';

HINT You should also do this for local database acces!

Sensitive data

To manage sensitive data, use a local database or a secured connections to a remote database!

If you want to use a remote database to manage sensitive data, you need should secure your database traffic with SSL. To setup a SSL connection you need to enable OpenSLL support. Please read this support topic to learn how. Thank you Andre! 🙂