Based on excellent suggestions by Rob Biedenharn <Rob@AgileConsultingLLC.com> in
Storing constant data in a separate database - Rails - Ruby-Forum
...here's a recipe for setting up access to an external readonly database.
Motivation: My application has several very large tables that are constant (from the point of my Rails app). In fact, there are external scripts that will occasionally update the large tables, but such updates are infrequent. I want my Rails app to access but not modify these tables.
Overall approach: we create an external database ('myapp_external') and a database user ('readonly_user') that is granted only SELET privileges on the database. We then create a subclass of ActiveRecord ('ReadonlyDB') that connects to the database through readonly_user, so any attempts to modify the database will raise an error.
=== Step 1: extend config/database.yml with a new entry specifying a database named 'myapp_external' and a username of "readonly_user". For mysql, it might look like this:
readonly_db: adapter: mysql encoding: utf8 reconnect: false database: myapp_external # see note 1 pool: 5 username: readonly_user password: readonly_pass # see note 2 socket: /tmp/mysql.sock
[note 1]: You could, in fact specify "myapp_development" or "myapp_production", in which case you'd have a readonly window onto your development or production database. In our case, we want a separate database altogether.
[note 2]: Naturally, you can pick any username and password that you want for the readonly user.
=== Step 2: create the external database and readonly user.
% mysql -uroot -pxxxxx
create database myapp_external;
Query OK, 1 row affected (0.15 sec)
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY
'readonly_pass'; Query OK, 0 rows affected (0.82 sec)
GRANT SELECT ON *.* TO 'readonly_user'@'localhost' IDENTIFIED BY
'readonly_pass'; Query OK, 0 rows affected (0.00 sec)
