Automating MySQL User Creation with Read-Only Access Using Bash

Managing databases efficiently requires secure and controlled access. When it comes to granting database users read-only access, automation can simplify the process, especially in environments where new users are created frequently. In this article, we’ll demonstrate how to create a bash script that automates adding a read-only user to a MySQL database, with options for specifying the username, database, and host.

Why Automate MySQL User Creation?

Database security is paramount, and part of that responsibility involves controlling user access. A read-only user is a common role assigned to those who need to view but not modify data. Manually creating these users can be time-consuming, particularly if done repeatedly in a production environment.

By creating a bash script, we can automate this process, ensuring consistency and efficiency. The script will:

1. Prompt for the necessary inputs (username, database, and host).
2. Automatically generate a secure password.
3. Set up the user with the correct permissions.

Prerequisites

Before we dive into the script, you’ll need the following:

• MySQL installed and configured on your server.
• Access to the MySQL root account (or another MySQL user with GRANT privileges).
• OpenSSL installed to generate random passwords (optional but recommended for secure password generation).

The Bash Script

Here’s the complete bash script that automates creating a MySQL user with read-only access.

#!/bin/bash

generate_password() {
    echo $(openssl rand -base64 12)
}

read -p "Enter the MySQL username you want to create: " username
read -p "Enter the database name for read-only access: " dbname
read -p "Enter the host (e.g., localhost, %, or IP address): " host

password=$(generate_password)

mysql <<MYSQL_SCRIPT
CREATE USER '${username}'@'${host}' IDENTIFIED BY '${password}';
GRANT SELECT ON ${dbname}.* TO '${username}'@'${host}';
FLUSH PRIVILEGES;
MYSQL_SCRIPT

echo "User '${username}' has been created with read-only access to database '${dbname}' from host '${host}'."
echo "Password: ${password}"

How It Works

1. Input Prompts: The script asks for three essential pieces of information:

   • The MySQL username to be created.
   • The database for which read-only access is granted.
   • The host from which the user will connect (this could be localhost, % for all hosts, or a specific IP address).

2. Password Generation: A random password is generated using the openssl library. This password will be assigned to the new MySQL user. Using random passwords is a best practice for enhancing security.

3. MySQL User Creation: The script connects to the MySQL database using the root user. It creates the new user with the provided username, host, and password, and grants SELECT privileges on the specified database, which allows the user to view data but not modify it.

4. Flushing Privileges: After granting the permissions, the script runs FLUSH PRIVILEGES to ensure that MySQL applies the changes immediately.

5. Output: Once the user is created, the script outputs the username, host, and generated password. This makes it easy for the administrator to note down the credentials for distribution to the new user.

Benefits of Using the Script

• Simplicity: Instead of manually entering MySQL commands, the script automates everything in one go.
• Security: The script generates strong, random passwords, reducing the risk of weak credentials.
• Efficiency: If you're regularly creating new users, this script will save time and prevent errors by automating the process.
• Consistency: Using automation ensures that the same steps are followed each time, eliminating human error.

Running the Script

Step 1: Save the Script

First, save the script to a file, for example, create_readonly_user.sh.

Step 2: Make the Script Executable

To make the script executable, run the following command in your terminal:

chmod +x create_readonly_user.sh

Step 3: Run the Script

Execute the script by typing:

./create_readonly_user.sh

The script will then prompt you for the username, database name, and host. After providing this information, the user will be created, and their credentials will be displayed on your terminal.

Example Output

Enter the MySQL username you want to create: read_user
Enter the database name for read-only access: sample_db
Enter the host (e.g., localhost, %, or IP address): %

User 'read_user' has been created with read-only access to database 'sample_db' from host '%'.
Password: j3Fv93hH2cA=

In this example, the user read_user is created with access to the sample_db database. The user can connect from any host (%), and a randomly generated password is assigned.

Customizing the Script

This script can be easily customized to fit your specific needs. For example, you can:

• Change the permissions granted (e.g., give more advanced permissions like INSERT, UPDATE, etc.).
• Modify the password generation function to suit your security policies.
• Add error checking to ensure the MySQL commands are successful.

Conclusion

This simple bash script takes the hassle out of manually creating MySQL users with read-only access. By automating this process, you can save time, reduce errors, and improve security through strong, random password generation. Whether you're managing a small MySQL instance or a large-scale environment, this automation can significantly streamline your workflow.

Now, whenever you need to add a new read-only user, simply run this script and let it handle the rest!