Hi,
I'm currently using freeradius 1.0.2 with a mysql database for authorization. Because I have multiple client applications I use the hints file to define groups based on Client-IP-Address:
DEFAULT Client-IP-Address == a.b.c.d
Group = webmail
DEFAULT Client-IP-Address == e.f.g.h
Group = vpnadmin,
Group += vpnuser
The client e.f.g.h differentiates between users and admins. Whether the user is an normal user or an admin is defined by the returned attributes from radiusd which are handled by groups in the mysql database. I have the problem that only the first group (vpnadmin) is queried by the sql statements which I use. I tried to use Group = "'vpnadmin','vpnuser'" and change my WHERE clause to use IN instead of =, but then I had a problem with escaped characters.
My SQL statements are the following:
authorize_check_query = "SELECT ${authcheck_table}.id,${authcheck_table}.UserName,${authcheck_table}.Attribute,${authcheck_table}.Value,${authcheck_table}.op FROM ${authcheck_table},${usergroup_table} WHERE ${authcheck_table}.Username = ${usergroup_table}.UserName AND ${authcheck_table}.Username = '%{SQL-User-Name}' AND ${usergroup_table}.LifeTime > NOW() AND ${usergroup_table}.AuthFailsLeft AND ${usergroup_table}.GroupName = '%{Group}' > 0 ORDER BY ${authcheck_table}.id"
authorize_group_check_query = "SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op FROM ${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName AND ${usergroup_table}.LifeTime > NOW() AND ${usergroup_table}.AuthFailsLeft > 0 AND ${usergroup_table}.GroupName = '%{Group}' ORDER BY ${groupcheck_table}.id"
authorize_reply_query and authorize_group_reply_query look alike.
I have extended the mysql database scheme a little bit (especially usergroup), here are my table descriptions:
mysql> desc radcheck;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | | PRI | NULL | auto_increment |
| UserName | varchar(64) | | MUL | | |
| Attribute | varchar(32) | | | | |
| op | char(2) | | | == | |
| Value | varchar(253) | | | | |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc radgroupcheck;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | | PRI | NULL | auto_increment |
| GroupName | varchar(64) | | MUL | | |
| Attribute | varchar(32) | | | | |
| op | char(2) | | | == | |
| Value | varchar(253) | | | | |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc usergroup;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------------------+----------------+
| id | int(11) unsigned | | PRI | NULL | auto_increment |
| UserName | varchar(64) | | MUL | | |
| GroupName | varchar(64) | | | | |
| LifeTime | datetime | | | 0000-00-00 00:00:00 | |
| AuthFailsLeft | int(11) | | | 1 | |
+---------------+------------------+------+-----+---------------------+----------------+
5 rows in set (0.00 sec)
Any pointers for me?
Thanks for your help!
Daniel
