MySQL Users

Create a New User

CREATE USER `my_app_user`@localhost IDENTIFIED BY 'my_app_user_passord';

Verify the user exists

SELECT DISTINCT User FROM mysql.user;
+-------------+
| User        |
+-------------+
| root        |
| my_app_user |
+-------------+
2 rows in set (0.002 sec)

Get the users hashed password

Get the users hashed password in order to grant privileges. Here, the users password hash is *2DCEE07DFCE222276BE0066CFF88AEE797359DE4.

SHOW GRANTS FOR 'my_app_user'@localhost;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for my_app_user@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `my_app_user`@`localhost` IDENTIFIED BY PASSWORD '*2DCEE07DFCE222276BE0066CFF88AEE797359DE4' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.085 sec)

Grant privileges

Grant the my_app_user user privileges.

GRANT SELECT, INSERT, UPDATE ON `my_database`.* TO 'my_app_user'@'localhost';
Query OK, 0 rows affected (0.002 sec)

Verify the privileges

SHOW GRANTS FOR 'my_app_user'@localhost;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for my_app_user@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `my_app_user`@`localhost` IDENTIFIED BY PASSWORD '*2DCEE07DFCE222276BE0066CFF88AEE797359DE4' |
| GRANT SELECT, INSERT, UPDATE ON `my_database`.* TO `my_app_user`@`localhost`                                       |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

DELETE user

NOTE: This will delete the user account from mysql permanently!

DROP USER `my_app_user`@`localhost`;