<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40"
xmlns:ns0="urn:schemas-microsoft-com:office:smarttags">
<head>
<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="PersonName"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Microsoft Sans Serif";
panose-1:2 11 6 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:Arial;}
span.StileMessaggioDiPostaElettronica18
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:595.3pt 841.9pt;
margin:70.85pt 107.65pt 2.0cm 107.65pt;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:315958177;
mso-list-type:hybrid;
mso-list-template-ids:2050262132 420622760 68157443 68157445 68157441 68157443 68157445 68157441 68157443 68157445;}
@list l0:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:36.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Arial;
mso-fareast-font-family:"Times New Roman";}
@list l0:level2
{mso-level-tab-stop:72.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-tab-stop:108.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level4
{mso-level-tab-stop:144.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-tab-stop:180.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-tab-stop:216.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level7
{mso-level-tab-stop:252.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-tab-stop:288.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-tab-stop:324.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l1
{mso-list-id:861362354;
mso-list-type:hybrid;
mso-list-template-ids:39735088 160837006 68157465 68157467 68157455 68157465 68157467 68157455 68157465 68157467;}
@list l1:level1
{mso-level-text:%1;
mso-level-tab-stop:70.65pt;
mso-level-number-position:left;
margin-left:70.65pt;
text-indent:-35.25pt;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=IT link=blue vlink=purple>
<div class=Section1>
<div>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>Ok,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>I’ve tested the function once again on RADIUS, It
was an error in the function (I used “utente” instead of “user”)
that caused RADIUS malfunction.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>The process logic is as follows:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:70.65pt;text-indent:-35.25pt;mso-list:
l1 level1 lfo3'><![if !supportLists]><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial'><span style='mso-list:Ignore'>1<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>check if user’s MAC
is present in radcheck table<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:70.65pt;text-indent:-35.25pt;mso-list:
l1 level1 lfo3'><![if !supportLists]><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial'><span style='mso-list:Ignore'>2<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>if not present his MAC
will be inserted into radcheck<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>In sql.conf Postauth_query is changed as detailed <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>postauth_query =
"INSERT into ${postauth_table} (id, user, pass, reply, date) values ('',
'%{User-Name}', '%{User-Password:-Chap-Password}', '%{replyacket-Type}',
NOW()); SELECT mac_check('%{User-Name}', '%{Calling-Station-Id}')"<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-right:.9pt;text-autospace:none'><b><font
size=2 color=purple face="Microsoft Sans Serif"><span lang=EN-GB
style='font-size:10.0pt;font-family:"Microsoft Sans Serif";color:purple;
font-weight:bold'><o:p> </o:p></span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>it is simply added a call to the function mac_check,
defined as follows<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>CREATE FUNCTION
`mac_check`(user VARCHAR(20),mac VARCHAR(30)) RETURNS varchar(50)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> DETERMINISTIC<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>BEGIN<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>IF (SELECT (SELECT
MAX((Attribute='Calling-Station-Id')) from radcheck WHERE UserName=user))<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>THEN<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> RETURN
'record already present';<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>END IF;<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> INSERT INTO
radcheck (UserName,Attribute,op,Value) VALUES (user,'Calling-Station-Id',':=',
mac);<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> RETURN
'record inserted';<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>END<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>Regards,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>Carlo<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
</div>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>Da:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'> Carlo Prestopino
[mailto:<st1:PersonName w:st="on">c.prestopino@waitalia.com</st1:PersonName>] <br>
<b><span style='font-weight:bold'>Inviato:</span></b> venerd́ 3 febbraio 2006
13.57<br>
<b><span style='font-weight:bold'>A:</span></b> '<st1:PersonName w:st="on">FreeRadius
developers mailing list</st1:PersonName>'<br>
<b><span style='font-weight:bold'>Oggetto:</span></b> R: rlm_sql</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>Yes, it’s ok.<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>I’ve simply made a mistake in cut/paste
operations.<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>The corret one is as follows<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>CREATE FUNCTION `mac_check`(utente VARCHAR(20),mac
VARCHAR(30)) RETURNS varchar(50)<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'> DETERMINISTIC<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>BEGIN<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>IF (SELECT (SELECT
MAX((Attribute='Calling-Station-Id')) from radcheck WHERE UserName=user))<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>THEN<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>
#INSERT into radpostauth (id, user, pass, reply, date) values ('', utente,
passwd,reply_packet, NOW());<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>
RETURN 'record already present';<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>END IF;<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>
INSERT INTO radcheck (UserName,Attribute,op,Value) VALUES
(utente,'Calling-Station-Id',':=', mac);<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>
RETURN 'record inserted';<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>END<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>The five arguments one is because I tried to change
the content of postauth_query with a function that have to perform two actions:<o:p></o:p></span></font></p>
<p class=MsoPlainText style='margin-left:36.0pt;text-indent:-18.0pt;mso-list:
l0 level1 lfo2'><![if !supportLists]><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'><span style='mso-list:Ignore'>-<font size=1
face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]><span lang=EN-GB>INSERT into
radcheck (needed to perform MAC control)<o:p></o:p></span></p>
<p class=MsoPlainText style='margin-left:36.0pt;text-indent:-18.0pt;mso-list:
l0 level1 lfo2'><![if !supportLists]><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'><span style='mso-list:Ignore'>-<font size=1
face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]><span lang=EN-GB>INSERT into
radpostauth (original behaviour of postauth_query)<o:p></o:p></span></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'><o:p> </o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>Regards,<o:p></o:p></span></font></p>
<p class=MsoPlainText><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt'>Carlo<o:p></o:p></span></font></p>
</div>
</body>
</html>