Multiple MySQL Users Per App?

Rails
1

I am not fond of Rails’ reliance on a single database user in its db
config setup. I’d really like to be able to use multiple user
definitions for connections in my applications.

Based on separation of concerns and principle of least privilege, I
prefer to have multiple db users with specific rights, and use a
specific user for particular actions and sections of an application.
The exact breakdown will vary by app, but where possible I separate
read/write/delete and table access rights.

So far I have not seen any reference to Rails having the ability to
define multiple users for db connections, and I haven’t seen any
encouragement for doing this. It’s one of a few things I personally
consider a security weakness in Rails, or at least a lack of security
reinforcement.*

Is it possible in any way to define multiple connections to a
database, and tell individual ActiveRecord actions which user to run
the action as?

  • (Not trying to say Rails is not secure, but there are areas where I
    think it could be better, and this is one of them.)

– gw

2

On 30 May 2008, at 01:14, Greg W. wrote:

  • (Not trying to say Rails is not secure, but there are areas where I
    think it could be better, and this is one of them.)

Dr nic has some stuff on that:
http://drnicwilliams.com/2007/04/12/magic-multi-connections-a-facility-in-rails-to-talk-to-more-than-one-database-at-a-time/
I think most people have be worrying about splitting certain models
off to a different database or makeing write operations go the the
master and read operations to the slave, so I don’t know how well it
fits in with your stuff. You can also call establish_connection in
your model classes, but that’s definitely a per model connection which
won’t (I think) give you what you want.

Fred

3

On May 29, 2008, at 5:35 PM, Frederick C. wrote:

fits in with your stuff. You can also call establish_connection in
your model classes, but that’s definitely a per model connection which
won’t (I think) give you what you want.

From an interface standpoint, I’d like it to be something like:

model.find(:user => :public_search,
-or-
model.find(:connect_as => :public_search,

If :user is not defined then it would fall back to a default just
like it does now. With the presence of user, it pulls from a list of
defined users in database.yml – though I don’t know what the
preferred syntax for that would be…

development:
host: x.x.x.x
adapter: mysql
database: my_big_db
encoding: utf8
username:
password:
users:
public_search:
username:
password:
new_accounts:
username:
password:
admin_all:
username:
password:

Seems like something down where a query inherits the connection
params of the model/application might be tweakable to check the
presence of that :user parameter and cosult an expanded version of a
connection definition.

Something to explore as a project I guess…

– gw