Create Users and Grants in MySQL
This blog post provides instructions on how to set up multiple users on MySQL. This is an advisable practice since it allows you to restrict access to certain databases to certain users only.
It is also advisable to create another super user (same as root) as a backup for super administrative rights to MySQL.
To create users, the current MySQL user has to have permissions to create on the mysql database. The root user is usually sufficient.
The basic command for creating users in MySQL is:
For example, to create a user
localhost with the password as
There is also another way to create users on MySQL. This method involves creating users while at the same time giving grants to tables. Grants in MySQL are similar to permissions.
The command is:
This grants all permissions (
DROP, etc) to the user on all tables in the
You can grant permissions to specific tables only, e.g.
This will give permissions to the user on
tbl_1 table only.
If the user already exists, you don’t have to specify the IDENTIFIED BY part. However, you may use it if you want to change the user’s password.
Alternatively, you can give grants on certain operations to a user, e.g:
To replicate the root user, you can run:
This gives all permissions on all tables of all databases to
So, to give permissions on a certain table to a user, simply run: