Resolving Authentication Issues With Regards To privileges(GRANTS) On A MySQL Database

Sometimes you have created a user and still have authentication issues like the one shown below when reviewing the logs. This guide will assist you in identifying the privileges(GRANTS) a user has and assist you with applying the correct privileges(GRANTS) to resolve the authentication issue.

ERROR 1045: Access denied for user: 'digitalriver@yourserverip' (Using password: YES)

Prerequisites 

Before you begin this guide you'll need the following: 

• One Ubuntu 18.04 server is configured with a firewall and a non-root user. You can refer to the Initial Server Setup with Ubuntu 18.04 guide to configuring your server.
  
  
• A MySQL server is set up as detailed in this How To Install MySQL on Ubuntu 18.04 tutorial. Ensure you set a root password for the MySQL server.

Step 1 - Access Your Server And MySQL Environment

First, ssh into your Ubuntu 18.04 server:

ssh user_name@your_server_ip

Then, run the following command to log in to the MySQL server as the root user:

sudo mysql -u root -p

Step 2 - Identify What Privilege(GRANTS) The User Has On The Database: 

To show privileges the user has on the database run the following command:

mysql> SHOW GRANTS FOR 'digitalriver@your_server_ip';

You should get output similar to the following:

Output

+-----------------------------------------------------------------+
| Grants for digitalriver@yourserverip                            |
+-----------------------------------------------------------------+
|                                                                 |
|  GRANT USAGE ON *.* TO `digitalriver`@`yourserverip`            | 
|                                                                 |
| GRANT SELECT ON `customer_db`.* TO digitalriver`@`yourserverip` |
+-----------------------------------------------------------------+

Step 3 - Review Privileges(GRANTS) Required And Apply The Privileges

You need to review the correct privileges(GRANTS) you want to assign to the user. You can identify these by examining what actions the user performs on a specific database. These are the typical privilege(GRANTS) you can apply to a user on a database:

<$>[note]
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES,
INDEX, ALTER, SHOW DATABASES, SUPER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE,
REPLICATION SLAVE, REPLICATION CLIENT,
CREATE VIEW, SHOW VIEW, CREATE ROUTINE,
ALTER ROUTINE, CREATE USER, EVENT, TRIGGER,
CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON
<$>

To assign privileges(GRANTS) you can run the following command:

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* to digitalriver@yourserverip IDENTIFIED BY 'password';

Step 4 - Flush Privileges

You can use the flush privileges command that will reload the grant tables in the MySQL database enabling the changes to take effect without reloading or restarting the MySQL service by running the following command:

FLUSH PRIVILEGES;

Conclusion

This guide aims at assisting you with identifying if a user has sufficient privileges(GRANTS) on a database to perform a specified task that results from this specific error:

ERROR 1045: Access denied for user: 'digitalriver@yourserverip' (Using password: YES)