If you’re new to MySQL or need a refresher on how to add a new user and grant access privileges, you’re in the right place. MySQL server allows you to create numerous user accounts and grant appropriate privileges so that the users can access and manage databases, ensuring that only authorized users can perform specific actions.
So, once you have MySQL installed on the server, you need to create a database and additional user accounts. This guide will walk you through adding a new MySQL user and granting them the necessary access privileges.
To run the commands shown in this guide, first, you need to log into the MySQL server with the MySQL root account.
mysql -u root -pHow 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';Code language: JavaScript (javascript)Replace the username and password with your desired username and password in the syntax above.
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 the hostname.
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';Code language: JavaScript (javascript)However, this user will only be able to work with the MySQL databases once they are granted additional privileges.
How to Grant Privileges to a MySQL User
After successfully creating the new user, we can grant privileges to this new user. In most cases, you’ll give privileges to MySQL users based on the particular database to which the account should have access.
Multiple types of privileges can be granted to a user account. You can find a complete 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'; Code language: JavaScript (javascript)For example, to grant all privileges to the user james on the jamesdb database, use the following command:
GRANT ALL PRIVILEGES ON jamesdb.* TO 'james'@'localhost';Code language: JavaScript (javascript)However, 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';Code language: JavaScript (javascript)Furthermore, if you want to grant access not to the entire database but only to a single table, you can achieve this as shown below.
For example, let’s grant SELECT privileges to the user james only to the salaries table on the employees‘ database:
GRANT SELECT ON employees.salaries TO 'james'@'localhost';Code language: JavaScript (javascript)Creating a New MySQL Superuser
You may need to create a superuser with permissions similar to the MySQL root user in some situations.
So, to grant a user with the same privileges as the MySQL root user, use the following command, which gives global privileges to the user james connecting via localhost:
GRANT ALL ON *.* TO 'james'@'localhost' WITH GRANT OPTION;Code language: JavaScript (javascript)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';Code language: JavaScript (javascript)In the above SQL statement, make sure to change james with your database user and localhost with the user’s host.
Furthermore, if you have forgotten your MySQL root password and are unsure how to reset it, please see our guide on the subject.
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 to 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';Code language: JavaScript (javascript)Revoke Privileges from a MySQL User Account
The syntax to revoke one or more privileges from a user account is almost identical to 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';Code language: JavaScript (javascript)Remove User from MySQL
Instead of revoking the privileges, you may want to remove that user. So, you can remove a database user using the following command:
DROP USER 'james'@'localhost';Code language: JavaScript (javascript)The command above will remove the user james and all of its privileges.
Saving Your Changes
As a final step, use the FLUSH PRIVILEGES command each time you update or change permission.
FLUSH PRIVILEGES;Conclusion
Adding a new MySQL user and granting access privileges is crucial for managing your database and ensuring that only authorized users can perform specific actions. After completing this tutorial, you should know how to add new users and grant them various permissions in a MySQL database.
Always use strong, unique passwords for your users to increase security, and consider revoking or modifying permissions as needed. With some practice, you’ll be a pro at adding and managing MySQL users in no time.
If you have any questions or feedback, feel free to comment.
