How could I tie Rails user authentication/authorization to existing permissions within a MySQL database server?

Question

We have a MySQL database server with scores of databases in it. We have lots of user accounts within the server which control access to the various databases. I would like to tie a Rails app into these pre-existing accounts. Every authentication system I can find (4 of them, so far) presumes that I will be creating a table dedicated to a "User" model. I just want to use the mysql.user table for authentication and the mysql.db table for authorization. Are there any Rails authentication systems out there that could use the "real" MySQL privilege system, instead of laying one on TOP of the database? Would this even be advisable? If I have to code this myself, does anyone foresee lurking difficulties with such an approach?

Answer 1

So I saw this get upvoted, an noted that I never posted what I did. Here was what I sorted out. "GINA" was a separate, legacy database that held the data and the user accounts. I created a YAML stanza for that connection in database.yml, and modified the User model to deal with the schema.

user.rb

class User < ActiveRecord::Base
    establish_connection :gina
    self.table_name = "mysql.user"
    self.primary_key = "user"
end

users_controller.rb

def authenticate
    user = params[:username]
    pass = params[:password]
    valid = authenticate_user(user, pass)
    if valid
        session[:username] = user
        session[:password] = pass
        cookies[:username] = user
        cookies[:password] = pass
        redirect_to gina_tables_path
    else
        redirect_to gina_tables_path, :notice => "Login failed."
    end
end

application_controller.rb

def authenticate_user(user, pass)
    begin
        u = nil if u == 'root'
        u = User.find(user) # <- Here's the call to get the user, with password.
        unless u.nil?
            hash = "*" + Digest::SHA1.hexdigest(Digest::SHA1.digest(pass)).upcase
            old_hash = old_password(pass)
            if u.Password === hash || u.Password === old_hash
                session[:username] = user
                return true
            end
            return false
        end
    rescue Exception => e
        Rails.logger.info(e)
        return false
    end
end

Since the User model is keyed by the string of the user name, User.find(user) gets the user from MySQL's user table, which brings along it's hashed password. So the whole trick came down to using the exact same sort of password hash that MySQL uses, and comparing that hash (of the supplied password) to the one in the database for that user. If they're equal, that's an authenticated login.

Answer 2

In short: your requirement is quite exotic. Usually web applications have an authentication/authorization system for their users that allows them to define roles and gives these roles to users. But the entire web app itself usually uses one DB username/password to access/interact with the DB. This DB username usually has highly restricted permissions for security reasons. Example, you do not want your users to do schema manipulations, or delete records, but solely mark existing records as deleted, so you often just give a web app Select, Insert and Update privileges with rare Delete privileges for special tables that need it.

Another reason why web apps often use the same DB username/connection is because the run in a multi process/thread environment trying to take advantage of DB connection pools where they keep DB connections open and just put them back into a pool of available connections. If every user had its own connection that he would have to initiate, you would have serious issues scaling, since you would have an open connection connection to the DB for every users and could not reuse existing connections between users.

If you can clarify your requirement a bit, maybe we can come up with a better solution.