Multiple groups via hints file and rlm_sql

permalink · #1 (**permalink**) 03-24-2005

This is a multi-part message in MIME format.

------_=_NextPart_001_01C53057.F6CC3729
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 =3D=3D a.b.c.d
Group =3D webmail

DEFAULT Client-IP-Address =3D=3D e.f.g.h
Group =3D vpnadmin,
Group +=3D 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 =3D "'vpnadmin','vpnuser'" =
and change my WHERE clause to use IN instead of =3D, but then I had a =
problem with escaped characters.

My SQL statements are the following:

authorize_check_query =3D "SELECT =
${authcheck_table}.id,${authcheck_table}.UserName, ${authcheck_table}.Attr=
ibute,${authcheck_table}.Value,${authcheck_table}. op FROM =
${authcheck_table},${usergroup_table} WHERE ${authcheck_table}.Username =
=3D ${usergroup_table}.UserName AND ${authcheck_table}.Username =3D =
'%{SQL-User-Name}' AND ${usergroup_table}.LifeTime > NOW() AND =
${usergroup_table}.AuthFailsLeft AND ${usergroup_table}.GroupName =3D =
'%{Group}' > 0 ORDER BY ${authcheck_table}.id"

authorize_group_check_query =3D "SELECT =
${groupcheck_table}.id,${groupcheck_table}.GroupNa me,${groupcheck_table}.=
Attribute,${groupcheck_table}.Value,${groupcheck_t able}.op FROM =
${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.Username =
=3D '%{SQL-User-Name}' AND ${usergroup_table}.GroupName =3D =
${groupcheck_table}.GroupName AND ${usergroup_table}.LifeTime > NOW() =
AND ${usergroup_table}.AuthFailsLeft > 0 AND =
${usergroup_table}.GroupName =3D '%{Group}' ORDER BY =
${groupcheck_table}.id"
=20

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) | | | =3D=3D | =
|
| 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) | | | =3D=3D | =
|
| 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

------_=_NextPart_001_01C53057.F6CC3729
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7226.0">
<TITLE>Multiple groups via hints file and rlm_sql</TITLE>
</HEAD>
<BODY>


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      &n bsp;  =
Client-IP-Address =3D=3D a.b.c.d

        &nb sp;   &n=
bsp;    Group =3D webmail


DEFAULT      &n bsp;  =
Client-IP-Address =3D=3D e.f.g.h

        &nb sp;   &n=
bsp;    Group =3D vpnadmin,

        &nb sp;   &n=
bsp;    Group +=3D 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 =3D "'vpnadmin','vpnuser'" and change my WHERE =
clause to use IN instead of =3D, but then I had a problem with escaped =
characters.

My SQL statements are the =
following:


authorize_check_query =3D =
"SELECT =
${authcheck_table}.id,${authcheck_table}.UserName, ${authcheck_table}.Attr=
ibute,${authcheck_table}.Value,${authcheck_table}. op FROM =
${authcheck_table},${usergroup_table} WHERE ${authcheck_table}.Username =
=3D ${usergroup_table}.UserName AND ${authcheck_table}.Username =3D =
'%{SQL-User-Name}' AND ${usergroup_table}.LifeTime > NOW() AND =
${usergroup_table}.AuthFailsLeft AND ${usergroup_table}.GroupName =3D =
'%{Group}' > 0 ORDER BY ${authcheck_table}.id"

authorize_group_check_query =3D =
"SELECT =
${groupcheck_table}.id,${groupcheck_table}.GroupNa me,${groupcheck_table}.=
Attribute,${groupcheck_table}.Value,${groupcheck_t able}.op  FROM =
${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.Username =
=3D '%{SQL-User-Name}' AND ${usergroup_table}.GroupName =3D =
${groupcheck_table}.GroupName AND ${usergroup_table}.LifeTime > NOW() =
AND ${usergroup_table}.AuthFailsLeft > 0 AND =
${usergroup_table}.GroupName =3D '%{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       &nbs p;   &nb=
sp; | Null | Key | Default | =
Extra       &nb sp;  |

 +-----------+------------------+------+-----+---------+-------------=
---+

 | =
id        | int(11) unsigned =
|      | PRI | NULL    | =
auto_increment |

 | UserName  | =
varchar(64)      =
|      | MUL =
|       &nbsp ; =
|        & nbsp;   =
    |

 | Attribute | =
varchar(32)      =
|      |     =
|       &nbsp ; =
|        & nbsp;   =
    |

 | =
op        | =
char(2)       & nbsp;  =
|      |     | =
=3D=3D      =
|        & nbsp;   =
    |

 | Value     =
| varchar(253)     |      =
|     =
|       &nbsp ; =
|        & nbsp;   =
    |

 +-----------+------------------+------+-----+---------+-------------=
---+

 5 rows in set (0.00 sec)


mysql> desc =
radgroupcheck;

 +-----------+------------------+------+-----+---------+-------------=
---+

 | Field     =
| =
Type       &nbs p;   &nb=
sp; | Null | Key | Default | =
Extra       &nb sp;  |

 +-----------+------------------+------+-----+---------+-------------=
---+

 | =
id        | int(11) unsigned =
|      | PRI | NULL    | =
auto_increment |

 | GroupName | =
varchar(64)      =
|      | MUL =
|       &nbsp ; =
|        & nbsp;   =
    |

 | Attribute | =
varchar(32)      =
|      |     =
|       &nbsp ; =
|        & nbsp;   =
    |

 | =
op        | =
char(2)       & nbsp;  =
|      |     | =
=3D=3D      =
|        & nbsp;   =
    |

 | Value     =
| varchar(253)     |      =
|     =
|       &nbsp ; =
|        & nbsp;   =
    |

 +-----------+------------------+------+-----+---------+-------------=
---+

 5 rows in set (0.00 sec)


mysql> desc usergroup;

 +---------------+------------------+------+-----+-------------------=
--+----------------+

 | =
Field       &nb sp; | =
Type       &nbs p;   &nb=
sp; | Null | Key | =
Default       & nbsp;   =
  | Extra       &nb sp;  =
|

 +---------------+------------------+------+-----+-------------------=
--+----------------+

 | =
id             | =
int(11) unsigned |      | PRI | =
NULL       &nbs p;   &nb=
sp;    | auto_increment |

 | =
UserName      | =
varchar(64)      =
|      | MUL =
|        & nbsp;   =
       &nbsp ; =
|        & nbsp;   =
    |

 | =
GroupName     | =
varchar(64)      =
|      |     =
|        & nbsp;   =
       &nbsp ; =
|        & nbsp;   =
    |

 | =
LifeTime      | =
datetime          =
|      |     | 0000-00-00 =
00:00:00 =
|        & nbsp;   =
    |

 | AuthFailsLeft | =
int(11)       & nbsp;  =
|      |     | =
1        & nbsp;   =
       =
|        & nbsp;   =
    |

 +---------------+------------------+------+-----+-------------------=
--+----------------+

 5 rows in set (0.00 sec)

 

Any pointers for me?

 Thanks for your help!


Daniel

 

</BODY>
</HTML>
------_=_NextPart_001_01C53057.F6CC3729--

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode