setting database connection with session variable

You can rewrite your method as:

class Stuff < ActiveRecord::Base
 def establish_connection_user(user, pass, database)
   establish_connection(
    :adapter  => "mysql2",
    :host     => "127.0.0.1",
    :username => user,
    :password => pass,
    :database => database)
  end
end

and in your controller:

  class StuffController < ApplicationController
    def login #example 
      stuff = Stuff.new
      stuff.establish_connection_user(
        session[:dbuser], 
        session[:dbuserpass],
        session[:dbname]) 
    end

This way you also encapsulate it and make the details less obvious. I suggest you also encrypt your cookie so you don't have the credentials so exposed. You can take an idea from this answer:

Storing an encrypted cookie with Rails

You can select the database in your model like this:

establish_connection "db_name_#{session[:something]}"

This way your model know which database to pull/push data.

Look at this: http://m.onkey.org/how-to-access-session-cookies-params-request-in-model

As hinted by kwon in the comments on the original question, I would approach it through using the session only to retain the identity of the user. I would then pull the desired database connection from logic in a model and a central database (the default rails DB) persisting user details and user connection information.

Start with a modification to your user model (assuming that your user has a model that is persisted in a central database)

• add an attribute to the user representing the data to be used
• in your application controller set the user in a before_filter, based on the session key
• initialize your Stuff model with a user argument

You can then lookup your database connection based on the database.yml. Or if you have one database per user and you need this to be dynamic, create a second model (in the central database) representing the database connection with a foreign key onto the user model.

The following is a bunch of code that may or may not work in reality, but hopefully gives you a template for getting started.

class ApplicationController < ActionController::Base
  before_filter :set_user

  def set_user
    begin
      @user = UserProfile.find(session[:usernumber]) if session[:usernumber]        
    rescue
      logger.warn "Possible error in set_user. Resetting session: #{$!}"
      @user=nil
      session[:usernumber]=nil
      reset_session
    end
  end

end

class StuffController < ApplicationController
  def show
    @stuff = Stuff.user_get(@user, params[:id])
  end
end

class Stuff < ActiveRecord::Base
  # This would be better moved to a module to reuse across models
  def self.establish_connection_user(user)
    establish_connection(user.connection_hash)
  end
  def establish_connection_user(user)
    establish_connection(user.connection_hash)
  end

  def self.user_get user, item_id        
    establish_connection_user(user)
    find(id)
  end

  def self.user_where user, *query_args        
    establish_connection_user(user)
    where(query_args)
  end
  # Even better than replicating 'where', create model methods 
  # that are more representative of your desired functionality
end

class User  < ActiveRecord::Base
  has_one :user_connection
  def connection_hash
    uc = self.user_connection
    {:database=>uc.db, :password=>uc.pass, :user=>uc.username, :host=>uc.dbhost, :adapter=>uc.adapter}
  end
  # User probably contains other user-facing details
end

If you have the option of using PostgreSQL you can use the Schemas feature of PostgreSQL to effectively have separate table namespaces (schemas) for each user. The benefit here is you are still connected to the same database (thus avoiding hacking up the rails API), but you get the same benefits of multiple DBs in terms of database separation.

If you have a RailsCasts Pro subscription ($9/mo) Ryan Bates has an excellent video on the subject: http://railscasts.com/episodes/389-multitenancy-with-postgresql

Jerod Santo also did a great write up on his blog: http://blog.jerodsanto.net/2011/07/building-multi-tenant-rails-apps-with-postgresql-schemas/

In both examples they use subdomains to switch between tenants/schemas but you could easily link it to a user record.