This blog was last modified 430 days before.

Create User

We can use the following command:

CREATE USER IF NOT EXIST 'username'@'localhost' IDENTIFIED BY 'password of this user';

Here the localhost part could be omitted which then would be default to %.

To check the user list, use:

SELECT user, host FROM mysql.user;

Grant Privileges

On common senario is that you want to give a user all control to a specified database. You can use following command to achieve this:

GRANT ALL ON 'tablename'.* to 'username'@'localhost';

If you don't specified the host when you create account, then here you should not add the host part.

One things important that is you should run FLUSH PRIVILEGES to make this privileges changes comes into effects immediately without restarting MySQL. Check this page for more info.

When using command like GRANT

Based on testing, it seems that the privileges changes performed by instructions like GRANT, REVOKE etc doesn't necessarily require a FLUSH command.

When directly editing mysql.user

However, if you directly modified the info inside mysql.user, then flush privileges becomes a compulsory.

Here from the picture, we changed the username from test_account to test_account_1 by directly editing field inside mysql.user WITHOUT calling FLUSH PRIVILEGES. And from the pic, we are failed to connect with both old and new username.

And after execute command FLUSH PRIVILEGES:

We could successfully connect with the new username test_account_1.

Delete User

In SQL if we want to delete a user, we need to use DROP command.

DROP USER 'username' IF EXIST;

More info on MySQL Official Docs.

If there is an opened session by the user you dropped, the drop action will be deferred to after the open session is closed. And this behaviour is by design based on the official docs.