How to Create MySQL User Accounts and Grant Permissoins

MySQL is a great open-source database management app that allows you to store, organize, and retrieve information. It has a lot of options to grant specified users varied permissions within the tables and databases. If this is what you're interested in look no further. This is the guide for you.

How to Create a New User

Here's how to make a new user within the MySQL shell.
First, a user account in MySQL has two parts: a user name and a host name. To generate a new MySQL user account, run this command:
"mysql > CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password'"

Note: Replace "newuser" with the new user name, and "user_password" with the user password.

In this example, the hostname part is set to localhost. This means that the user will only be able to connect to the MySQL server from the localhost, meaning the system on which the MySQL Server runs.

How to grant permissions

Right now the new user has no permissions and can't do anything within the databases. So, the first thing you will want to do is to provide the user with access to the data that they will need. There are a lot of privileges you can assign to the new user, but the most commonly used ones are:

• ALL PRIVILEGES – Grants all privileges to a user account.
• CREATE – Allows the user account to create databases and tables.
• DROP – Allows the user account to drop databases and tables.
• DELETE - Allows the user account to delete rows from a specific table.
• INSERT - Allows the user account to insert rows into a specific table.
• SELECT – Allows the user account to read a database.
• UPDATE - Allows the user account to update table rows.

To grant specific privileges to a user account, use the following syntax:
mysql > GRANT permission1, permission2 ON database_name.table_name TO 'database_user'@'localhost';

For example:
Grand all privileges to a user account over a specific database:
mysql > GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
Grand all privileges to a user account on all databases:
mysql > GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
Grand all privileges to a user account over a specific table from a database:
mysql > GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
Grant multiple privileges to a user account over a specific database:
mysql > GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'

Whenever you update or change a permission remember to use the Flush Privileges command.
When you're done with the permissions that you wanted to set up for your new users, always reload all the privileges.

How to revoke a permission

If you need to revoke a permission, the structure is almost identical to granting it.
To revoke all privileges from a user account over a specific database, run this command:
mysql > REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@ localhost'

Note: When revoking permissions, the syntax requires that you use FROM, instead of TO as we used when granting permissions.

Hopefully, you have a better sense of how to add new users and grant them a variety of permissions in a MySQL database after reading this guide.