Topic: Per-user database login

Is there any way to get RoR to establish a separate database connection for each logged-in user, or for each page request? This is unconventional, I know, but is necessitated by the following.

(1) the database already exists and has been in use for some time. Because the database is accessed through several different mechanisms (e.g. Windows applications) much of the business logic has been implemented in the database as triggers and stored procedures. (The database is Oracle.)

(2)  the data is highly sensitive (medical records), so the database implements per-user security (i.e. certain individuals can see certain tables, etc) and keeps a detailed audit of activity, including the user ID for every data access

(3) this is not a public site: only a few dozen people will have login rights. We can manage that many accounts on the database, and the server load is very light.

For these reasons, Oracle needs to know the ID of every user, regardless of whether they came in via RoR or any other application. The usual arrangement (a generic "web-user" account with its credentials stored in config/database.yml) won't work. Any ideas, anyone?

Re: Per-user database login

I hate to make a "me too" post, but I'm interested in this as well. If you stumble on a solution before somone posts one please share it with the forum.

Our motivation is for problem-diagnosis. If a single user is having an issue we can run a trace and see just what that user is doing by the raw DB access. That gets a bit awkward when you have installations with double- and triple-digit user counts!

Re: Per-user database login

This wiki page on setting up multiple databases with Rails shows you how to establish the database connection in a before filter in the controller. You can probably add logic here to change the database connection based on the logged in user.

Railscasts - Free Ruby on Rails Screencasts

Re: Per-user database login

Thanks for this suggestion. So far, it all works fine. One comment: on the wiki page, the reference to app/controllers/abstract_application.rb should in fact be app/controllers/application.rb and the class AbstractApplicationController should now be ApplicationController.

In Oracle, it's necessary to create public sysnonyms for all the tables and views in the application. If not, Oracle won't find them, because it assumes the owner is the logged in user.

The problem now is speed. Before implementing this, most pages loaded in a fraction of a second; now they take about three seconds each, which is unacceptable. At the moment it is re-establishing a database connection on every page request; what I need to do next is try to implement some kind of connection cache per user. Back to the keyboard...

Last edited by graham (2007-02-13 10:57:07)

Re: Per-user database login

RE: The schema name problem...

Instead of the public synonyms, have you tried passing the fully-qualified name in set_table_name? i.e.

class FooInAnotherSchema < ActiveRecord::Base
  set_table_name 'another_schema.foos'
end

Re: Per-user database login

Thanks for the set_table_name suggestion. This would work fine, but I found
the Oracle synonym to be slightly more intuitive. (We've got lots of tables and lookups.)

We've now got it all working OK using a connection pool (one connection per user).

class ApplicationController < ActionController::Base

    # see http://wiki.rubyonrails.org/rails/pages … eDatabases
    # Hop into the front of the request-handling pipeline
  before_filter :our_login

  # Manually establish a connection to the database
  def our_login
        ApplicationController.establish_connection_using_pool(
            session[:user_id], session[:p_word]
        )
  end

    # The connection pool is implemented as a hash, indexed by the user id.
    # Each value is a hash with two items: the connection and its expiry time.
    def self.establish_connection_using_pool(usr, pwd)
        return if usr.nil? || pwd.blank?
        $pool ||= {}
        $pool[usr] ||= {}
        if $pool[usr][:expires] && $pool[usr][:expires] > ::Time.now
            ActiveRecord::Base.connection = $pool[usr][:connection]
        else
            ActiveRecord::Base.establish_connection(
                :adapter => 'oci',
                :host => 'myhost',
                :username => usr.to_s,
                :password => pwd
            )
            $pool[usr][:connection] = ActiveRecord::Base.connection
        end
        $pool[usr][:expires] = 20.minutes.from_now
    end

end


Notes
The login page stores user id and password in the session hash. To protect the password from prying eyes, the session is stored in memory (which is OK because there are not many users and not much session data).

One repurcussion is that users only get one try at entering a password; if they get it wrong, the wrong password remains in the session, and they can't even get to the login page again. They have to close the browser and start again. This is fine for our application: we don't want users stabbing away with multiple passwords.

I'm still not sure about expiring connections. Need to do some more testing.

Last edited by graham (2007-02-16 05:27:14)