Multiple MySQL Users Per App?

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

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

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