#!/bin/sh
#
# $Id$
#
# sc: ser control; tool for maintaining ser's databases
#
# realm
DOMAIN=iptel.org
# SQL config
SQL_DB=csps107
SQL_HOST=dbhost
SQL_USER=csps
# the read-only user for whom password may be stored here
RO_USER=serro
RO_PW=47serro11
# binaries
GENHA1='gen_ha1'
MYSQL='mysql'
SER='sr'
# SQL names
USER_COLUMN=user
REALM_COLUMN=realm
HA1_COLUMN=HA1
HA1B_COLUMN=HA1B
# subscriber table
TABLE=subscriber
# Usr Loc Table
USRLOC=location
PASSWORD_COLUMN=password
SUBSCRIBER_COLUMN='user_id'
EMAIL_COLUMN=email_address
# acl table
ACL_TABLE=grp
ACL_USER_COLUMN=user
ACL_GROUP_COLUMN=grp
usage() {
echo "usage: "
echo -n " `basename $0` add <name> <password> <email> "
echo "... add a new subscriber (*)"
echo " `basename $0` dul <name> ... delete user's UsrLoc entries (*)"
echo " `basename $0` show [<name>] ... show online users"
echo " `basename $0` mail <name> ... send an email to a user"
echo " `basename $0` rm <name> ... delete a user (*)"
echo " `basename $0` passwd <user> <passwd> ... change user's password (*)"
echo " `basename $0` stat ... show runnig processes + $0 show"
echo " `basename $0` acl show [<user>] ... show user membership"
echo " `basename $0` acl grant <user> <group> ... grant user memberhsip (*)"
echo " `basename $0` acl revoke <user> [<group>] ... grant user memberhsip(s) (*)"
echo
echo " commands labeled with (*) will prompt for a MySQL password"
echo
}
acl() {
case $1 in
show)
if [ $# -eq 2 ] ; then
CLAUSE=" WHERE $ACL_USER_COLUMN='$2' "
elif [ $# -ne 1 ] ; then
usage
exit 1
fi
QUERY="select * FROM $ACL_TABLE $CLAUSE ; "
$MYSQL -h $SQL_HOST -u $RO_USER "-p$RO_PW" \
-e "$QUERY ;" $SQL_DB
;;
grant)
if [ $# -ne 3 ] ; then
usage
exit 1
fi
$MYSQL -h $SQL_HOST -u $SQL_USER -p$PW \
-e "use $SQL_DB; insert into $ACL_TABLE \
($ACL_USER_COLUMN,$ACL_GROUP_COLUMN) \
values ('$2','$3');";
;;
revoke)
if [ $# -eq 3 ] ; then
CLAUSE=" and $ACL_GROUP_COLUMN='$3' "
elif [ $# -ne 2 ] ; then
usage
exit 1
fi
QUERY="delete from $ACL_TABLE where \
$ACL_TABLE.$ACL_USER_COLUMN='$2' $CLAUSE"
$MYSQL -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$QUERY ;" $SQL_DB
;;
*)
usage
exit 1
;;
esac
}
# params: user
# output: false if exists, true otherwise
is_user() {
CNT=`$MYSQL -B -h $SQL_HOST -u $SQL_USER -p$PW \
-e "use $SQL_DB; select count(*) from $TABLE \
where $SUBSCRIBER_COLUMN='$1' and $REALM_COLUMN='$DOMAIN';" \
| grep -v ERROR | tail -n1`
if [ "0$CNT" -eq 0 ] ; then
false
else
true
fi
}
#params: none
# output: PW
prompt_pw() {
read -s -p "MySql Password: " PW
echo
}
# params: user, password
# output: HA1, HA1B
credentials()
{
HA1=`$GENHA1 $1 $DOMAIN $2`
if [ $? -ne 0 ] ; then
echo "HA1 calculation failed"
exit 1
fi
HA1B=`$GENHA1 "$1@$DOMAIN" $DOMAIN $2`
if [ $? -ne 0 ] ; then
echo "HA1B calculation failed"
exit 1
fi
}
case $1 in
passwd)
if [ $# -ne 3 ] ; then
usage
exit 1
fi
shift
credentials
prompt_pw
is_user $1
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1
fi
$MYSQL -h $SQL_HOST -u $SQL_USER -p$PW \
-e "use $SQL_DB; update $TABLE \
set $HA1_COLUMN='$HA1', $HA1B_COLUMN='$HA1B', $PASSWORD_COLUMN='$2' \
WHERE $SUBSCRIBER_COLUMN='$1' and $REALM_COLUMN='$DOMAIN';"
if [ $? -ne 0 ] ; then
echo "password change failed"
else
echo "password change succeeded"
fi
;;
add)
if [ $# -ne 4 ] ; then
usage
exit 1
fi
shift
credentials
prompt_pw
is_user $1
if [ $? -eq 0 ] ; then
echo user already exists
exit 1
fi
$MYSQL -h $SQL_HOST -u $SQL_USER -p$PW \
-e "use $SQL_DB; insert into $TABLE \
($SUBSCRIBER_COLUMN,$REALM_COLUMN,$HA1_COLUMN,\
$HA1B_COLUMN,$PASSWORD_COLUMN,$EMAIL_COLUMN) \
values ('$1','$DOMAIN','$HA1','$HA1B','$2', '$3');";
if [ $? -ne 0 ] ; then
echo "introducing a new user to the database failed"
else
echo "new user added"
fi
;;
dul)
if [ $# -ne 2 ] ; then
usage
exit 1
fi
prompt_pw
CNT=`$MYSQL -B -h $SQL_HOST -u $SQL_USER -p$PW \
-e "use $SQL_DB; select count(*) from $USRLOC \
where $USRLOC.$USER_COLUMN='$2';" | grep -v ERROR | tail -n1`
if [ "0$CNT" -eq 0 ] ; then
echo Nothing to delete
exit 1
fi
QUERY="delete from $USRLOC where $USRLOC.$USER_COLUMN='$2'"
$MYSQL -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$QUERY ;" $SQL_DB
if [ $? -ne 0 ] ; then
echo "database operation failed"
else
echo "ok -- but beware, the server caches the usrloc entries"
echo "and the changes don't take effect until ser reboot"
fi
;;
mail)
if [ $# -ne 2 ] ; then
usage
exit 1
fi
shift
QUERY="select $TABLE.$EMAIL_COLUMN from $TABLE where \
$TABLE.$SUBSCRIBER_COLUMN='$1'"
EA=`$MYSQL -B -h $SQL_HOST -u $RO_USER "-p$RO_PW" \
-e "$QUERY ;" $SQL_DB| grep -v ERROR | tail -n1`
if [ $? -ne 0 ] ; then
echo "MySql query failed"
exit 1
fi
echo "Write email to $1: $EA now ..."
mail -s "Message from $DOMAIN SIP admin" $EA
if [ $? -eq 0 ] ; then
echo message sent
else
echo sending message failed
fi
;;
show)
if [ $# -ne 1 -a $# -ne 2 ] ; then
usage
exit 1
fi
shift
QUERY1="select $TABLE.$EMAIL_COLUMN from $TABLE where \
$TABLE.$SUBSCRIBER_COLUMN='$1'"
QUERY2="select $USRLOC.* from $USRLOC where \
$USRLOC.$USER_COLUMN='$1' order by expires desc"
QUERY3="select $USRLOC.$USER_COLUMN, $TABLE.$EMAIL_COLUMN \
from $TABLE, $USRLOC where \
$TABLE.$SUBSCRIBER_COLUMN=$USRLOC.$USER_COLUMN"
if [ $# -eq 1 ] ; then
$MYSQL -h $SQL_HOST -u $RO_USER "-p$RO_PW" -e "$QUERY1 ;" $SQL_DB
$MYSQL -h $SQL_HOST -u $RO_USER "-p$RO_PW" -e "$QUERY2 ;" $SQL_DB
else
$MYSQL -h $SQL_HOST -u $RO_USER "-p$RO_PW" -e "$QUERY3 ;" $SQL_DB
fi
echo -n "Note: Due to usage of cache, server's list "
echo "may differ from DB list."
;;
rm)
if [ $# -ne 2 ] ; then
usage
exit 1
fi
shift
prompt_pw
is_user $1
if [ $? -ne 0 ] ; then
echo non-existent user
exit 1
fi
QUERY="delete from $TABLE where $TABLE.$SUBSCRIBER_COLUMN='$1'"
$MYSQL -h $SQL_HOST -u $SQL_USER "-p$PW" -e "$QUERY ;" $SQL_DB
;;
stat)
if [ $# -ne 1 ] ; then
usage
exit 1
fi
ps -o "pid,user,args" $i -C $SER
$0 show
;;
acl)
shift
acl "$@"
;;
*)
usage
exit 1
;;
esac