dba:mysql:mysql_database_creator
This is an old revision of the document!
Table of Contents
[SCRIPT] 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
dba/mysql/mysql_database_creator.1644577953.txt.gz · Last modified: 2022/02/11 11:12 by dodger