Creating user accounts with grants in MYSQL

Standard

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.

Grant permissions

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

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.

Other useful commands