There are several aspects to MySQL Authentication, this is because you cannot escape thinking about where you are connecting from as well as who you are. Anyway, some useful queries:
select user(); - this show who you tried to authenticate as
select current_user(); - returns who you actually authenticated as
Clearly if the two are different then you may not get the permission you wanted, but using these function help identify the problem, alternatively you can put them together:

SELECT user() AS "Tried to Authenticate as",
       current_user() AS "Actually Authenticated as";

If you are using the MySQL Command Line Utility then just enter \s to see all the status information but note that this shows who you tried to authenticate as, not who you actually authenticated as.

Flushing Privileges

You will see lots of references to using flush privileges when adding users or changing their permissions, however the basic principle is that if you use the proper user and permissions commands this is not needed you do however need it if you directly modify the user or permissions tables, see Stop using FLUSH PRIVILEGES – I used to be a MySQL DBA for Hire for more details.

Security Considerations

It is very important that you read the security section of the MySQL or MariaDB documentation, failure to do so will be very bad news. It is important to note that if you use the correct user and password administration commands like "CREATE USER..." and "ALTER USER..." then everything is much better. For example of you use insert or update statements on the mysql.user table then your SQL commands may appear in log files, which will include any passwords! The passwords should be hashed out with the correct commands. This is why reading security documentation is so important and why it is also best to use the right commands for things.

Creating Users

I found that after creating a new user I still could not authenticate as that user, the problem as solved with the following command: flush privileges;
This gets MySQL to reload the privileges and after this my new user worked.

If you are creating users with a script then it is good to have a re-runnable script, in which case creating as follows is helpful:
create or replace user if not exists 'user'@'%' identified by 'password'; however this might only work with MariaDB.

Listing Users

In a MySQL instance you will find a standard database called "mysql" which has several useful tables, including one called user. The following query will list all the users:

       CONCAT(QUOTE(User),'@',QUOTE(Host)) UserAccount,
FROM mysql.user;
It also contains some other useful information without listing all the privileges.
User Permissions

It can be a little tricky to see who has permissions on what, the obvious, basic option is:
SHOW GRANTS FOR 'username'@'host';
You can just do SHOW GRANTS; to see the information for the current user. If you want more details you can study the tables documented at MySQL :: MySQL 5.7 Reference Manual :: 6.2.2 Grant Tables.


If someone other than yourself is the database administrator then you might wish to change your own password, I did because I considered the given password to be too weak. The command you need is this:
SET PASSWORD = PASSWORD('newplaintextpassword');

SET PASSWORD FOR 'newuser'@'%' = PASSWORD('theplaintextpassword');

UPDATE mysql.user SET password=PASSWORD('newpassword') WHERE user='username';

There is also:
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'password';
ALTER USER USER() IDENTIFIED BY 'password'; -- assuming you are not anonymous!
See MySQL :: MySQL 5.7 Reference Manual :: 6.3 MySQL User Account Management

New Database

If you wish to create a new database and a new user and give the user permissions on that database then you need to execute the following:
create database my_new_db;
create user 'the_new_user'@'%' identified by 'secret_password';
grant all privileges on my_new_db.* to 'the_new_user'@'%';
flush privileges;
This will allow the user to connect from anywhere (the @'%' bit) and work with any table (the .* bit) in the new database. However the '%' host only permits TCP connections, a local socket connection requires 'localhost' which is the default when running the mysql command. Therefore it is often worth adding the following:
create user 'the_new_user'@'localhost' identified by 'secret_password';
When you want to test the user locally via the command line.