How To Create a New User and Grant Permissions in MySQL

Create a New User in MySql:

First access the MySQL shell by typing the following command into terminal:

After entering the root MySQL password into the prompt, you will be able to enter the MySQL shell.

Now Let’s start by making a new user within the MySQL shell:

At this point, newuser has no permissions to do anything with the databases. Even if newuser tries to login, and will fail to reach the MySQL shell.

Grant Different User Permissions:

So, provide the user with access to the information they will need.

In this command, asterisks refer to the database & table that they command allows to the newuser to read, edit, execute and perform all tasks across all the databases and tables.

Now after applying for permissions, we need to reload all the privileges using the following command.

Here is short list of other common possible permissions for MySql ‘newuser’.

• ALL PRIVILEGES – this would allow MySQL user all access to database (or if no database is selected, across the system)
• CREATE – allows to create new tables or databases
• DROP – allows to delete tables or databases
• DELETE – allows to delete rows from tables
• INSERT – allows to insert rows into tables
• SELECT – allows to use the Select command
• UPDATE – allow to update table rows
• GRANT OPTION – allows to grant or remove other users’ privileges

Syntax to provide a specific user with a permission:

Put an asterisk (*) in the place of the database name or table name, If need to give the user access to any database or to any table.

Flush Privileges command is required each time when made a change in permission of the user.

Syntax to revoke a permission:

Syntax to delete a user:

To test out your new user, log out by typing

and log back in with this command in terminal: