<HTML>
<HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META content="Open WebMail 2.32 20040525" name=GENERATOR>
</HEAD>
<BODY bgColor=#ffffff>
<font size="2">OK, Phil, you got me. I thought all I did was copy the to address, but must have used a reply instead. Sorry.
<br />
<br />Thanks for the code suggestions. I understand what you see as the issue. Makes sense. I will experiment with what you suggest and see what I get.
<br />
<br />Scott Reed
<br />
Owner
<br />
NewWays
<br />
Wireless Networking
<br />
Network Design, Installation and Administration
<br />
<a target="_blank" href="http://www.nwwnet.net/">www.nwwnet.net</a>
<br />
<br />
<br /><b>---------- Original Message
-----------</b>
<br />
From: Phil Mayers <p.mayers@imperial.ac.uk>
<br />
To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org>
<br />
Sent: Fri, 07 Apr 2006 11:09:48 +0100
<br />
Subject: Re: User in Multiple Groups
<br />
<br />> Scott Reed wrote:
<br />>
> I did not usurp a thread, I reposted my own.
<br />>
<br />>
Really? How odd:
<br />>
<br />>
Message-ID: <002101c658de$6ceb9400$0500a8c0@laptop>
<br />>
From: "debik" <debik@vp.pl>
<br />>
Subject: Re: Couldn't stop freeradius server!!
<br />>
<br />>
From: "Scott Reed" <sreed@nwwnet.net>
<br />>
Date: Wed, 5 Apr 2006 07:25:29 -0500
<br />>
Message-Id: <20060405121401.M70783@nwwnet.net>
<br />>
In-Reply-To: <002101c658de$6ceb9400$0500a8c0@laptop>
<br />>
Subject: User in Multiple Groups
<br />>
<br />>
>
<br />>
> I changed radcheck to have := instead of ==. No change.
<br />>
>
<br />>
> First query returns:
<br />>
> +----+--------------+--------------+-------------+----+
<br />>
> | id | GroupName | Attribute | Value
| op |
<br />>
> +----+--------------+--------------+-------------+----+
<br />>
> | 28 | MS1-AP1 | Service-Type | Framed-User | == |
<br />>
> | 31 | Router-Admin | Service-Type | Login-User | == |
<br />>
> +----+--------------+--------------+-------------+----+
<br />>
<br />>
Ah ok. Lightbulb moment.
<br />>
<br />>
Disclaimer: I'm not an expert w.r.t. rlm_sql (or much else in the server
<br />>
in fact)
<br />>
<br />>
BUT I've taken quite a detailed look at the code in the past, and as far
<br />>
as I can tell it does this:
<br />>
<br />>
check_items = []
<br />>
<br />>
radcheck_items = query("<radcheck query>")
<br />>
check_items += radcheck_items
<br />>
<br />>
groupcheck_items = query("<radgroupcheck query>")
<br />>
check_items += groupcheck_items
<br />>
<br />>
...that is, ALL the groupcheck items for a user are added to the check
<br />>
items (see src/modules/rlm_sql/rlm_sql.c line 782, at least in 1.1.0
<br />>
source).
<br />>
<br />>
So, in your case the check items from both groups will be merged:
<br />>
<br />>
username Service-Type == Framed-User, Service-Type == Login-User
<br />>
<br />>
...and obviously will never match. So you're correct, with the default
<br />>
queries >1 groupcheck where the groups have the same check item will
<br />>
seldom (if ever) work as expected.
<br />>
<br />>
You could try changing the groupcheck query to something like:
<br />>
<br />>
SELECT
<br />>
${groupcheck_table}.id,
<br />>
${groupcheck_table}.GroupName,
<br />>
${groupcheck_table}.Attribute,
<br />>
${groupcheck_table}.Value,
<br />>
${groupcheck_table}.op
<br />>
FROM
<br />>
${groupcheck_table},
<br />>
${usergroup_table}
<br />>
WHERE
<br />>
${usergroup_table}.Username = '%{SQL-User-Name}'
<br />>
AND
<br />>
${usergroup_table}.GroupName = ${groupcheck_table}.GroupName
<br />>
-- this bit has been added
<br />>
AND
<br />>
(
<br />>
-- all groups without Service-Type checks
<br />>
NOT EXISTS (
<br />>
select 1 from ${groupcheck_table} as ot
<br />>
where ot.Attribute=='Service-Type'
<br />>
and ot.GroupName==${groupcheck_table}.GroupName
<br />>
)
<br />>
OR
<br />>
-- all groups with Service-Type checks matching our Service-Type
<br />>
EXISTS (
<br />>
select 1 from ${groupcheck_table} as ot
<br />>
where ot.Attribute=='Service-Type'
<br />>
-- WARNING: this assumes ot.Op is "=="
<br />>
and ot.Value=='%{Service-Type}'
<br />>
and ot.GroupName==${groupcheck_table}.GroupName
<br />>
)
<br />>
)
<br />>
-- the above bit has been added
<br />>
ORDER BY ${groupcheck_table}.id
<br />>
<br />>
...which is a bit complex (and untested / off the top of my head) but
<br />>
should work. Having said that I note you're using MySQL, which I can't
<br />>
remember if it support sub-selects.
<br />>
<br />>
Really the module should be recoded IMHO to do this:
<br />>
<br />>
usercheck = query("<radcheck query>")
<br />>
if usercheck AND paircmp(usercheck, request):
<br />>
userreply = query("<radreply query>")
<br />>
pairxlatmove(request.reply, userreply)
<br />>
groups = query("<usergroup query> order by priority")
<br />>
for group in groups:
<br />>
groupcheck = query("<groupcheck query> WHERE
GroupName=$group")
<br />>
if groupcheck and paircmp(groupcheck, request):
<br />>
groupreply = query("<groupreply query> WHERE
GroupName=$group")
<br />>
pairxlatmove(request.reply, groupreply)
<br />>
<br />>
...but I don't know if there's any interest in doing that.
<br />>
-
<br />>
List info/subscribe/unsubscribe? See <a target="_blank" href="http://www.freeradius.org/list/users.html">http://www.freeradius.org/list/users.html</a>
<br /><b>------- End
of Original Message
-------</b>
<br />
</font>
</BODY>
</HTML>