MYSQL: Users - Create, Grants, Revoke, and Drop

Now that you already have your databases, you need to have users. In this guide, we will learn how to create user, grant/revoke privileges and drop user.

CREATING NEW USER

To create new user, use below command:

CREATE USER '[username]@'localhost' IDENTIFIED BY '[password]';

Here is an example:

CREATE USER 'DBuser@'localhost' IDENTIFIED BY 'P@ssw0rd123';

You can then check if the user has been created

 mysql> select user, host from mysql.user;
+----------+---------------+
| user     | host          |
+----------+---------------+
| DBuser   | localhost     |
| root     | localhost     |
+----------+---------------+
2 rows in set (0.01 sec)


GRANTING/REVOKING PRIVILEGES

Now that you have a user, you then need to grant privileges on it.

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

Here are the list of some basic privileges that you may need to grant your user.

CREATE - allows the user to create new databases or tables
SELECT - allows the user to use select command
UPDATE - allows the user to update table rows
INSERT - allows the user to insert rows into tables
DROP - allows the user to delete databases or tables
DELETE - allows the user to delete rows from tables
GRANT OPTION - allows the user to grant/remove other users' privileges

If you need to grant ALL on the user, you can use below command

GRANT all ON [database_name].* TO '[username]'@'localhost';

If you want to REVOKE privileges to a user:

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

After privileges are granted or revoked, you need to run below command for the changes to take effect.

FLUSH PRIVILEGES;


DROPPING USER

Lastly, to drop a user, use below command:

DROP USER '[username]@'localhost'


For more MYSQL guides, here! 

MYSQL: How to Create and Delete Database

Before creating a database, we need first to be able to login to mysql. Here is how you can login.

mysql -u[username] -p[password] -h[hostname] -P [port]

CREATING NEW DATABASE

After you are able to login, you can list all the databases that are currently present in your MYSQL server.

 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.01 sec)

You can then create a new database as easy as this:

create database [dbname];

Here is an example:

create database newDB;

Then you can check it by listing all your databases.

 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| mysql              |
| test               |
| newDB              |
+--------------------+
5 rows in set (0.01 sec)


You can then start working on that particular database using below command:

use database newDB;


DELETING DATABASE;

Here is a very simple way to delete your database:

drop database [dbname];

Here is an example:

drop database newDB;

After deleting the database, you can again verify it with the SHOW DATABASES command.

 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)


Hopefully you've learned from this very short guide. Click here to for more MYSQL guides.