MySQL Create User and Grant Permissions: How To

If you work with MySQL, there will be a point when you’ll need to create a new user.

This could be for another part of the system or another developer on your team. Or perhaps you want to use a user other than the default “root” account on MySQL.

In this article, you’ll learn:

• How to create a new user
• How to give it the permissions it needs
• How to check that the new account works
• How to delete a user if you no longer need it

Let’s get into the article.

Log in to MySQL

The first step to creating a new MySQL user is to log in to the database. You’ll need to log in using an account that has the privilege to create new users. This will most likely be the root account, but if not, you can adjust the commands here.

You can either log in using the command line (Terminal or Command Line) or using an IDE such as MySQL Workbench.

 

Command Line

To log in as root:

Open the command line or terminal.

Then, run the following command:

mysql -u root -p

This will run the mysql command and attempt to log in. The -u indicates that the following parameter is a username, which is root. The -p indicates that the password is not provided and you will need to enter it. This is for security reasons: you should enter your password when prompted, rather than have it shown in plain text on the command line.

Once you run this command, enter your password, and you’ll be at the MySQL prompt:

 

MySQL Workbench (or other IDE)

If you prefer to do this using an IDE, such as MySQL Workbench, you can log in using the IDE.

Similar to the command line, you’ll need to log in as the root account or with another user that has privileges to create new users.

For example, here’s my connection screen for root in MySQL Workbench on my computer:

Here’s what it looks like using JetBrains DataGrip:

Once you’ve logged in to your database, you should be ready to create a new user.

 

Create User

To create a new user in MySQL, we run the MySQL CREATE USER command:

CREATE USER 'new_username'@'localhost' IDENTIFIED BY 'user_password';

To run this command yourself:

• Change the new_username to the username you want to create.
• Change the user_password to the password you want for this new user.
• Run the command in either the command line or a new SQL window in your IDE.

Also, the word “localhost” means this computer. If you run your database somewhere else, then change the word localhost to your server’s address.

For example, to create a new user called “bob” with the password “electric”, then the command would be:

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'electric';

If you run the MySQL CREATE USER at the terminal, it will look like this:

Press Enter and the command will run.

If you run the command in your IDE, such as MySQL Workbench, it will look like this:

Click the Execute button and the query will run.

 

MySQL Privileges

We’ve now created a new user.

However, the new user will not be able to do anything. This is because they don’t have any privileges. You may be able to log in as this user, but you won’t be able to create any tables or select any data.

By default, a new user is not given any privileges. Users need privileges on the database to be able to take certain actions, such as creating tables and adding data.

Fortunately, it’s easy to add privileges to a user. You run a command while you’re still logged in as the root account to grant privileges to the new user.

Giving privileges to a user is called “granting”, and removing privileges is called “revoking”.

The command for granting privileges in MySQL is GRANT:

GRANT privileges
ON databasename.table_name TO username@server;

There are a few things to specify in this command:

• The privileges you want to grant for the new account
• The database you want to apply them to
• The tables you want to apply them to
• The username and server to receive the privileges

We know the username and server. Which privileges can we grant?

There’s a full list here on the MySQL documentation. Some of the more common ones are:

• CREATE: create databases and tables (related: How to Create a Database in MySQL)
• DROP: drop databases and tables
• SELECT: run Select statements to view data
• INSERT: run Insert statements to add data to a table
• UPDATE: run Update statements to change data in a table
• DELETE: run Delete statements to remove data from a table
• ALL PRIVILEGES: all privileges available

Granting ALL PRIVILEGES may seem easy, but it essentially gives the user admin access. This is often something we want to avoid.

So, for this example, we’ll give them access to create tables and work with them.

To grant privileges for creating, modifying, and removing tables:

GRANT CREATE, ALTER, DROP
ON yourdb.* TO 'bob'@'localhost';

This will give the privileges to all tables on the database “yourdb”, which you would substitute with your actual database name.

To grant privileges for working with data in the table:

GRANT SELECT, INSERT, UPDATE, DELETE
ON yourdb.* TO 'bob'@'localhost';

Run these two commands on your database, either in the terminal or in your IDE. They will give you the privileges you need for now.

 

Viewing Privileges

You can see which privileges are enabled for a specific account by running the MySQL SHOW GRANTS command:

SHOW GRANTS FOR 'bob'@'localhost';

Result:

Grants for bob@localhost

GRANT USAGE ON *.* TO ‘bob’@’localhost’

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `yourdb`.* TO ‘bob’@’localhost’

This will show the privileges for the new user. The first entry, “USAGE”, is a default privilege indicating that they start with nothing. The second entry is a result of the commands we ran.

 

What About Flush Privileges?

Once you have updated the privileges for a user, you may have read or heard that you need to run a Flush Privileges command:

FLUSH PRIVILEGES;

However, this is not needed in most situations.

If you assign privileges using the GRANT command, like we have just done, MySQL will notice the changes immediately and apply them.

Another way to assign privileges is to insert (or update or delete) data into the “grant tables”, which are MySQL tables that store the privileges for each user. If you adjust the rows in this table (instead of running GRANT), then MySQL won’t see the new privileges, and you’ll have to run FLUSH PRIVILEGES.

More information is here on the MySQL documentation.

 

Log In as New User

To test that the new user is working, you can login to the database as the new user.

Command Line

To log in with the new user on the command line:

First, exit your current connection to MySQL by typing:

exit

Press enter, and you’ll return to the command prompt.

Then, enter in the mysql command again, but with the new username.

mysql -u bob -p

You’ll be prompted to enter a password.

Once you enter the password (which you set up earlier in this article), you’ll be logged in.

 

MySQL Workbench

To log in with the new user in MySQL Workbench (or any other IDE), you’ll need to create a new connection:

Test the connection, and if it works, click OK and log in with it.

Congratulations! Your new user is now set up and ready to use!

 

Remove Privileges

You’ve granted privileges for a user. What if you want to remove privileges?

For example:

• You granted a privilege to the user by mistake.
• The user requirements have changed and they no longer need the same access
• You were testing something and now the test has finished.

Privileges can be removed easily by using the REVOKE command. It’s similar to the GRANT command, except the REVOKE word is used:

REVOKE privilege ON database.table FROM 'username';

For example, to remove the Select privilege from the customer table:

REVOKE SELECT ON main.customer FROM 'alice';

This example removes the Create privilege as well:

REVOKE CREATE ON main.* FROM 'alice';

These commands can be used to remove privileges from users in the database.

 

Delete a User

If you want to delete a MySQL user from the database, you can use the DROP USER command.

DROP USER 'username'@'localhost';

For example, to drop the user bob that we just created:

DROP USER 'bob'@'localhost';

The user and all of the privileges are removed. You won’t be able to log in with this user anymore.

 

Conclusion

So that’s how you create a new user. After reading this guide, you should be able to create a new user, give them privileges, log in with the user, and even delete the user if you no longer need it.

If you have any questions or issues with this, feel free to leave a comment below.