(RADIATOR) Question on flat ISDN-users file -> CDB.
Hugh Irvine
hugh at open.com.au
Thu Jun 7 18:46:38 CDT 2001
Hello Jon -
My suggestion would be to configure Radiator to access the Oracle database
directly. This is very simple to set up with an AuthBy SQL clause. You will
need to set up DBI and DBD-Oracle, together with the Oracle client libraries
on the Radiator host, then configure the AuthBy SQL clause with an AuthSelect
statement for authentication and an AccountingTable and AcctColumnDef's for
accounting.
Here is an example:
<AuthBy SQL>
Identifier CheckOracleDatabase
# DBSource, DBUsername and DBAuth are
# passed to DBI->connect
# something like this:
# DBI->connect(DBSource, DBUsername and DBAuth).
# DBSource should be a 'new-style' database
# specification
# something like "dbi:drivername:..."
# The exact meaning of these variables depends
# on the Perl DBD
# driver you wish to use.See the
# documentation for DBI and DBD for more information
# on how to set them.
# For Oracle use something like this
# You can specify more than 1 set, and Radiator
# will try to connect to the first one, and if
# that fails, the second one etc.
#DBSource dbi:Oracle:(DESCRIPTION=\
#(ADDRESS=(PROTOCOL=TCP)(HOST=oscar.open.com.au)\
#(PORT=1521))(CONNECT_DATA=(SID=osc)))
DBSource dbi:Oracle:osc
DBUsername system
DBAuth manager
# AuthSelect defines an SQL select statement that will
# return the password, check items and reply items for
# the user. You can use the special macros such as
# %n and others to specify the username to select.
# The first column returned is exptected to be
# the password
# the second is the check items (if any) and the
# third is the reply items if any. (You can change this
# behaviour with AuthColumnDef
# Defaults to select PASSWORD
# from SUBSCRIBERS
# where USERNAME='%n'
# Which does not return any check or reply items
# You can make arbitrarily complicated
# SQL statements so that
# you will only authenticate users whose
# account status is
# OK or who have not exceeded their download limit etc.
# If you define this to be an empty string,
# no authentication will be done
# AuthSelect select PASSWORD from SUBSCRIBERS where USERNAME=%0
# If you want to use check and reply items from your
# database too, you will need something like:
AuthSelect select PASSWORD, CHECKATTR, REPLYATTR \
from SUBSCRIBERS where USERNAME=%0
# AccountingTable is the name of the table
# that is used to
# store accounting information.
# Defaults to 'ACCOUNTING'
# If you define this to be an empty string,
# no accounting info will be stored. You can use
# special macros: table names based on the currnet year
# and month might be very useful.
AccountingTable ACCOUNTING
# AcctColumnDef is used to define which attributes
# in accounting requests are to be inserted
# into AccountingTable, and it also specifies
# which column they are to be inserted into
# The general form is
# AcctColumnDef COLUMN,Attribute[,type][,format]
# You can have 0 or more of these. If there are none,
# then the accounting table will not be updated.
# If type is "integer", the insertion will be done
# as an integer data type, otherwise as a string.
# If type is integer-date, the integer will be converted
# from Unix seconds to a SQL date
# If type is "formatted-date", then the format will
# be used as a format string for Date::Format
# You must install the TimeDate package from CPAN for
# this to work
#
# The attribute Timestamp is always available,
# and represents the time the packet was received,
# adjusted by Acct-Delay-Time.
# One day Radiator will be able to deduce this from
# the table schema.
# The optional format arg is a sprintf-style format
# string that will be used to format the value. %s
# in the format will be replaced by the value of
# interest
AcctColumnDef USERNAME,User-Name
AcctColumnDef TIME_STAMP,Timestamp,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 ACCTSESSIONTIME,Acct-Session-Time,integer
AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
AcctColumnDef NASIDENTIFIER,NAS-Identifier
AcctColumnDef NASIDENTIFIER,NAS-IP-Address
AcctColumnDef NASPORT,NAS-Port,integer
AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
# Heres how you can convert Timestamps into a format
# that Oracle likes
AcctColumnDef TIME_STAMP,Timestamp, \
formatted-date,to_date \
('%e %m %Y %H:%M:%S', 'DD MM YYYY HH24:MI:SS')
# As an alternative to AcctColumnDef, you can specify
# any number of arbitrary SQL statements to execute
# for every accounting request with AcctSQLStatement
# You can use special formatting macros (the %{attribute}
# ones are probably the most useful)
# AcctSQLStatement DELETE from ONLINE where
SessionID='%{Acct-Session-Id}'
</AuthBy>
Have a look at section 6.26 in the Radiator 2.18.1 reference manual and the
examples in the "goodies" directory of the distribution.
regards
Hugh
On Friday 08 June 2001 01:51, Jon Nistor wrote:
> Hi there, I'm new to the list =) just to start that off. I've checked
> the archives as well, and didn't find anything, so hopefully this won't be
> redundant, if so, sorry :/
>
> I'm trying to convert out ISDN users file, which is automatically
> generated from values in oracle that are pulled down into a flat file that
> looks like this:
>
> #----------------------------------------------------------------
> # NOTE: This file is autogenerated from a database.
> # Any manual changes will be lost when the database is updated
> # and this file is refreshed!!!
> #----------------------------------------------------------------
>
> # 1197538
> aaacredit: Encrypted-Password = "HSaooI.RbBbxU" #
> Service-Type = Framed,
> Framed-Protocol = PPP,
> Framed-IP-Address = x.x.x.x,
> Framed-Route = "x.x.x.x/30 x.x.x.x 1",
> Framed-IP-Netmask = 255.255.255.255,
> Port-Limit = 2,
> Idle-Timeout = 1234342
>
> # 1197843
> abmtools: Encrypted-Password = "Dt4DbQJyZ7PUI" #
> Service-Type = Framed,
> Framed-Protocol = PPP,
> Framed-IP-Address = x.x.x.x,
> Framed-Route = "x.x.x.x/29 x.x.x.x 1",
> Framed-IP-Netmask = 255.255.255.255,
> Port-Limit = 2,
> Idle-Timeout = 120
>
>
> ---------------
>
> And I'd like to covert it to something like:
>
> +9,252:aaacredit->Encrypted-Password = "HSaooI.RbBbxU"
> Service-Type = Framed,
> Framed-Protocol = PPP,
> Framed-IP-Address = x.x.x.x,
> Framed-Route = "x.x.x.x/30 x.x.x.x 1",
> Framed-IP-Netmask = 255.255.255.255,
> Port-Limit = 2,
> Idle-Timeout = 1234342
> +8,251:abmtools->Encrypted-Password = "Dt4DbQJyZ7PUI"
> Service-Type = Framed,
> Framed-Protocol = PPP,
> Framed-IP-Address = x.x.x.x,
> Framed-Route = "x.x.x.x/29 x.x.x.x 1",
> Framed-IP-Netmask = 255.255.255.255,
> Port-Limit = 2,
> Idle-Timeout = 120
>
> .........
>
> I have a perl script that looks like this:
> #!/opt/perl5/bin/perl
>
> $tmpusers = @ARGV[0];
>
> if (! @ARGV) {
> print "Might wanna specify a filename\n";
> }
>
> open(FILE,"$tmpusers") || die "Couldn't open $tmpusers\n";;
> while (<FILE>) {
> if (/^(.+):\s+.+Password = \"(.+)\"/) {
> $block = "+" . length($1) .
> ",GHETTO_LENGTH:$1->Encrypted-Password = \"$2\"\n"; } elsif (/^$/) {
> if ($block =~ /->(.*)$/s) {
> $length = length($1);
> $block =~ s/GHETTO_LENGTH/$length/;
> print $block;
> }
> } elsif (!/^#/){
> $block .= $_;
> }
> }
> print "\n";
> close(FILE);
>
> ........
>
> now here's the question:
>
> I wanna use CDB_File to make the file directly, but I can't get
> $cdb->insert to work properly on the value's. (not a perl guy =/). Does
> anyone have an idea of an easier way to conver it or any place with goodie
> scripts? :)
>
> Thanks,
--
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.
===
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