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

Craig Gittens cgittens at sunbeach.net
Fri Nov 28 13:38:05 CST 2003


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.




===
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