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.
- 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:
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';