1. First, you have to log in with the root user, which has the CREATE USER privilege
Run this command to create a new user with a password:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'the_password';
At this point, the new user has no permission over the databases. The next thing to do is to grant privileges to the new user.
2. Make it a superuser
To make this new user a superuser, we have to provide it with full root access to everything in the database, which means to GRANT ALL PRIVILEGES:
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;
It’s done, the new user now has the root-like permission.
3. Then create another account for the same new username
CREATE USER 'username'@'%' IDENTIFIED BY 'the_password';
And grant full root access:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
Both 'username'@'localhost'
and 'username'@'%'
are superuser accounts with full privileges to do anything.
The 'username'@'localhost'
account can be used only when connecting from the local host. The 'username'@'%'
account uses the '%'
wildcard for the host part, so it can be used to connect from any host.
4. To double check the privileges given to the new user, run SHOW GRANTS
command:
SHOW GRANTS FOR username;
5. Finally, when everything is settled, reload all the privileges:
FLUSH PRIVILEGES;
And all the changes will take effect immediately.