openfpc-dbmaint

#!/bin/bash 

#########################################################################################
# Copyright (C) 2010 Leon Ward
# leon@openfpc.org
# openfpc-dbmaint.pl - Part of the OpenFPC - (Full Packet Capture) project
#
# Quick script to create an OpenFPC connection database.
#
# The mysql IPv6 function source in addfuncs() came from edward@openfpc.org
#########################################################################################
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
#########################################################################################

# Default values for configuration in case user chooses to not include
# unneeded values in the config file. E.g. GUI_DB_NAME when the GUI
# is not being used.
# Change them in your openfpc config file, not here. They are replaced at runtime

ENABLE_SESSION=0
SESSION_DB_NAME="openfpc" 
SESSION_DB_USER="openfpc"
SESSION_DB_PASS="openfpc"
GUI_DB_NAME="gui"
GUI_DB_USER="opnepfc"
GUI_DB_PASS="openfpc"
PROXY_DB_NAME="proxy_openfpc"
PROXY_DB_USER="proxy_openfpc"
PROXY_DB_PASSWORD="proxy_openfpc"
OPENFPCVER="1.0"
DBUSER=0
DBPASS=0
CONFIG=0

function die
{
	echo -e "    $1"
	exit 1
}

function chkroot()
{
	IAM=$(whoami)
        if [ "$IAM" != "root" ]
        then
                die " Must be root to run this script"
        fi  
}


function addfuncs
{
	# Create mysql IPv6 functions
	echo -e " -  Adding function INET_ATON6 to DB $SESSION_DB_NAME"
	SQL="
use $SESSION_DB_NAME;
DELIMITER //
CREATE FUNCTION INET_ATON6(n CHAR(39))
RETURNS DECIMAL(39) UNSIGNED
DETERMINISTIC
BEGIN
    RETURN CAST(CONV(SUBSTRING(n FROM  1 FOR 4), 16, 10) AS DECIMAL(39))
                       * 5192296858534827628530496329220096 -- 65536 ^ 7
         + CAST(CONV(SUBSTRING(n FROM  6 FOR 4), 16, 10) AS DECIMAL(39))
                       *      79228162514264337593543950336 -- 65536 ^ 6
         + CAST(CONV(SUBSTRING(n FROM 11 FOR 4), 16, 10) AS DECIMAL(39))
                       *          1208925819614629174706176 -- 65536 ^ 5
         + CAST(CONV(SUBSTRING(n FROM 16 FOR 4), 16, 10) AS DECIMAL(39))
                       *               18446744073709551616 -- 65536 ^ 4
         + CAST(CONV(SUBSTRING(n FROM 21 FOR 4), 16, 10) AS DECIMAL(39))
                       *                    281474976710656 -- 65536 ^ 3
         + CAST(CONV(SUBSTRING(n FROM 26 FOR 4), 16, 10) AS DECIMAL(39))
                       *                         4294967296 -- 65536 ^ 2
         + CAST(CONV(SUBSTRING(n FROM 31 FOR 4), 16, 10) AS DECIMAL(39))
                       *                              65536 -- 65536 ^ 1
         + CAST(CONV(SUBSTRING(n FROM 36 FOR 4), 16, 10) AS DECIMAL(39))
         ;
END;
//

CREATE FUNCTION INET_NTOA6(n DECIMAL(39) UNSIGNED)
RETURNS CHAR(39)
DETERMINISTIC
BEGIN
  DECLARE a CHAR(39)             DEFAULT '';
  DECLARE i INT                  DEFAULT 7;
  DECLARE q DECIMAL(39) UNSIGNED DEFAULT 0;
  DECLARE r INT                  DEFAULT 0;
  WHILE i DO
    -- DIV doesnt work with nubers > bigint
    SET q := FLOOR(n / 65536);
    SET r := n MOD 65536;
    SET n := q;
    SET a := CONCAT_WS(':', LPAD(CONV(r, 10, 16), 4, '0'), a);

    SET i := i - 1;
  END WHILE;

  SET a := TRIM(TRAILING ':' FROM CONCAT_WS(':',
                                            LPAD(CONV(n, 10, 16), 4, '0'),
                                            a));

  RETURN a;

END;
//
DELIMITER ;	
"
	mysql -u$DBUSER -p$DBPASS -e "$SQL"
}

function readconfig
{
  if [ "$CONFIG" == "0" ]; then
    die "Please specify an OpenFPC config file with the -c option"
  fi
    # Read in configuration from $CONFIG file.
      if [ -f $CONFIG ] ; then
		    source $CONFIG || die "Unable to read config file \"$CONFIG\""
		    # Check all vars required are set in $CONFIG
		    [ -n "$ENABLE_SESSION" ] || die "ENABLE_SESSION not defined in $CONFIG"
		    [ -n "$NODENAME" ] || die "NODENAME not set in $CONFIG"
        [ -n "$PASSWD" ] || die "PASSWD file not set in $CONFIG"
        [ "$PROXY_DB_USER" == "$SESSION_DB_USER" ] && die "PROXY_DB_USER and SESSION_DB_USER in $CONFIG must be unique, they can't both be $SESSION_DB_USER"
        [ "$GUI_DB_USER" == "$SESSION_DB_USER" ] && die "GUI_DB_USER and SESSION_DB_USER in $CONFIG must be unique, they can't both be $SESSION_DB_USER"
        [ "$GUI_DB_USER" == "$PROXY_DB_USER" ] && die "GUI_DB_USER and PROXY_DB_USER in $CONFIG  must be unique, they can't both be $GUI_DB_USER"
      
        # check db names for invalid chars
        DBNAMES="$SESSION_DB_NAME $GUI_DB_NAME $PROXY_DB_NAME"

        for i in $DBNAMES 
        do
          echo $i | grep - > /dev/null && die "Error in Database name $i. Name cannot contain a '-' character."
        done


        if [ "$DBUSER" == "0" ]; then
          LOGIN=0
          COUNT=0
          while [ "$LOGIN" == "0" ] 
          do
		        echo -e "[*] Enter mysql credentials of an account with that can create/update/drop databases"
		        read -p "    DB root Username: " DBUSER
		        stty -echo
		        read -p "    DB root Password: " DBPASS
		        stty echo
            echo -e "\n"
                
            mysql -u$DBUSER -p$DBPASS -e 'SHOW DATABASES;' > /dev/null && break
            COUNT=$(($COUNT + 1))
            if [ "$COUNT" -gt "3" ]
              then
                die "[!] Too many failed attempts. Stopping."
              else 
                echo "[!] Invalid credentials. Please try again $COUNT/3." 
            fi
          done
        else 
          echo "[*] DB username & password set on command line"
        fi  
     
      else
        die "Cant find config file $CONFIG. Check -h for help"
      fi

}

function configure_session
{
  if [ "$QUIET" == "0" ]; then
		echo ---------------------------------------------------------
		echo -e "[*] Working on Instance $CONFIG ."
		read -p "    Would you like session capture ENABLED on $NODENAME? (y/n)"  SESSION_ON
		if [ "$SESSION_ON" == "y" ] 
		then
			echo -e "[-] Enabling session capture in $NODENAME config"
			sed -e 's/^ENABLE_SESSION=0/ENABLE_SESSION=1/g' -i $CONFIG
			echo -e "    Done."
		else 
			echo -e "[-] Disabling session capture in $NODENAME config"
			sed -e 's/^ENABLE_SESSION=1/ENABLE_SESSION=0/g' -i $CONFIG
      echo -e "    Done."
		fi

		if which cxtracker >/dev/null ; then
			echo -e "[-] Found cxtracker."	
		else 
			echo -e "[!] WARNING: cxtracker is NOT found on this host, and is required for session capture"
			echo -e "    You can find it here -> http://www.openfpc.org/downloads"
			echo -e "    Continuing, but don't expect session capture to work until cxtracker is installed"
		fi	
  else
    if [ "$CMD_ENABLE_SESSION" ] ; then
        sed -e 's/^ENABLE_SESSION=0/ENABLE_SESSION=1/g' -i $CONFIG
    fi
  fi

}

function configure_gui
{
    echo -e "\n[*] Enter an initial username for the first OpenFPC GUI user."
    read -p "    GUI Username: " ADMIN_USER
    stty -echo
    read -p "    GUI Password: " ADMIN_PASS
    stty echo
    echo ""
    read -p "    Email address: " EMAIL
    read -p "    Real Name: " REAL_NAME
    
    DESCRIPTION="Administrator"
    TIMEZONE="Europe/London"
    DEFAULTNODE="None"
    
    if ADMIN_HASH=$(openfpc-password -n --hash --user $ADMIN_USER --password $ADMIN_PASS)
    then
         echo HASH is $ADMIN_HASH > /dev/null
    else
        echo Error setting admin hash
    fi
    
    # Add this user to the openfpc $CONFIG to allow them to get pcaps
    # 1 Check if the user already exists

    if grep ^SHA1=$ADMIN_USER= $PASSWD > /dev/null
    then
        echo -e "[*] Found user $ADMIN_USER IN $PASSWD"
        echo -e "    Updating the password for $ADMIN_USER in to match the password just entered"
        openfpc-password -f $PASSWD --user $ADMIN_USER -password $ADMIN_PASS -a change
    else
        echo USER NOT FOUND. Adding $ADMIN_USER.
        openfpc-password -f $PASSWD --user $ADMIN_USER --password $ADMIN_PASS -a add
    fi
}


function create_proxy
{
  
  echo -e "[*] Creating proxy database\n" 
  # Test we have access

  # Check if DB already exist
  mysql -u$DBUSER -p$DBPASS -e "USE $PROXY_DB_NAME;" > /dev/null 2>&1 && die "Database $PROXY_DB_NAME already exists, won't create another one"

  # Create new DB
  mysql -u$DBUSER -p$DBPASS -e "CREATE DATABASE $PROXY_DB_NAME;" > /dev/null 2>&1 || die "Unable to create DB $PROXY_DB_NAME - Did you enter the correct user/password?"

  # Check if user already exists (maybe attached to another ofpc database)
  mysql -u$DBUSER -p$DBPASS -B -N -e "use mysql; SELECT user FROM user" |grep -w $PROXY_DB_USER > /dev/null && die "User $PROXY_DB_USER already exists. This user must be unique."
  # Create new DB user

  mysql -u$DBUSER -p$DBPASS -e "use 'mysql'; CREATE USER '$PROXY_DB_USER'@'localhost' IDENTIFIED BY '$PROXY_DB_PASS';"
  mysql -u$DBUSER -p$DBPASS -e "use $PROXY_DB_NAME; GRANT ALL PRIVILEGES ON $PROXY_DB_NAME.* TO '$PROXY_DB_USER'@'localhost';"


  echo -e "    OpenFPC Proxy DB Created.\n";
    
  # Create tables
  SQL="CREATE TABLE search (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         timestamp DATETIME,
         username VARCHAR(20),
         comment VARCHAR(100),
         search VARCHAR(500) 
         );"
  mysql -u$DBUSER -p$DBPASS $PROXY_DB_NAME -e "$SQL"
  SQL="CREATE TABLE IF NOT EXISTS session              \
        (                                                  \
        id            INT NOT NULL AUTO_INCREMENT,\
        search_id     INT UNSIGNED               NOT NULL, \
        start_time    DATETIME                   NOT NULL, \
        ip_proto      TINYINT UNSIGNED           NOT NULL, \
        ip_version    TINYINT UNSIGNED,                    \
        src_ip        DECIMAL(39,0) UNSIGNED,              \
        src_port      SMALLINT UNSIGNED,                   \
        dst_ip        DECIMAL(39,0) UNSIGNED,              \
        dst_port      SMALLINT UNSIGNED,                   \
        src_bytes     INT UNSIGNED               NOT NULL, \
        dst_bytes     INT UNSIGNED               NOT NULL, \
        total_bytes   INT UNSIGNED             NOT NULL, \
        node_name     CHAR(100),                           \
        PRIMARY KEY (id),                                  \
        INDEX src_ip (src_ip),                             \
        INDEX dst_ip (dst_ip),                             \
        INDEX dst_port (dst_port),                         \
        INDEX src_port (src_port),                         \
        INDEX start_time (start_time)                      \
        ) ENGINE=MyISAM                                    \
        ";
  mysql -u$DBUSER -p$DBPASS $PROXY_DB_NAME -e "$SQL"
    
}

function drop_proxy
{
  echo -e "[*] Removing proxy database $NODENAME"
  # Test we have access
  mysql -u$DBUSER -p$DBPASS -e 'SHOW DATABASES;' > /dev/null 2>&1 || die "Unable to connect to database"
  # Check if DB already exists
  mysql -u$DBUSER -p$DBPASS -e "USE $PROXY_DB_NAME;" > /dev/null 2>&1 || die "Database $PROXY_DB_NAME Not found!"
  mysql -u$DBUSER -p$DBPASS -e "DROP DATABASE $PROXY_DB_NAME;" > /dev/null 2>&1 || die "Database $PROXY_DB_NAME Not found!"
  mysql -u$DBUSER -p$DBPASS -e "use 'mysql'; DROP USER '$PROXY_DB_USER'@'localhost';" || die "Unable to remove user $PROXY_DB_USER"

  echo -e " -  Proxy database removed."
}

function create_gui
{

    echo CREATING GUI DATABASE
    echo ---------------------------
    # Test we have access

    # Check if DB already exists
    mysql -u$DBUSER -p$DBPASS -e "USE $GUI_DB_NAME;" > /dev/null 2>&1 && die "Database $GUI_DB_NAME already exists"

    # Create new DB
    mysql -u$DBUSER -p$DBPASS -e "CREATE DATABASE $GUI_DB_NAME;" > /dev/null 2>&1 || die "Unable to create DB $GUI_DB_NAME - Did you enter the correct user/password?"

    # Check if user already exists (maybe attached to another ofpc database)
    mysql -u$DBUSER -p$DBPASS -B -N -e "use mysql; SELECT user FROM user" |grep -w $GUI_DB_USER > /dev/null && die "User $GUI_DB_USER already exists. This user must be unique."

    # Create new DB user
    mysql -u$DBUSER -p$DBPASS -e "use 'mysql'; CREATE USER '$GUI_DB_USER'@'localhost' IDENTIFIED BY '$GUI_DB_PASS';"
    mysql -u$DBUSER -p$DBPASS -e "use $GUI_DB_NAME; GRANT ALL PRIVILEGES ON $GUI_DB_NAME.* TO '$GUI_DB_USER'@'localhost';"


    echo GUI DB Created.
    
    # Create tables
    SQL="CREATE TABLE users (
         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         username VARCHAR(20),
         realname VARCHAR(20),
         email VARCHAR(20),
         description VARCHAR(20),
         password VARCHAR(40),
         timezone VARCHAR(30),
         defaultnode VARCHAR(30)
       );"
    mysql -u$DBUSER -p$DBPASS $GUI_DB_NAME -e "$SQL"
    
    SQL="INSERT INTO users (username,password,realname,email,description,defaultnode,timezone)
        VALUES (
            '$ADMIN_USER', '$ADMIN_HASH', '$REAL_NAME', '$EMAIL', '$DESCRIPTION', '$DEFAULTNODE', '$TIMEZONE'
        );"
    mysql -u$DBUSER -p$DBPASS $GUI_DB_NAME -e "$SQL"

    echo "New user $ADMIN_USER added."        
    

}

function drop_gui
{
    echo REMOVING DATABASE
    echo ---------------------------
    # Test we have access
    mysql -u$DBUSER -p$DBPASS -e 'SHOW DATABASES;' > /dev/null 2>&1 || die "Unable to connect to database - Did you enter the correct user/password"
    # Check if DB already exists
    mysql -u$DBUSER -p$DBPASS -e "USE $GUI_DB_NAME;" > /dev/null 2>&1 || die "Database $GUI_DB_NAME Not found!"
    
    mysql -u$DBUSER -p$DBPASS -e "DROP DATABASE $GUI_DB_NAME;" > /dev/null 2>&1 || die "Database $GUI_DB_NAME Not found!"
    mysql -u$DBUSER -p$DBPASS -e "use 'mysql'; DROP USER '$GUI_DB_USER'@'localhost';" || die "Unable to remove user $GUI_DB_USER"

    echo GUI DB Dropped.

}

function create_session
{
  echo "[*] Creating Session database on $NODENAME"
	# Test we have access
	mysql -u$DBUSER -p$DBPASS -e 'SHOW DATABASES;' > /dev/null || die "Unable to connect to database"

	# Check if DB already exists
	mysql -u$DBUSER -p$DBPASS -e "USE $SESSION_DB_NAME;" > /dev/null 2>&1 && die "Database $SESSION_DB_NAME already exists"

	# Create new DB	
	mysql -u$DBUSER -p$DBPASS -e "CREATE DATABASE $SESSION_DB_NAME;" > /dev/null 2>&1 || die "Unable to create DB $SESSION_DB_NAME"

  # Check if user already exists (maybe attached to another ofpc database)
  mysql -u$DBUSER -p$DBPASS -B -N -e "use mysql; SELECT user FROM user" |grep -w $SESSION_DB_USER > /dev/null && die "User $SESSION_DB_USER already exists. This user must be unique."

  # Create new DB user
	mysql -u$DBUSER -p$DBPASS -e "use 'mysql'; CREATE USER '$SESSION_DB_USER'@'localhost' IDENTIFIED BY '$SESSION_DB_PASS';"
	mysql -u$DBUSER -p$DBPASS -e "use $SESSION_DB_NAME; GRANT ALL PRIVILEGES ON $SESSION_DB_NAME.* TO '$SESSION_DB_USER'@'localhost';"
	echo -e " -  Session DB Created"
}

function drop_session
{
	echo -e "[*] Removing session database $NODENAME";
	# Test we have access
	mysql -u$DBUSER -p$DBPASS -e 'SHOW DATABASES;' > /dev/null 2>&1 || die "Unable to connect to database"
	# Check if DB already exists
	mysql -u$DBUSER -p$DBPASS -e "USE $SESSION_DB_NAME;" > /dev/null 2>&1 || die "Database $SESSION_DB_NAME Not found!"
	mysql -u$DBUSER -p$DBPASS -e "DROP DATABASE $SESSION_DB_NAME;" > /dev/null 2>&1 || die "Database $SESSION_DB_NAME Not found!"
	mysql -u$DBUSER -p$DBPASS -e "use 'mysql'; DROP USER '$SESSION_DB_USER'@'localhost';" || die "Unable to remove user $SESSION_DB_USER"

  echo -e " -  Session database removed";
}

function showhelp
{
    echo -e ""
    echo -e "* openfpc-dbmaint version $OPENFPCVER"
    echo -e "  Create and maintain an OpenFPC databases"
    echo -e ""
    echo -e "  Usage:"
    echo -e "  openfpc-dbmaint.sh -a <action> -t <db_type> -c <config_file>\n"
    echo -e "  Mandatory arguments:"
    echo -e "      -a <action>          create / drop"
    echo -e "      -t <db_type>         session / gui / proxy"
    echo -e "      -c <config_file>     /path/to/openfpc-config.conf"
    echo -e ""
    echo -e "  Optional arguments. If not supplied they will be promoted for:"
    echo -e "      -u <username>        Mysql root username"
    echo -e "      -p <password>        Mysql root user password"
    echo -e ""
    echo -e "  Example: Creating a session database"
    echo -e "  openfpc-dbmaint -a create -t session -c /etc/openfpc/openfpc-default.conf"
    echo -e ""

}

function restartopenfpc
{
    echo -e "[*] Restarting OpenFPC Node $NODENAME"
    sudo openfpc -a stop -t $NODENAME
    sudo openfpc -a start -t $NODENAME
}
echo -e "[*] openfpc-dbmaint: Create and manage OpenFPC databases"

ARGC=$#

while getopts :a:c:t:hu:p:qs OPT; do
  case $OPT in 
  a) 
    ACTION=$OPTARG
    echo " -  Action: $OPTARG"
  ;;
  c) 
    CONFIG=$OPTARG
    echo " -  Config file: $CONFIG"
  ;;
  t) 
    TYPE=$OPTARG
    echo " -  Database type: $TYPE"
  ;;
  h)
    showhelp
    exit 1;
  ;;
  u)
    DBUSER=$OPTARG
    echo " -  Mysql DB root user is $DBUSER"
  ;;
  p) 
    DBPASS=$OPTARG
    echo " -  Mysql DB root password is set"
  ;;
  q) 
    QUIET=1
    echo " -  Quiet enabled."
  ;;
  s)
    CMD_ENABLE_SESSION=1
    echo " -  Session data will be automatically enabled in config"
  ;;
  esac  
done

case $ACTION in 
	create)
    chkroot	
    readconfig
          
    if [ "$TYPE" == "session" ]
    then
		  configure_session
      create_session
		  addfuncs
      restartopenfpc
    elif [ "$TYPE" == "gui" ]
    then
      configure_gui
      create_gui
      restartopenfpc
      echo -e "[*] DB Configured and admin user added. Now navigate to http://<ip.add.re.ss>/openfpc/\n"
    elif [ "$TYPE" == "proxy" ]
    then
      create_proxy
      restartopenfpc
    else
      die "Invalid database type $TYPE. See usage"
    fi
	;;
	drop)
    chkroot
    readconfig
    if [ "$TYPE" == "session" ]
    then
		  drop_session
    elif [ "$TYPE" == "gui" ]
    then
      drop_gui
    elif [ "$TYPE" == "proxy" ]
    then
      drop_proxy
    else
      die "ERROR: Invalid type $TYPE. See usage"
    fi
	;;
	clean)
	;;
	*)
            echo -e "\nERROR: Invalid args."
            showhelp
	;;

esac