1

I have written some java code to connect to a database I have created in mysql.

I have never used mysql before. I just installed, and ran "sudo mysql", which then allowed me to create a db, and add a table.

The java code to connect to the db is as follows....

Connection conn = null;

try {
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb");

} catch (SQLException ex) {
    // handle any errors
    System.out.println("SQLException: " + ex.getMessage());
    System.out.println("SQLState: " + ex.getSQLState());
    System.out.println("VendorError: " + ex.getErrorCode());
}

When I try to run this code, an exception is thrown, and printed out as follows...

SQLException: Access denied for user ''@'localhost' (using password: NO) SQLState: 28000 VendorError: 1045

I looked at the apis and one of the getConnection variants take a username and password, which I supplied root and my root password. This didn't work either.

But more than this specific error, I am not just not sure how to approach user and passwords when connecting to mysql from java on linux. Is it really acceptable to put my root password in a string literal in code? Should I always create a new separate user other than root, to connect to the database? Is there a more secure place where I can put my user and password, to connect to mysql?

Abichellam
  • 509
  • 2
  • 7
  • 17
Scorb
  • 1,654
  • 13
  • 70
  • 144
  • "Is it really acceptable to put my root password in a string literal in code?" No, you should never directly interact with your database from a front facing application. All access should be done through an API (such as a REST API) – Matthew Kerian Jul 18 '19 at 03:56
  • The application is not front facing. – Scorb Jul 18 '19 at 03:57
  • 1
    can you connect to the database using `mysql` CLI client using root username and password?. Creating a new user with access to a particular DB is a better idea. You put the username and password in properties file and then retrieve it from there. – Ryotsu Jul 18 '19 at 04:08
  • Yes. I mentioned that I can run "sudo mysql" and then enter the root password. Then it drops to the mysql prompt. – Scorb Jul 18 '19 at 04:10
  • could you try this `DriverManager.getConnection("jdbc:mysql://localhost/test?"+"user=root&password=your_root_pass");` taken from here [https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.html] – Ryotsu Jul 18 '19 at 04:14
  • "I looked at the apis and one of the getConnection variants take a username and password, which I supplied root and my root password " -- did you get the same exception? – Ryotsu Jul 18 '19 at 04:18
  • 1
    Rather than using a root user and password, I recommend making a [new user with permissions to access that database](https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql) after accessing the database with the command line. From there, you can use that username and password to access it through Java. – Tim Hunter Jul 18 '19 at 04:32
  • Basic workflow I use for stuff like this at my job is first I access MySQL using the `sudo mysql` command like you did, create the new database using SQL commands, add a new user with permission to all tables using SQL like in the link I posted above, then use this newly created user to start building my database tables using an ORM modeler (my place of work uses [Apache Cayenne](https://cayenne.apache.org/) for this). From there, use Java to access the database also using the created username/password (here my job also uses the ORM since it has functions to make working with it easy in Java). – Tim Hunter Jul 18 '19 at 04:43
  • You shouldn't use the root password, you should create an application (or user) specific user with minimal but necessary rights. – Mark Rotteveel Jul 18 '19 at 08:45

1 Answers1

0

I am just trying to sum up the answers in the comments.

Firstly the error you are getting means you are providing wrong credentials for login, or you are not providing the required fields like the password. I guess you have not set a password for the root user in MySQL.

Try mysql -u root -p and press Enter when prompted for a password. If you get error, saying ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) try using root as password.

Try using this method for connection to MySQL.

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "password");

If none of the above work, Try resetting the root password, as mentioned in the Ubuntu Help page

  1. Log in with your password in MySQL mysql -u root -p

  2. Reset your password with SET PASSWORD FOR root@'localhost' = PASSWORD('password');

Designing a DBMS Application

  1. Login to MySQL as a root user and create a user on your MySQL server.
  2. If you are going to connect your MySQL DB from other computers, you should edit the config, to allow binding from other computers.
  3. You can put your password in a string literal if the code resides on your pc and it's for personal use. Else you can use API for accessing data from the server as mentioned in the comments.
Saravanan Rajenderan
  • 43
  • 7