Create a New User on MySQL and Grant Permissions with 5 Easy Steps

In this guide, we’ll walk you through how to Create a New User on MySQL and Grant Permissions. MySQL is a cornerstone of modern web applications, standing as the world’s most popular open-source database management system. DB-Engines ranks MySQL as the second-most-used database, only surpassed by Oracle Database.

The longevity and widespread adoption of MySQL stem from its open-source nature and the collaborative development process refined over 25 years. This extensive history ensures compatibility with a vast array of applications and programming languages, making it a versatile choice for diverse projects.

To effectively Create a New User on MySQL and Grant Permissions, you’ll need access to a MySQL Database server. For the purposes of this tutorial, we assume you have MySQL installed on a Rocky Linux 8 system. If you haven’t already done so, you can refer to our guide on How To Install MySQL on Rocky Linux 8.

The instructions provided are generally applicable to any Linux system with MySQL installed.

For alternative installation guides, consider the following:

Let’s proceed with the steps to Create a New User on MySQL and Grant Permissions.

1. Create a New User in MySQL

First, you must log in to your MySQL shell. Use the following command:

sudo mysql

Note: If you configured a password for your MySQL root user, use this command instead:

sudo mysql -u root -p

[Image of MySQL shell login]

Once logged in, use the following syntax to create a new user:

-- Create a New User on MySQL and Grant Permissions
CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

If you intend to access the user only locally from the server, specify localhost as the host.

Alternatively, you can omit the IDENTIFIED WITH authentication_plugin clause, which will default to MySQL’s default authentication plugin, caching_sha2_password.

For instance, let’s create a user named orcauser with a strong password:

-- Create a New User on MySQL and Grant Permissions
CREATE USER 'orcauser'@'localhost' IDENTIFIED BY 'strongpassword';

[Image of creating a new user]

Note: Some PHP versions have compatibility issues with caching_sha2_password. If you’re using PHP applications (like phpMyAdmin), consider using the older, but secure, mysql_native_password plugin:

-- Create a New User on MySQL and Grant Permissions
CREATE USER 'orcauser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strongpassword';

If unsure, create a user with caching_sha2_plugin and later alter it using:

-- Create a New User on MySQL and Grant Permissions
ALTER USER 'orcauser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strongpassword';

2. MySQL Grant Permissions for Users

Now that you’ve created the user, grant the necessary privileges. The general syntax is:

-- Create a New User on MySQL and Grant Permissions
GRANT PRIVILEGE ON database.table TO 'username'@'host';

The PRIVILEGE value determines the allowed actions on the specified database and table. Grant multiple privileges by separating them with commas. Use asterisks (*) for global privileges across all databases or tables.

For example, to grant privileges to orcauser:

-- Create a New User on MySQL and Grant Permissions
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'orcauser'@'localhost' WITH GRANT OPTION;

Or, grant all privileges:

-- Create a New User on MySQL and Grant Permissions
GRANT ALL PRIVILEGES ON *.* TO 'orcauser'@'localhost' WITH GRANT OPTION;

Apply the changes by flushing the privileges:

-- Create a New User on MySQL and Grant Permissions
FLUSH PRIVILEGES;

3. Revoke User Permissions

To revoke permissions, use the following syntax:

-- Create a New User on MySQL and Grant Permissions
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

To view a user’s current permissions, use:

-- Create a New User on MySQL and Grant Permissions
SHOW GRANTS FOR 'username'@'host';

4. Delete a User in MySQL

Delete a user using the DROP command:

-- Create a New User on MySQL and Grant Permissions
DROP USER 'username'@'localhost';

Exit the MySQL shell after creating the user and granting privileges:

-- Create a New User on MySQL and Grant Permissions
exit

5. Log in to your Shell with a New MySQL User

Access the MySQL shell with the new user using:

sudo mysql -u orcauser -p

The -p flag prompts for the user’s password.

Conclusion

You’ve now learned how to Create a New User on MySQL and Grant Permissions, revoke permissions, and delete users.

Hope you found this helpful! You may also find these articles interesting:

Alternative Solutions for User Management in MySQL

While the command-line approach detailed above is effective, alternative methods exist for managing MySQL users and permissions. These options often provide a more user-friendly interface and can streamline the process. Here are two different ways to achieve the same result:

1. Using MySQL Workbench

MySQL Workbench is a powerful GUI tool developed by Oracle for managing MySQL databases. It offers a visual interface for user management, making it easier to create, modify, and delete users and grant permissions.

Explanation:

MySQL Workbench simplifies user management by providing a clear and intuitive interface. Instead of typing SQL commands, you can use the GUI to select the desired privileges and apply them to specific users and databases. This reduces the risk of syntax errors and provides a better overview of user permissions.

Steps to create a user and grant permissions using MySQL Workbench:

Connect to your MySQL server: Open MySQL Workbench and connect to your MySQL server using your root credentials or another account with sufficient privileges.
Navigate to the "Users and Privileges" section: In the Navigator panel, under "Administration," click on "Users and Privileges."
Create a new user: Click the "Add Account" button. Enter the username, hostname (e.g., localhost or % for any host), and password for the new user.
Assign privileges: In the "Schema Privileges" tab, select the database(s) and table(s) to which you want to grant permissions. Choose the specific privileges (e.g., SELECT, INSERT, UPDATE, DELETE) from the available options.
Apply changes: Click the "Apply Changes" button to save the user and their assigned privileges.

Code Example:

While MySQL Workbench doesn’t directly involve code, it generates the equivalent SQL commands behind the scenes. For example, if you create a user named workbenchuser with SELECT privilege on the mydatabase.mytable table, Workbench would execute a command similar to:

GRANT SELECT ON mydatabase.mytable TO 'workbenchuser'@'localhost';
FLUSH PRIVILEGES;

2. Using phpMyAdmin

phpMyAdmin is a web-based tool designed to manage MySQL databases. It provides a user-friendly interface for performing various database operations, including user management.

Explanation:

phpMyAdmin offers a convenient way to manage MySQL users and permissions through a web browser. This is especially useful for users who prefer a visual interface or who don’t have direct access to the server’s command line. Like MySQL Workbench, it abstracts the underlying SQL commands, making the process more accessible to less experienced users.

Steps to create a user and grant permissions using phpMyAdmin:

Access phpMyAdmin: Open your web browser and navigate to your phpMyAdmin installation (usually http://localhost/phpmyadmin).
Log in to your MySQL server: Log in using your root credentials or another account with sufficient privileges.
Navigate to the "Users" section: In the top navigation bar, click on "Users."
Add a new user: Click the "Add user account" link. Enter the username, hostname (e.g., localhost or % for any host), and password for the new user.
Assign database privileges: In the "Database-specific privileges" section, select the database to which you want to grant permissions. Choose the desired privileges from the available options.
Global privileges: If you need to assign global privileges, you can find these options further down the page.
Apply changes: Click the "Go" button at the bottom of the page to create the user and apply the assigned privileges.