Clear text password and MySQL

[Eric S. Pulley]

Hi list,

I just thought I share this since I see a lot of people using mysql with 
clear text passwords.  It's probably obvious to everyone but since I never 
see anyone talking about it I though I'd share my config for using 
encrypted password in mysql.  This config makes it so your users can use 
secure methods of authentication over the Internet and still have there 
data in an encrypted form in the database.

It's not perfect. An admin that knows the SALT you are using to encrypt the 
password field can retrieve the decrypted passwords from the db.  But I 
find this to be an advantage in many cases.

Using mysql 4+ you can encrypt fields with the 
AES_ENCRYPT("text-to-encrypt","SALT") function.  Just make sure your 
password field is a blob (binary varchar works too I think).

so your settings in imapd.conf are:
sasl_pwcheck_method: auxprop
sasl_sql_engine: mysql
sasl_sql_user: Yada
sasl_sql_passwd: Yadayada
sasl_sql_hostnames: localhost or whatever
sasl_sql_database: YadaDB
sasl_sql_statement: SELECT AES_DECRYPT(password_field,"SALT_YADA") FROM 
users_table WHERE username_field ='%u'

In this scenario you are still passing the SALT in clear text to the db but 
IMO this is much better than having your users logging in with plaintext 
passwords over an open network.  Especially if your DB is on the same host 
as cyrus-imap since you can contain it to a socket and not use a network at 
all for the DB lookups.

Also your mail server and user accounts are only as secure as the 
imapd.conf file.  So use at your own risk.

Anyway I hope someone finds this useful.


-- 
ESP
---
Home Page: http://asg.web.cmu.edu/cyrus
Wiki/FAQ: http://cyruswiki.andrew.cmu.edu
List Archives/Info: http://asg.web.cmu.edu/cyrus/mailing-list.html