David Krider David Krider - 5 months ago 12
MySQL Question

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

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

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.

Comments