How To Create a New User and Grant Permissions in MySQL

MySQL is a popular and widely used database management system that stores and organizes data and allows users to retrieve it. It comes with a vast array of options that grant users certain permissions to tables and databases.

In this guide, you will learn how to create a new user and grant permissions in the MySQL database.

How to Create a New User in MySQL

To create a new user first log in to the MySQL shell.

$ sudo mysql -u root -p

Provide the sudo password followed by the password that provided when setting up the MySQL database and hit ENTER. Thereafter, you will get this prompt.

Connect to MySQL Shell
Connect to MySQL Shell

To create a new user, use the syntax shown below:

MariaDB [none]> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

For example, to create a new user called ‘tecmint’ within the database, invoke the command:

MariaDB [none]> CREATE USER 'tecmint'@'localhost' IDENTIFIED BY 'QkYKmw$5tec';

A Few Points to Keep in Mind

When adding a user locally i.e., on the system that you have installed MySQL, the user’s host is specified as localhost, and not the IP address. The keyword ‘localhost‘ translates to ‘this computer‘ and MySQL treats it uniquely. Basically, localhost is used by the mysql client to establish a connection to the locally installed MySQL database server.

Thus far, the tecmint user does not have any permissions to interact with the databases. As a matter of fact, the user cannot even access the MySQL shell.

To grant the user full access to all the databases, including the tables, run.

MariaDB [none]> GRANT ALL PRIVILEGES ON * . * TO 'tecmint'@'localhost';

In the command above, the asterisks point to the database and the table respectively that the user can access. It grants the user all the rights on the database – read, write, edit, and execute including performing all the tasks across other databases and tables as well.

So far, we have granted the user full access to the database. While this is handy in explaining MySQL concepts, it’s generally not recommended as it could pose a security risk to your databases. Just think of what might happen if a hacker got hold of the user’s password. We will proceed further and discuss how to assign specific permissions in the next section.

When you are done assigning permissions to the user, reload all the privileges as shown for the changes to take effect.

MariaDB [none]> FLUSH PRIVILEGES

How To Grant Different User Permissions

Here’s a breakdown of the possible permissions that you can grant users:

  • ALL PRIVILEGES – As previously seen, this grants a MySQL user full access to a specific database.
  • CREATE – Allows users to create new databases or tables.
  • DROP – Enables users to delete databases or users.
  • INSERT – Allows users to insert rows in tables.
  • DELETE – Allows users to delete rows from tables.
  • SELECT – with ‘SELECT’ permission, users are able to read the contents of a table.
  • UPDATE – Allows users to updates the rows in a table.
  • GRANT OPTION – Users can grant or remove the privileges of other users.

To grant a specific user permission, use the syntax:

MariaDB [none]> GRANT permission_type ON database_name.table_name TO 'username'@'localhost';

Additionally, you can assign permissions to all the tables in a database with a single asterisk symbol as shown:

MariaDB [none]> GRANT permission_type ON database_name.* TO 'username'@'localhost';

For example, to assign SELECT permissions to ‘tecmint‘ user on all tables of the database testdb, run the command.

MariaDB [none]> GRANT SELECT ON testdb.* TO 'tecmint'@'localhost';

Then flush privileges for the changes to take effect.

MariaDB [none]> FLUSH PRIVILEGES;
Assign Select Permissions to User on Database
Assign Select Permissions to User on Database

Additionally, you can assign multiple permissions at a go by separating them with a comma as shown.

MariaDB [none]> GRANT INSERT, UPDATE ON testdb.* TO 'tecmint'@'localhost';
Assign Multiple Permissions to Database
Assign Multiple Permissions to Database

How to Revoke MySQL Permissions

To revoke permissions from a user, use the syntax:

MariaDB [none]> REVOKE permission_type ON database_name.table_name FROM 'username'@'localhost';

For example, to revoke INSERT permissions from the user ‘tecmint’, run the command.

MariaDB [none]> REVOKE INSERT ON testdb.* FROM tecmint'@'localhost';
MariaDB [none]> FLUSH PRIVILEGES
Revoke MySQL Permissions
Revoke MySQL Permissions

To have a peek at a user’s current permissions, execute:

MariaDB [none]> SHOW GRANTS FOR 'username'@'localhost';

From the output below, we can see that the INSERT permission has been expunged from the ‘tecmint’ user leaving only SELECT and UPDATE rights on the testdb database.

Check User MySQL Permissions
Check User MySQL Permissions

To test out logging in to the MySQL shell using the new user, first log out.

MariaDB [none]> quit;

Then login back again.

$ sudo mysql -u tecmint -p

Provide the user’s password and hit ENTER to access the shell.

To drop a user, use the DROP command, just as you would when deleting a database.

MariaDB [none]> DROP USER 'username'@'localhost';

You might also like to read the following MySQL related articles:

Conclusion

Hopefully, by now, you can create users in your MySQL database servers and comfortably assign or revoke permissions.

If you liked this article, then do subscribe to email alerts for Linux tutorials. If you have any questions or doubts? do ask for help in the comments section.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

Support Us

We are thankful for your never ending support.

Got something to say? Join the discussion.

Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.