User Tools

Site Tools


mysql_database_creator

Description

This very simple script will allow you to grant any linux user the avility of create databases without giving him any additional privilege (just sudo for the script).

Pre-requisites

  • Create a Mysql user with the following grants:
GRANT CREATE, RELOAD, SHOW DATABASES, CREATE USER ON *.* TO 'database_creator'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD' ;
GRANT INSERT ON `mysql`.`db` TO 'database_creator'@'localhost' ;
  • That will allow us to create databases, users, make some checks and reload privs, while we don't create a new “root”…

Code

database_creator.sh
#!/bin/bash
CREATORUSER="database_creator"
CREATORPASS="YOUR_PASSWORD"
 
MYSQLCONN="mysql -u ${CREATORUSER} -p${CREATORPASS}"
 
 
usage()
{
        echo "$0 <NEW_DATABASE_NAME>"
        echo "<NEW_DATABASE_NAME> must not have spaces and punctuation symbols excepting _ (underscore)"
        exit 1
}
check_dbname()
{
        local RESTRICTEDNAMES="information_schema test mysql information-schema"
        if [[ "${NEWDB}" =~ ^${RESTRICTEDNAMES// /|}$ ]] ; then
                echo "Name not allowed"
                exit 2
        fi
        ACTUALDDBB="$(echo "SHOW DATABASES ;" | ${MYSQLCONN} | egrep -v "^(Database|${RESTRICTEDNAMES// /|})$")"
        if [[ "${ACTUALDDBB}" =~ ^${NEWDB}$ ]] ; then
                echo "Database exists: ${BASH_REMATCH[0]}"
                exit 3
        fi
 
}
 
create_database()
{
        local let RES=1
        echo "CREATE DATABASE ${NEWDB} ;" | ${MYSQLCONN}
        RES=$?
        echo $RES
        if [ $RES -eq 0 ] ; then
                echo "Creation successful"
        else
                echo "Some error creating ddbb, please report"
                exit 1
        fi
}
 
create_user()
{
        local let RES=1
        local CLEANPASSWORD="$(cat /dev/urandom|tr -dc "a-zA-Z0-9-_"|fold -w 25|head -1)"
        echo "CREATE USER ${NEWDB::13}@localhost identified by '${CLEANPASSWORD}' ;" | ${MYSQLCONN}
        echo "INSERT INTO db VALUES ('localhost','${NEWDB}','${NEWDB::13}','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');" |  ${MYSQLCONN} mysql
        echo "FLUSH PRIVILEGES ;"  |  ${MYSQLCONN}
        echo "##################################################################"
        echo "User created for database: ${NEWDB}"
        echo " Username: ${NEWDB::13}"
        echo " Password: ${CLEANPASSWORD}"
        echo "##################################################################"
        echo "Press enter to finish"
        read
}
 
 
[ $g{#@} -ne 1 ] && usage
NEWDB="$@"
 
 
check_dbname
create_database
create_user

Important variables

Variable Name Default Description
CREATORUSER
database_creator
The Username with GRANT created on Pre-req's steps
CREATORPASS
YOUR_PASSWORD
The password for the privilege user
MYSQLCONN
mysql -u ${CREATORUSER} -p${CREATORPASS}
Just the connection command with the above credentials

Install

To install it, I just recommend to move it wherever the user can't read it, change the permissions and create the sudoers line:

mkdir -p /home/apps/scripts/
mv database_creator.sh /home/apps/scripts/
chmod 700 /home/apps/scripts/database_creator.sh
chown root:root /home/apps/scripts/database_creator.sh
echo "username         ALL=(ALL)       NOPASSWD: /home/apps/scripts/new_database.sh" >> /etc/sudoers
mysql_database_creator.txt · Last modified: 2012/04/18 16:21 by dodger