Creating new user and grant permission in MySQL/MariaDB – VPS

Creating a new user and allocating a set of permissions are the first steps you must take when you install a new application that operates based on MySQL/MariaDB database engine. The guide provides tutorial on how to do this via the MySQL command line interface or other tools that allow the execution of SQL commands

Step One: Adding a New Database

To add a new database, you must type the following command line:

mysql> CREATE DATABASE `mydb`;

Step Two: Adding a New User to the Database

After the database is created, it is time to add a new user to it. You must add the user so that he can login and access its features. To add a new user, you simply type the command line:

mysql> CREATE USER 'myuser' IDENTIFIED BY 'mypassword';

You are to replace myuser with your preferred MySQL username and mypassword with a strong password. If you don’t want to assign any password, you just leave the mypassword area empty. This means that the user can login into the database without the need of entering any password.

To check whether you have added a new user correctly, you must first log out by entering the command:

mysql> quit

After logging out, you must login with the new username and password that you’ve just created:

mysql -u UserNameHere -p

Step Three: Allocating Permissions to the New User

Right now, you have created a new user but he can’t access any function of the database until you grant him the permission to do so.

You can either grant all privilege to the user or just grant him selected privilege on the database. To grant the user all the privileges, you can type the command line:

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

If you want to grant selected permissions, you must include the syntax for that particular permission in the GRANT command line. Examples of permissions you can grant to the user are CREATE (creation of new tables), DROP (delete tables), and UPDATE (update table rows). The command line for granting selected permissions should be as follows:

mysql> GRANT type_of_permission ON database_name.table_name TO ‘username’@'localhost’;

When you use the above syntex, make sure you put an asterisk in the place of the table_name.

Step Four: Removing Privilege and User from the Database

The command line for removing the privilege is the same as the granting the permission. However, you must use the word REVOKE instead of GRANT, for example:

mysql> REVOKE type_of_permission ON database_name.table_name FROM ‘username’@‘localhost’;

To find out what privilege a user has, you can type:

SHOW GRANTS username;

Deleting a user is just the same as deleting a database. When you want to delete a user, you must use the syntex DROP, for example:

DROP USER ‘username’@‘localhost’;

Conclusion

In conclusion, it is very simple to create a new user and grant him permission on a Maria DB MySQL. Even beginners can quickly pick up this knowledge and learn how to do this without any help from a professional programmer.