#!/usr/bin/perl -w
use strict;

################################################################################
# Configuration
################################################################################

# Location of gnu-radius sqlserver config file.
my $sqlserver = '/usr/local/etc/raddb/sqlserver';

# This DB information is not easily readable from sqlserver

## User account info
my $auth_table = 'Radius_users';

# This field must be the same in all tables that reference the
# username. (Yes, it's possible to hack this to use different
# column names but I'm lazy.)
my $user_field = 'username';

my $service_field = 'service';

# If false, the clear password will not be stored in the DB.
my $clearpw_field = 'clearpw';

my $cryptpw_field = 'cryptpw';

# If false, accounts will be suspended be generating a random password
my $active_field = 'active';

## Attributes
# Attribute table name
my $attrib_table = 'Radius_attribs';

my $attrib_field = 'attrib';
my $value_field = 'value';
my $op_field = 'op';

## Groups
my $group_table = 'Radius_groups';
my $group_field = 'groupname';

## Sessions
# This is ignored if 'doacct' is 'no' in sqlserver
my $session_table = 'Radius_sessions';

my $pod2usage = '/usr/local/bin/pod2usage';

################################################################################
# Code
################################################################################

use DBI;

# Read server settings out of sqlserver
# See http://www.gnu.org/software/radius/manual/html_chapter/radius_8.html#SEC63
# for a description of these params.
my %rdb_conf = (server => '',
		port => '',
		login => '',
		password => '',
		interface => '',
		auth_db => '',
		doacct => 0,
		# Auth Table info
		auth_table => $auth_table,
		user_field => $user_field,
		service_field => $service_field,
		clearpw_field => $clearpw_field,
		cryptpw_field => $cryptpw_field,
		active_field => $active_field,
		# Attributes table info
		attrib_table => $attrib_table,
		attrib_field => $attrib_field,
		value_field => $value_field,
		op_field => $op_field,
		# Group table info
		group_table => $group_table,
		group_field => $group_field,
		# Session table
		session_table => $session_table
		);
open (SQLSERVER, $sqlserver) or die "Can't open SQL config ($sqlserver):$!\n";
while (my $line = <SQLSERVER>)
{
    chomp $line;
    if ($line =~ /^\s*interface\s+(.+)\s*$/)
    {
	$rdb_conf{interface} = $1;
    }
    elsif ($line =~ /^\s*port\s+(\d+)\s*$/)
    {
	$rdb_conf{port} = $1;
    }
    elsif ($line =~ /^\s*login\s+(.+)\s*$/)
    {
	$rdb_conf{login} = $1;
    }
    elsif ($line =~ /^\s*password\s+(.*)\s*$/)
    {
	$rdb_conf{password} = $1;
    }
    elsif ($line =~ /^\s*auth_db\s+(.*)\s*$/)
    {
	$rdb_conf{auth_db} = $1;
    }
    elsif ($line =~ /^\s*doacct\s+(yes|no)\s*$/)
    {
	if    ($1 eq 'yes') { $rdb_conf{doacct} = 1; }
	elsif ($1 eq 'no')  { $rdb_conf{doacct} = 0; }
    }
}
close SQLSERVER;

# Connect to DB
my $rdb = DBI->connect ("DBI:$rdb_conf{interface}:$rdb_conf{auth_db}:$rdb_conf{server}:$rdb_conf{port}",
			$rdb_conf{login}, $rdb_conf{password})
    or die "Can't connect to RADIUS DB: $DBI::errstr\n";

# Deal with cmd line
if (@ARGV < 2) { help(); exit 0 };

my $cmd = shift @ARGV;
$cmd = lc $cmd;

if    ($cmd eq 'add')	{ add (@ARGV); }
elsif ($cmd eq 'del')	{ del (@ARGV); }
elsif ($cmd eq 'mod')   { mod (@ARGV); }
elsif ($cmd eq 'show')  { show (@ARGV); }
elsif ($cmd eq 'attrib'){ attrib (@ARGV); }
elsif ($cmd eq 'group') { group (@ARGV); }
else			{ help(); }

exit 0;

sub add 
{
    my ($account, @params) = @_;
    die "No accoubt specified\n" if not $account;

    # Get other params
    my $password = '';
    for (my $i = 0; $i < @params; $i++)
    {
	if    ($params[$i] eq '-p') { $password = $params[$i+1]; }
    }

    $password = generate_password(10) unless $password;

    my $sql = "INSERT into $rdb_conf{auth_table} SET";
    $sql .= " $rdb_conf{user_field} = ".$rdb->quote($account);
    $sql .= ", $rdb_conf{cryptpw_field} = ".$rdb->quote(crypt($password, generate_password(2)));
    $sql .= ", $rdb_conf{clearpw_field} = ".$rdb->quote($password) if $rdb_conf{clearpw_field};
    $sql .= ", $rdb_conf{active_field} = 1" if $rdb_conf{active_field};

    $rdb->do($sql) or die "Can't add user ($account): ".$rdb->errstr."\n";
}

sub del
{
    my ($account, @params) = @_;
    die "No accoubt specified\n" if not $account;

    my $sql = "DELETE from $rdb_conf{auth_table}";
    $sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
    $rdb->do($sql) or die "Can't del user ($account): ".$rdb->errstr."\n";

    $sql = "DELETE from $rdb_conf{attrib_table}";
    $sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
    $rdb->do($sql) or die "Can't del user attribs ($account): ".$rdb->errstr."\n";

    $sql = "DELETE from $rdb_conf{group_table}";
    $sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
    $rdb->do($sql) or die "Can't del user from groups ($account): ".$rdb->errstr."\n";
}

sub mod
{
    my ($account, @params) = @_;
    die "No accoubt specified\n" if not $account;

    # Get other params
    my $password = '';
    my $new_acct = '';
    my $active = '';
    for (my $i = 0; $i < @params; $i++)
    {
	if    ($params[$i] eq '-p') { $password = $params[$i+1]; }
	elsif ($params[$i] eq '-u') { $new_acct = $params[$i+1]; }
	elsif ($params[$i] eq '-a') { $active = ($params[$i+1] eq 'y')? 1 : 0;}
    }

    if ($password)
    {
	my $sql = "UPDATE $rdb_conf{auth_table} SET";
	$sql .= " $rdb_conf{cryptpw_field} = ".$rdb->quote(crypt($password, generate_password(2)));
	$sql .= ", $rdb_conf{clearpw_field} = ".rdb->quote($password) if $rdb_conf{clearpw_field};
	$sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
	
	$rdb->do($sql) or die "Can't edit user ($account): ".$rdb->errstr."\n";
    }

    if ($active)
    {
	my $sql = "UPDATE $rdb_conf{auth_table} SET";
	$sql .= " $rdb_conf{active_field} = ";
	if ($active eq 'y') { $sql .= "1"; }
	else                { $sql .= "0"; }
	$sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);

	$rdb->do($sql) or die "Can't edit user ($account): ".$rdb->errstr."\n";
    }

    if ($new_acct)
    {
	my $sql = "UPDATE $rdb_conf{auth_table} SET";
	$sql .= " $rdb_conf{user_field} = ".$rdb->quote($new_acct);
	$sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
	$rdb->do($sql) or die "Can't edit user ($account): ".$rdb->errstr."\n";

	# Change user in groups
	$sql = "UPDATE $rdb_conf{group_table} SET";
	$sql .= " $rdb_conf{user_field} = ".$rdb->quote($new_acct);
	$sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
	$rdb->do($sql) or die "Can't edit user ($account): ".$rdb->errstr."\n";

	# Change user attributes
	$sql = "UPDATE $rdb_conf{attrib_table} SET";
	$sql .= " $rdb_conf{user_field} = ".$rdb->quote($new_acct);
	$sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
	$rdb->do($sql) or die "Can't edit user ($account): ".$rdb->errstr."\n";
    }
}

sub show
{
    my ($account, @params) = @_;
    die "No accoubt specified\n" if not $account;

    my $sql = "SELECT * from $rdb_conf{auth_table}";
    $sql .= " WHERE $rdb_conf{user_field} like ".$rdb->quote($account);
    my $sth = $rdb->prepare($sql) or die "Can't show user ($account): ".$rdb->errstr."\n";
    $sth->execute() or die "Can't show user ($account): ".$rdb->errstr."\n";

    while (my $res = $sth->fetchrow_hashref())
    {
	print $res->{$rdb_conf{user_field}};
	print ":", $res->{$rdb_conf{cryptpw_field}};
	print (":", $res->{$rdb_conf{clearpw_field}}) if defined $rdb_conf{clearpw_field};
	print ":", $res->{$rdb_conf{service_field}};
	print (":", $res->{$rdb_conf{active_field}}) if defined $rdb_conf{active_field};
	print "\n";
    }
}

sub attrib
{
    my ($cmd, @args) = @_;
    if    ($cmd eq 'add') { attrib_add (@args); }
    elsif ($cmd eq 'del') { attrib_del (@args); }
    elsif ($cmd eq 'mod') { attrib_mod (@args); }
    elsif ($cmd eq 'show'){ attrib_show (@args); }
    else		  { help(); }
}

sub attrib_add
{
    my ($account, $attribute, @params) = @_;
    die "No account specified\n" unless $account;
    die "No attribute specified\n" unless $attribute;

    my $value = '';
    my $op = '';
    for (my $i = 0; $i < @params; $i++)
    {
	if    ($params[$i] eq '-v') { $value = $params[$i+1]; }
	elsif ($params[$i] eq '-o') { $op = $params[$i+1]; }
    }

    die "Bad operator ($op)\n" if ($op and not _check_op($op));

    my $sql = "INSERT into $rdb_conf{attrib_table} SET";
    $sql .= "  $rdb_conf{user_field} = ".$rdb->quote($account);
    $sql .= ", $rdb_conf{attrib_field} = ".$rdb->quote($attribute);
    $sql .= ", $rdb_conf{value_field} = ".$rdb->quote($value) if $value;
    $sql .= ", $rdb_conf{op_field} = ".$rdb->quote($op) if $op;
    $rdb->do($sql) or die "Can't add attribute: ".$rdb->errstr."\n";
}

sub attrib_del
{
    my ($account, $attribute, @params) = @_;
    die "No account specified\n" unless $account;
    die "No attribute specified\n" unless $attribute;

    my $sql = "DELETE from $rdb_conf{attrib_table}";
    $sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
    $sql .= "  AND $rdb_conf{attrib_field} = ".$rdb->quote($attribute);
    $rdb->do($sql) or die "Can't delete attribute: ".$rdb->errstr."\n";
}

sub attrib_mod
{
    my ($account, $attribute, @params) = @_;
    die "No account specified\n" unless $account;
    die "No attribute specified\n" unless $attribute;

    my $value = '';
    my $op = '';
    for (my $i = 0; $i < @params; $i++)
    {
	if    ($params[$i] eq '-v') { $value = $params[$i+1]; }
	elsif ($params[$i] eq '-o') { $op = $params[$i+1]; }
    }

    my $sql_head = "UPDATE $rdb_conf{attrib_table} SET";
    my $sql_where = " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
    $sql_where .= "  AND $rdb_conf{attrib_field} = ".$rdb->quote($attribute);

    my $sql = '';
    if ($value)
    {
	$sql = $sql_head;
	$sql .= " $rdb_conf{value_field} = ".$rdb->quote($value);
	$sql .= $sql_where;
	$rdb->do($sql) or die "Can't edit attribute: ".$rdb->errstr."\n";
    }
    if ($op)
    {
	$sql = $sql_head;
	$sql .= " $rdb_conf{op_field} = ".$rdb->quote($op);
	$sql .= $sql_where;
	$rdb->do($sql) or die "Can't add attribute: ".$rdb->errstr."\n";
    }
}

sub attrib_show
{
    my ($account, @attributes) = @_;
    die "No account specified\n" unless $account;
    @attributes = ('%') unless @attributes;

    my $sql = "SELECT * from $rdb_conf{attrib_table}";
    $sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);
    $sql .= " AND ( $rdb_conf{attrib_field} LIKE ".$rdb->quote($attributes[0]);
    for (my $i = 1; $i < @attributes; $i++)
    {
	$sql .= " OR $rdb_conf{attrib_field} LIKE ".$rdb->quote($attributes[$i]);
    }
    $sql .= ")";

    my $sth = $rdb->prepare($sql) or die "Can't show user attributes: ".$rdb->errstr."\n";
    $sth->execute() or die "Can't show user attributes: ".$rdb->errstr."\n";

    while (my $res = $sth->fetchrow_hashref())
    {
	print $res->{$rdb_conf{attrib_field}};
	print ":$res->{$rdb_conf{value_field}}";
	print ":", (defined $res->{$rdb_conf{op_field}} ? $res->{$rdb_conf{op_field}} : '');
	print "\n";
    }
    $sth->finish;
}

sub _check_op
{
    my $op = shift;
    if ($op =~ /^[<>!]?=$/	# Match =, <=, >=, !=
	or $op =~ /^[<>]$/)	# Match <, >
    {
	return 1;
    }
    else
    {
	return 0;
    }
}

sub group
{
    my ($cmd, @args) = @_;
    if    ($cmd eq 'del') { group_del (@args); }
    elsif ($cmd eq 'mod') { group_mod (@args); }
    elsif ($cmd eq 'show'){ group_show (@args); }
    elsif ($cmd eq 'user'){ group_user (@args); }
    else                  { help(); }
}

sub group_del
{
    my ($group, @params) = @_;
    die "No group specified\n" unless $group;

    my $sql = "DELETE from $rdb_conf{group_table} ";
    $sql .= " WHERE $rdb_conf{group_field} = ".$rdb->quote($group);
    $rdb->do($sql) or die "Can't delete group ($group): ".$rdb->errstr."\n";
}


sub group_mod
{
    my ($group, @params) = @_;
    die "No group specified\n" unless $group;

    my $new_group = '';
    for (my $i = 0; $i < @params; $i++)
    {
	if    ($params[$i] eq '-g') { $new_group = $params[$i+1]; }
    }

    if ($new_group)
    {
	my $sql = "UPDATE $rdb_conf{group_table} SET";
	$sql .= " $rdb_conf{group_field} = ".$rdb->quote($new_group);
	$sql .= " WHERE $rdb_conf{group_field} = ".$rdb->quote($group);
	$rdb->do($sql) or die "Can't modify group ($group): ".$rdb->errstr."\n";
    }
}

sub group_show
{
    my ($group, @params) = @_;
    $group = '%' unless $group;

    my $sql = "SELECT * from $rdb_conf{group_table}";
    $sql .= " WHERE $rdb_conf{group_field} LIKE ".$rdb->quote($group);

    my $sth = $rdb->prepare($sql) or die "Can't show group: ".$rdb->errstr."\n";
    $sth->execute() or die "Can't show group: ".$rdb->errstr."\n";

    while (my $res = $sth->fetchrow_hashref)
    {
	print "$res->{$rdb_conf{group_field}}";
	print ":$res->{$rdb_conf{user_field}}";
	print "\n";
    }

    $sth->finish;
}

sub group_user
{
    my ($cmd, @args) = @_;
    if    ($cmd eq 'add') { group_user_add (@args); }
    elsif ($cmd eq 'del') { group_user_del (@args); }
    elsif ($cmd eq 'show'){ group_user_show (@args); }
    else                  { help(); }
}

sub group_user_add
{
    my ($group, $account, @params) = @_;
    die "No group specified\n" unless $group;
    die "No account specified\n" unless $account;

    my $sql = "INSERT into $rdb_conf{group_table} SET";
    $sql .= " $rdb_conf{group_field} = ".$rdb->quote($group);
    $sql .= ", $rdb_conf{user_field} = ".$rdb->quote($account);
    $rdb->do($sql) or die "Can't add user to group ($account:$group): ".$rdb->errstr."\n";
}

sub group_user_del
{
    my ($group, $account, @params) = @_;
    die "No group specified\n" unless $group;
    die "No account specified\n" unless $account;

    my $sql = "DELETE from $rdb_conf{group_table} ";
    $sql .= " WHERE $rdb_conf{group_field} = ".$rdb->quote($group);
    $sql .= "  AND $rdb_conf{user_field} = ".$rdb->quote($account);
    $rdb->do($sql) or die "Can't delete user from group ($account:$group): ".$rdb->errstr."\n";
}

sub group_user_show
{
    my ($account, @params) = @_;
    die "No account specified\n" unless $account;
    
    my $sql = "SELECT * from $rdb_conf{group_table}";
    $sql .= " WHERE $rdb_conf{user_field} = ".$rdb->quote($account);

    my $sth = $rdb->prepare($sql) or die "Can't show groups for user: ".$rdb->errstr."\n";
    $sth->execute or die "Can't show groups for user: ".$rdb->errstr."\n";
    
    while (my $res = $sth->fetchrow_hashref)
    {
	print "$res->{$rdb_conf{group_field}}";
	print ":$res->{$rdb_conf{user_field}}";
	print "\n";
    }

    $sth->finish;
}

sub help
{
    system($pod2usage, $0);
}

sub generate_password
{
    my $len = shift || 10;
    my @valid = (0..9, 'a'..'z', 'A'..'Z', '@', '#', '%', '^', '*');
    my $password = '';
    for (1 .. $len)
    {
	$password .= $valid[int (rand $#valid)];
    }
    return $password;
}


__END__

=head1 NAME

ruser - Modify users in a SQL DB for GNU RADIUS

=head1 SYNOPSIS

 ruser add account [-p password]
 ruser del account
 ruser mod account [-p password] [-u new_account] [-a y|n]
 ruser show account

 ruser attrib add account attribute [-v value] [-o op]
 ruser attrib del account attribute
 ruser attrib mod account attribute [-v value] [-o op]
 ruser attrib show account [attribute ...]

 ruser group del group
 ruser group mod group [-g new_group]
 ruser group show group

 ruser group user add group account
 ruser group user del group account
 ruser group user show account

 ruser help

=head1 DESCRIPTION

B<ruser> handles accounts for GNU Radius with a SQL backend.

In all cases, I<account> is the name of the account to operate on.

=head2 add

=over 4

=item -p password

If provided, then the account's password is set to I<password>.
Otherwise, the account is assigned a random password.

=back

=head2 del

=head2 mod

=head2 show

=head2 help

=head2 attrib add

=over 4

=item addribute

A valid RADIUS attribute.

=item op

One of C<=>, C<!=>, C<E<lt>>, C<E<gt>>, C<E<lt>=>, C<E<gt>=> or C<undef>.

If I<op> is I<undef> (or not defined), then the attribute is a reply
attribute, otherwise it is a check attriute.

=back

=head2 attrib del

=head2 attrib mod

=head2 attrib show

=head2 group del

=head2 group mod

=head2 group show

Show the users in the group

=head2 group user add

=head2 group user del

=head2 group user show

Show the groups a user is in.

=head1 AUTHOR

Randy Smith <randys@amigo.net>

=head1 COPYRIGHT

 Copyright (c) 2002 Randy Smith
 All rights reserved.
 
 Redistribution and use in source and binary forms, with or without
 modification, are permitted provided that the following conditions
 are met:
 1. Redistributions of source code must retain the above copyright
    notice, this list of conditions and the following disclaimer.
 2. Redistributions in binary form must reproduce the above copyright
    notice, this list of conditions and the following disclaimer in the
    documentation and/or other materials provided with the distribution.
 
 THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
 ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
 FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
 OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
 LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
 OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 SUCH DAMAGE.

=cut