Fwd: (RADIATOR) Bind Variables and Oracle
Mike McCauley
mikem at open.com.au
Thu Nov 22 17:00:53 CST 2001
On Thu, 22 Nov 2001 22:45, Paul wrote:
> Thanks for this Mike,
>
> Would it be possible (in the next release!) to have a hook that runs when a
> 'stop' record is received?
Attached is a new version of AuthINTERNAL.pm that supports the use of
optional hooks
to figure out how to reply to various types of request.
You can now define the following hooks:
RequestHook
AuthHook
AcctHook
AcctStartHook
AcctStopHook
AcctAliveHook
AcctOtherHook
OtherHook
There is also an example config file attached.
Perhaps you would like to test it and let me know how it goes?
Cheers.
>
> Thanks,
>
> Paul
>
> ----- Original Message -----
> From: "Mike McCauley" <mikem at open.com.au>
> To: "Paul" <paul at level9.net>
> Cc: <hugh at open.com.au>; <radiator at open.com.au>
> Sent: Wednesday, November 21, 2001 11:09 PM
> Subject: Re: Fwd: (RADIATOR) Bind Variables and Oracle
>
> > Hi Paul,
> >
> > Radiator has low level support in the API for using bound variables (see
>
> the
>
> > trailing arguments to Radius::SqlDb::do and prepareAndExecute), so you
> > can use bound variables in hooks etc.
> >
> > However, AuthBy SQL and SessionDatabase SQL do not support bound
> > variables with their various queries. That means that there is presently
> > no way to
>
> use
>
> > bound variables just by tweaking the AuthBy SQL or SessionDatabase SQL
> > parameters. It could only be done through a hook.
> >
> > Hope that helps.
> >
> > Cheers.
> >
> > On Thu, 22 Nov 2001 09:47, Hugh Irvine wrote:
> > > Mikey -
> > >
> > >
> > >
> > > ---------- Forwarded Message ----------
> > > Subject: (RADIATOR) Bind Variables and Oracle
> > > Date: Wed, 21 Nov 2001 17:20:59 -0000
> > > From: "Paul" <paul at level9.net>
> > > To: <radiator at open.com.au>
> > >
> > >
> > > Hi,
> > >
> > > Wondering if anyone has had any experience with using bind variables
>
> with
>
> > > Radiator / Oracle.
> > >
> > > I am running Radiator 2.18.2 and Oracle 8.0.5 (both on Solaris) using
>
> DBD
>
> > > 1.12 and DBI 1.20.
> > >
> > > We use a SessionDatabase SQL clause to perform IP vs. CLI writes to an
> > > external Oracle database. We have found that the query we are currently
> > > using is very inefficient as Oracle has to parse the whole query every
>
> time
>
> > > and would like to modify the query to use bind variables as per below:
> > >
> > > ### WITHOUT BIND VARIABLE
> > > $query = "delete from radius_clid_tbl where " .
> > > "IP_ADDR= '$framedipaddress' or " .
> > > "MSISDN= '$callingstation'";
> > > my $sth = $sess_handle->prepareAndExecute($query);
> > >
> > > ### USING BIND VARIABLE
> > > $query = "delete from radius_clid_tbl where " .
> > > "IP_ADDR= ? or " .
> > > "MSISDN= ? ";
> > > my $sth = $sess_handle->prepareAndExecute($query, $framedipaddress,
> > > $callingstation);
> > >
> > > The SessionDb SQL clause currently looks like this:
> > >
> > > <SessionDatabase SQL>
> > > DBSource dbi:Oracle:
> > > DBUsername user/password at sid
> > > AddQuery
> > > DeleteQuery delete from radius_clid_tbl \
> > > where (IP_ADDR='%{Framed-IP-Address}' \
> > > or IP_ADDR='%{Framed-Address}' \
> > > or MSISDN='%{Calling-Station-Id}')
> > > ClearNasQuery
> > > CountQuery
> > > </SessionDatabase SQL>
> > >
> > > The delete needs to be performed when an access-request is recvd and
>
> when a
>
> > > stop record is recvd, so I don't think I can use a hook. Also the DBA's
> > > involved won't let me use a stored procedure :-(
> > >
> > > If anyone has any experience with this, some help would be much
>
> appreciated
>
> > > :-)
> > >
> > > Thanks,
> > >
> > > Paul
> > >
> > >
> > > ___________________
> > >
> > > Paul O'Shea
> > > Level9 Networks
> > > ___________________
> > >
> > >
> > >
> > >
> > > ===
> > > 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.
> > >
> > > -------------------------------------------------------
> >
> > --
> > Mike McCauley mikem at open.com.au
> > Open System Consultants Pty. Ltd Unix, Perl, Motif, C++, WWW
> > 24 Bateman St Hampton, VIC 3188 Australia http://www.open.com.au
> > Phone +61 3 9598-0985 Fax +61 3 9598-0955
> >
> > Radiator: the most portable, flexible and configurable RADIUS server
> > anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
> > Platypus, Freeside, TACACS+, PAM, external, Active Directory etc etc
> > on Unix, Win95/8, 2000, NT, MacOS 9, MacOS X
> > ===
> > 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.
--
Mike McCauley mikem at open.com.au
Open System Consultants Pty. Ltd Unix, Perl, Motif, C++, WWW
24 Bateman St Hampton, VIC 3188 Australia http://www.open.com.au
Phone +61 3 9598-0985 Fax +61 3 9598-0955
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory etc etc
on Unix, Win95/8, 2000, NT, MacOS 9, MacOS X
-------------- next part --------------
# AuthINTERNAL.pm
#
# Object for handling requests in fixed, parameterised ways
#
# Author: Mike McCauley (mikem at open.com.au)
# Copyright (C) 1997 Open System Consultants
# $Id: AuthINTERNAL.pm,v 1.2 2001/11/19 07:18:10 mikem Exp mikem $
package Radius::AuthINTERNAL;
use Radius::AuthGeneric;
use strict;
use vars qw($VERSION @ISA);
BEGIN
{
@ISA = qw(Radius::AuthGeneric);
}
# Just a name for useful printing
my $class = 'AuthINTERNAL';
#####################################################################
# Do per-instance default initialization
# This is called by Configurable during Configurable::new before
# the config file is parsed. Its a good place initialize instance
# variables
# that might get overridden when the config file is parsed.
sub initialize
{
my ($self) = @_;
$self->SUPER::initialize;
$self->{DefaultResult} = 'ignore';
}
#####################################################################
# Override the keyword function in Configurable
sub keyword
{
my ($self, $file, $keyword, $value) = @_;
$self->match_keyword
($keyword, $value,
'DefaultResult' => 'string',
'AuthResult' => 'string',
'AcctResult' => 'string',
'AcctStartResult' => 'string',
'AcctStopResult' => 'string',
'AcctAliveResult' => 'string',
'AcctOtherResult' => 'string',
'RequestHook' => 'hook',
'AuthHook' => 'hook',
'AcctHook' => 'hook',
'AcctStartHook' => 'hook',
'AcctStopHook' => 'hook',
'AcctAliveHook' => 'hook',
'AcctOtherHook' => 'hook',
'OtherHook' => 'hook',
) && return 1;
return $self->SUPER::keyword($file, $keyword, $value);
}
#####################################################################
# Handle a request
# Return according to the type of request and config parameters
sub handle_request
{
my ($self, $p, $rp, $extra_checks) = @_;
$self->log($main::LOG_DEBUG, "Handling with $class: $self->{Identifier}", $p);
# Call the RequestHook, if there is one
if (defined $self->{RequestHook})
{
# We use an eval so an error in the hook wont
# kill us.
my @result;
eval{ @result = &{$self->{RequestHook}}($p, $rp, $extra_checks); };
&main::log($main::LOG_ERR, "Error in RequestHook(): $@", $p)
if $@;
return @result;
}
if ($p->code eq 'Access-Request')
{
# Call the AuthHook, if there is one
if (defined $self->{AuthHook})
{
# We use an eval so an error in the hook wont
# kill us.
my @result;
eval{ @result = &{$self->{AuthHook}}($p, $rp, $extra_checks); };
&main::log($main::LOG_ERR, "Error in AuthHook(): $@", $p)
if $@;
return @result;
}
return ($main::IGNORE, "Ignored due to IgnoreAuthentication")
if $self->{IgnoreAuthentication};
return $self->handleResult('AuthResult', $p, $rp)
if defined $self->{AuthResult};
}
elsif ($p->code eq 'Accounting-Request')
{
return ($main::IGNORE, "Ignored due to IgnoreAccounting")
if $self->{IgnoreAccounting};
# Call the AcctHook, if there is one
if (defined $self->{AcctHook})
{
# We use an eval so an error in the hook wont
# kill us.
my @result;
eval{ @result = &{$self->{AcctHook}}($p, $rp, $extra_checks); };
&main::log($main::LOG_ERR, "Error in AcctHook(): $@", $p)
if $@;
return @result;
}
# Handle any of the specialised accounting types
my $type;
foreach $type ('Start', 'Stop', 'Alive')
{
if (defined $self->{"Acct${type}Hook"})
{
# We use an eval so an error in the hook wont
# kill us.
my @result;
eval{ @result = &{$self->{"Acct${type}Hook"}}($p, $rp, $extra_checks); };
&main::log($main::LOG_ERR, "Error in Acct${type}Hook(): $@", $p)
if $@;
return @result;
}
return $self->handleResult("Acct${type}Result", $p, $rp)
if defined $self->{"Acct${type}Result"}
&& $p->getAttrByNum
($Radius::Radius::ACCT_STATUS_TYPE) eq $type;
}
# No specific result, try Other
# Call the AcctOtherHook, if there is one
if (defined $self->{AcctOtherHook})
{
# We use an eval so an error in the hook wont
# kill us.
my @result;
eval{ @result = &{$self->{AcctOtherHook}}($p, $rp, $extra_checks); };
&main::log($main::LOG_ERR, "Error in AcctOtherHook(): $@", $p)
if $@;
return @result;
}
return $self->handleResult('AcctResult', $p, $rp)
if defined $self->{AcctResult};
}
else
{
# Call the OtherHook, if there is one
if (defined $self->{OtherHook})
{
# We use an eval so an error in the hook wont
# kill us.
my @result;
eval{ @result = &{$self->{OtherHook}}($p, $rp, $extra_checks); };
&main::log($main::LOG_ERR, "Error in OtherHook(): $@", $p)
if $@;
return @result;
}
}
return $self->handleResult('DefaultResult', $p, $rp);
}
#####################################################################
# Convert a string of the type permitted by the config
# paramters to a return code.
sub stringToResult
{
my ($self, $s) = @_;
if ($s =~ /accept/i)
{
return $main::ACCEPT;
}
elsif ($s =~ /reject/i)
{
return $main::REJECT;
}
elsif ($s =~ /ignore/i)
{
return $main::IGNORE;
}
elsif ($s =~ /challenge/i)
{
return $main::CHALLENGE;
}
else
{
$self->log($main::LOG_WARNING, "Unknown result code string $s. Using IGNORE");
return $main::IGNORE;
}
}
#####################################################################
# Works out the reply depending ont he result code.
# Adjusts the reply message according at AddToReply etc
# if necessary
sub handleResult
{
my ($self, $resultparam, $p, $rp) = @_;
my $result = $self->stringToResult($self->{$resultparam});
if ($result == $main::ACCEPT)
{
$self->adjustReply($p, $rp);
}
return ($result, "Fixed by $resultparam");
}
1;
-------------- next part --------------
# internalhook.cfg
#
# Example Radiator configuration file.
# This very simple file will allow you to get started with
# AuthBy INTERNAL using hooks.
#
# AuthBy INTERNAL allows you to define in a fixed way how to respond
# to each kind of request handled by the AuthBy. And it also allows
# you to call a hook for each kind of request. If a hook is defined
# the result of the request depends on the returned value from the hook,
# otherwise it depends on the Result* parameters
# For each type of request you can specify whether to accept, reject,
# ignore or challenge the request. This can be useful for
# ignoring certain types of requests, or to adjust the behaviour
# of cascades of AuthBy clauses.
# The following hooks can be defined:
# RequestHook
# AuthHook
# AcctHook
# AcctStartHook
# AcctStopHook
# AcctAliveHook
# AcctOtherHook
# OtherHook
#
# All hooks are called with 3 arguments:
# $p the current request being handled
# $rp the reply being constructed in response to the request
# $extra_checks, a string of additional check items that might be
# required to be checked.
# Hooks are expected to reply with an array of 2 variables.
# The first variable in the array is the result of the hook. Allowable
# results are:
# $main::ACCEPT
# $main::REJECT
# $main::IGNORE
# $main::CHALLENGE
# $main::REJECT_IMMEDIATE
# (Note that for accounting requests, anything except ACCEPT means to
# to send no reply to the client.)
# The second variable in the array is an optional reason message that
# will be used to log the reason for rejections.
#
# You should consider this file to be a starting point only
# $Id: internal.cfg,v 1.1 2001/10/26 05:37:06 mikem Exp $
Foreground
LogStdout
LogDir .
DbDir .
# User a lower trace level in production systems:
Trace 4
# You will probably want to add other Clients to suit your site,
# one for each NAS you want to work with
<Client DEFAULT>
Secret mysecret
DupInterval 0
</Client>
# All the *Result parameters take one of the following keywords
# to determine how to reply to that type of request. The keywords
# are case insensitive, and may be embedded within any string
# accept
# reject
# ignore
# challenge
# anything else will IGNORE
# Caution: for accounting requests, reject is the same as ignore
<Realm DEFAULT>
<AuthBy INTERNAL>
# If RequestHook is defined, then the result of every request
# will be determined by the return from the hook
#RequestHook sub {return ($main::REJECT, "Rejected by RequestHook");}
# If AuthHook is defined, then the result of every
# Access-Request
# will be determined by the return from the hook
# This example ACCEPTS user mikem (irrespective of password
# and REJECTS everyone else
#AuthHook sub {return $_[0]->getUserName() eq 'mikem' ? ($main::ACCEPT) : ($main::REJECT, "Not mikem");}
# If AcctHook is defined, then the result of every
# Accouting-Request
# will be determined by the return from the hook
#AcctHook sub {return ($main::REJECT, "Rejected by AcctHook");}
# If AcctHook is defined, then the result of every
# Accounting-Request Start
# will be determined by the return from the hook
# You can also define AcctStartHook, AcctStopHook,
# AcctAliveHook, AcctOtherHook
#AcctStartHook sub {return ($main::REJECT, "Rejected by AcctStopHook");}
# DefaultResult will determine what is done with requests
# not handled by a hook
DefaultResult ACCEPT
# You can use all the standard AddToReply etc
# to adjust accepts
# AddToReply Reply-Message=test
</AuthBy>
</Realm>
More information about the radiator
mailing list