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!