This is a short summary about user management in MySQL - the first thing to do is login to the MySQL server as the preconfigured 'root' user:
$> mysql -u root -p
A new user can be created as follows:
mysql> CREATE USER 'john'@'localhost' IDENTIFIED BY 'john123';
This creates a normal user called 'john' with a password of 'john123' who can only login from the same computer on which the MySQL server is running (as identified by the 'localhost' restriction).
Note that the 'IDENTIFIED BY' section is optional within MySQL, but quite critical - if you omit it the user will still be created, but with no password associated with the account. This allows the user to login without needing to specify a password and can potentially leave MySQL exposed.
It's also worth pointing out that the following would also work
mysql> CREATE USER john IDENTIFIED BY 'john123';
However, this would allow the user 'john' to connect to the MySQL server from anywhere, including remote connections - this is not recommended unless absolutely necessary.
At the moment our 'john' user will not be able to do much - you need to grant them some privileges:
mysql> GRANT ALL ON exampledb.* TO 'john'@'localhost'
-> IDENTIFIED BY 'john123';
This allows the 'john' user to control (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc) the 'exampledb' database, but he will be unable to access any other database within the MySQL server. You can double-check the grants given to a user as follows:
mysql> SHOW GRANTS FOR 'john'@'localhost';
You could restrict access further if necessary:
mysql> GRANT INSERT, UPDATE ON exampledb.* TO 'john'@'localhost'
-> IDENTIFIED BY 'john123';
You may want to limit access to specific tables:
mysql> GRANT SELECT ON exampledb.example_table TO 'john'@'localhost';
Such a fine-grained level of access is likely to be used for automated processes that only needs limited actions on a subset of tables (a report-generating process, for example) as the above would probably prove too restrictive for a regular user.
