How To Create New MySQL User and Grant Privileges

July 18, 2024

Introduction

After installing MySQL, the root user has full access to the entire database installation. For security reasons, administrators must create specific user accounts to allow non-root users to manage particular data sets.

Learn how to create MySQL user accounts and manage their permissions and privileges.

Creating a new MySQL user.

Prerequisites

  • A Linux server with MySQL installed and running.
  • Access to a terminal window/command line.
  • A user account with sudo privileges.
  • A MySQL root user account.

How to Create New MySQL User

To create a new MySQL user:

1. Open a terminal window and enter the following command to launch the MySQL shell as the root user:

sudo mysql -u root -p

2. Type the root password and press Enter to access the mysql> shell.

Accessing MySQL from Linux terminal.

3. When creating new users, specify the host to ensure only authorized machines can connect to the MySQL server. For users working on the same machine as the MySQL server, use 'localhost' to streamline access:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace username and password with a username and password of your choice.

4. For users who need to connect remotely, specify their IP addresses (or hostname) to enable secure remote access:

CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';

Replace username, ip_address, and password with your desired values.

Note: Before users can log in from a remote machine, the MySQL server must be configured to allow remote connections.

5. More lenient access controls are common in development environments. Enter the following command to create a MySQL user that can connect from any machine:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Use a strong and complex password and avoid using this option in a production environment due to security concerns.

Note: Learn how to list MySQL database users to monitor active user accounts and manage database security effectively.

How to Grant Permissions in MySQL

Permissions are actions the user is allowed to perform in a database. Depending on how much authority you want users to have, grant them one, several, or all the following privileges:

PrivilegeActions
ALL PRIVILEGESFull access to the database.
INSERTInsert rows into tables.
DELETERemove rows from tables.
CREATECreate entirely new tables and databases.
DROPDrop (remove) entire tables and databases.
SELECTRead the information in the databases.
UPDATEUpdate table rows.
GRANT OPTIONThe user can modify other user account privileges. This option is usually reserved for the MySQL root user.

The syntax for granting privileges to a user account on a local machine is:

GRANT permission_type ON database_name.table_name TO 'username'@'localhost';

The database_name.table_name parameter is mandatory, but you can use wildcard characters to broaden the scope of the privileges:

*.*Grant access to all databases and tables.
database_name.*Grant access to all tables within a specific database.
database_name.table_nameGrant access to a specific table within a specific database.

Note: If you need to find a specific database, learn how to list and filter databases in MySQL.

Grant All Privileges

The ALL PRIVILEGES permission gives the user complete control over the specified databases and tables and allows them to perform any operation.

Follow these steps:

1. Use one of the following commands to grant ALL PRIVILEGES to a MySQL user:

All databases

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

All tables in a specific database

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

Specific table

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'localhost';

Replace username with the actual username and hostname with the hostname or IP address of the user's machine.

2. Flush the MySQL privilege tables to apply the changes:

FLUSH PRIVILEGES;

3. Enter the following command to check if the privileges have been awarded:

SHOW GRANTS FOR 'username'@'localhost';

Note: For more information on how to check existing user privileges on a MySQL server, read our article MySQL show user privileges.

Grant Insert Privileges

The INSERT privilege allows MySQL users to add new table rows and input data into the database.

1. Enter one of the following statements to grant INSERT privileges:

All databases

GRANT INSERT ON *.* TO 'username'@'localhost';

All tables in a specific database

GRANT INSERT ON database_name.* TO 'username''@'localhost';

Specific table

GRANT INSERT ON database_name.table_name TO 'username'@'localhost';

Replace the placeholder parameters with the actual MySQL user credentials.

2. To apply the changes, flush the privileges:

FLUSH PRIVILEGES;

3. Verify that the user has been granted the new privileges:

SHOW GRANTS FOR 'username'@'localhost';
User granted INSERT privileges in MySQL.

Grant Delete Privileges

The DELETE privilege lets users remove incorrect or outdated database entries by deleting table rows.

Follow these steps:

1. Use one of the following commands to grant DELETE privileges to a MySQL user:

All databases

GRANT DELETE ON *.* TO 'username'@'localhost';

All tables in a specific database

GRANT DELETE ON database_name.* TO 'username''@'localhost';

Specific table

GRANT DELETE ON database_name.table_name TO 'username'@'localhost';

Replace username, database_name, and table_name with the names you are using in your MySQL setup.

2. Apply the changes by flushing MySQL privileges:

FLUSH PRIVILEGES;

3. Confirm the new privileges have been awarded:

SHOW GRANTS FOR 'username'@'localhost';
Command to grant DELETE privilege in MySQL.

Grant Create Privileges

The CREATE privilege allows a MySQL user to create new tables and databases.

Follow these steps:

1. Use one of the following commands to grant CREATE privileges:

All databases

GRANT CREATE ON *.* TO 'username'@'localhost';

All tables in a specific database

GRANT CREATE ON database_name.* TO 'username''@'localhost';

Specific table

GRANT CREATE ON database_name.table_name TO 'username'@'localhost';

The database_name, table_name, username, and localhost are example values. Replace them with the actual database name, table name, username, and hostname or IP address.

2. To ensure the changes take effect immediately, refresh the privilege tables:

FLUSH PRIVILEGES;

3. Check if the new privileges have been applied:

SHOW GRANTS FOR 'username'@'localhost';
Command to check CREATE privilege on MySQL server.

Grant Drop Privileges

A user with DROP privileges can delete entire tables and databases. To do so:

1. Enter one of the following commands to grant DROP privileges to a MySQL user:

All databases

GRANT DROP ON *.* TO 'username'@'localhost';

All tables in a specific database

GRANT DROP ON database_name.* TO 'username''@'localhost';

Specific table

GRANT DROP ON database_name.table_name TO 'username'@'localhost';

Substitute the values in the example command with the actual parameters in your MySQL setup.

2. Refresh the MySQL privilege tables:

FLUSH PRIVILEGES;

3. Confirm that the user has DROP privileges:

SHOW GRANTS FOR 'username'@'localhost';
Granting the DROP pribilege in MySQL.

Grant Select Privileges

The SELECT privilege allows MySQL user accounts to read data from tables, which is ideal for users who need to view or query the database without making any changes.

To configure this privilege:

1. Use one of the following commands to grant SELECT privileges:

All databases

GRANT SELECT ON *.* TO 'username'@'localhost';

All tables in a specific database

GRANT SELECT ON database_name.* TO 'username''@'localhost';

Specific table

GRANT SELECT ON database_name.table_name TO 'username'@'localhost';

Replace the values in the command with the actual parameters for your MySQL user and database.

2. Reload the MySQL privilege tables:

FLUSH PRIVILEGES;

3. Confirm that the user has been granted SELECT privileges:

SHOW GRANTS FOR 'username'@'localhost';
Granting the SELECT privilege in MySQL database.

Grant Update Privileges

The UPDATE privilege enables users to alter or correct data records in MySQL databases.

Follow these steps:

1. Enter one of the following commands to grant UPDATE privileges:

All databases

GRANT UPDATE ON *.* TO 'username'@'localhost';

All tables in a specific database

GRANT UPDATE ON database_name.* TO 'username''@'localhost';

Specific table

GRANT UPDATE ON database_name.table_name TO 'username'@'localhost';

Substitute the database_name, table_name, username, and localhost example values with the actual parameters on your system.

2. To ensure the privileges are updated, run:

FLUSH PRIVILEGES;

3. Use the following command to verify if the user has UPDATE privileges:

SHOW GRANTS FOR 'username'@'localhost';
Steps to grant the UPDATE privilege in MySQL.

How to Revoke Privileges in MySQL

You can use the REVOKE command to remove specific privileges from existing users without deleting their accounts.

The basic syntax for the REVOKE statement is:

REVOKE permission_type ON database.table FROM 'username'@'localhost';

To delete a MySQL user account completely, which removes all their privileges and database access, use the DROP command:

DROP USER 'username'@'localhost';

After revoking user privileges or deleting an account, it is essential to flush the privileges in MySQL:

FLUSH PRIVILEGES;

This ensures that the changes take effect immediately and prevents unauthorized access due to outdated privilege settings.

Conclusion

The steps in this guide showed you how to create users and grant different types of permissions in a MySQL database.

Download this comprehensive MySQL Commands Cheat Sheet to manage MySQL databases and users even more efficiently.

Was this article helpful?
YesNo
Sofija Simic
Sofija Simic is an experienced Technical Writer. Alongside her educational background in teaching and writing, she has had a lifelong passion for information technology. She is committed to unscrambling confusing IT concepts and streamlining intricate software installations.
Next you should read
MySQL Performance Tuning and Optimization Tips
April 25, 2024

The performance of MySQL databases is an essential factor in the optimal operation of your server. This tutorial shows how to avoid the common pitfalls concerning MySQL queries and system setup.
Read more
How to Repair MySQL Database
August 5, 2021

A corrupt database can happen at any moment. Try some of these methods to repair your MySQL database quickly.
Read more
How To Use MySQL JOINS
April 20, 2021

This tutorial explains what MySQL JOINS are and how to use them to combine information located in multiple tables, producing a unified result.
Read more
How to Use MySQL String Functions
August 12, 2021

MySQL string functions allow you to manipulate character string data using the SELECT statement. Learn to use MySQL string functions with this comprehensive tutorial.
Read more