Question:
When I try to connect to the database with MySQL Connector/J, I get the following exception:
SQLException: Server configuration denies access to data source
SQLState: 08001
VendorError: 0
What’s going on? I can connect just fine with the MySQL command-line client.
Answer:
MySQL Connector/J must use TCP/IP sockets to connect to MySQL, as Java does not support Unix Domain Sockets. Therefore, when MySQL Connector/J connects to MySQL, the security manager in MySQL server will use its grant tables to determine whether the connection should be allowed.
You must add grants to allow this to happen. The following is an example of how to do this (but not the most secure).
From the mysql command-line client, logged in as a user that can grant privileges, issue the following command:
GRANT ALL PRIVILEGES ON [dbname].* to
‘[user]’@’[hostname]’ identified by
‘[password]’
replacing [dbname] with the name of your database, [user] with the user name, [hostname] with the host that MySQL Connector/J will be connecting from, and [password] with the password you want to use. Be aware that RedHat Linux is broken with respect to the hostname portion for the case when you are connecting from localhost. You need to use “localhost.localdomain” for the [hostname] value in this case. Follow this by issuing the “FLUSH PRIVILEGES” command.
Note
Testing your connectivity with the mysql command-line client will not work unless you add the --host flag, and use something other than localhost for the host. The mysql command-line client will use Unix domain sockets if you use the special hostname localhost. If you are testing connectivity to localhost, use 127.0.0.1 as the hostname instead.
Warning
If you don’t understand what the ‘GRANT’ command does, or how it works, you should read and understand the ‘General Security Issues and the MySQL Access Privilege System’ section of the MySQL manual before attempting to change privileges.
Changing privileges and permissions improperly in MySQL can potentially cause your server installation to not have optimal security properties.