Create a MySQL User with a Secure Password

This article provides a straightforward, step-by-step guide on how to create a new user account in MySQL and assign a secure password. You will learn the exact SQL commands needed to create the user, grant the appropriate database privileges, and apply the changes safely to ensure your database remains secure.

Step 1: Log in to the MySQL Server

To create a new user, you must first log in to your MySQL server as the root user or another account with administrative privileges. Open your terminal and run the following command:

mysql -u root -p

Enter your administrative password when prompted.

Step 2: Create the New User with a Secure Password

Once logged in, use the CREATE USER statement to define the new account and its password. For security purposes, ensure the password is complex, containing a mix of uppercase letters, lowercase letters, numbers, and special symbols.

Run the following command, replacing new_user with your desired username and your_strong_password_here with a highly secure password:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'your_strong_password_here';

(Note: Using 'localhost' restricts the user to connecting only from the local machine. If the user needs to connect from any remote host, replace 'localhost' with '%').

Step 3: Grant Privileges to the User

By default, a newly created MySQL user has no permissions to read or modify any databases. You must explicitly grant them access.

To grant all privileges on a specific database to the new user, execute:

GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost';

If you want to follow the principle of least privilege, grant only the specific permissions the user needs, such as:

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'new_user'@'localhost';

Step 4: Apply the Changes

To ensure that the newly granted privileges are applied immediately, reload the grant tables using the following command:

FLUSH PRIVILEGES;

Step 5: Test the Connection

You can verify the setup by exiting the administrative session and logging in with the newly created user:

EXIT;

Then, log in using the new credentials:

mysql -u new_user -p

Enter the secure password you created in Step 2 to establish the connection.