I've managed to use mod_auth_mysql to handle a user, group and usergroup table. I thought I would share my setup for those who may also have trouble figuring this out like I did.
AuthMySQLGroupTable "groups g RIGHT JOIN usergroups ug ON (ug.groupid=g.groupid) RIGHT JOIN users u ON (u.userid=ug.userid)"
AuthMySQLGroupField "g.groupname"
AuthMySQLGroupCondition "((g.datetime_expire=0 OR g.datetime_expire > NOW()) AND (u.datetime_expire=0 OR u.datetime_expire > NOW()))"
A member can be a user of any number of groups as long as a record with the userid and groupid exists in the usergroup table. The datetime_expire of either the user or the group must also either be 0 ('0000-00-00 00:00:00') or greater than NOW(). To expire the login without deleting the table row do either 'UPDATE users SET datetime_expire=NOW() WHERE userid=XXX'; or 'UPDATE groups SET datetime_expire=NOW() WHERE groupid=XXX';
You can use any of the following in httpd.conf or .htaccess:
# Allow any user
require valid-user
# Allow specific users
require user user1 user2 ...
# Allow specific groups
require group group1 group2 ...
Remember using more than 1 of the above require(s) is a logical 'OR'.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've managed to use mod_auth_mysql to handle a user, group and usergroup table. I thought I would share my setup for those who may also have trouble figuring this out like I did.
Table Structure:
CREATE TABLE `users` (
`userid` int(11) NOT NULL auto_increment,
`datetime_expire` datetime NOT NULL default '0000-00-00 00:00:00',
`username` varchar(80) collate utf8_unicode_ci NOT NULL default '',
`password` varchar(32) collate utf8_unicode_ci NOT NULL default '',
UNIQUE KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
CREATE TABLE `groups` (
`groupid` int(11) NOT NULL auto_increment,
`datetime_expire` datetime NOT NULL default '0000-00-00 00:00:00',
`groupname` varchar(80) collate utf8_unicode_ci NOT NULL default '',
UNIQUE KEY `groupid` (`groupid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
CREATE TABLE `usergroups` (
`usergroupid` int(11) NOT NULL auto_increment,
`userid` int(11) NOT NULL default '0',
`groupid` int(11) NOT NULL default '0',
UNIQUE KEY `usergroupid` (`usergroupid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
httpd.conf directives:
AuthName "mod_auth_mysql test"
AuthType Basic
AuthMySQLHost localhost
AuthMySQLDB [DATABASE NAME]
AuthMySQLUser [DATABASE USER]
AuthMySQLPassword [DATABASE PASS]
AuthMySQLEnable On
AuthMySQLUserTable "users u"
AuthMySQLNameField "u.username"
AuthMySQLPasswordField "u.password"
AuthMySQLPwEncryption md5
AuthMySQLUserCondition "(u.datetime_expire=0 OR u.datetime_expire > NOW())"
AuthMySQLGroupTable "groups g RIGHT JOIN usergroups ug ON (ug.groupid=g.groupid) RIGHT JOIN users u ON (u.userid=ug.userid)"
AuthMySQLGroupField "g.groupname"
AuthMySQLGroupCondition "((g.datetime_expire=0 OR g.datetime_expire > NOW()) AND (u.datetime_expire=0 OR u.datetime_expire > NOW()))"
A member can be a user of any number of groups as long as a record with the userid and groupid exists in the usergroup table. The datetime_expire of either the user or the group must also either be 0 ('0000-00-00 00:00:00') or greater than NOW(). To expire the login without deleting the table row do either 'UPDATE users SET datetime_expire=NOW() WHERE userid=XXX'; or 'UPDATE groups SET datetime_expire=NOW() WHERE groupid=XXX';
You can use any of the following in httpd.conf or .htaccess:
# Allow any user
require valid-user
# Allow specific users
require user user1 user2 ...
# Allow specific groups
require group group1 group2 ...
Remember using more than 1 of the above require(s) is a logical 'OR'.