--- Forwarded mail from [EMAIL PROTECTED]
Date: Thu, 12 Apr 2001 06:40:29 +1000 (EST)
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: BOUNCE [EMAIL PROTECTED]: Non-member submission from ["Wasim
Khan" <[EMAIL PROTECTED]>]
>From mikem Thu Apr 12 06:40:24 2001
Received: by oscar.open.com.au (8.9.0/8.9.0) id GAA18753
for [EMAIL PROTECTED]; Thu, 12 Apr 2001 06:40:24 +1000 (EST)
>Received: from ns2.eworld.net.pk ([64.86.172.45]) by perki.connect.com.au with
ESMTP id GAA20959
(8.8.8/IDA-1.7 for <[EMAIL PROTECTED]>); Thu, 12 Apr 2001 06:05:34 +1000
(EST)
Received: from ns2.eworld.net.pk ([64.86.172.45]) by perki.connect.com.au with
ESMTP id GAA20959
(8.8.8/IDA-1.7 for <[EMAIL PROTECTED]>); Thu, 12 Apr 2001 06:05:34 +1000
(EST)
Received: from eworld.net.pk ([127.0.0.1]) by ns2.eworld.net.pk
(Netscape Messaging Server 4.1) with ESMTP id GBN94E00.909 for
<[EMAIL PROTECTED]>; Thu, 12 Apr 2001 01:05:02 +0500
From: "Wasim Khan" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Message-ID: <[EMAIL PROTECTED]>
X-Mailer: Netscape Webmail
MIME-Version: 1.0
Content-Language: en
Subject: Help Required
X-Accept-Language: en
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Date: Thu, 12 Apr 2001 01:05:02 +0500
Content-Type: text/plain; charset=us-ascii
Hello!
I'm immplimenting CLI (caller id) varification ... so the customer
those want CLI on there account's can only get connect'd by there given
number, mean while the user don't want CLI option ennabled on their
account's, can get connect'd with any phone number ...
I'm sending u two files, one is for RADIUS (RADIATOR) auth. and the
second one is for view's in PLAT ... in my view they r ok, but it's
not work'n ... pls take a look at it and see where the error is as it's
not working ...
-------------------------------------------------
Original File...
# AuthEMERALD.pm
#
# Object for handling Authentication and accounting from Emerald
# (http://www.emerald.iea.com)
# This is a subclass of SQL that can also get radius attributes
# from Emeralds special attribute tables.
#
# We only need to override the findUser function so that it
# extracts reply items from RadConfifgs and RadATConfigs
#
# Author: Mike McCauley ([EMAIL PROTECTED])
# Copyright (C) 1997 Open System Consultants
# $Id: AuthEMERALD.pm,v 1.11 2000/11/21 01:36:22 mikem Exp $
package Radius::AuthEMERALD;
use Radius::AuthSQL;
use strict;
use vars qw($VERSION @ISA);
BEGIN
{
@ISA = qw(Radius::AuthSQL);
}
#####################################################################
# Do per-instance default initialization
# This is called by Configurabel during Configurable::new before
# the config file is parsed. Its a good place initalze
# instance variables
# that might get overridden when the config file is parsed.
sub initialize
{
my ($self) = @_;
$self->Radius::AuthSQL::initialize;
$self->{AuthSelect} = ' ';
}
#####################################################################
# Override the keyword function in Configurable
sub keyword
{
my ($self, $file, $keyword, $value) = @_;
if ($keyword eq 'TimeBanking')
{
$self->{TimeBanking}++;
}
# Modification by Andrew Ruthven - 1999/02/27
# Only add the contents of RadATConfigs if AddATDefaults is
defined
# in the configuration file.
elsif ($keyword eq 'AddATDefaults')
{
$self->{AddATDefaults}++;
}
else
{
return $self->SUPER::keyword($file, $keyword, $value);
}
return 1;
}
#####################################################################
# Find a the named user by looking in the database, and constructing
# User object if we found the named user
# This is tailored exactly to Emerald's user database
sub findUser
{
my ($self, $name, $p) = @_;
# (Re)-connect to the database if necessary,
return (undef, 1) unless $self->reconnect;
my $q = "select DateAdd(Day, ma.extension+ma.overdue, maExpireDate),
DateAdd(Day, sa.extension, saExpireDate), sa.AccountID, sa.AccountType,
sa.password, sa.login, sa.shell, sa.TimeLeft $self->{AuthSelect}
from masteraccounts ma, subaccounts sa
where (sa.login = '$name' or sa.shell = '$name')
and ma.customerid = sa.customerid
and sa.active <> 0 and ma.active <> 0";
my $sth = $self->prepareAndExecute($q);
return undef unless $sth;
my $user;
my ($date1, $date2, $account_id, $account_type,
$password, $login, $shell, $timeleft, @extras);
if (($date1, $date2, $account_id, $account_type,
$password, $login, $shell, $timeleft, @extras)
= $self->getOneRow($sth))
{
$self->log($main::LOG_DEBUG,
"Select results: $date1, $date2, $account_id,
$account_type, $password, $login, $shell, $timeleft, @extras");
$user = new Radius::User $name;
$account_id = int $account_id; # MSSQL 7 makes it numeric
# Add a *-Password check item unless the correct password
# was NULL in the database, This means that if
# the password column for a user is NULL,
# then any password is accepted for that user.
if (defined $password)
{
$user->get_check->add_attr
(defined $self->{EncryptedPassword} ?
'Encrypted-Password' : 'User-Password', $password);
}
$user->get_check->add_attr('Expiration', $date1)
if defined $date1;
$user->get_check->add_attr('Expiration', $date2)
if defined $date2;
if (defined $timeleft && $self->{TimeBanking})
{
if ($timeleft > 0)
{
$user->get_reply->add_attr('Session-Timeout',
$timeleft * 60);
}
else
{
$self->log($main::LOG_DEBUG,
"User $name has no more time left");
return undef;
}
}
# If the config has defined how to handle the columns
# in the AuthSelect statement with AuthColumnDef, use
# that to extraxt check and reply items from @extras
$self->getAuthColumns($user, @extras)
if defined $self->{AuthColumnDef};
my ($attr_id, $vendor_id, $vendor_type,
$attr_name, $attr_data, $attr_value, $attr_type,
$radcheck, $attr_list);
# Now get any radius attributes from the Emerald database for
this
# account ID and account type
$q = "select ra.RadAttributeID, ra.RadVendorID,
ra.RadVendorType,
Data, Value, Type, RadCheck
from RadConfigs rc, RadAttributes ra
where ra.RadAttributeID = rc.RadAttributeID
and ra.RadVendorID = rc.RadVendorID
and ra.RadVendorType = rc.RadVendorType
and rc.AccountID=$account_id";
$sth = $self->prepareAndExecute($q);
return undef unless $sth;
my $got_configs; # Dont get AT Configs if there are per-user
# Modification by Andrew Ruthven - 1999/02/20
# This will allow us to specify default settings and then have
# user specific settings over ride them.
my @set_configs;
while (($attr_id, $vendor_id, $vendor_type, $attr_data,
$attr_value, $attr_type, $radcheck)
= $sth->fetchrow())
{
# Modification by Andrew Ruthven - 1999/02/20
# Added the test on 1999/02/27
if (defined($self->{AddATDefaults}))
{
$set_configs[$attr_id]++;
}
else
{
$got_configs++;
}
# Dictionaries may not agree, so we use the
# attribute number to find the name
# Some DBs return attr_id as a float!
$attr_id = int $attr_id;
my @attrDetails;
if ($vendor_id && $attr_id == 26)
{
@attrDetails = $main::dictionary->attrByNum
($vendor_type, $vendor_id);
}
else
{
@attrDetails = $main::dictionary->attrByNum
($attr_id);
}
$attr_list = $radcheck
? $user->get_check()
: $user->get_reply();
if ($attr_type == 1) # integer
{
$attr_list->add_attr($attrDetails[0],
int $attr_value);
}
else
{
$attr_list->add_attr($attrDetails[0],
$attr_data);
}
}
$sth->finish;
# We only get the Account Type configs if there were no per
# user configs, or if AddATDefaults is set
if (!$got_configs)
{
$q = "select ra.RadAttributeID, ra.RadVendorID,
ra.RadVendorType,
Data, Value, Type, RadCheck
from RadATConfigs rc, RadAttributes ra
where ra.RadAttributeID = rc.RadAttributeID
and ra.RadVendorID = rc.RadVendorID
and ra.RadVendorType = rc.RadVendorType
and rc.AccountType='$account_type'";
$sth = $self->prepareAndExecute($q);
return undef unless $sth;
while (($attr_id, $vendor_id, $vendor_type, $attr_data,
$attr_value, $attr_type, $radcheck)
= $sth->fetchrow())
{
# Modification by Andrew Ruthven - 1999/02/20
next if $set_configs[$attr_id];
# Dictionaries may not agree, so we use the
# attribute number to find the name
# Some DBs return attr_id as a float!
$attr_id = int $attr_id;
my @attrDetails;
if ($vendor_id && $attr_id == 26)
{
@attrDetails = $main::dictionary->attrByNum
($vendor_type, $vendor_id);
}
else
{
@attrDetails = $main::dictionary->attrByNum
($attr_id);
}
$attr_list = $radcheck
? $user->get_check()
: $user->get_reply();
if ($attr_type == 1) # integer
{
$attr_list->add_attr($attrDetails[0],
int $attr_value);
}
else
{
$attr_list->add_attr($attrDetails[0],
$attr_data);
}
}
$sth->finish;
}
}
return $user;
}
1;
----------------------------
This One Made By Me
# AuthEMERALD.pm
#
# Object for handling Authentication and accounting from Emerald
# (http://www.emerald.iea.com)
# This is a subclass of SQL that can also get radius attributes
# from Emeralds special attribute tables.
#
# We only need to override the findUser function so that it
# extracts reply items from RadConfifgs and RadATConfigs
#
# Author: Mike McCauley ([EMAIL PROTECTED])
# Copyright (C) 1997 Open System Consultants
# $Id: AuthEMERALD.pm,v 1.11 2000/11/21 01:36:22 mikem Exp $
package Radius::AuthEMERALD;
use Radius::AuthSQL;
use strict;
use vars qw($VERSION @ISA);
BEGIN
{
@ISA = qw(Radius::AuthSQL);
}
#####################################################################
# Do per-instance default initialization
# This is called by Configurabel during Configurable::new before
# the config file is parsed. Its a good place initalze
# instance variables
# that might get overridden when the config file is parsed.
sub initialize
{
my ($self) = @_;
$self->Radius::AuthSQL::initialize;
$self->{AuthSelect} = ' ';
}
#####################################################################
# Override the keyword function in Configurable
sub keyword
{
my ($self, $file, $keyword, $value) = @_;
if ($keyword eq 'TimeBanking')
{
$self->{TimeBanking}++;
}
# Modification by Andrew Ruthven - 1999/02/27
# Only add the contents of RadATConfigs if AddATDefaults is
defined
# in the configuration file.
elsif ($keyword eq 'AddATDefaults')
{
$self->{AddATDefaults}++;
}
else
{
return $self->SUPER::keyword($file, $keyword, $value);
}
return 1;
}
#####################################################################
# Find a the named user by looking in the database, and constructing
# User object if we found the named user
# This is tailored exactly to Emerald's user database
sub findUser
{
my ($self, $name, $p) = @_;
# (Re)-connect to the database if necessary,
return (undef, 1) unless $self->reconnect;
my $q = "select DateAdd(Day, ma.extension+ma.overdue, maExpireDate),
DateAdd(Day, sa.extension, saExpireDate), sa.AccountID, sa.AccountType,
sa.password, sa.login,
sa.shell,sa.phone,sa.cli,calls.callerid,sa.TimeLeft $self->{AuthSelect}
from masteraccounts ma, subaccounts15 sa,calls
where (sa.login = '$name' or sa.shell = '$name')
and ma.customerid = sa.customerid
and sa.active <> 0 and ma.active <> 0 and sa.phone=calls.callerid or
cli=4";
my $sth = $self->prepareAndExecute($q);
return undef unless $sth;
my $user;
my ($date1, $date2, $account_id, $account_type,
$password, $login, $shell,$phone,$cli,$callerid, $timeleft,
@extras);
if (($date1, $date2, $account_id, $account_type,
$password, $login, $shell, $timeleft, @extras)
= $self->getOneRow($sth))
{
$self->log($main::LOG_DEBUG,
"Select results: $date1, $date2, $account_id,
$account_type, $password, $login, $shell, $timeleft, @extras");
$user = new Radius::User $name;
$account_id = int $account_id; # MSSQL 7 makes it numeric
# Add a *-Password check item unless the correct password
# was NULL in the database, This means that if
# the password column for a user is NULL,
# then any password is accepted for that user.
if (defined $password)
{
$user->get_check->add_attr
(defined $self->{EncryptedPassword} ?
'Encrypted-Password' : 'User-Password', $password);
}
$user->get_check->add_attr('Expiration', $date1)
if defined $date1;
$user->get_check->add_attr('Expiration', $date2)
if defined $date2;
if (defined $timeleft && $self->{TimeBanking})
{
if ($timeleft > 0)
{
$user->get_reply->add_attr('Session-Timeout',
$timeleft * 60);
}
else
{
$self->log($main::LOG_DEBUG,
"User $name has no more time left");
return undef;
}
}
# If the config has defined how to handle the columns
# in the AuthSelect statement with AuthColumnDef, use
# that to extraxt check and reply items from @extras
$self->getAuthColumns($user, @extras)
if defined $self->{AuthColumnDef};
my ($attr_id, $vendor_id, $vendor_type,
$attr_name, $attr_data, $attr_value, $attr_type,
$radcheck, $attr_list);
# Now get any radius attributes from the Emerald database for
this
# account ID and account type
$q = "select ra.RadAttributeID, ra.RadVendorID,
ra.RadVendorType,
Data, Value, Type, RadCheck
from RadConfigs rc, RadAttributes ra
where ra.RadAttributeID = rc.RadAttributeID
and ra.RadVendorID = rc.RadVendorID
and ra.RadVendorType = rc.RadVendorType
and rc.AccountID=$account_id";
$sth = $self->prepareAndExecute($q);
return undef unless $sth;
my $got_configs; # Dont get AT Configs if there are per-user
# Modification by Andrew Ruthven - 1999/02/20
# This will allow us to specify default settings and then have
# user specific settings over ride them.
my @set_configs;
while (($attr_id, $vendor_id, $vendor_type, $attr_data,
$attr_value, $attr_type, $radcheck)
= $sth->fetchrow())
{
# Modification by Andrew Ruthven - 1999/02/20
# Added the test on 1999/02/27
if (defined($self->{AddATDefaults}))
{
$set_configs[$attr_id]++;
}
else
{
$got_configs++;
}
# Dictionaries may not agree, so we use the
# attribute number to find the name
# Some DBs return attr_id as a float!
$attr_id = int $attr_id;
my @attrDetails;
if ($vendor_id && $attr_id == 26)
{
@attrDetails = $main::dictionary->attrByNum
($vendor_type, $vendor_id);
}
else
{
@attrDetails = $main::dictionary->attrByNum
($attr_id);
}
$attr_list = $radcheck
? $user->get_check()
: $user->get_reply();
if ($attr_type == 1) # integer
{
$attr_list->add_attr($attrDetails[0],
int $attr_value);
}
else
{
$attr_list->add_attr($attrDetails[0],
$attr_data);
}
}
$sth->finish;
# We only get the Account Type configs if there were no per
# user configs, or if AddATDefaults is set
if (!$got_configs)
{
$q = "select ra.RadAttributeID, ra.RadVendorID,
ra.RadVendorType,
Data, Value, Type, RadCheck
from RadATConfigs rc, RadAttributes ra
where ra.RadAttributeID = rc.RadAttributeID
and ra.RadVendorID = rc.RadVendorID
and ra.RadVendorType = rc.RadVendorType
and rc.AccountType='$account_type'";
$sth = $self->prepareAndExecute($q);
return undef unless $sth;
while (($attr_id, $vendor_id, $vendor_type, $attr_data,
$attr_value, $attr_type, $radcheck)
= $sth->fetchrow())
{
# Modification by Andrew Ruthven - 1999/02/20
next if $set_configs[$attr_id];
# Dictionaries may not agree, so we use the
# attribute number to find the name
# Some DBs return attr_id as a float!
$attr_id = int $attr_id;
my @attrDetails;
if ($vendor_id && $attr_id == 26)
{
@attrDetails = $main::dictionary->attrByNum
($vendor_type, $vendor_id);
}
else
{
@attrDetails = $main::dictionary->attrByNum
($attr_id);
}
$attr_list = $radcheck
? $user->get_check()
: $user->get_reply();
if ($attr_type == 1) # integer
{
$attr_list->add_attr($attrDetails[0],
int $attr_value);
}
else
{
$attr_list->add_attr($attrDetails[0],
$attr_data);
}
}
$sth->finish;
}
}
return $user;
}
1;
----------------------------------------------------------------
Original View
CREATE VIEW SubAccounts
(CustomerID,AccountID,extension,saExpireDate,Active,Login,Shell,Password
,AccountType,LoginLimit,timeleft,Email,LastModifyDate,LastUsed) as
select c.id, c.id, 0,dateadd(year,1,getdate()),1,c.username,'',
c.password,c.accttype,c.logons,
case when c.blockuser = 'y' then c.timeleft when c.blockuser = 'g'
then b.timeleft else null End ,
c.email,GETDATE(),GETDATE()
from customer c, customer b
where c.guarantor *= b.id and c.active='Y' and b.active='Y'
and (c.blockuser is NULL or c.blockuser = 'N'
or (c.blockuser='y' and c.timeleft > 0 and (getdate() <
c.expire_date or c.expire_date is null))
or (c.blockuser='g' and (select timeleft from customer where
active='Y' and id=c.guarantor) > 0) and (getdate() < c.expire_date or
c.expire_date is null))
------------------------------------------
This Made View By Me
CREATE VIEW SubAccounts15
(Phone,Cli,CustomerID,AccountID,extension,saExpireDate,Active,Login,Shel
l,Password,AccountType,LoginLimit,timeleft,Email,LastModifyDate,LastUsed
) as
select c.phone,c.cli,c.id, c.id, 0,dateadd(year,1,getdate
()),1,c.username,'', c.password,c.accttype,c.logons,
case when c.blockuser = 'y' then c.timeleft when c.blockuser = 'g'
then b.timeleft else null End ,
c.email,GETDATE(),GETDATE()
---End of forwarded mail from [EMAIL PROTECTED]
--
Mike McCauley [EMAIL PROTECTED]
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.starport.net/~radiator/
Announcements on [EMAIL PROTECTED]
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
