(RADIATOR) Re: time-based access and radiator

[Hugh Irvine]

Hello Tunde -

The following lines need to be in the AuthBy SQL clause, not the 
Hanlder:

# Adjust the time left when they log out
 AccountingStopsOnly
 AcctSQLStatement update SUBSCRIBERS set 
TIMELEFT=TIMELEFT-0%{Acct-Session-Time} \
  where USERNAME='%n' and ISPREPAID = 1


regards

Hugh

On Thursday, Apr 3, 2003, at 23:13 Australia/Melbourne, Ayotunde 
Itayemi wrote:

> Hi All, Hi Hugh,
>  
> Seems like I am having a major brain block.
> I am trying to implement a config which allows both time-based 
> (prepaid) access
> and unlimited access (just two options). The description of my 
> subscribers table is given
> below.
>  
> I am having problems configuring radiator. Simply stated what I want 
> it as follows:
>  
> a user attempts to connect:
> 1. radiator checks subscribers table to see if he is prepaid -in which 
> case it returns a
>     the amount of time (in secs) left as the max amount of time the 
> user can stay online
>     to the NAS/RAS otherwise (the user is allowed unlimited access)
>     Of course if the TIMELEFT field is zero for prepaid client, the 
> user is not allowed to connect.
>  
> 2. when the user disconnects (if prepaid, his TIMELEFT field is 
> reduced by the amount
>     of time he stayed online) - this does not affect "unlimited" 
> clients or else there would be
>     negative values in the TIMELEFT column
>  
> I have attached my proposed radius config file which i am sure is far 
> from being complete or correct.
> I think the above should be simple but I cant seem to get my mind 
> around it.
>  
> Other issue is that I have to get the accouting data back into my 
> accouting package called optigold.
> I was thinking of getting the radius parser file from optigold to 
> parse the radius accounting logfile.
> Any suggestions/ideas will be appreciated. Is there anybody on this 
> list using radiator and optigold
> for acccouting etc?
>  
> Hope to hear from you very soon.
> Thanks.
>  
> Tunde Itayemi.
> ==============================================================
>  USERNAME varchar(100) primary key,-- Users login name, including realm
>  PASSWORD varchar(30), -- Cleartext password
>  CHECKATTR varchar(200), -- Optional check radius attributes
>  REPLYATTR varchar(200), -- Optional reply radius attributes
>  TIMELEFT int,
>  ISPREPAID       int,         -- 0 for unlimited and 1 for prepaid
>  EMAILATTR varchar(200),
>  STATUS  varchar(10),    -- Optional (used for enable/disable)
>  FULLNAME varchar(60) -- Optional user fullname
> ==============================================================
>  
>
> <SessionDatabase SQL>
>  Identifier SDB1
>  DBSource dbi:Oracle:radius00
>  DBUsername
>  DBAuth
>  DeleteQuery
> </SessionDatabase>
>  
> # =================== CLIENTs   =================================
> <Client x.x.x.x>
>         Secret patt123
>         DupInterval 0
>  NasType Patton
>         SNMPCommunity public
>         Identifier pattonrases
> </Client>
>  
>
> <Client b.b.b.b>
> # (surgemail) mail server
>         Secret surge at mail!
>         DupInterval 0
>         Identifier mailserver
> </Client>
>  
> # =================== AUTH BYs =================================
> <AuthBy SQL>
>  Identifier SQLClientauth
>  NoDefault
>  DBSource dbi:Oracle:radius11
>  DBUsername
>  DBAuth
>  AuthSelect select PASSWORD, TIMELEFT*ISPREPAID from SUBSCRIBERS \
>   where USERNAME='%n' and (TIMELEFT > 0 or ISPREPAID=0) and STATUS = 
> 'Enabled'
>  
>  AuthColumnDef 0, User-Password, check
>  AuthColumnDef 1, GENERIC, check
>  AuthColumnDef 2, GENERIC, check
>  AutoMPPEKeys Yes
>  DefaultSimultaneousUse 1
> </Auth>
> <AuthBy SQL>
>         Identifier SQLmailauth
>         NoDefault
>         DBSource        dbi:Oracle:radius00
>         DBUsername      nitelradius
>         DBAuth          radius4nitel
>         AuthSelect select PASSWORD, CHECKATTR, EMAILATTR \
>          from SUBSCRIBERS where USERNAME = '%n'
>         AuthColumnDef   0,      User-Password,  check
>         AuthColumnDef   1,      GENERIC,        check
>         AuthColumnDef   2,      Vendor-Specific, reply
>         DefaultSimultaneousUse 1
> </Auth>
>  
> #=================== HANDLERs   ================================
> <Handler Client-Identifier=pattonrases>
>         AuthByPolicy ContinueWhileAccept
>         RewriteUsername s/^([^@]+).*/$1/
>  RewriteUsername tr/A-Z/a-z/
>         UsernameCharset a-zA-Z0-9\._ at -
>         AcctLogFileName         %L/account.log
>         PasswordLogFileName     %L/password.log
>         SessionDatabase SDB1
>         AuthBy SQLClientauth
> # Adjust the time left when they log out
>  AccountingStopsOnly
>  AcctSQLStatement update SUBSCRIBERS set 
> TIMELEFT=TIMELEFT-0%{Acct-Session-Time} \
>   where USERNAME='%n' and ISPREPAID = 1
> </Handler>
>  
> <Handler Client-Identifier=mailserver>
>         AuthByPolicy ContinueWhileAccept
>         RewriteUsername tr/A-Z/a-z/
>         UsernameCharset a-zA-Z0-9\._ at -
>         AuthBy SQLmailauth
> </Handler>
>

NB: have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?

-- 
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: text/enriched
Size: 7137 bytes
Desc: not available
URL: <http://www.open.com.au/pipermail/radiator/attachments/20030404/047c3986/attachment.bin>