Access denied for user 'root'@'localhost'

java mysql jdbc access-denied

28,587

Solution 1

The problem was the permissions granted to root in the information.schema table. 'root'@'%' didnt have any permissions.. And as I was using 127.0.0.1 as my connection address, so it was giving the access denied error.. % is the wildcard for an ip address. so mysql considers [email protected] as any other ip address but not localhost. so just granting it permission solved my problem.. Try using some Mysql client like SQLYog etc.. it is easy to grant the privileges and also to view the privileges with the user.

Solution 2

Start mysql client in the console and execute this query: select Host, User from mysql.user;. You MUST have a row like this:

+----------------+------------------+  
| Host           | User             |  
+----------------+------------------+  
| localhost      | root             |
+----------------+------------------+

a row with "localhost" in Host and "root" in User. If you don't have it that's the cause of your problem (it doesn't matter if you have other rows with "root" in User)

If you don't have such row, add a new user with this:

CREATE USER 'appUser'@'localhost' IDENTIFIED BY 'appPassword';

Change 'appUser' by 'root' if you want, but I strongly suggest to use another user. Then add permissions to your new user by executing this in the mysql client:

GRANT ALL PRIVILEGES ON employees.* TO 'appUser'@'localhost';

(again, change 'appUser' by 'root' if you want)

Solution 3

Try it:

mysql --no-defaults --force --user=root --host=localhost --database=mysql

Change a new password:

UPDATE user SET Password=PASSWORD('NEWPASSWORD') where USER='root';
FLUSH PRIVILEGES;

28,587

Author by

Mitaksh Gupta

Updated on February 13, 2020

Comments

Mitaksh Gupta over 4 years

I am trying to fetch records from database. but I am facing this access denied issue. I tried the other solutions mentioned on Stack Overflow like granting privilege to the user.. but none worked.

Code for accessing database :

public void service(HttpServletRequest request,HttpServletResponse response) throws IOException, ServletException{
  response.setContentType("text/html");
  PrintWriter out = response.getWriter();
  out.println("<html>");
  out.println("<head><title>Servlet JDBC</title></head>");
  out.println("<body>");
  out.println("<h1>Servlet JDBC</h1>");
  out.println("</body></html>");  
  // connecting to database
  Connection con = null;  
  Statement stmt = null;
  ResultSet rs = null;
  try {
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost:3306/employees","root","root");
      stmt = con.createStatement();
      rs = stmt.executeQuery("SELECT * FROM employee");
      // displaying records
      while(rs.next()){
      out.print(rs.getObject(1).toString());
      out.print("\t\t\t");
      out.print(rs.getObject(2).toString());
      out.print("<br>");
  }
  } catch (SQLException e) {
      throw new ServletException("Servlet Could not display records.", e);
  } catch (ClassNotFoundException e) {
      throw new ServletException("JDBC Driver not found.", e);
  } finally {
      try {
          if(rs != null) {
              rs.close();
              rs = null;
          }
          if(stmt != null) {
              stmt.close();
              stmt = null;
          }
          if(con != null) {
              con.close();
              con = null;
          }
      } catch (SQLException e) {}
  }
    out.close();
  }

Stack trace of the error :

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
    com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4686)
    com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1304)
    com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2483)
    com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2516)
    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2301)
    com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
    com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    java.lang.reflect.Constructor.newInstance(Unknown Source)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
    com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
    java.sql.DriverManager.getConnection(Unknown Source)
    java.sql.DriverManager.getConnection(Unknown Source)
    WebAppAss.DatabaseAccess.service(DatabaseAccess.java:36)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

What could be the problem in this case. I tried creating a new database but that didn't work too.

Mitaksh Gupta almost 11 years

I already have the root user in users... I created a new user but the command u mentioned to grant privileges gave the following error : ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'previliges on employees.* to 'dbUser'@'localhost'' at line 1
morgano almost 11 years

you have a typo: "previliges" instead of "privileges"
Mitaksh Gupta almost 11 years

the command ran successfully.. but still same error.. the new user I created was with the name 'dbUser' but still when I run my application, it is giving the same error that access denied for 'root' even when I changed the name to dbUser in the connection string
kavita over 10 years

I had the same error and even after adding a user 'root'@'%', granting it all permissions the same error is coming. Access Denied for 'root'@'localhost'(using password(yes))
Mitaksh Gupta over 10 years

did you try to add root@localhost to information.schema?
ankit almost 10 years

Thanks Gustavo. That above query help me in flushing the privileges for the root.