Re: Updated User Survey - Which DB backend do you use?

[Keith Yoder]

I am going to add a table to blacklist CallingStationIDs that aren't

allowed to connect no matter what password they use to keep out old
users who cancelled to use their neighbor's / cousin's / mother-in-law's
account.
   

Good idea. Send it to me when you have done it :-)

 

Peter,
The fact that someone else might be interested in this feature was 
enough for me to go ahead and do it. I created a table:

CREATE TABLE `bad_callingstationids` (
 `CALLINGSTATIONID` varchar(18) NOT NULL default '',
 `OBSERVATION` varchar(100) NOT NULL default '',
 PRIMARY KEY  (`CALLINGSTATIONID`)
);
and then altered our authorize_check_query to the following

SELECT logins.USUARIO,logins.LOGIN,Attribute,logins.SENHA,op, 
bad_callingstationids.CALLINGSTATIONID
FROM usuarios.logins, usuarios.usuarios LEFT JOIN 
radius.bad_callingstationids ON '%{Calling-Station-Id}' = 
bad_callingstationids.CALLINGSTATIONID WHERE usuarios.CADASTRO = 
logins.USUARIO
AND usuarios.ATIVO = 1
AND logins.LOGIN = '%{SQL-User-Name}'
AND bad_callingstationids.CALLINGSTATIONID IS NULL
ORDER BY USUARIO

Like I said before we use an existing database schema to store user and 
password info.  The important part is the LEFT JOIN in the FROM clause.  
I was going to use a sub-query but MySQL 4.1.0 doesn't fully support 
them yet. (I guess that would be another good reason to use Postgres :) )

I'm interested in any feedback.  I don't know if this was the best way 
to do this but it works for me. 

Peter, I get the impression you are one of the developers for the 
rlm_sql module.  I'm interested in putting the radius log into a 
database.  This would help me give our support staff information to 
diagnose people who don't know who to type their password correctly and 
other such things.  Do you know if anyone else has worked on this at 
all? Is anyone else interested?

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