MySQL - Create Users

Securely Managing Users in MySQL

Proper user management is the foundation of database security. Instead of using a single powerful account (like root) for everything, you should create dedicated user accounts for applications and administrators, each with the minimum necessary permissions to perform their tasks.

The CREATE USER Statement

The CREATE USER statement creates a new account in MySQL. To run it, you must be logged in as a user with the CREATE USER privilege (such as root).

Syntax

CREATE USER [IF NOT EXISTS] 'username'@'hostname'
IDENTIFIED BY 'password';

• username: The name of the new user.
• hostname: The host from which the user is allowed to connect. Common values are 'localhost' (for local connections) or an IP address. Using '%' is a wildcard that allows connection from any host, which should be used with extreme caution.
• IF NOT EXISTS: An optional clause that prevents an error if the user already exists.

Modern Authentication: caching_sha2_password

Since MySQL 8.0, the default authentication plugin is caching_sha2_password, which is more secure than the older mysql_native_password. The syntax above will use this default. If you need to support older clients that don’t understand the new plugin, you can specify the old one explicitly:

-- Creating a user for a modern application (recommended)
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'a_very_strong_password';

-- Creating a user for a legacy system (use only if necessary)
CREATE USER 'legacy_user'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'another_strong_password';

Granting Privileges: The Principle of Least Privilege

A newly created user has no permissions. You must grant them explicitly using the GRANT statement. The best security practice is the Principle of Least Privilege: grant only the permissions a user absolutely needs.

Avoid GRANT ALL PRIVILEGES ON *.* unless you are creating a true administrator account. For application users, this is dangerously excessive.

Example: A Realistic Application User

Let’s grant our app_user the ability to read and write data (SELECT, INSERT, UPDATE, DELETE) but only on the tables within the my_app_db database.

GRANT SELECT, INSERT, UPDATE, DELETE ON `my_app_db`.* TO 'app_user'@'localhost';

-- It's good practice to apply the changes immediately.
FLUSH PRIVILEGES;

Advanced User Management

Password Expiration Policies

You can enforce password security policies at the time of user creation. This example creates a user whose password expires every 90 days and who will be locked out for 1 day after 5 failed login attempts.

CREATE USER 'secure_user'@'localhost'
IDENTIFIED BY 'MyPassword123!'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

Verifying Users and Privileges

You can see what users exist and from where they can connect:

SELECT user, host FROM mysql.user;

To see the grants for a specific user:

SHOW GRANTS FOR 'app_user'@'localhost';

Creating Users with a Client Program

Automating user creation is common in deployment scripts. Here’s a secure way to do it in Python.

Python Example

import mysql.connector
from mysql.connector import errorcode

def create_app_user(admin_config, new_user, new_pass, database):
    try:
        # Connect as an administrator (e.g., root)
        with mysql.connector.connect(**admin_config) as connection:
            with connection.cursor() as cursor:
                print(f"Creating user {new_user}...")
                # Use IF NOT EXISTS to avoid errors on re-runs
                cursor.execute(f"CREATE USER IF NOT EXISTS '{new_user}'@'localhost' IDENTIFIED BY '{new_pass}'")

                print(f"Granting privileges on {database}...")
                cursor.execute(f"GRANT SELECT, INSERT, UPDATE, DELETE ON `{database}`.* TO '{new_user}'@'localhost'")

                cursor.execute("FLUSH PRIVILEGES")
                print("User created and privileges granted successfully.")

    except mysql.connector.Error as err:
        print(f"Database Error: {err}")

# --- Usage ---
admin_credentials = {
    'user': 'root',
    'password': 'your_root_password',
    'host': 'localhost'
}

create_app_user(admin_credentials, 'api_user', 'super_secret_api_pass', 'my_app_db')