Creating a New User and Granting it Permissions in MySQL

About MySQL

MySQL is an open source database management software that helps users store, organize, and retrieve data. It has a variety of options to grant specific users nuanced permissions within the tables and databases. In this tutorial we will teach you how to create a new User and grant it permissions.

Creating a New User

Begin by creating a new user inside the MySQL shell.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

At first the new user will have no permissions with the databases. Even if the new user tries to login, then they will not be able to get to the MySQL shell.
This means that the first step is to provide the user with permit to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in the command above will refer to the database and the table (respectively) so they can access. This specific command will allow the user to read, edit, execute, and use all tasks across all of the databases and tables.
After finalizing the permissions, you wanted set up for your new users, ensure you keep reloading all of the privileges.

FLUSH PRIVILEGES;

Now your changes should be in effect.

Granting Different User Permissions

Below you will find a short list of common possible permissions that users may enjoy.

• ALL PRIVILEGES- as you have seen previously, this is going to permit a MySQL user easy access to a designated database or, if no database is selected, across the system.
• CREATE- allows them to create new tables or databases
• DROP- allows them to them to delete tables or databases
• DELETE- allows them to delete rows from tables
• INSERT- allows them to insert rows into tables
• SELECT- allows them to use the Select command to read through databases
• UPDATE- allow them to update table rows
• GRANT OPTION- allows them to grant or remove other user privileges

If you want to provide a specific user with a permission, you may use this framework below.

 GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you would like to give them access to any database or to any table, simply put an asterisk ‘*’ in the place of the database name or table name.
Any time you want to update, or perhaps change, a permission make sure that you are using the Flush Privileges command.
In the case that you would like to revoke a permission, the structure is nearly identical to granting it.

 REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

In the same way that you can delete databases with DROP, you can also use DROP to remove a user altogether.

DROP USER ‘demo’@‘localhost’;

If you want to test out the new user, log out by typing the below.

 quit

Afterwards, log back in with the command in terminal.

mysql -u [username]-p

Conclusion

You should now have everything you need to easily create a new user and give any permissions necessary.