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
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 (
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.
- The first thing is to determine with which databases this user needs to work.
- Does this user needs access to all tables within those databases, or just a selection?
- And finally, which actions does the user need to be able to perform? Just querying or also updating data?
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:
This way you’re sure all your users have the correct permissions.
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';
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.
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 USER 'myuser'@'localhost';