How to Create MySQL user and Grant permission ?

Introduction :

MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications.  It is commonly deployed as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) and, as of this writing, is the most popular open-source database in the world.

Prerequisites :

• A Linux server with MySQL or MariaDB installed and running
• Access to the MySQL root user credentials
• Access to a terminal window/command-line (Ctrl-Alt-T / Ctrl-Alt-F2)

How to Create New MySQL User :

You have MySQL installed on the server(s) that will host your MySQL environment, you need to create a database and additional user accounts. In order to run the following commands, log into the MySQL instance with the MySQL root account.

MySQL defines users with a username and the hostname or IP address that they’re using to access the MySQL instance. To create a new user in MySQL, specify the username, the hostname the user can use to access the database management system, and a secure password:

mysql> CREATE USER ‘local_user’@’localhost’

IDENTIFIED BY ‘password’;

This command will allow the user with username local_user to access the MySQL instance from the local machine (localhost) and prevent the user from accessing it directly from any other machine. Then, execute the following command:

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

• new_user is the name we’ve given to our new user account and the IDENTIFIED BY ‘password’ section sets a passcode for this user. You can replace these values with your own, inside the quotation marks.

• In order to grant all privileges of the database for a newly created user, execute the following command:

GRANT ALL PRIVILEGES ON * . *

TO ‘new_user’@’localhost’;

• For changes to take effect immediately flush these privileges by typing in the command:

FLUSH PRIVILEGES;

How to Grant Permissions in MySQL :

When specifying the database name and table name, separate them with a . (period) and no spaces. This will give the root user fine-grain control over certain data.

Also, replace the PERMISSION_TYPE value with the kind of access you want to grant to your new user account.

Permissions are actions that the user is allowed to perform in the database. Depending on how much authority you want your user to have, you can grant them one, Here are the most used commands in MySQL:

• Insert: The users add new entries into tables.
• Delete: enable users to erase table entries.
• Create: The user can create entirely new tables and databases
• Drop: The user can drop (remove) entire tables and databases
• Select: The user gets access to the select command, to read the information in the databases
• Update: The user can update table rows
• All Privileges: The user account has full access to the database

To use any of these options, simply replace PERMISSION_TYPE with the appropriate keyword. To apply multiple privileges, separate them with a comma.

The grant specific privileges to a user account, use the syntax:

GRANT permission_type ON database.table TO 'username'@'localhost';

With that command, MySQL to:

• GRANT the PRIVILEGES of type ALL (thus everything of course). Note: Most modern MySQL installations do not require the optional privileges keyword.
• These privileges are for database_nameand it applies to all tables of that database, which is indicated by the .* that follows.
• These privileges are assigned to username when that username is connected through locally, as specified by @ localhost‘. To specify any valid host, replace ‘localhost' with '%

simply replace PERMISSION_TYPE with the appropriate keyword. To apply multiple privileges, separate them with a comma. For example, we can assign CREATE and SELECT to our non-root MySQL user account with this command:

GRANT CREATE, SELECT ON * . * TO 'user_name'@'localhost';

Sometimes, you might come across a situation where you need to revoke given privileges from a user. You can do so by entering:

REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘user_name’@‘localhost’;

For example, to withdraw all privileges for our non-root user we should use:

REVOKE ALL PRIVILEGES ON * . * FROM 'user_name'@'localhost';

Finally, you can entirely delete an existing user account by using the following command:

DROP USER ‘user_name’@‘localhost’;

Revoke Privileges from a MySQL User Account

The syntax to revoke one or more privileges from a user account is almost identical as when granting privileges.

To revoke all privileges from a user account over a specific database, run the following command:

REVOKE ALL PRIVILEGES ON database_name.* FROM ‘database_user’@’localhost’;

Remove an Existing MySQL User Account

To delete mysql user account use the DROP USER statement:

DROP USER 'user'@'localhost'

Revoke Privileges from a MySQL User Account

The syntax to revoke one or more privileges from a user account is almost identical as when granting privileges.

To revoke all privileges from a user account over a specific database, run the following command:

REVOKE ALL PRIVILEGES ON database_name.* FROM ‘database_user’@’localhost’;

Remove an Existing MySQL User Account

To delete mysql user account use the DROP USER statement:

DROP USER 'user'@'localhost'