My activities New request

contact atmail support

PH: +61 (7) 5357-6605

support@atmail.com

Follow

.htaccess password protection via MySQL

Nathan Salt -

PROBLEM

Using .htaccess files via MySQL

ENVIRONMENT

  • On-Premise Server + WebMail Installations: Version 6.0 > Current Version
  • Webmail Only Installations: Version 6.0 > Current Version

CAUSE

Additonal security module

RESOLUTION

'mod_auth_mysql' (http://modauthmysql.sourceforge.net/), is a module for apache which allows you to password protect a webserver directory with usernames and passwords from a MySQL table. This is ideal if you would like to password protect the Webmail URL of @Mail, if your organization requires extra security, and you would like to prevent access to the Webmail system for authorized IP's only.Read below on how to setup Mod-Auth Mysql with Apache

Things to note:

-It is assumed that you are running a linux system with apache web server.
-Detailed instructions regarding the installation and configuration of mod_auth_mysql can be found here: http://modauthmysql.sourceforge.net/

Installing mod_auth_mysql

After building the module, you need to install it to your modules directory.

Apache 1.x:

apxs -i mod_auth_mysql.so

Apache 2.x:

apxs -i mod_auth_mysql.la

Next, add the following directive to httpd.conf:

LoadModule mysql_auth_module modules/mod_auth_mysql.so

Restart the apache webserver.

Once the webserver has restarted, mod_auth_mysql will be started as a module with apache.

Creating the user table

Login to mysql:

mysql -u root -p

The command line will prompt you for a password, and when you hit enter you should be presented with something like this:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2975 to server version: 5.0.22-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

You are now in a mysql shell!

First you must create a mysql database called auth with the following query:

CREATE DATABASE auth;

You must now create a mysql table as below:

CREATE TABLE `users` (
`id` int(5) NOT NULL auto_increment,
`user_name` char(30) NOT NULL,
`user_passwd` char(20) NOT NULL,
`ip_address` varchar(15) default NULL,
`DateAdded` datetime default NULL,
`Expire` int(1) default NULL,
PRIMARY KEY  (`id`)
)
  • The 'id' column simply serves as the PRIMARY KEY to satisfy MySQL requirements, you do not need to enter data into this field, it will enter itself a unique number.
  • The 'user_name' column will hold usernames under 30 characters.
  • The 'user_passwd' column will hold passwords under 20 characters, which should be encrypted.
  • The 'ip_address' column holds allowed ip addresses, this is optional, it will allow ip address based ACL.
  • The 'DateAdded' column will store the date that the user was added, this is optional, it can be used to allow expiry of a user, for this functionality you will also need to include the 'Expire' column.
  • The 'Expire' column holds either '1' or '0', it is also necessary for expiry of a user, but not mandatory for simple username/password authentication.

Adding users

Users can be added into the MySQL table with the following command from the mysql shell.

INSERT INTO users (user_name,user_passwd,ip_address,Expire) VALUES ('newuser','newpassword','computersip','1');

If you did not include the IP ACL or Expire options, you should use something more like this:

INSERT INTO users (user_name,user_passwd) VALUES ('newuser','newpassword');

Setting up .htaccess

mod_auth_mysql uses the .htaccess file to know which directories need to be protected.

Your .htaccess file should be located in /usr/local/webmail/atmail/ and should contain the following:

AuthName "MySQL authenticated zone"
AuthType Basic
AuthMySQLEnable on
AuthMySQLUser username_for_mysql_database
AuthMySQLPassword password_for_mysql_database
AuthMySQLDB auth
AuthMySQLUserTable users
AuthMySQLNameField user_name
AuthMySQLPasswordField user_passwd
AuthMySQLUserCondition "ip_address = '%a'"
require valid-user

The AuthMySQLUserCondition "ip_address = '%a'" line should only be added if you have ip_address columns set up.

To test your new configuration navigate to www.yourdomain.com/mail/

If you get an error 500, then there is most likely something wrong with your .htaccess file, if you get a login box, then enter the username/password combination that you have stored in your mysql table, and it should take you straight to your mail.

Have more questions? Submit a request

Comments