(RADIATOR) Can you use SQL if statements in radiator?

Hugh Irvine hugh at open.com.au
Fri Nov 28 17:25:53 CST 2003


Hello Craig -

In that case you should change the first AuthBy SQL to something like 
this:


	<AuthBy SQL>
		DBSource	dbi:ODBC:x
		DBUsername	xx
		DBAuth		xx

		FailureBackoffTime 30
		
		AuthSelect

		HandleAcctStatusTypes Start

		AcctSQLStatement \
				update Login \
					set Expiry_Date = getdate() + 7, First_Use = getdate() \
          				where Login_name = %U and \
          				First_Use is NULL

	</AuthBy SQL>


This will only run the update query when the accounting start is 
received for a successful login.

In regards to your question, I am not sure I understand what you are 
asking. But if you are talking about how you had configured this to 
begin with, the overall result would have been the result of the second 
AuthBy clause which was "Ignore" due to having both 
"IgnoreAuthentication" and "IgnoreAccounting" set. In general you want 
to have the last AuthBy clause in the sequence being the one that does 
the actual authentication, so the result from it is the overall result.


regards

Hugh


On 29/11/2003, at 6:38 AM, Craig Gittens wrote:

> Hey Hugh,
>
> The problem is that I don't want to change the expiry date on the 
> account
> until it has successfully logged in. Would changing the AuthBy 
> sequence do
> this even if the password was wrong? I don't understand why even 
> though the
> first AuthBy issued an Accept that the second prevents it from being 
> logged
> on correctly. Is this the designed behaviour?
>
> Craig.
>
> -----Original Message-----
> From: Hugh Irvine [mailto:hugh at open.com.au]
> Sent: Thursday, November 27, 2003 6:55 PM
> To: Craig Gittens
> Cc: Toomas Karner; Radiator
> Subject: Re: (RADIATOR) Can you use SQL if statements in radiator?
>
>
>
> Hello Craig -
>
> You should reverse the order of your AuthBy clauses and use an
> AuthByPolicy ContinueAlways.
>
> # define Realm
> # result of second AuthBy will be the overall result
>
> <Realm oneweek.sunbeach.net>
>
> 	#Will log Authentication failures to SQL table.
> 	AuthLog	AuthSQLLogger
>
> 	RewriteUsername s/^(.*)\\(.*)/$2\@$1/
> 	RewriteUsername s/^([^@]+).*/$1/
>
> 	#Continue to use AuthBy clauses if AccessAccept to get IP Address
> assigned
> 	AuthByPolicy ContinueAlways
>
> 	#Show Reject Reason From SQL Authenticate SP Query
> 	RejectHasReason
>
> 	<AuthBy SQL>
> 		DBSource	dbi:ODBC:x
> 		DBUsername	xx
> 		DBAuth		xx
>
> 		FailureBackoffTime 30
> 		NoDefault
> 		IgnoreAuthentication
> 		IgnoreAccounting
>
> 		AuthSQLStatement \
> 				update Login \
> 					set Expiry_Date = getdate() + 7, First_Use = getdate() \
>          				where Login_name = %U and \
>          				First_Use is NULL
>
>
> 	</AuthBy SQL>
>
> 	<AuthBy SQL>
> 		DBSource	dbi:ODBC:xx
> 		DBUsername	xx
> 		DBAuth		xx
>
> 		FailureBackoffTime 30
> 		NoDefault
> 		AddToReply Service-Type=Framed-User
> 		#DefaultSimultaneousUse	1
> 		CaseInsensitivePasswords
> 		RejectEmptyPassword
>
> 		# Accounting
> 		AccountingTable	CallAccounting
> 		....blah
>
>
> 		# Authentication query - calls function Authenticate.
> 		AuthSelect \
> 			select \
> 				Blah blah blah
>
> 		AuthColumnDef 0,User-Password,check
> 		AuthColumnDef 1,GENERIC,check
> 		AuthColumnDef 2,GENERIC,reply
>
> 	</AuthBy SQL>
>
> </Realm oneweek.sunbeach.net>
>
>
> regards
>
> Hugh
>
>
> On 28/11/2003, at 8:55 AM, Craig Gittens wrote:
>
>> Ok, thanks to Toomas I have come up with this solution but it doesn't
>> work
>> unless I comment out the second AuthBy...it does do an ACCEPT for the
>> first
>> AuthBy but doesn't work for some reason unless I comment out the 
>> second
>> AuthBy. Log below. It doesn't send a reply unless I comment out the
>> second
>> AuthBy.
>>
>> Thanks for your help guys.
>>
>> Craig.
>>
>> <Realm oneweek.sunbeach.net>
>>
>> 	#Will log Authentication failures to SQL table.
>> 	AuthLog	AuthSQLLogger
>>
>> 	RewriteUsername s/^(.*)\\(.*)/$2\@$1/
>> 	RewriteUsername s/^([^@]+).*/$1/
>>
>> 	#Continue to use AuthBy clauses if AccessAccept to get IP Address
>> assigned
>> 	AuthByPolicy ContinueUntilReject
>> 	#Show Reject Reason From SQL Authenticate SP Query
>> 	RejectHasReason
>>
>> 	<AuthBy SQL>
>> 		DBSource	dbi:ODBC:xx
>> 		DBUsername	xx
>> 		DBAuth		xx
>>
>> 		FailureBackoffTime 30
>> 		NoDefault
>> 		AddToReply Service-Type=Framed-User
>> 		#DefaultSimultaneousUse	1
>> 		CaseInsensitivePasswords
>> 		RejectEmptyPassword
>>
>> 		# Accounting
>> 		AccountingTable	CallAccounting
>> 		....blah
>>
>>
>> 		# Authentication query - calls function Authenticate.
>> 		AuthSelect \
>> 			select \
>> 				Blah blah blah
>>
>> 		AuthColumnDef 0,User-Password,check
>> 		AuthColumnDef 1,GENERIC,check
>> 		AuthColumnDef 2,GENERIC,reply
>>
>> 	</AuthBy SQL>
>>
>>
>> 	<AuthBy SQL>
>> 		DBSource	dbi:ODBC:x
>> 		DBUsername	xx
>> 		DBAuth		xx
>>
>> 		FailureBackoffTime 30
>> 		NoDefault
>> 		IgnoreAuthentication
>> 		IgnoreAccounting
>>
>> 		AuthSQLStatement \
>> 				update Login \
>> 					set Expiry_Date = getdate() + 7, First_Use = getdate() \
>>         				where Login_name = %U and \
>>         				First_Use is NULL
>>
>>
>> 	</AuthBy SQL>
>>
>> </Realm oneweek.sunbeach.net>
>>
>> Thu Nov 27 17:36:01 2003: DEBUG: Packet dump:
>> *** Received from 196.3.210.94 port 2048 ....
>> Code:       Access-Request
>> Identifier: 209
>> Authentic:  <23>_$<28>T<148>9<194><26>?<206><229>)s<207>%
>> Attributes:
>> 	User-Password =
>> "n)|<220><137>?<2><161><185><241><152><223><29>/<239><141>"
>> 	NAS-Identifier = "5"
>> 	User-Name = "sunweek0 at oneweek.sunbeach.net"
>> 	Acct-Session-Id = "000032E9"
>> 	Called-Station-Id = "2929700"
>> 	Calling-Station-Id = "2462280430"
>> 	NAS-Port = 1288
>> 	NAS-Port-Type = Async
>> 	Framed-Protocol = PPP
>> 	Service-Type = Framed-User
>>
>> Thu Nov 27 17:36:01 2003: DEBUG: Handling request with Handler
>> 'Realm=oneweek.sunbeach.net'
>> Thu Nov 27 17:36:01 2003: DEBUG: Rewrote user name to
>> sunweek0 at oneweek.sunbeach.net
>> Thu Nov 27 17:36:01 2003: DEBUG: Rewrote user name to sunweek0
>> Thu Nov 27 17:36:01 2003: DEBUG:  Deleting session for
>> sunweek0 at oneweek.sunbeach.net, 196.3.210.94, 1288
>> Thu Nov 27 17:36:01 2003: DEBUG: do query is: 'delete from RADONLINE
>> where
>> NASIDENTIFIER = '196.3.210.94' and NASPORT = 1288':
>>
>> Thu Nov 27 17:36:01 2003: DEBUG: Handling with Radius::AuthSQL
>> Thu Nov 27 17:36:01 2003: DEBUG: Handling with Radius::AuthSQL:
>> Thu Nov 27 17:36:01 2003: DEBUG: Query is: 'select LoginPassword,
>> CheckAttr,
>> ReplyAttr from Authenticate('sunweek0', '2462280430', '11/27/2003
>> 17:36:01',
>> 'Async')':
>>
>> Thu Nov 27 17:36:01 2003: DEBUG: Radius::AuthSQL looks for match with
>> sunweek0
>> Thu Nov 27 17:36:01 2003: DEBUG: Radius::AuthSQL ACCEPT:
>> Thu Nov 27 17:36:01 2003: DEBUG: Handling with Radius::AuthSQL
>>
>> Thu Nov 27 17:36:03 2003: DEBUG: Packet dump:
>> *** Received from 196.3.210.94 port 2048 ....
>> Code:       Access-Request
>> Identifier: 209
>> Authentic:  <23>_$<28>T<148>9<194><26>?<206><229>)s<207>%
>> Attributes:
>> 	User-Password =
>> "n)|<220><137>?<2><161><185><241><152><223><29>/<239><141>"
>> 	NAS-Identifier = "5"
>> 	User-Name = "sunweek0 at oneweek.sunbeach.net"
>> 	Acct-Session-Id = "000032E9"
>> 	Called-Station-Id = "2929700"
>> 	Calling-Station-Id = "2462280430"
>> 	NAS-Port = 1288
>> 	NAS-Port-Type = Async
>> 	Framed-Protocol = PPP
>> 	Service-Type = Framed-User
>>
>> Thu Nov 27 17:36:03 2003: INFO: Duplicate request id 209 received from
>> 196.3.210.94(2048): ignored
>>
>>
>>
>> ===
>> Archive at http://www.open.com.au/archives/radiator/
>> Announcements on radiator-announce at open.com.au
>> To unsubscribe, email 'majordomo at open.com.au' with
>> 'unsubscribe radiator' in the body of the message.
>>
>>
>
> 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, MacOS X.
> -
> Nets: internetwork inventory and management - graphical, extensible,
> flexible with hardware, software, platform and database independence.
> -
> CATool: Private Certificate Authority for Unix and Unix-like systems.
>
>
>
>

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, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.

===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.


More information about the radiator mailing list