[스프링 프레임워크] Spring JDBC SQLException Access denied for user 오류 해결 방법

java.sql.SQLException: Access denied for user 예외

스프링 프레임워크에서 JDBC 연동을 하는데, MySQL 서버에 대한 DataSource 생성시 예외가 발생했습니다. 정확히는 커넥션 연결시 발생한 것입니다.

[main] [2018-07-23 14:47:17] INFO  com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource:getPoolManager - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hgemtd9w1iujnxhk06s8g|6325a3ee, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hgemtd9w1iujnxhk06s8g|6325a3ee, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.111.128:3306/springdb?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
[C3P0PooledConnectionPoolManager[identityToken->1hgemtd9w1iujnxhk06s8g|6325a3ee]-HelperThread-#2] [2018-07-23 14:47:47] WARN  com.mchange.v2.resourcepool.BasicResourcePool:run - com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@921fb09 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:

java.sql.SQLException: Access denied for user 'springuser'@'DESKTOP-9BQHVLC' (using password: YES)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:926)
      at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1748)

문제 원인 및 해결 방법

서버에 대한 접근 정보는 이상이 없어보이고 예외 메시지를 보니 Access denied for user 입니다. 즉 서버에 연결은 이상이 없으나 특정 DB 유저에 대해 접근을 거부한 것입니다. 해결 방법은 매우 간단하게 DBMS에서 해당 유저에 대해 접근을 허용해주면 됩니다.

MySQL의 경우에는 다음과 같이 유저 권한을 부여합니다.

GRANT ALL PRIVILEGES ON [DB 또는 *].* TO '계정'@'접근 허용 대상';

GRANT ALL PRIVILEGES ON springdb.* TO 'springuser'@'localhost';
GRANT ALL PRIVILEGES ON springdb.* TO 'springuser'@'%';