After you have installed a mysql database, setup root permissions, and added a few schemas it is time to start adding users. When adding users it is likely that different users call for different databases. This can be handled by using GRANTS in mysql.

To begin we will assume that there are two databases; PROD and DEVEL. We also have two users; Sarah, and Jessie. Sarah is assigned to the PROD database, while Jessie can only work on the DEVEL.

Create a new user

Start first by creating both users, the % signifies they can login from any location.

CREATE USER 'sarah'@'%' IDENTIFIED BY 'myPassword';
CREATE USER 'jessie'@'%' IDENTIFIED BY 'myPassword';

Grant permissions

Next they need to be granted permissions to access his/her working database.

# Sarah gets prod database
GRANT ALL PRIVILEGES ON PROD.* TO 'sarah'@'%' WITH GRANT OPTION;

# Jessie gets devel database
GRANT ALL PRIVILEGES ON DEVEL.* TO 'jessie'@'%' WITH GRANT OPTION;

At this point, if sarah logged in she would only see the PROD database. As expected if Jessie logged in he would only see the DEVEL database. They now only have permissions to there own separate databases.

Reset a password

Lets say you forget sarah’s assigned password. It can easily be reset with the following command.

SET PASSWORD FOR ‘sarah’@'%’ = PASSWORD(‘myNewPassword’);

Other useful commands

# Show all users; identification
select user,host from mysql.user;

# Show a users grants
SHOW GRANTS FOR 'sarah'@'%';