Although I've never tried this personally, it seems like the following would be a good solution: First, set up two different database users: The first one, script_user, has read-access to only the users table (no access to other tables), which can look like this: 
users:
+----------+----------------+
| username | crypt_pw       |
+----------+----------------+
| joe      | x47s$EWn47#fdb |
| jane     | p98uyDNR93@$w5 |
| ...

[download]
while priv_user has read-write access to all tables.

Now the trick is in the crypt_pw column. This is not the hash of the users' passwords, as you may expect -- it is instead the result of a symmetric cipher of priv_user's database password, with each user's password as the key. Obviously generating these columns this must be done beforehand or by some otherwise privileged and "safe" script -- otherwise priv_user's password would have to be hard-coded into some script, making it vulnerable to attacks listed above. You get the point.

So the authentication process will look like this:

  1. User enters username joe and password in $joe_pw using some authentication scheme (*).
  2. The script logs into DB using unprivileged script_user and hard-coded password. Since we have read-only access, we are pretty safe hard-coding this password.
  3. The script gets the crypt_pw value for joe, and deciphers it using his $joe_pw as the cipher key -- this deciphered value is stored in $priv_pw.
  4. Attempt to relogin to the DB using login priv_user, password $priv_pw. If successful, then $joe_pw was indeed correct as joe's password.
  5. undef $priv_pw, $joe_pw; to be extra-safe ;)

(*) You can use many methods to achieve step 1. For instance, mod_perl will let you get the password from a HTTP basic authentication request, or you can use a CGI script over SSL, etc. depending on your paranoia level.

Of course, this whole scheme hinges on being able to get 2 DB users, one with with nonstandard permissions -- which isn't always possible at many ISPs. It also makes resetting priv_user's password hard without also resetting the other users' passwords.

Are there any knowledgeable security expert monks out there with critiques of this method? The only hard-coded password would be a "safe" one that is only allows read-only access to a single table. With a strong cipher, you will not be able to determine the password for priv_user without knowing one of the allowed users' passwords. Please share any insights, as my curiosity is piqued by this method.

I hope this helps, and good luck.

blokhead

In reply to Re: Protecting your DBI user/password in scripts? by blokhead
in thread Protecting your DBI user/password in scripts? by JPaul

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.