Re: MySQL Authentication Logging
On Mon, 18 Aug 2003, Adam Carmichael wrote: > Hi All! > > I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with MySQL4 for logging > accounting and retrieving authentication information. I am interested in knowing how > to log authentication attempts and even possibly why an attempt failled. > > For example, if we have a customer who thinks their dialup account is being > exploited - they can change their password, and then see if any authentication > requests are being made. (Actually, just thinking about it, the user would not need > to change their password, they could just see the times at which their logons (or > attempted logons) occur). > > I have made some Google searches on the list already, and I saw a few posts in which > Alan DeKok said that it is possible to do this - however the rest of the replies > seemed to wonder away from what I had hoped. Check out dialup_admin/bin/log_badlogins. It will do a tail -f on radius.log and log each failed login as a separate session in the radacct table. > > > > > Thanks in advance > > Adam > > > Adam Carmichael > Network Operations Manager > email: [EMAIL PROTECTED] > web: http://www.no1.com.au > icq: 2207644 > > #1 Computer Services, Empowerment Through Internet Communications. > -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Authentication Logging
- Original Message - From: "Nicolas Baradakis" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 20, 2003 6:57 PM Subject: Re: MySQL Authentication Logging > Adam Carmichael wrote: > > > I was considering relearning C/C++ all over again so I could help > > with this just because we need this feature quite badly. I'd love to > > finally submit something back to an opensource product, but I don't > > think my coding skills are quite up to scratch for this kind of > > challenge yet. I'd be more than happy to provide feedback and help > > with testing however, and I'll help patch what I can. > > Thanks for the offer of assistance but I can manage the coding part > alone. Since the architecture of FR is really clean it's not a big > problem. And of course it much better if you do more tests on your > side when it's done. > > > Users of this might need additional features, such as the ability to > > log other kinds of errors (such as RADIUS clients not in > > clients.conf (or the deprecated clients file) trying to > > authenticate, or if for example a particular NAS / LNS is running an > > old secret) so perhaps an auth_default_log() might also be required > > to log anything that doesn't match one of the other authentication > > types it could be logged into a kind of table that has a few BLOB or > > TEXT fields and places the entire log entry into that field. Another > > scenario is if you use ENCRYPT()'ed passwords within MySQL, and a > > user tries to log in using CHAP. > > While writing the patch I gave up about the "auth_badpass_table" and > the "auth_goodpass_table". Just one "authlog_table" is sufficient, and > you chose what you put inside with the "authlog_query". > > > What do you think? (with regards to the above outlined mysql logging > > scenarios). If you'd like to further development, then I'll > > subscribe to freeradius-developers and help out where I can. > > I think indeed we should follow the discussion in the freeradius-devel > mailing list. > > -- > Nicolas Baradakis Indeed, I'll subscribe to the list now :) -- Adam > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Authentication Logging
Adam Carmichael wrote: > I was considering relearning C/C++ all over again so I could help > with this just because we need this feature quite badly. I'd love to > finally submit something back to an opensource product, but I don't > think my coding skills are quite up to scratch for this kind of > challenge yet. I'd be more than happy to provide feedback and help > with testing however, and I'll help patch what I can. Thanks for the offer of assistance but I can manage the coding part alone. Since the architecture of FR is really clean it's not a big problem. And of course it much better if you do more tests on your side when it's done. > Users of this might need additional features, such as the ability to > log other kinds of errors (such as RADIUS clients not in > clients.conf (or the deprecated clients file) trying to > authenticate, or if for example a particular NAS / LNS is running an > old secret) so perhaps an auth_default_log() might also be required > to log anything that doesn't match one of the other authentication > types it could be logged into a kind of table that has a few BLOB or > TEXT fields and places the entire log entry into that field. Another > scenario is if you use ENCRYPT()'ed passwords within MySQL, and a > user tries to log in using CHAP. While writing the patch I gave up about the "auth_badpass_table" and the "auth_goodpass_table". Just one "authlog_table" is sufficient, and you chose what you put inside with the "authlog_query". > What do you think? (with regards to the above outlined mysql logging > scenarios). If you'd like to further development, then I'll > subscribe to freeradius-developers and help out where I can. I think indeed we should follow the discussion in the freeradius-devel mailing list. -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Authentication Logging
Nic, I would love to help you test this!!! Adam - Original Message - From: "Nicolas Baradakis" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 20, 2003 6:16 PM Subject: Re: MySQL Authentication Logging > Alan DeKok wrote: > > > "Adam Carmichael" <[EMAIL PROTECTED]> wrote: > > > > > I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with MySQL4 > > > for logging accounting and retrieving authentication information. I am > > > interested in knowing how to log authentication attempts and even > > > possibly why an attempt failled. > > > > See the 'detail' module in the latest CVS snapshot. It will create > > "detail" style files for authentication requests, responses, proxied > > packets, and replies from a home server. > > > > It won't log all of the information you see in debugging mode, but > > it will log a fair amount of useful data. > > It's nice to get a lot of data in the detail files, but as I already > said before : > > <<<<< > When you have multiple freeradius servers, you want to store > authentication attempts in a database rather than a flat file. > >>>>> > > I'm doing a patch in rlm_sql to put information in a "authlog table" > after authentification. In fact it's nearly finished, but I want to > do more tests and add more commentaries in my source. > > -- > Nicolas Baradakis > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Authentication Logging
Alan DeKok wrote: > "Adam Carmichael" <[EMAIL PROTECTED]> wrote: > > > I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with MySQL4 > > for logging accounting and retrieving authentication information. I am > > interested in knowing how to log authentication attempts and even > > possibly why an attempt failled. > > See the 'detail' module in the latest CVS snapshot. It will create > "detail" style files for authentication requests, responses, proxied > packets, and replies from a home server. > > It won't log all of the information you see in debugging mode, but > it will log a fair amount of useful data. It's nice to get a lot of data in the detail files, but as I already said before : < When you have multiple freeradius servers, you want to store authentication attempts in a database rather than a flat file. > I'm doing a patch in rlm_sql to put information in a "authlog table" after authentification. In fact it's nearly finished, but I want to do more tests and add more commentaries in my source. -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Authentication Logging
"Adam Carmichael" <[EMAIL PROTECTED]> wrote: > I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with MySQL4 > for logging accounting and retrieving authentication information. I am > interested in knowing how to log authentication attempts and even > possibly why an attempt failled. See the 'detail' module in the latest CVS snapshot. It will create "detail" style files for authentication requests, responses, proxied packets, and replies from a home server. It won't log all of the information you see in debugging mode, but it will log a fair amount of useful data. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Authentication Logging
Hi Nicolas, I was considering relearning C/C++ all over again so I could help with this just because we need this feature quite badly. I'd love to finally submit something back to an opensource product, but I don't think my coding skills are quite up to scratch for this kind of challenge yet. I'd be more than happy to provide feedback and help with testing however, and I'll help patch what I can. Users of this might need additional features, such as the ability to log other kinds of errors (such as RADIUS clients not in clients.conf (or the deprecated clients file) trying to authenticate, or if for example a particular NAS / LNS is running an old secret) so perhaps an auth_default_log() might also be required to log anything that doesn't match one of the other authentication types it could be logged into a kind of table that has a few BLOB or TEXT fields and places the entire log entry into that field. Another scenario is if you use ENCRYPT()'ed passwords within MySQL, and a user tries to log in using CHAP. These are just two of the kinds of problems that have plagued the last week of my work. In the end, I just left a whole bunch of users with the Password attribute set, and another bunch with Crypt-Password. Not very good practise I know, but all scripts (both online and Windows based VB applications) update the attribute field when editing / inserting a password. What do you think? (with regards to the above outlined mysql logging scenarios). If you'd like to further development, then I'll subscribe to freeradius-developers and help out where I can. Adam. - Original Message - From: "Nicolas Baradakis" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 18, 2003 10:05 PM Subject: Re: MySQL Authentication Logging > Chris van Meerendonk wrote: > > > On Mon, 2003-08-18 at 05:30, Adam Carmichael wrote: > > > > > I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with > > > MySQL4 for logging accounting and retrieving authentication > > > information. I am interested in knowing how to log authentication > > > attempts and even possibly why an attempt failled. > > > > I'm using a simple script that reads radius.log and put that in a > > mysql table that can be accessed by our helpdesk by using a simple > > php-interface to help people with their dialin problems. Maybe you > > can do something with it. > > The situation isn't so easy when you have multiple freeradius servers, > and in that case you want to store authentication attempts in a > database rather than a flat file. > > For different reasons I need also logging connexion requests and I > already thought a little about it. > > I'm considering writing a patch for this problem, and I would like > advice from the developpers to do it the efficiently. Please correct > me if the following doesn't make sense. > > The extension should be made in module rlm_sql because here you have > all you need to connect the database and make a request (and the > administrator may store the authcheck_table, the acct_table and the > authentication attempts in the same db). > > Then in sql.conf you should add four lines with auth_badpass_table, > auth_goodpass_table, auth_badpass_query, auth_goodpass_query (or > something like that). > > We know in authentication whether the password is valid, so you have > to enter module rlm_sql at this time. It requires to add a function > rlm_sql_authenticate() to manage it... > > If I get something working from this idea I'll submit the patch in the > mailing list later. > > -- > Nicolas Baradakis > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Authentication Logging
Chris van Meerendonk wrote: > On Mon, 2003-08-18 at 05:30, Adam Carmichael wrote: > > > I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with > > MySQL4 for logging accounting and retrieving authentication > > information. I am interested in knowing how to log authentication > > attempts and even possibly why an attempt failled. > > I'm using a simple script that reads radius.log and put that in a > mysql table that can be accessed by our helpdesk by using a simple > php-interface to help people with their dialin problems. Maybe you > can do something with it. The situation isn't so easy when you have multiple freeradius servers, and in that case you want to store authentication attempts in a database rather than a flat file. For different reasons I need also logging connexion requests and I already thought a little about it. I'm considering writing a patch for this problem, and I would like advice from the developpers to do it the efficiently. Please correct me if the following doesn't make sense. The extension should be made in module rlm_sql because here you have all you need to connect the database and make a request (and the administrator may store the authcheck_table, the acct_table and the authentication attempts in the same db). Then in sql.conf you should add four lines with auth_badpass_table, auth_goodpass_table, auth_badpass_query, auth_goodpass_query (or something like that). We know in authentication whether the password is valid, so you have to enter module rlm_sql at this time. It requires to add a function rlm_sql_authenticate() to manage it... If I get something working from this idea I'll submit the patch in the mailing list later. -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Authentication Logging
On Mon, 2003-08-18 at 05:30, Adam Carmichael wrote:
> Hi All!
>
> I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with
> MySQL4 for logging accounting and retrieving authentication
> information. I am interested in knowing how to log authentication
> attempts and even possibly why an attempt failled.
I'm using a simple script that reads radius.log and put that in a mysql
table that can be accessed by our helpdesk by using a simple
php-interface to help people with their dialin problems. Maybe you can
do something with it.
Succes, Chris
The db struct of radproblems is:
mysql> describe radproblems;
+--+--+--+-+-++
| Field| Type | Null | Key | Default |
Extra |
+--+--+--+-+-++
| RadProblemId | bigint(21) | | PRI | NULL|
auto_increment |
| UserName | varchar(255) | | MUL |
||
| Password | varchar(255) | | |
||
| AuthTime | datetime | | | -00-00 00:00:00
||
| Realm| varchar(64) | YES | |
||
| NASIPAddress | varchar(15) | | |
||
| CalledStationId | varchar(30) | | |
||
| CallingStationId | varchar(30) | | MUL |
||
| TerminateCause | varchar(64) | | |
||
+--+--+--+-+-++
9 rows in set (0.00 sec)
The import script:
cat /usr/local/bin/parse-radiuslog.sh
#!/bin/sh
# Input format:
# Mon Mar 10 11:07:06 2003 : Auth: Login incorrect (rlm_ldap: Bind as
user failed): [user/password] (from client nas port 16578 cli 012345678)
INFILE="/var/log/freeradius/radius.log"
TMPFILE="/var/log/freeradius/radius.tmp"
ADDTOFILE="/var/log/freeradius/radius.parsed"
SQLTMPFILE="/var/log/freeradius/radius.tmp.sql"
if [ -f $TMPFILE ]
then
rm $TMPFILE
fi
if [ -f $SQLTMPFILE ]
then
rm $SQLTMPFILE
fi
mv $INFILE $TMPFILE
check=`cat "${TMPFILE}" | grep 'Auth: Login incorrect'`
if [ -z "$check" ]
then echo " "; else
cat "${TMPFILE}" | grep 'Auth: Login incorrect' | while read LINE; do
P1=`echo ${LINE} | sed -e 's/^.*\[\([^/]*\).*$/\1/' -e s/\'/#/g
-e s/\"/#/g`
P2=`echo ${LINE} | sed -e 's/^.*\(\[.*\]\).*$/\1/' -e
's/^.*\/\(.*\)]$/\1/' -e s/\'/#/g -e s/\"/#/g`
# P1=`echo ${LINE} | sed 's/^.*\(\[.*\]\).*$/\1/'`
P3=`echo ${LINE} | awk '{print $5 "-" $2 "-" $3 " " $4}' | sed
-e 's/Jan/1/' -e 's/Feb/2/' -e 's/Mar/3/' -e 's/Apr/4/' -e 's/May/5/' -e
's/Jun/6/' -e 's/Jul/7/' -e 's/Aug/8/' -e 's/Sep/10/' -e 's/Oct/10/' -e
's/Nov/11/' -e 's/Dec/12/'`
P4=`echo ${LINE} | grep ' cli ' | sed 's/^.*cli
\b\([0-9]*\).*$/\1/'`
P5=`echo ${LINE} | grep 'rlm_ldap:' | sed 's/^.*rlm_ldap:
\([A-Za-z0-9 ]
*\).*$/\1/'`
echo "INSERT INTO radproblems VALUES
('','${P1}','${P2}','${P3}','','','
','${P4}','${P5}');" | sed 's/\\//' >> $SQLTMPFILE
done
mysql -hyour.mysql.host -usqluser -ppassword database < $SQLTMPFILE
fi
cat $TMPFILE >> $ADDTOFILE
> For example, if we have a customer who thinks their dialup account is
> being exploited - they can change their password, and then see if any
> authentication requests are being made. (Actually, just thinking about
> it, the user would not need to change their password, they could just
> see the times at which their logons (or attempted logons) occur).
>
> I have made some Google searches on the list already, and I saw a few
> posts in which Alan DeKok said that it is possible to do this -
> however the rest of the replies seemed to wonder away from what I had
> hoped.
>
>
>
>
> Thanks in advance
>
> Adam
>
>
> Adam Carmichael
> Network Operations Manager
> email: [EMAIL PROTECTED]
> web: http://www.no1.com.au
> icq: 2207644
>
> #1 Computer Services, Empowerment Through Internet Communications.
>
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
MySQL Authentication Logging
Hi All! I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with MySQL4 for logging accounting and retrieving authentication information. I am interested in knowing how to log authentication attempts and even possibly why an attempt failled. For example, if we have a customer who thinks their dialup account is being exploited - they can change their password, and then see if any authentication requests are being made. (Actually, just thinking about it, the user would not need to change their password, they could just see the times at which their logons (or attempted logons) occur). I have made some Google searches on the list already, and I saw a few posts in which Alan DeKok said that it is possible to do this - however the rest of the replies seemed to wonder away from what I had hoped. Thanks in advance Adam Adam CarmichaelNetwork Operations Manageremail: [EMAIL PROTECTED] web: http://www.no1.com.auicq: 2207644#1 Computer Services, Empowerment Through Internet Communications. BEGIN:VCARD VERSION:2.1 N:Carmichael;Adam FN:Adam Carmichael - #1 NICKNAME:carneeki ORG:#1 Computer Services;Operations TITLE:Network Operations Manager ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;1 Lowing Close=0D=0A;Forrestville;NSW;2087;Australia LABEL;WORK;ENCODING=QUOTED-PRINTABLE:1 Lowing Close=0D=0A=0D=0AForrestville, NSW 2087=0D=0AAustralia X-WAB-GENDER:2 URL;HOME:http://www.no1.com.au URL;WORK:http://www.no1.com.au EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;PREF;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] REV:20030818T033005Z END:VCARD
