Connecting to multiple databases with multiple database user


#1

Hi everyone,

I was wondering what the common practice for handling multiple db
users with fine grained privileges on multiple databases is. Against
the often read guideline for rails users to keep with a single db as
“more dbs don’t really make sense anyway”, my opinion is that it DOES
make sense to use more than one db schema for a number of reasons that
I won’t explain here. Anyway, I think a lot of users have to face the
need for multiple dbs, so it should be easy to do it in rails :slight_smile:
shouldn’t it?

I’m working on a project with the goal to put an evaluation system
online. Customers must be able to fill out the answers to the
evaluation questions, and Admins must be able to (1) keep track of all
current evaluations and (2) trigger statistic calculations once
customers complete their evaluation. Customers must not be able to
view/change other Customer’s data, Admins must not be able to change
Customer data but are allowed to view it. These rules MUST be enforced
on the database level. Which leaves me with the facts that

I have an admin and a customer user on my mysql db, and i have three
database schemas called authentication, customer_sandbox and
persistent_base in there as well. The db of most interest is
customer_sandbox. On most of the tables in customer_sandbox only Admin
is allowed to perform CRUD operations. However there is a set of
tables where both Admin and Customer users are allowed CRUD
operations. These tables are the ones where the Customer’s answers to
the evaluation questions are stored. They are a bit tricky because
column privileges restrict access in a way that

  • only Admins are granted INSERT into these tables (make place for new
    evaluation answers)
  • Admins are granted UPDATE only on some of the columns (foreign keys
    and stuff)
  • Customers are granted UPDATE on the rest of the colums (answers to
    the questions - Admins are not allowed to UPDATE those columns)

I currently do the connection configuration in database.yml and I have
different sections for all combinations of database/user pairs, for
all three rails environments. My database.yml looks roughly like this
(the following entries occur for “test” and “production” as well, also
I have the obviously necessary sections labelled development, test and
production with a rather randomly chosen copy of one of my real db
configurations - rails apparently needs these sections, however,
connection details are handled in the model classes, there simply IS
NO default db in my (?any?) multiple db setting)

webadmin_authentication_development:
adapter: mysql
database: ca_authentication
username: webadmin
password:
host: localhost

webadmin_sandbox_development:
adapter: mysql
database: ca_sandbox
username: webadmin
password:
host: localhost

webadmin_persistent_base_development:
adapter: mysql
database: ca_sandbox
username: webadmin
password:
host: localhost

customer_sandbox_development:
adapter: mysql
database: sandbox
username: customer
password:
host: localhost

customer_authentication_development:
adapter: mysql
database: authentication
username: customer
password:
host: localhost

Following Chad F.‘s Rails Recipe on multiple database connections
and Dave T.’ blogentry at
http://blogs.pragprog.com/cgi-bin/pragdave.cgi/Tech/Ruby/Connections.rdoc
I created a subclass of ActiveRecord::Base to establish a connection
for all models that only webadmin is allowed to CRUD. Although this is
currently not working at the moment (see my previous post to this
list) I’m pretty sure this is the way to go.

The question arises on the set of tables that can be accessed as both
webadmin and customer. I see the following possibilities: (When i say
“default the connection to …” I mean following the above mentioned
rails recipe)

  1. Default the connection of those tables to a customer connection. In
    the WebadminController action performing the “webadmin-only”
    operations, remember the old connection and manually change it to the
    webadmin connection by calling establish_connection. Perform the
    “webadmin-only” operations. Change the connection back to the old one

  2. Same scenario as 1 but vice versa (i.e. defaulting to webadmin and
    changing to customer in the CustomerController)

  3. Create the model classes two times, one time with a superclass
    using a webadmin connection, and one time with a superclass using a
    customer connection. I think it would at least be necessary to give
    rails information about the underlying table name since we need names
    different from the rails convention in order for the classnames not to
    clash (although I think this should be possible to solve using
    modules, then again I don’t know about how models behave in different
    modules). The Controllers referencing those models would need to
    declare precisely which model class they refer to. WebadminController
    would refer to WebadminEvaluationFigureInput and CustomerController
    would refer to CustomerEvaluationFigureInput thus using the same
    table with a different db connection. I am aware that this is not
    really DRY, but I also don’t know which implications on the number of
    open db connections the other 2 possibilities would have, if any. I
    even don’t know if this is working anyway. I will try it out after I
    got some sleep and let you all know! Here is the proposed code

class WebadminSandboxBase < ActiveRecord::Base
establish_connection “webadmin_sandbox_#{RAILS_ENV}”
end

class CustomerSandboxBase < ActiveRecord::Base
establish_connection “customer_sandbox_#{RAILS_ENV}”
end

class WebadminEvaluationFigureInput < WebadminSandboxBase
set_table_name “evaluation_figure_inputs”
end

class CustomerEvaluationFigureInput < CustomerSandboxBase
set_table_name “evaluation_figure_inputs”
end

class WebadminController < ApplicationController
model :webadmin_evaluation_figure_input

webadmin operations using webadmin db connection

end

class CustomerController < ApplicationController
model :customer_evaluation_figure_input

customer operations using customer db connection

end

I would appreciate very much ANY THOUGHTS on how to handle multiple
users on multiple databases with rails! How to properly use the
database.yml to configure connections for multiple users on multiple
database(schemata)? What to do with the default connections needed by
rails (development, test, production sections in the yml) that just
don’t really make sense in such a setting?

PLEASE also read my last posting on Connecting to multiple databases.
I still haven’t solved this problem …

Many many thx for taking the time!

gamsl