#!/bin/sh # # dbmail_acl.sh - Manage ACLs for shared mailboxes # # Any system that this script is expected to interact with should have a # dbmail configuration file listed below # # Adam Kosmin, 8/10/06 # Stef Bon, 20/07/08 # # :set ts=2 # # script_version=0.8 # Changelog # 0.8 - add support for subscribing to other folders than INBOX # 0.7 - column now used for formatting # 0.6 - list operation now displays orphaned users and mailboxes ACLs by default. _dbmail_mysqldatabase=1 _dbmail_mysqluser=2 _dbmail_mysqlpassword=3 _dbmail_mysqlhost=4 _dbmail_conffile=5 _dbmail_driver=6 DBMAIL[_dbmail_mysqldatabase]="" DBMAIL[_dbmail_mysqluser]="" DBMAIL[_dbmail_mysqlpassword]="" DBMAIL[_dbmail_mysqlhost]="" DBMAIL[_dbmail_conffile]="/etc/dbmail.conf" DBMAIL[_dbmail_driver]="" usage() { echo "$0 $script_version" echo "Usage: $0 add|del user mailbox [folder]" echo "folder (like INBOX, Trash and Sent) to suscribe to, defaults to INBOX" echo "Usage: $0 list" exit 1 } get_user_idnr() { local userid=$1 # determine the numerical id of username # note that this could also be done direct via the database witha query $DBMAIL_USERS -l $userid | awk -F: '{print $3}' } function get_user_id() { local user_idnr=$1 # return the username of user with idnr $MYSQL_CLIENT -N -e "SELECT userid FROM dbmail_users WHERE user_idnr = $user_idnr;" } function get_mailbox_idnr() { local owner_idnr=$1 local name=$2 if [ -z "$name" ]; then name="INBOX" fi $MYSQL_CLIENT -N -e "SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name = '$name' and owner_idnr = $owner_idnr;" } function get_mailbox_name() { local mailbox_idnr=$1 $MYSQL_CLIENT -N -e "SELECT name FROM dbmail_mailboxes WHERE mailbox_idnr = $mailbox_idnr;" } function get_mailbox_owner_idnr() { local mailbox_idnr=$1 $MYSQL_CLIENT -N -e "SELECT owner_idnr FROM dbmail_mailboxes WHERE mailbox_idnr = $mailbox_idnr;" } function get_mailbox_owner_name() { local mailbox_idnr=$1 $MYSQL_CLIENT -N -e "SELECT userid FROM dbmail_users du, dbmail_mailboxes dm WHERE du.user_idnr = dm.owner_idnr and dm.mailbox_idnr = $mailbox_idnr;" } function get_mailbox_acl_users() { local mailbox_idnr=$1 $MYSQL_CLIENT -N -e "SELECT user_id FROM dbmail_acl where mailbox_id = $mailbox_idnr;" } function get_all_mailboxes_with_acl() { # get all the mailboxes with acls # simply go to the dbmail_acl table # this is better since it will just grab all mailbox ids listed in the dbmail_acl table # unfortunatley, we won't be able to sort alphabetically like we did before. $MYSQL_CLIENT -N -e "SELECT DISTINCT mailbox_id FROM dbmail_acl ORDER BY mailbox_id;" } function get_all_acls() { local mailbox_idnr=$1 local user_idnr=$2 $MYSQL_CLIENT -N -e "SELECT lookup_flag, read_flag, seen_flag, write_flag, insert_flag, post_flag, create_flag, delete_flag, administer_flag FROM dbmail_acl WHERE mailbox_id = $mailbox_idnr and user_id = $user_idnr;" } acl_add() { local user_idnr=$1 local mailbox_idnr=$2 $MYSQL_CLIENT -e "INSERT INTO dbmail_acl values ($user_idnr, $mailbox_idnr, 1, 1, 1, 1, 1, 1, 1, 1, 1);" } acl_del() { local user_idnr=$1 local mailbox_idnr=$2 $MYSQL_CLIENT -e "DELETE FROM dbmail_acl WHERE user_id = $user_idnr and mailbox_id = $mailbox_idnr;" } acl_purge() { local user_idnr=$1 $MYSQL_CLIENT -e "DELETE FROM dbmail_acl WHERE user_id = $user_idnr;" } subscription_add() { local user_idnr=$1 local mailbox_idnr=$2 $MYSQL_CLIENT -e "INSERT INTO dbmail_subscription VALUES ($user_idnr, $mailbox_idnr);" } subscription_del() { local user_idnr=$1 local mailbox_idnr=$2 $MYSQL_CLIENT -e "DELETE FROM dbmail_subscription WHERE user_id = $user_idnr and mailbox_id = $mailbox_idnr;" } subscription_purge() { local user_idnr=$1 $MYSQL_CLIENT -e "DELETE FROM dbmail_subscription WHERE user_id = $user_idnr;" } acl_list() { # get all the mailboxes with acls shared=$(get_all_mailboxes_with_acl) if [ -z "$shared" ]; then echo "No shared mailboxes were found." exit fi output="MAILBOX FOLDER USERID LOOKUP READ SEEN WRITE INSERT POST CREATE DELETE ADMIN" for mailbox_idnr in $shared; do mailbox_owner_name=$(get_mailbox_owner_name $mailbox_idnr) mailbox_name=$(get_mailbox_name $mailbox_idnr) user_idnrs=$(get_mailbox_acl_users $mailbox_idnr) # There may be no users, so we should check for an orphaned mailbox here if [ -z "$user_idnrs" ]; then output="${output}\n${mailbox_owner_name:-($mailbox_idnr)} ${mailbox_name:-($mailbox_idnr)}" continue fi for user_idnr in $user_idnrs; do rights=$(get_all_acls $mailbox_idnr $user_idnr) if [ -n "$rights" ]; then # At this point, we should know the mailbox id, the first user id, and their acls. # we should report now username=$(get_user_id $user_idnr) output="${output}\n${mailbox_owner_name:-($mailbox_idnr)} ${mailbox_name:-($mailbox_idnr)} ${username:-($uid)}" for right in $rights; do output="${output} $right" done output="${output}\n" fi done done printf "${output}" | column -t } function read_parameter() { local parameter=$1 grep "^$parameter" ${DBMAIL[_dbmail_conffile]} | awk -F= '{print $2}' | sed -e 's/^ *//g' -e 's/ *$//g' } read_conf() { if [ ! -f "${DBMAIL[_dbmail_conffile]}" ] ; then echo "Error: configuration file ${DBMAIL[_dbmail_conffile]} not found. Cannot continue." exit elif [ ! -r "${DBMAIL[_dbmail_conffile]}" ] ; then echo "Error: ${DBMAIL[_dbmail_conffile]} is not readable." exit fi DBMAIL[_dbmail_mysqluser]=$(read_parameter user) DBMAIL[_dbmail_mysqlpassword]=$(read_parameter pass) DBMAIL[_dbmail_mysqlhost]=$(read_parameter host) DBMAIL[_dbmail_mysqldatabase]=$(read_parameter db) DBMAIL[_dbmail_driver]=$(read_parameter driver) if [ -z "${DBMAIL[_dbmail_driver]}" ]; then echo "Error: SQL driver not set." exit elif [ ! "${DBMAIL[_dbmail_driver]}" = "mysql" ]; then echo "Error: this script does not support other databases than MySQL." exit fi if [ -z "${DBMAIL[_dbmail_mysqldatabase]}" ]; then echo "Error: MySQL database not set." exit fi if [ -z "${DBMAIL[_dbmail_mysqlhost]}" ]; then echo "Error: MySQL host not set." exit fi if [ -z "${DBMAIL[_dbmail_mysqluser]}" ]; then echo "Error: MySQL user not set." exit fi if [ -z "${DBMAIL[_dbmail_mysqlpassword]}" ]; then echo "Error: MySQL password not set." exit fi DBMAIL_USERS=$(which dbmail-users 2>>/dev/null) if [ -z "$DBMAIL_USERS" ]; then echo "Error: binary dbmail-users not found." exit fi MYSQL_CLIENT=$(which mysql 2>>/dev/null) if [ -z "$MYSQL_CLIENT" ]; then echo "Error: binary mysql not found." exit fi MYSQL_CLIENT="$MYSQL_CLIENT -u${DBMAIL[_dbmail_mysqluser]} -p${DBMAIL[_dbmail_mysqlpassword]} -h ${DBMAIL[_dbmail_mysqlhost]} ${DBMAIL[_dbmail_mysqldatabase]}" } # # # Script starts here # no input [ -z "$1" ] && usage read_conf COMMAND="$1" case "$COMMAND" in add|del) USERID="$2" if [ -z "$USERID" ]; then usage exit fi user_idnr=$(get_user_idnr $USERID) if [ -z "$user_idnr" ]; then echo "Error: no user $USERID found." exit fi mailbox_owner="$3" if [ -z "$mailbox_owner" ]; then usage else mailbox_owner_idnr=$(get_user_idnr "$mailbox_owner") if [ -n "$4" ]; then mailbox_name="$4" mailbox_idnr=$(get_mailbox_idnr $mailbox_owner_idnr "$mailbox_name") else echo "Taking default folder INBOX" mailbox_idnr=$(get_mailbox_idnr $mailbox_owner_idnr) fi if [ -z "$mailbox_idnr" ]; then echo "Error: folder $mailbox_name not found in mailbox $mailbox_owner." exit fi if [ "$COMMAND" = "add" ]; then acl_add $user_idnr $mailbox_idnr subscription_add $user_idnr $mailbox_idnr elif [ "$COMMAND" = "del" ]; then acl_del $user_idnr $mailbox_idnr subscription_del $user_idnr $mailbox_idnr fi fi ;; list) acl_list ;; *) usage ;; esac