Access denied when using JDBC to connect to MySQL

Question

This question has several occurrence, but no answer provides information on how to fix or diagnose the problem. I have a simple java application that uses MySQL JDBC to connect to MySQL database on my local server:

    String url = "jdbc:mysql://localhost:3306/dbName";
    String userName = "parser";
    String password = "123";
    try {
        Connection conn = DriverManager.getConnection(url,userName, password);
    } catch (SQLException e1) {
        e1.printStackTrace();
    }

However java throws an exception:

java.sql.SQLException: Access denied for user 'parser'@'localhost' (using password: YES)

Many answers to simillar question suggest that user has no privileges for connetction, but in my case it has:

+---------------------------------------------------------------+
| Grants for parser@%                                           |
+---------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'parser'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------+

Can any one explain to me why this happens, how to diagnose or understand what's the issue?

Update 1: Permissions list for mysql users

+--------------------------+---------------+-------------------------+--------------+
| GRANTEE                  | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+--------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'       | def           | SELECT                  | YES          |
| 'root'@'localhost'       | def           | INSERT                  | YES          |
| 'root'@'localhost'       | def           | UPDATE                  | YES          |
| 'root'@'localhost'       | def           | DELETE                  | YES          |
| 'root'@'localhost'       | def           | CREATE                  | YES          |
| 'root'@'localhost'       | def           | DROP                    | YES          |
| 'root'@'localhost'       | def           | RELOAD                  | YES          |
| 'root'@'localhost'       | def           | SHUTDOWN                | YES          |
| 'root'@'localhost'       | def           | PROCESS                 | YES          |
| 'root'@'localhost'       | def           | FILE                    | YES          |
| 'root'@'localhost'       | def           | REFERENCES              | YES          |
| 'root'@'localhost'       | def           | INDEX                   | YES          |
| 'root'@'localhost'       | def           | ALTER                   | YES          |
| 'root'@'localhost'       | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost'       | def           | SUPER                   | YES          |
| 'root'@'localhost'       | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost'       | def           | LOCK TABLES             | YES          |
| 'root'@'localhost'       | def           | EXECUTE                 | YES          |
| 'root'@'localhost'       | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost'       | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost'       | def           | CREATE VIEW             | YES          |
| 'root'@'localhost'       | def           | SHOW VIEW               | YES          |
| 'root'@'localhost'       | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost'       | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost'       | def           | CREATE USER             | YES          |
| 'root'@'localhost'       | def           | EVENT                   | YES          |
| 'root'@'localhost'       | def           | TRIGGER                 | YES          |
| 'root'@'localhost'       | def           | CREATE TABLESPACE       | YES          |
| 'mysql.sys'@'localhost'  | def           | USAGE                   | NO           |
| 'parser'@'127.0.0.1'     | def           | SELECT                  | YES          |
| 'parser'@'127.0.0.1'     | def           | INSERT                  | YES          |
| 'parser'@'127.0.0.1'     | def           | UPDATE                  | YES          |
| 'parser'@'127.0.0.1'     | def           | DELETE                  | YES          |
| 'parser'@'127.0.0.1'     | def           | CREATE                  | YES          |
| 'parser'@'127.0.0.1'     | def           | DROP                    | YES          |
| 'parser'@'127.0.0.1'     | def           | RELOAD                  | YES          |
| 'parser'@'127.0.0.1'     | def           | SHUTDOWN                | YES          |
| 'parser'@'127.0.0.1'     | def           | PROCESS                 | YES          |
| 'parser'@'127.0.0.1'     | def           | FILE                    | YES          |
| 'parser'@'127.0.0.1'     | def           | REFERENCES              | YES          |
| 'parser'@'127.0.0.1'     | def           | INDEX                   | YES          |
| 'parser'@'127.0.0.1'     | def           | ALTER                   | YES          |
| 'parser'@'127.0.0.1'     | def           | SHOW DATABASES          | YES          |
| 'parser'@'127.0.0.1'     | def           | SUPER                   | YES          |
| 'parser'@'127.0.0.1'     | def           | CREATE TEMPORARY TABLES | YES          |
| 'parser'@'127.0.0.1'     | def           | LOCK TABLES             | YES          |
| 'parser'@'127.0.0.1'     | def           | EXECUTE                 | YES          |
| 'parser'@'127.0.0.1'     | def           | REPLICATION SLAVE       | YES          |
| 'parser'@'127.0.0.1'     | def           | REPLICATION CLIENT      | YES          |
| 'parser'@'127.0.0.1'     | def           | CREATE VIEW             | YES          |
| 'parser'@'127.0.0.1'     | def           | SHOW VIEW               | YES          |
| 'parser'@'127.0.0.1'     | def           | CREATE ROUTINE          | YES          |
| 'parser'@'127.0.0.1'     | def           | ALTER ROUTINE           | YES          |
| 'parser'@'127.0.0.1'     | def           | CREATE USER             | YES          |
| 'parser'@'127.0.0.1'     | def           | EVENT                   | YES          |
| 'parser'@'127.0.0.1'     | def           | TRIGGER                 | YES          |
| 'parser'@'127.0.0.1'     | def           | CREATE TABLESPACE       | YES          |
| 'simple_cms'@'localhost' | def           | USAGE                   | NO           |
| 'root'@'%'               | def           | SELECT                  | YES          |
| 'root'@'%'               | def           | INSERT                  | YES          |
| 'root'@'%'               | def           | UPDATE                  | YES          |
| 'root'@'%'               | def           | DELETE                  | YES          |
| 'root'@'%'               | def           | CREATE                  | YES          |
| 'root'@'%'               | def           | DROP                    | YES          |
| 'root'@'%'               | def           | RELOAD                  | YES          |
| 'root'@'%'               | def           | SHUTDOWN                | YES          |
| 'root'@'%'               | def           | PROCESS                 | YES          |
| 'root'@'%'               | def           | FILE                    | YES          |
| 'root'@'%'               | def           | REFERENCES              | YES          |
| 'root'@'%'               | def           | INDEX                   | YES          |
| 'root'@'%'               | def           | ALTER                   | YES          |
| 'root'@'%'               | def           | SHOW DATABASES          | YES          |
| 'root'@'%'               | def           | SUPER                   | YES          |
| 'root'@'%'               | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'%'               | def           | LOCK TABLES             | YES          |
| 'root'@'%'               | def           | EXECUTE                 | YES          |
| 'root'@'%'               | def           | REPLICATION SLAVE       | YES          |
| 'root'@'%'               | def           | REPLICATION CLIENT      | YES          |
| 'root'@'%'               | def           | CREATE VIEW             | YES          |
| 'root'@'%'               | def           | SHOW VIEW               | YES          |
| 'root'@'%'               | def           | CREATE ROUTINE          | YES          |
| 'root'@'%'               | def           | ALTER ROUTINE           | YES          |
| 'root'@'%'               | def           | CREATE USER             | YES          |
| 'root'@'%'               | def           | EVENT                   | YES          |
| 'root'@'%'               | def           | TRIGGER                 | YES          |
| 'root'@'%'               | def           | CREATE TABLESPACE       | YES          |
| 'parser'@'%'             | def           | SELECT                  | YES          |
| 'parser'@'%'             | def           | INSERT                  | YES          |
| 'parser'@'%'             | def           | UPDATE                  | YES          |
| 'parser'@'%'             | def           | DELETE                  | YES          |
| 'parser'@'%'             | def           | CREATE                  | YES          |
| 'parser'@'%'             | def           | DROP                    | YES          |
| 'parser'@'%'             | def           | RELOAD                  | YES          |
| 'parser'@'%'             | def           | SHUTDOWN                | YES          |
| 'parser'@'%'             | def           | PROCESS                 | YES          |
| 'parser'@'%'             | def           | FILE                    | YES          |
| 'parser'@'%'             | def           | REFERENCES              | YES          |
| 'parser'@'%'             | def           | INDEX                   | YES          |
| 'parser'@'%'             | def           | ALTER                   | YES          |
| 'parser'@'%'             | def           | SHOW DATABASES          | YES          |
| 'parser'@'%'             | def           | SUPER                   | YES          |
| 'parser'@'%'             | def           | CREATE TEMPORARY TABLES | YES          |
| 'parser'@'%'             | def           | LOCK TABLES             | YES          |
| 'parser'@'%'             | def           | EXECUTE                 | YES          |
| 'parser'@'%'             | def           | REPLICATION SLAVE       | YES          |
| 'parser'@'%'             | def           | REPLICATION CLIENT      | YES          |
| 'parser'@'%'             | def           | CREATE VIEW             | YES          |
| 'parser'@'%'             | def           | SHOW VIEW               | YES          |
| 'parser'@'%'             | def           | CREATE ROUTINE          | YES          |
| 'parser'@'%'             | def           | ALTER ROUTINE           | YES          |
| 'parser'@'%'             | def           | CREATE USER             | YES          |
| 'parser'@'%'             | def           | EVENT                   | YES          |
| 'parser'@'%'             | def           | TRIGGER                 | YES          |
| 'parser'@'%'             | def           | CREATE TABLESPACE       | YES          |
+--------------------------+---------------+-------------------------+--------------+

Can you logged in with that use and password in mysql workbench? — TuyenNTA, Jul 29 '17 at 22:30
Besides granting all privileges, you must also add this user in the list of users allowed to connect. You can find this somewhere in mysql server configuration. — Mike Nakis, Jul 29 '17 at 22:31
yes, of course i can login with command line, workbench and even same credentials work in python script — iColdBeZero, Jul 29 '17 at 22:32
Did you check for [this case](https://stackoverflow.com/a/11216911/4039840) — TuyenNTA, Jul 29 '17 at 22:33
i did, as i mentioned before i can login through command line and workbench, and user has privileges. — iColdBeZero, Jul 29 '17 at 22:37
check in mysql configuration file if you have bind-address = 127.0.0.1 uncommented to enable network access on localhost. Also create an aditional user parse@127.0.0.1 with the same password. — Juan, Jul 29 '17 at 22:41
Double checked that. bind adress is commented out, created user as you suggest still doesn't work. — iColdBeZero, Jul 29 '17 at 22:58

score -1 · Answer 1 · answered Jul 29 '17 at 22:58

-1

It seems like the permission of user "parser" is not correct. You can check the configuration of user with statement.

SELECT * FROM  information_schema.USER_PRIVILEGES;

If you have granded an user new permission you have to reload the settings. You can do this with this FLUSH PRIVILEGES;

How To Grant Different User Permissions:

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables INSERT- allows them to insert rows into tables
SELECT- allows them to use the Select command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this framework:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

I hope this will help you solve your issue. Never forget to flush!!!

answered Jul 29 '17 at 22:58

Noob

732
8
30

As i mentioned in my question user HAS all privileges, but still when trying to log through java program access is denied. – iColdBeZero Jul 29 '17 at 23:04
1

@iColdBeZero can you login with the user parser and see the database in the list ? If you have control of your database via the mysql command line there is an problem in your java code. Did you flush your settings as i mentioned ? – Noob Jul 29 '17 at 23:08
yes i can login and yes i flushed privileges, java code shouldn't be a problem, i just don't know what to do. – iColdBeZero Jul 29 '17 at 23:10
1

@iColdBeZero can you show me your user permission with the given select query ? – Noob Jul 29 '17 at 23:13
@iColdBeZero In your user permission i dont see that your user parser have permission to connect to the database only the 127.0.0.1 address. GRANT ALL PRIVILEGES ON *.* TO 'parser'@'localhost' IDENTIFIED BY 'parser' WITH GRANT OPTION; This should fix your problem. If you change your localhost to 127.0.0.1 it will also be fixed. – Noob Jul 29 '17 at 23:26
sry, but this wasn't, problem still presist =/ – iColdBeZero Jul 29 '17 at 23:55
@iColdBeZero can you login with the root user ? – Noob Jul 29 '17 at 23:58
through java? no through console yes – iColdBeZero Jul 30 '17 at 00:05
@iColdBeZero then your problem seems in the jave code and not with the permision i guess since root is also not working with your java code. – Noob Jul 30 '17 at 00:12
i would assume that, but it's simplest 3 line code, i can't imagine it has a problem – iColdBeZero Jul 30 '17 at 00:18
@iColdBeZero can you share your full code? Is the mysql driver loaded ? – Noob Jul 30 '17 at 10:50

Access denied when using JDBC to connect to MySQL

1 Answers1