Authenticating to mySQL through DBI on Apache?

Massyn has asked for the wisdom of the Perl Monks concerning the following question:

#!/fellow/monks.pl

I found this site which explained how you can authenticate Apache against a mySQL server. However, the problem I have with this example is that I still need to provide (on the webserver config) the username and password to connect to the database -- I don't want to do that.

What I'm trying to achieve, is for Apache to ask for a username and password (easy bit), but then to pass that username and password onto my perl code, which will then use the username and password within the DBI dsn connect string for access to the database.

I could go and write my own routine for this, but for the sake of security, I'd like to find a more elegant way of giving my users access to the database.

I'm not trying to be difficult. I'm thinking of security, and building the system to the point where the webserver is just a monkey, doing what it's told. It passes the request onto the database for execution, which is usually far behind at the backend, with no publically accessable address at all. Even if you manage to get onto the web server, you should not be able to get the username and password off any text file on the server -- that's what I'm trying to achieve.

Thank you kind monks..

#!/massyn.pl

Don't -- me without telling me why. If you asked a stupid question, I wouldn't down vote you.

Comment on Authenticating to mySQL through DBI on Apache?

Replies are listed 'Best First'.
Re: Authenticating to mySQL through DBI on Apache? by tilly (Archbishop) on Jul 13, 2003 at 13:44 UTC
It is possible to do what you want with mod_perl. For a fuller picture of what you can do, see Writing Apache Modules in Perl and C. (One of the online chapters walks through authentication and authorization.) However note that your approach should not be used if performance matters to you. Opening a new database connection is a fairly heavy operation, and a standard technique in mod_perl is to cache the connection and not incur this cost. But that will be impossible for you to do because your authentication requires connecting to the database. Update: I should have mentioned that the default authentication method is Basic, which passes name/password combinations in the clear. By coincidence the newest story on perl.com right now is Integrating mod_perl with Apache 2.1 Authentication which walks through custom authentication schemes in more detail.	[reply]
Re: Authenticating to mySQL through DBI on Apache? by bobn (Chaplain) on Jul 13, 2003 at 15:44 UTC
I believe that so far we've missed the OP's point. The database is on a different server than the webserver, behind a(nother) firewall. Authenticating users from infromation stored in the database has been done before with modules to make it fairly trivial - once an initial connection is opened from Apache to the db in order to submit the requests. It is the opening of this initial connection that the poster is concerned about. he wants the intiial db connection somehow done without the id,password being stored on the webserver. And the only ways I see around this are: to have an entry for every user in the table that defines who can connect to mySQL and what they can do in mySQL, as well as possibly another table handling the same stuff for the webserver. But this implies a separate process for setting up these users. OR That the ID,password for db connection is stored in an encrypted file (eg using gpg) and the Apache startup process requires you to enter a passphrase at startup, after which the id,passwd are held by processes in RAM and used as needed. (The Agent thing for OpenSSL that stores users keys in RAM for use by other processes might be worth looking at in thgis regard.) But, even after you figure out how to do this, this may be a cure worse than the disease. --Bob Niederman, http://bob-n.com	[reply]
Re: Re: Authenticating to mySQL through DBI on Apache? by tilly (Archbishop) on Jul 14, 2003 at 01:21 UTC
Perhaps my post above was unclear. The PerlAuthenHandler approach in mod_perl allows any method that you want for verifying the authentication. Including attempting to connect to the database and seeing whether the database accepts that user name/pasword for connecting. There are also potential performance problems with this method that I pointed out.	[reply]
Re: Authenticating to mySQL through DBI on Apache? by barrd (Canon) on Jul 13, 2003 at 13:33 UTC
Hello Massyn, I'm not trying to be funny here but when you say: Even if you manage to get onto the web server, you should not be able to get the username and password off any text file on the server -- that's what I'm trying to achieve. Have you considered what's in the `/var/lib/mysql/mysql/` directory? (Of course your path may be different). Taking a peek at `user.ISD` will give you usernames and passwords, albeit the password is encrypted, but that for a wily cracker wouldn't present too much of a problem if they where smart enough to get past your firewall and gain entry to your server. Again, I'm not trying to be a smarty pants its just so you take that into consideration if you hadn't known or thought about it yet. :)	[reply] [d/l] [select]
Re: Authenticating to mySQL through DBI on Apache? by antirice (Priest) on Jul 13, 2003 at 22:43 UTC
Just wondering, but what keeps the guy who breaks into your system from setting up something that harvests those usernames and passwords from any method of authorization? This is why they introduced digest authorization and are further beefing up security with the Apache 2.1 authorization scheme. As a side note, do not encrypt the username and password for a DBI connection on the server unless you REALLY REALLY want to. Think about it if you will: the server has to decrypt them somehow. The decryption key will be either stored somewhere on the machine or provided by the user. If someone takes control of the system, they can either find it wherever it is on the hard drive or harvest it the next time someone comes in. Forming the database connection is already expensive and decrypting the username and password will only make this process more expensive. antirice The first rule of Perl club is - use Perl The ith rule of Perl club is - follow rule i - 1 for i > 1	[reply]