Your WordPress installation connects to your WordPress database, which is usually installed on the same server as your web server. Custom tables and views are often created in the WordPress database. WP Data Access allows you to create and use tables and views in other databases as well.
A local database is a database (schema) located on the same physical server as your WordPress installation. With WP Data Access you can directly connect to other local databases.
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.
A remote database is a database which is located on another physical server than your WordPress installation or a local database running in another instance.
Connecting to a local database
Local databases are available from the listbox in the Data Explorer as shown in the image below. Only databases to which the user has access are shown.
The plugin allows users to create and drop databases from the Data Explorer in the WordPress dashboard. The plugin needs to have access and the necessary priveleges to perform these actions (CREATE and DROP). 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.
Connecting to a remote database
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 (see previous image rdb:remote). When you saved the connection it becomes available in all database listboxes.
You can use remote connections just like your WordPress database. The plugin handles both types of databases in the same way. If you have the necessary privileges you can create, alter and drop tables, views and so on in the Data Explorer or from the 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, you need to select the remote database in the Data Explorer first and then click on button Data Backup.
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.
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!
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'@'126.96.36.199';
HINT You should also do this for local database acces!
To manage sensitive data, use a local database or a secured (SSL) connection to a remote database.
Remote SSL connections
Starting from version 3.6.0 the plugin allows to create remote database connections using SSL (see image below: click to enlarge).
To use SSL the plugin needs to have access to the client key and certificate and the ca certificate. Make sure these files are readable!
When activated, connections using SSL are available in the same way as standard connections. SSL connections are available for all tools, presuming the remote account has the necessary privilieges.