help centre
For more info visit status.atmail.com

How can we help?


Search our knowledge base for answers to
common questions and latest updates.



My activities New request

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


Contact our support team


+61 (7) 5357 6605       support@atmail.com