How to create/delete a user in MySQL?

MySQL

I just installed MySQL/MariaDB, now what? Well, from the very start of using either database software, one will need to know how to create users that can log in and manage particular databases or tables…

Before we get started…

As an F.Y.I, from here on I will be referring to MariaDB as MySQL for simplicity sake! Oh and also, you should take into account what permissions you give to a MySQL user as this can help prevent future security issues.

Getting started

Before we begin, we will need to create the user in which we will use to access the MySQL shell… Depending on your setup, you may or may not need a password… But anyhow, we can go ahead and access the MySQL shell by running:

mysql

Creating the user

After dropping into your MySQL shell, we can go ahead and begin creating our user (called “user”) by running the following in the MySQL shell:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'secure_pass';

As you can see in the statement above, we created a user called ‘user’ at ‘localhost’ with the password “secure_pass”… Feel free to change the name of the user and its password to anything you want.

The only thing I recommend leaving the same is the “@localhost” part because it only allows this user to be logged in from localhost…

Granting permissions to the user

After creating our new MySQL user, we will have to grant that user permissions as to allow them to only access certain databases or tables. We can grant permissions to all databases and tables to our MySQL user by running the following:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;

After running this statement, MySQL should notice these changes immediately and reload the grant tables. Anyways, this statement basically allows the user “user”,  to access any database or table within MySQL for reading and writing…

Deleting a MySQL user

Let’s say that you need to revoke or rather delete a MySQL user… How should you do this? Well, as simple it is to create a user, it’s even easier to delete one… You can do so by running the following:

DROP USER 'user'@'localhost';

And voila! You have deleted or dropped a MySQL user!

Conclusion

To sum this post up, we created a new MySQL user that can only be accessed from localhost and has unlimited access to any table or database within MySQL…

Refer to the actual MySQL developer manual here!

Leave a Reply