How to Add a New MySQL User and Grant Access Privileges

For security reasons, it is generally better to create and handle data as specific MySQL users.

Add MySQL User

In this tutorial, we are going to add a new user in MySQL and grant different types of privileges on a MySQL database.

MySQL server allows us to create numerous user accounts and grant appropriate privileges so that the users can access and manage databases. Once you have MySQL installed on the server, you need to create a database and additional user accounts.

Related: How to Run MySQL in Docker Container: A Simple, Easy to Follow Guide

In order to run the following commands, first you need to log into the MySQL Server with the MySQL root account.

mysql -u root -p

How to Create a New MySQL User

The CREATE USER statement creates a new user in the MySQL database server. Here is the basic syntax of the statement:

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

In the syntax above, make sure to replace the username and password with your desired username and password.

Set the hostname to localhost if you want the user to be able to connect to MySQL Server only from the localhost, which means “this computer”. If you want the user to be able to connect from any host, use the % wildcard as host name.

For example, we will create a user with the name james, and the password MyStrongPass123 using the following command:

CREATE USER 'james'@'localhost' IDENTIFIED BY 'MyStrongPass123';

However, this user will not be able to work with any of the MySQL databases until they are granted additional privileges.

How to Grant Privileges to a MySQL User

Right after successfully creating the new user, we can grant privileges to this new user. In most cases, you’ll be granting privileges to MySQL users based on the particular database that account should have access to. 

There are multiple types of privileges that can be granted to a user account. You can find a full list of privileges supported by MySQL here.

  • ALL PRIVILEGES – Grants all privileges to a user account.
  • ALTER – The user can change the structure of a table or database.
  • CREATE – The user account is allowed to create databases and tables.
  • DROP – The user account is allowed to drop databases and tables.
  • DELETE – The user account is allowed to delete rows from a specific table.
  • INSERT – The user account is allowed to insert rows into a specific table.
  • SELECT – The user account is allowed to read a database.
  • UPDATE – The user account is allowed to update table rows.

To provide a user with access to the MySQL database and give permissions, you generally need to use the following GRANT statement:

GRANT permission_type ON privilege_level TO 'username'@'hostname'; 

To grand all privileges to the user james on the jamesdb database, use the following command:

GRANT ALL PRIVILEGES ON jamesdb.* TO 'james'@'localhost';

Type the following to grant only SELECT and INSERT privileges to the user james on the jamesdb database:

GRANT SELECT, INSERT ON jamesdb.* TO 'james'@'localhost';

Grand SELECT privileges to the user james only to salaries table on the employees database:

GRANT SELECT ON employees.salaries TO 'james'@'localhost';

In some cases you may wish to create another ‘super user’. To grant a user with the same privileges as the MySQL root user, use the following command, which grants global privileges to the user james connecting via localhost:

GRANT ALL ON *.* TO 'james'@'localhost' WITH GRANT OPTION;

Change a MySQL User Account Password

Suppose you want to change the password for the james  user that connects from the localhost to NewStrongPass123, you need to execute the following SQL statement:

ALTER USER 'james'@'localhost' IDENTIFIED BY 'NewStrongPass123';

In the above SQL statement, make sure to change james with your database user and localhost with the user’s host.

Show Privileges for a User in MySQL

In MySQL, you can use the SHOW GRANTS command to display all grant information for a user. Let’s take some examples.

The following statement uses the SHOW GRANTS statement to display the privileges granted for the current user:

SHOW GRANTS;

To view the grants for a MySQL user, you can use SHOW GRANTS while specifying the username:

SHOW GRANTS FOR 'james'@'localhost';

Revoke Privileges from a MySQL User Account

The syntax to revoke one or more privileges from a user account is almost identical as when granting privileges.

If you need to revoke privileges from the user james on a jamesdb database, apply the syntax that is similar to the one you used when granting permissions:

REVOKE ALL PRIVILEGES ON jamesdb.* FROM 'james'@'localhost';

Remove User from MySQL

Instead of revoking the privileges, you may want to remove that user as well. So, you can remove a database user using the following command:

DROP USER 'james'@'localhost';

The command above will remove the user james together with all of its privileges.

Saving Your Changes

As a final step, each time you update or change a permission be sure to use the FLUSH PRIVILEGES command.

FLUSH PRIVILEGES;

Conclusion

After completing this tutorial, you should have a sense of how to add new users and grant them a variety of permissions in a MySQL database.

If you have any questions or feedback, feel free to leave a comment.

If this guide has helped you, please consider buying us a coffee.

Buy me a coffee!

Your support and encouragement are greatly appreciated!

Leave a Reply

Your email address will not be published.

Latest from Linux Knowledge