Added on Mar 24th, 2015 and marked as mysql phpmyadmin

A confession:

My name is Marek and I’m an recreational phpMyAdmin user.

Although I’m not a big fan of phpMyAdmin - its imaginative user interface keeps surprising me - there is one thing for which I use it constantly: adding new users to MySQL and assigning the correct privileges.

I always found that the whole process of adding a new MySQL user from the command line was way too cumbersome. And since phpMyAdmin makes it quite easy I never made a real effort to fix this hole in my MySQL know-how. Up till now.

And it turns out the process is not as fuzzy as I remembered it to be.

Create a new user

From the command line, start the MySQL shell:

mysql -uroot -p

To create a new user myuser:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

Note that the new user myuser can only access the database from the local machine (i.e. localhost). If you want to access the database from a remote server you can specify the remote hostname or use a wildcard to allow from all remote locations (%).

Set permissions

Great! So we’ve added a user, but by default it has absolute no permissions to do anything (except login).

My view on permissions is that you need as little as possible.

Most common case

In most cases I need a user that has read, write, update and delete permissions to all tables in a single database. This command will assign the relevant permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON `database`.* TO 'myuser'@'localhost';

If the user also needs to change the database structure (for instance to complete a installation process) then you need this command:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, 
ALTER, CREATE TEMPORARY TABLES ON `database`.* TO 'myuser'@'localhost';

When you have set up the desired permissions you can finalize them using:

FLUSH PRIVILEGES;

This way you’re sure all your users have the correct permissions.

Full access

The following command gives the user full access to all databases and tables. In general, I would not recommend using these.

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost';

To have a bit more restricted type of access you can use:

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';

In general

The general form of the GRANT command is as follows:

GRANT [type-of-permission] ON [database-name].[table-name] TO '[username]'@'[hostname]';

As you can see, it is possible to set up a really detailed permission structure if needed.

Check out the entire list of MySQL privileges for more detailed information.

Revoke access

If you can give an user access it is also possible to retract those permissions. This is the general form to do so:

REVOKE [type-of-permission] ON [database-name].[table-name] FROM '[username]'@'[hostname]';

Delete a user

Eventually the moment will come when you also want to get rid of a user. This can easily be done by using the DROP command:

DROP USER 'myuser'@'localhost';