Creating AR access to an external, readonly database

Based on excellent suggestions by Rob B.
[email protected] in

Storing constant data in a separate database - Rails - Ruby-Forum

…here’s a recipe for setting up access to an external readonly

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

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:

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
mysql> create database myapp_external;
Query OK, 1 row affected (0.15 sec)

mysql> CREATE USER ‘readonly_user’@‘localhost’ IDENTIFIED BY
Query OK, 0 rows affected (0.82 sec)

mysql> GRANT SELECT ON . TO ‘readonly_user’@‘localhost’ IDENTIFIED BY
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR ‘readonly_user’@‘localhost’;
| Grants for readonly_user@localhost |
| GRANT SELECT ON . TO ‘readonly_user’@‘localhost’ IDENTIFIED BY PASS… |
1 row in set (0.00 sec)

mysql> quit

=== Step 3: populate the external database with some data Here, we’re
just copying a table from myapp_development into myapp_external.

% mysqldump -c -uroot -pxxxxx myapp_development employees >
% mysql -uroot -pxxxx myapp_external < /tmp/empls.sql

=== Step 4: Create ReadonlyDB < ActiveRecord::Base and a test subclass

— file: apps/models/readonly_db.rb:
class ReadonlyDB < ActiveRecord::Base
establish_connection :readonly_db

Mark ReadonlyDB as abstract so AR won’t try to access any underlying

tables. But subclasses will be concrete (abstract_class?() => true)

@abstract_class = true

This hook is called when ReadonlyDB is subclassed. We use it to set

the table name from the class name (just like ActiveRecord does)

def self.inherited(c)

I’m not sure this is 100% required, but it allows AR to catch


to modify the db earlier than if it went all the way down to the db.

def readonly?


— file: apps/models/employee.rb:
class Employee < ReadonlyDB

=== Step 5: Test it out!

bash-3.2$ script/console
Loading development environment (Rails 2.3.8)

=> 2
=> #<Employee id: 1, name: “Roger Dodger”, created_at: “2010-06-17
Employee.create(:name => “Pete Shelley”) # (…“I don’t exist”)
ActiveRecord::ReadOnlyRecord: ActiveRecord::ReadOnlyRecord
create_or_update' ... Employee.first.delete ActiveRecord::StatementInvalid: Mysql::Error: DELETE command denied to user 'readonly_user'@'localhost' for table 'employees': DELETE FROM employees WHERE (idIN (1)) from /Users/r/Development/Ruby/lib/ruby/gems/1.9.1/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract_adapter.rb:221:inrescue in log’

ReadonlyDB # Show that AR knows that ReadonlyDB is abstract
=> ReadonlyDB(abstract)
Employee # Show that AR knows the structure of Employee
=> Employee(id: integer, name: string, created_at: datetime, …)

=== Summary & Comments:

I’ve only tried this with Rails 2.3.8 and mysql 5.1.36 – YMMV. But the
approach should be generally applicable: create a database user that has
readonly privileges, create a new entry in config/database.yml to
reference that user, create a subclass of AR that connects through that
entry. Voila: a readonly external database.

As hinted at in Step 1 Note 1 (above), your entry in config/database.yml
doesn’t have to reference an external database: it can reference one of
your existing databases (e.g. your development or production database).
Since it connects via a readonly user, anything subclassed under
ReadonlyDB won’t be allowed to modify the database. This may be useful
for some applications.

Send comments, questions, improvements. I’m still learning myself.

  • ff