Cannot connect to mysql programmatically

Question

I have setup mysql (mariadb) on linux machiche. I have created a user 'newuser' like:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

when i do :

select user,password from user;

i can see:

+--------------+-------------------------------------------+
| user         | password                                  |
+--------------+-------------------------------------------+
| root         | *password                                 |
| newuser      | *password                                 |
+--------------+-------------------------------------------+

and when i execute this command :

select user();

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

I am trying to connect to my database using java but i am getting below exception:

java.sql.SQLException: Cannot create connection:Access denied for user 'newuser'@'localhost' (using password: YES)

+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for newuser@localhost                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' IDENTIFIED BY PASSWORD '*7ABDF971526E9441B919C9FE77D50DB0363B3509' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------+

Java code to connect to db:

try {
        Class.forName("com.mysql.jdbc.Driver");
    //  Connection connection = DriverManager.getConnection(connectionString);
        Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/iot?autoReconnect=true","newuser","password");
        System.out.println("Connected --->"  + connection);
        return connection;
    } catch (SQLException e) {
        throw new SQLException("Cannot create connection:" + e.getMessage());
    }

Can anyone help me what i am missing?

Using `GRANT` does not require flushing - https://stackoverflow.com/questions/36463966/when-is-flush-privileges-in-mysql-really-needed — tomaytotomato, Apr 04 '18 at 15:40
@tomaytotomato yep, I found that while looking for documentation to point OP to. I'll just leave my comment there as a testament to my own ignorance. — Federico klez Culloca, Apr 04 '18 at 15:41
@Rohitesh try this and see what it says `SHOW GRANTS FOR 'newuser'@'localhost'` — tomaytotomato, Apr 04 '18 at 15:44
@Rohitesh please paste the java code used to connect to the DB. — Alexius DIAKOGIANNIS, Apr 04 '18 at 15:49
@Rohitesh can you try `GRANT ALL PRIVILEGES ON *.* TO 'newuser2'@'localhost' IDENTIFIED BY 'password';` and try to connect with newuser2? Also what version are you using? — Alexius DIAKOGIANNIS, Apr 04 '18 at 16:04
@AlexiusDiakogiannis Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0 — Rohitesh, Apr 04 '18 at 16:05
@Rohitesh I dont see the Grant usage permission above and I guess this causes the issue. — Alexius DIAKOGIANNIS, Apr 04 '18 at 16:06

score 1 · Answer 1 · answered Apr 04 '18 at 16:37

1

Try to connect to mysql and do

update user set host = '%' where user = 'newuser';
flush privileges;

Try to connect again. If you succeed then (as I suspect) you are NOT running the code on the same machine as mysql. You need to find the ip of your local machine (ifconfig on linux, ipconfig on windows) and execute

update user set host = 'xxx.xxx.xxx.xxx.' where user = 'newuser';
flush privileges;

to allow connections from your ip (xxx.xxx.xxx.xxx) only.

To revert changes execute

update user set host = 'localhost' where user = 'newuser';
flush privileges;

answered Apr 04 '18 at 16:37

Alexius DIAKOGIANNIS

2,465
2
21
32

now its getting connected but one thing i want to know i am running mysql on rashbeery and trying to connect using remote machine using mysql workbench why its not connecting from workbench . Do i need to allow the tcp connection for remote connection? – Rohitesh Apr 04 '18 at 16:49
You are probably trying to connect from workbench as root so you need to re-run the procedure for the root user or use the option in workbench in order to use an ssh tunnel. Since you managed to connect and my answer help you to identify and solve the issue can you be so kind to mark my answer as a solution? Thanks. – Alexius DIAKOGIANNIS Apr 04 '18 at 17:45

score 0 · Answer 2 · answered Apr 04 '18 at 15:46

0

Do you actually connect from the local machine? If not, you should substitute the "localhost" with a description of your host.

answered Apr 04 '18 at 15:46

Daniel Metzger

1
1

1

@danielmetzer what actually you mean to say can you please elobrate ? – Rohitesh Apr 04 '18 at 15:54

score 0 · Answer 3 · answered Apr 04 '18 at 16:36

As per your scenario, I think you should have used

    FLUSH PRIVILEGES;

In case of modification of privilege FLUSH not needed as it will be reloaded but here you are creating new user and grant new privilege to new user so try with FLUSH PRIVILEGES.

I have used the following steps and it is working fine here:-

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Now login with new newuser:-

     mysql -u newuser -p
     password
     show GRANTS;

    Output:- 
    +------------------------------------------------------+
    | Grants for newuser@localhost                         |
    +------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' |
    +------------------------------------------------------+

As select user() -- It will show current user which is logged in.
SELECT User FROM mysql.user ---It will show all users created in mysql.

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        //  Connection connection = DriverManager.getConnection(connectionString);
            Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/iot?autoReconnect=true","newuser","password");
            System.out.println("Connected --->"  + connection);
        } catch (SQLException e) {
            throw new SQLException("Cannot create connection:" + e.getMessage());
        } 

        }

It is running without error.I don't see any issues here.Have a look on it.

FLUSH PRIVILEGES; is NOT needed and if you read carefully the comments he has tried it with no success. — Alexius DIAKOGIANNIS, Apr 04 '18 at 16:39
Yeah ! Then It is IP issue as 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0 must be connecting on different addresses. It is not privilege problem. java.sql.SQLException: Cannot create connection means it must be connecting to some other host as As password and user name seems correct. — Himanshu Sharma, Apr 04 '18 at 17:13

Cannot connect to mysql programmatically

3 Answers3