How to Create User in MySQL? (Step-by-Step Guide Create User in MySQL)

MySQL is a widely used open-source relational database management system (RDBMS). It is often a key component of the LAMP stack, which includes Linux, Apache, MySQL, and PHP. Known for its popularity and extensive adoption, MySQL is one of the most commonly used open-source databases globally.

In this guide, you will learn how to create user in MySQL and assign them the necessary permissions to carry out various tasks.

Prerequisites

You need access to a MySQL database to follow this guide. This guide is based on a MySQL database installed on an Ubuntu 22.04 virtual private server, but the instructions generally apply to other environments.

The steps for creating a new MySQL user and assigning permissions are similar across different operating systems and server setups.

How to Create User in MySQL (Grant All Privileges MySQL)

When MySQL is installed, it sets up a default root user account with full access to the MySQL server. This account has complete control over all databases, tables, and users. However, it's best to reserve the root account for administrative tasks and create separate user accounts for other purposes. 

This section explains how to create a new MySQL user and assign them specific permissions using the root account.

On Ubuntu systems with MySQL 5.7 and newer, the root MySQL user typically authenticates via the auth_socket plugin instead of a password. This means that the MySQL root user matches the operating system user executing the command. 

You can easily check the installed MySQL version using this command:

To access the MySQL root account, you need to use sudo with the MySQL client command, granting it the necessary privileges to connect as the root MySQL user.

If the root MySQL user is set to authenticate with a password, you’ll need a different command to access the MySQL shell. Use the following command to log in as a regular user and enter the root user’s password to gain administrative privileges within the database:

# mysql -u root -p

Create New User in MySQL

After accessing the MySQL prompt, you can create a new user by using the CREATE USER statement. The general syntax for this command is as follows:

> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

To create a new user in MySQL, start by specifying the username after the CREATE USER command. Follow the username with an @ symbol and the hostname the user will connect from. If the user will only access the database locally from the Ubuntu server, you can use localhost. While enclosing the username and hostname in single quotes is optional, it’s recommended to avoid potential errors.

You can choose from different authentication plugins for your user. The auth_socket plugin, mentioned earlier, offers strong security and eliminates the need for users to enter a password. However, it restricts remote connections, which can be an issue for external programs requiring database access.

Alternatively, you can skip the WITH authentication_plugin part of the syntax to default to MySQL's caching_sha2_password plugin. This plugin is recommended for password-based authentication due to its robust security features.

Use the following command to create user in MySQL that authenticates with caching_sha2_password. Replace sammy with your desired username and password with a strong, secure password:

> CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';

Some PHP versions have a known issue with the caching_sha2_password plugin that can cause compatibility problems. If you plan to use the database with a PHP application, such as phpMyAdmin, you may prefer to create a user that uses the older but still secure mysql_native_password plugin instead. You can create user in MySQL with the following command:

> CREATE USER 'techUser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

If you're unsure, you can initially create the user with the caching_sha2_password plugin and switch to mysql_native_password later if needed. To make this change, use the following command:

> ALTER USER 'techUser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Once the new user is created, you can proceed to assign the necessary privileges to them.

Create Database in MySQL

You need to create the database and assign the necessary privileges to the user. To create database in MySQL, run the following command:

> CREATE DATABASE testdatabase;

This will create an empty database named “testdatabase” that you can use for your application.

Grant Privileges to the User: After creating the database, you need to give the user testUser full access to it. This is done by running:

> GRANT ALL PRIVILEGES ON testdatabase.* TO 'techUser'@'localhost';

This command grants techUser complete control over the test database, allowing them to perform all actions (like SELECT, INSERT, UPDATE, DELETE, etc.) on all tables within that database. The localhost part means that the user can only access the database from the local server.

This ensures that the user has the required permissions to manage the specific database, while also restricting their access to only the mytestapp database and from the local server.

Grant All Permissions to User in MySQL (Grant All Privileges MySQL)

To grant a user-specific privileges in MySQL, the general syntax is:

> GRANT PRIVILEGE ON database.table TO 'username'@'host';

Here, PRIVILEGE specifies the actions the user can perform on a given database and table. You can assign multiple privileges in one command, separated by commas. To grant global privileges (for all databases and tables), use asterisks (*) to represent "all."

For example, the following command grants a user the ability to create, alter, drop databases and tables, as well as insert, update, delete data, and query with SELECT. It also allows the creation of foreign keys and performing FLUSH operations with the RELOAD privilege:

> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'techUser'@'localhost' WITH GRANT OPTION;

The WITH GRANT OPTION allows the user to grant their privileges to others. Be sure to assign only the necessary permissions to your users for security reasons.

You can find a complete list of available privileges in the official MySQL documentation.

After creating a user or granting privileges, some guides suggest running the FLUSH PRIVILEGES command to reload the grant tables and apply the new permissions:

FLUSH PRIVILEGES;

However, according to MySQL's official documentation, the grant tables are automatically reloaded into memory when you use account management statements like GRANT, so FLUSH PRIVILEGES is not strictly necessary. That said, running it won't harm the system.

Important Note: Granting a MySQL user the ALL PRIVILEGES permission provides them with extensive superuser access, similar to the root user. This command would look like:

> GRANT ALL PRIVILEGES ON *.* TO 'techUser'@'localhost' WITH GRANT OPTION;

However, this level of access should be used with caution, as it gives the user full control over all databases on the server. Anyone with access to this user account will have the ability to modify or delete any database.

If you need to revoke permissions, the syntax is similar to granting them, but you’ll use FROM instead of TO:

> REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

To view a user's current permissions, you can run:

> SHOW GRANTS FOR 'username'@'host';

Drop User in MySQL

If you want to delete a user, use the DROP USER command:

> DROP USER 'username'@'localhost';

Once you've finished configuring the user and privileges, you can exit the MySQL client:

> exit

To log in as your new MySQL user in the future, use the following command:

# mysql -u techUser -p

The -p flag will prompt you for the user's password.

Conclusion

In this guide, you have learned how to create user in MySQL and grant all permissions to user in MySQL. You can now further explore and experiment with different permission settings for your users or dive deeper into advanced MySQL configurations.

For additional details on MySQL fundamentals, feel free to explore further resources and SQL documentation.

BlueServers.com – Dedicated Servers Across the Globe!

BlueServers is a dynamic and passionate hosting provider committed to delivering exceptional online solutions. 

If you're searching for top-tier hosting services backed by a dedicated team of experts, your search ends here. We're here to ensure you receive the best customer experience possible. Get started with us today and elevate your online presence!