In this post, we will discuss how to Create a New User in MySQL and grant permissions to it using the MySQL Shell or MySQL Command-Line. Most important we will also discuss why it is important to create a user in MySQL?

Why to Create a New User?

Default ‘root’ user in MySQL has full access to all databases and tables.

However, in certain situations, we may require to put restrictions on some databases for security purposes. In that case, we create a new user in MySQL and grant specific permission to it, so that only the permissible databases and tables is accessible by him/her.

This provides access control power to the database administrator.

How to Create a New User?

The general syntax to create a new user in MySQL is

Note: Make sure to replace the user_name and password with your new user name and password.

‘localhost’ is the host of your MySQL database. This can be some server’s IP address where the MySQL database is located but if the MySQL is installed in your local machine or is relatively located in the same server (or machine) then we use ‘localhost’ instead.

Executing the above statement will create the new user but the new user has no permissions to do anything with the databases. In fact, the ‘new_user’ will not be able to login to MySQL using the password.

So to complete the process, we need to grant appropriate MySQL permissions to the new user.

Grant Permissions to User

The general syntax to grant permissions to any user in MySQL is

Example: If you want to grant all MySQL permissions to the user for all available databases

The asterisk in this statement refers to all databases and tables respectively.

Executing the above statement will grant full root-level access to ‘user1’. It means that the ‘user1’ can perform any MySQL operations on any table of any database.

Different User Permissions

These are the different user permission you can grant to a user in MySQL.

  • ALL PRIVILEGES – gives full access to the designated database and tables (or if no database is specified, global access across the system).
  • CREATE – allows creating new databases and tables.
  • DELETE – allows deleting of records from tables.
  • INSERT – allows inserting record into table.
  • DROP – allows deleting of databases or tables.
  • SELECT – allows select or read tables records.
  • UPDATE – allows updating of databases and tables.
  • GRANT OPTIOM – allows to grant or remove other users’ privileges.

Here is an example to demonstrate the use of these privileges while granting permission to a user

The ‘user1’ can only create and insert records to the tables of the ‘sales_db’ database.

Revoke Permissions from User

If you need to revoke permissions from the user then the syntax for that is almost similar to ‘grant MySQL syntax’ i.e

Whenever you change the permission for a user always use the flush privileges command to reload all the privileges.

Display MySQL User Privileges

To see what permissions or privileges does a MySQL user have, use the SHOW GRANTS statement.

Note: Make sure to replace the user_name with the name of the user.

List all MySQL Users

After successfully creating a user in MySQL, if you wish to see the list of all MySQL users then use the following SELECT statement

Delete an Existing User

To delete an existing user in MySQL use the following MySQL DROP syntax.

Note: Replace the user_name with the name of an existing MySQL user.

I hope now you will be able to create a user in MySQL as well as grant permissions to it. If you have any doubts regarding any explanation or have any suggestions then please comment below.

Leave a Reply