Database level authentication

Hello, Im trying to write a simple app for company internal usage.
The other applications we use (in php) authenticate users based on
database credentials, and to be honest I have no idea how to implement
this. Any suggestions will be great!

Take a look at the devise gem:

https://github.com/plataformatec/devise

I read README, and i don't see anywere it provides solution I need.

Let me clarify, username and password combination must be same as
DATABASE permission (set by db engine).
It has nothing to to do with content on that database itself.

What do you mean by database here, Do you need something like LDAP
authentication?

No, I think...

Its like every db on server have its own set of permissions for
different users ,right?
And i want to authenticate user based on that permissions.

There are many free DB UI tools available. Choose suitable one for your db choice.

I don't see how db configuration UI might help me on this.

maybe as example, at best

No, I think...

Its like every db on server have its own set of permissions for
different users ,right?
And i want to authenticate user based on that permissions.

Do you mean you want to connect to the database using the name and
password used by user to login, or do you want to prevent the user
from logging in unless he uses a valid name/password configured for
the db, or both?

Colin

That would be second option.

That would be second option.

Since you have top posted everyone will have to scroll down to see
which that is. I will repeat it here to make it easier for those
reading it:

prevent the user from logging in unless he uses a valid name/password configured for the db

The only way I can think of doing that is to attempt to connect to
re-connect to the db when he logs in, using his credentials, and see
if it successful.

Colin

There is actually a way to just query the database.

You haven't said what database you're using, but the procedure should be more or less the same once you figure out how your specific database stores things.

In case of MySQL, you would basically have to establish a connection with the database "mysql" from some ActiveRecord model (using "establish_connection", search it at http://api.rubyonrails.org/), then make sure your ActiveRecord model connects to the "user" table (singular! so use self.table_name="user" in Rails 3 or set_table_name in Rails 2) witin that database. Then you can just use a method like:

Rails 2.x
def authenticate(login, passwd)
    self.first(:conditions => ["Login=? and Password=PASSWORD(?)", login, passwd])
end

Rails 3.x
def authenticate(login, passwd)
    self.where("Login=? and Password=PASSWORD(?)", login, passwd).first
end

This is completely untested and it's an authentication method I'm not too fond of, but this is more or less how you could get it done.

Best regards

Peter De Berdt

http://railscasts.com/episodes/209-introducing-devise
or even more fun:
http://railscasts.com/episodes/235-omniauth-part-1 and
http://railscasts.com/episodes/236-omniauth-part-2

Sounds like you want DB to tell you the roles people play and what
permissions go with those roles.

I'm using postgres.
Yea I think thats what I'm looking for, already made some tests and it
appears to be working - but we will see if there are any other
consequences at later time.

Thanks everyone for discuession.

I appreciate what you're trying to do. I've been a dba in the past and
have built apps using database credentials in the past.

But this is a mistake. They key to being successful with rails is to
leave behind the ways you did things before and embrace 'the rails
way'.

ActiveRecord using a single connection string to connect to the
database -- it's in the database.yml file in the /config directory.
Having it somehow use different credentials based on the user would
make things much more complicated than need be.

My advice -- forget about how you did things before and embrace the
powerful and fast tools that rails provides to do things.

In this case, use 'device' -- it's what almost everyone else uses and
you'll thank yourself later for doing so. If you proceed with the way
you're going then later on you'll kick yourself and wonder what the
hell you were thinking.

Best of luck.

Small detail, it’s ‘devise’ (with an ‘s’).

https://github.com/plataformatec/devise

Peter

I know it's rather bad method, I'll try to convince them to use more
standard approach - i think there is also an LDAP service running, and
I saw LDAP plugin for 'AuthLogic' (or something like that).

Anyway i decided to give a shot to this 'bad method' and this is what
i came with (its not pretty).
I couldnt use regular establish_connection, bcos it reffering to the
application's main connection with database and after posting bad
password/username whole app derails, instead i'm talking to postgresql
adapter directly.

def authenticate(user,password)
  begin
   connection =
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.new(nil, nil,
["hostname", 5432, nil, nil, "dbname", user, password], {})

   rescue PGError
     #FAILED
     redirect_to root_path
     return
   end

    #SUCCES!
    connection.disconnect! unless connection.nil?
    redirect_to orders_path
  end

It's working - but thats all can be said :stuck_out_tongue: I'm not sure if there
arn't some leftovers after such call.