# Connecting to Multiple Databases with Multiple Database User

**URL:** <https://www.ruby-forum.com/t/connecting-to-multiple-databases-with-multiple-database-user/58958>\
**Category:** Rails\
**Created:** [April 27, 2006, 1:17pm UTC](https://www.ruby-forum.com/t/connecting-to-multiple-databases-with-multiple-database-user/58958 "2006-04-27T13:17:27Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![martin-ga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/9fc348/32.png) [@martin-ga](https://www.ruby-forum.com/u/martin-ga)\
**Post date:** [April 27, 2006, 1:17pm UTC](https://www.ruby-forum.com/t/connecting-to-multiple-databases-with-multiple-database-user/58958/1 "2006-04-27T13:17:27Z")

</div>

Hi everyone,

I am posting this again, as I didn’t get any replies …

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 🙂  
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](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?

cheers  
Martin

---

<div class="post-metadata">

**Author:** ![martin-ga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/9fc348/32.png) [@martin-ga](https://www.ruby-forum.com/u/martin-ga)\
**Post date:** [April 27, 2006, 5:33pm UTC](https://www.ruby-forum.com/t/connecting-to-multiple-databases-with-multiple-database-user/58958/2 "2006-04-27T17:33:45Z")

</div>

Hi!

On Apr 27, 2006, at 4:16 AM, Martin G. wrote:

> need for multiple dbs, so it should be easy to do it in rails 🙂  
> shouldn’t it?

Martin-

```
I know this isn't the answer to all your problems but this is the

```

way I am connecting to multiple db’s in one project. I have like 5  
models that each need a different db connection. I do it like this:

class PastDue \< ActiveRecord::Base  
end

PastDue.establish\_connection( :adapter =\> “mysql”,  
:host =\> “[mysql.foo.com](http://mysql.foo.com)”,  
:database =\> “pastdue”,  
:username =\> “customer\_front”,  
:password =\> “xxxxxxxx”  
)

-Ezra