Automate user creation and privilege assignment

Automate user creation and privilege assignment

Shell script for MariaDB DBAs to automate the user creation and privilege assignment process. This script can be adjusted for various other DBA tasks as well. The script prompts for input to create a user, grants privileges, and verifies the setup.

Shell Script: create_mariadb_user.sh




#!/bin/bash
# This script automates the process of creating a new MariaDB user and granting privileges.
# Prompt for input
echo "Enter MariaDB root password:"
read -s ROOT_PASS
echo "Enter the new username:"
read USERNAME
echo "Enter the password for the new user:"
read -s PASSWORD
echo "Enter the database name to grant privileges (leave blank for all databases):"
read DB_NAME
# Define SQL commands
if [ -z "$DB_NAME" ]; then
    # Grant privileges on all databases
    PRIVILEGES="GRANT ALL PRIVILEGES ON *.* TO '$USERNAME'@'localhost' IDENTIFIED BY '$PASSWORD';"
else
    # Grant privileges on a specific database
    PRIVILEGES="GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$USERNAME'@'localhost' IDENTIFIED BY '$PASSWORD';"
fi
FLUSH="FLUSH PRIVILEGES;"
# Execute the SQL commands
mysql -u root -p$ROOT_PASS -e "CREATE USER '$USERNAME'@'localhost' IDENTIFIED BY '$PASSWORD'; $PRIVILEGES $FLUSH"
# Verify the user creation
echo "Verifying the new user setup..."
mysql -u root -p$ROOT_PASS -e "SHOW GRANTS FOR '$USERNAME'@'localhost';"
echo "User creation and privilege assignment completed."

How to Use the Script

1. Save the script as create_mariadb_user.sh.

2. Make it executable by running: 

3. chmod +x create_mariadb_user.sh 

4. ./create_mariadb_user.sh

    Follow the prompts to enter the root password, new username, user password, and database name.

5. Explanation

   • Variables: The script takes input for the root password, new user details, and the database name.
   • SQL Execution: It combines the input into SQL commands and runs them using the mysql command-line client.
   • Verification: After creating the user, the script verifies the setup by showing the granted privileges for the new user.

   This script simplifies user management tasks for DBAs,.