Hi Hugh,
I finally figured how to take out the suffix before it uses the
AcctSQLStatement. Thanks for your advice! ^_^ I never knew you could
have a separate AuthBy SQL with just AcctSQLStatement. Radiator is so
flexible! Too cool!
As for the AuthBy Identiifers, I actually had (2) AuthBy SQLs - one for
regular users (SQL) and one for GCC users (GCCSQL). I just showed you
the authentication for the GCC users. Sorry for the confusion. It is
operating correctly - thanks to you! My config for GCC users looks like
this now:
<AuthBy SQL>
Identifier UPDATE
DBSource xxxxx
DBUsername xxxxx
DBAuth xxxxx
AccountingStopsOnly
AcctSQLStatement update USERS set SESSIONTIME = (SESSIONTIME -
0%{Acct-Session-Time}) \
where IDENTIFIER = '%n'
</AuthBy>
<AuthBy SQL>
Identifier SQL
DBSource xxxxx
DBUsername xxxxx
DBAuth xxxxx
AuthSelect select Password, Expiration, SimUse, \
IdleTime, SessionTime, StaticIP \
from USERS where IDENTIFIER = '%n' AND STATUS != 'C' AND
SESSIONTIME > 0
AuthColumnDef 1, Expiration, check
AuthColumnDef 2, Simultaneous-Use, check
AuthColumnDef 3, Idle-Timeout, reply
AuthColumnDef 4, Session-Timeout, reply
AuthColumnDef 5, Framed-IP-Address, reply
AccountingTable ACCOUNTING
AcctColumnDef IDENTIFIER,User-Name
AcctColumnDef TIME_STAMP,Timestamp,formatted-date,'%m-%d-%Y
%H:%M:%S'
AcctColumnDef DURATION,Acct-Session-Time,integer
AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer
AcctColumnDef ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
AcctColumnDef ACCTSESSIONID,Acct-Session-Id
AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
AcctColumnDef NASIDENTIFIER,NAS-IP-Address
AcctColumnDef NASPORT,NAS-Port,integer
AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
AcctColumnDef CONNECTSPEED,Connect-Info
AcctColumnDef CONNECTSPEED,USR-Connect-Speed
AcctColumnDef CALLERID,Calling-Station-Id
AcctColumnDef POPID,Called-Station-Id
</AuthBy>
<Handler Request-Type = Accounting-Request, Class = GCCSQL>
AuthByPolicy ContinueAlways
AuthBy SQL
<AuthBy GROUP>
RewriteUsername s/\+ppp//
AuthBy UPDATE
</AuthBy>
</Handler>
Very grateful,
Janet
Hugh Irvine wrote:
>
> Hello Janet -
>
> I think you will have to define a second AuthBy SQL clause with just
> the AcctSQLStatement in it (and remove it from the existing one) and
> use a different Identifier of course. You will also have to add
> another AuthBy FILE, similar to the existing check-sql that uses the
> above AuthBy SQL. Finally you would chain the two AuthBy's in your
> accounting Handler.
>
> Thanks for sending the information below, but I am a bit confused by
> the AuthBy Identifiers not matching the Auth-Type check items - is
> this operating correctly?
>
> regards
>
> Hugh
>
> At 9:48 +1000 18/8/00, Janet N del Mundo wrote:
> >Hi Hugh,
> >
> >Yes, my AuthBy SQL is being called for authentication and then for
> >accounting. However, I do not use RewriteUsername. I use AuthBy SYSTEM
> >to check the user's UNIX password and AuthBy FILE to check his group and
> >suffix.
> >
> >-------------------------------
> ><AuthBy SQL>
> > Identifier GCCSQL
> >
> > DBSource ...
> > DBUsername ...
> > DBAuth ...
> >
> > AuthSelect select Password, Expiration, SimUse, \
> > IdleTime, SessionTime, StaticIP \
> > from USERS where IDENTIFIER = '%n' AND STATUS != 'C' \
> > AND SESSIONTIME > 0
> >
> > AuthColumnDef 1, Expiration, check
> > AuthColumnDef 2, Simultaneous-Use, check
> > AuthColumnDef 3, Idle-Timeout, reply
> > AuthColumnDef 4, Session-Timeout, reply
> > AuthColumnDef 5, Framed-IP-Address, reply
> >
> > AccountingTable ACCOUNTING
> > AccountingStopsOnly
> >
> > AcctColumnDef IDENTIFIER,User-Name
> > AcctColumnDef TIME_STAMP,Timestamp,formatted-date,'%m-%d-%Y
> >%H:%M:%S'
> > AcctColumnDef DURATION,Acct-Session-Time,integer
> > AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
> > AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
> > AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer
> > AcctColumnDef ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
> > AcctColumnDef ACCTSESSIONID,Acct-Session-Id
> > AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
> > AcctColumnDef NASIDENTIFIER,NAS-IP-Address
> > AcctColumnDef NASPORT,NAS-Port,integer
> > AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
> > AcctColumnDef CONNECTSPEED,Connect-Info
> > AcctColumnDef CONNECTSPEED,USR-Connect-Speed
> > AcctColumnDef CALLERID,Calling-Station-Id
> > AcctColumnDef POPID,Called-Station-Id
> > AcctSQLStatement update USERS set SESSIONTIME = (SESSIONTIME -
> >0%{Acct-Session-Time}) \
> >where IDENTIFIER = '%n'
> ></AuthBy>
> >
> ><Handler Request-Type = Accounting-Request, Class = GCCSQL>
> > AuthByPolicy ContinueAlways
> > AuthBy GCCSQL
> ></Handler>
> >
> ><Handler>
> > AuthByPolicy ContinueWhileAccept
> >
> > <AuthBy GROUP>
> > AuthByPolicy ContinueUntilAccept
> > AuthBy check-statics
> > AuthBy check-gcc
> > AuthBy check-default
> > </AuthBy>
> > <AuthBy FILE>
> > Filename %D/users-sql
> > AddToReplyIfNotExist Framed-Protocol = PPP,\
> > Service-Type = Framed-User,\
> > Framed-MTU = 1500,\
> > Framed-IP-Address = 255.255.255.254
> > </AuthBy>
> ></Handler>
> >-------------------------------
> >
> >users-gcc file:
> >-------------------------------
> >DEFAULT Auth-Type = System, Group = "gcc"
> > Filter-Id = "pcitest"
> >
> >DEFAULT Suffix = "+ppp", Auth-Type = System, Group = "gcc"
> > Class = GCCSQL,
> > Framed-Protocol = PPP,
> > Service-Type = Framed-User,
> > Framed-MTU = 1500
> >-------------------------------
> >
> >users-sql file:
> >-------------------------------
> >DEFAULT Suffix = "\+ppp", Auth-Type = SQL
> >
> >DEFAULT Suffix = "\+static", Auth-Type = SQL
> >
> >DEFAULT Suffix = "\+netcom", Auth-Type = SQL
> >
> >DEFAULT Suffix = "\+uunet", Auth-Type = SQL
> >
> >DEFAULT Auth-Type = SQL
> >-------------------------------
> >
> >How can I rewrite the username (no suffixes) for the AcctSQLStatement
> >without affecting the accounting table (username with suffixes)?
> >
> >Thanks,
> >Janet
> >
> >
> >Hugh Irvine wrote:
> >>
> >> Hello Janet -
> >>
> >> Is your AuthBy SQL clause being called from two different places? One
> >> for authentication with rewriteUsername and the other for accounting?
> >> Somehow the processing for the two packets is different.
> >>
> >> hth
> >>
> >> Hugh
> >>
> >> At 17:47 +1000 17/8/00, Janet N del Mundo wrote:
> >> >Hi,
> >> >
> >> >I was wondering how Radiator can capture a username without the suffix
> >> >when it's writing the AcctSQLStatement in accounting. I want the
> >> >AcctSQLStatement to update the SQL users table, but it's grabbing
> >> >username with the suffix ('mikesanlite+ppp' instead of just
> >> >'mikesanlite').
> >> >
> >> >It's writing to the accounting table correctly, with suffixes and
> >> >realms. However, when it does the AcctSQLStatement, it cannot update
> >> >the SQL users table because of the suffix.
> >> >
> >> >How can this be done? Any clues?
> >> >
> >> >Trace 4 log:
> >> >-------------------------------
> >> >
> >> >Thu Aug 17 16:47:37 2000: DEBUG: Query is: select Password, Expiration,
> >> >SimUse, IdleTime, SessionTime, StaticIP from USERS where IDENTIFIER =
> >> >'mikesanlite' AND STATUS != 'C' AND SESSIONTIME > 0
> >> >.
> >> >.
> >> >.
> >> >Thu Aug 17 16:47:38 2000: DEBUG: do query is: update USERS set
> >> >SESSIONTIME = (SESSIONTIME - 01000) where IDENTIFIER = 'mikesanlite+ppp'
> >> >-------------------------------
> >> >
> >> >Config file:
> >> >-------------------------------
> >> ><AuthBy SQL>
> >> > .
> >> > .
> >> > .
> >> > AuthSelect select Password, Expiration, SimUse, \
> >> > IdleTime, SessionTime, StaticIP \
> >> > from USERS where IDENTIFIER = '%n' AND STATUS != 'C' \
> >> > AND SESSIONTIME > 0
> >> >
> >> > AuthColumnDef 1, Expiration, check
> >> > AuthColumnDef 2, Simultaneous-Use, check
> >> > AuthColumnDef 3, Idle-Timeout, reply
> >> > AuthColumnDef 4, Session-Timeout, reply
> >> > AuthColumnDef 5, Framed-IP-Address, reply
> >> >
> >> > AccountingTable ACCOUNTING
> >> > AccountingStopsOnly
> >> >
> >> > .
> >> > .
> >> > .
> >> > AcctSQLStatement update USERS set SESSIONTIME = (SESSIONTIME -
> >> >0%{Acct-Session-Time}) \
> >> >where IDENTIFIER = '%n'
> >> >
> >> ></AuthBy>
> >> >-------------------------------
> >> >
> >> >
> >> >Thank you,
> >> >Janet
> >> >
> >> >===
> >> >Archive at http://www.starport.net/~radiator/
> >> >Announcements on [EMAIL PROTECTED]
> >> >To unsubscribe, email '[EMAIL PROTECTED]' with
> >> >'unsubscribe radiator' in the body of the message.
> >>
> >> --
> >> --
> >> Radiator: the most portable, flexible and configurable RADIUS server
> >> anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
> >> Platypus, Freeside, Interbiller, TACACS+, PAM, external, etc, etc.
> >> Available on Unix, Linux, FreeBSD, Windows 95/98/2000, NT, MacOS X.
> >>
> >> ===
> >> Archive at http://www.starport.net/~radiator/
> >> Announcements on [EMAIL PROTECTED]
> >> To unsubscribe, email '[EMAIL PROTECTED]' with
> >> 'unsubscribe radiator' in the body of the message.
>
> --
> --
> Radiator: the most portable, flexible and configurable RADIUS server
> anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
> Platypus, Freeside, Interbiller, TACACS+, PAM, external, etc, etc.
> Available on Unix, Linux, FreeBSD, Windows 95/98/2000, NT, MacOS X.
>
> ===
> Archive at http://www.starport.net/~radiator/
> Announcements on [EMAIL PROTECTED]
> To unsubscribe, email '[EMAIL PROTECTED]' with
> 'unsubscribe radiator' in the body of the message.
===
Archive at http://www.starport.net/~radiator/
Announcements on [EMAIL PROTECTED]
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
