I've created some stored procedures on a mysql database, but when I try to execute them I get:
User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
I added to my connection string noAccessToProcedureBodies=true, but I still get this message. I use the DriverManager.getConnection(url, user, password) method, and the url I use is
jdbc:mysql://[server-url]/[database-name]?noAccessToProcedureBodies=true
EDIT: what I meant was that I have a user, called creautenti, that I use in this method to create new users:
public static boolean creazioneUtente(String user, String password) throws EccezioneDaMostrare{
if(apriConnessione(CREA_UTENTI_USER, CREA_UTENTI_PW, false)){
try{
conn.setAutoCommit(false);
String sqlCommand="CREATE USER ? @'%' IDENTIFIED BY PASSWORD ?";
String sqlCommandGrant="GRANT EXECUTE ON salvataggi.* TO ? REQUIRE SSL;";
String sqlCommandGrantEx="GRANT SELECT ON `mysql`.`proc` TO ? @'%';";
PreparedStatement s=conn.prepareStatement(sqlCommand);
PreparedStatement sGrant=conn.prepareStatement(sqlCommandGrant);
PreparedStatement sGrantEx=conn.prepareStatement(sqlCommandGrantEx);
s.setString(1, user);
sGrant.setString(1, user);
sGrantEx.setString(1, user);
s.setString(2, mySQLPASSWORD(password));
s.execute();
sGrant.executeUpdate();
sGrantEx.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
chiudiConnessione();
return true;
}
catch(SQLException e){
try {
conn.rollback();
} catch (SQLException e1) {
String msg=String.format("Errore durante la connessione al server MySQL:\n Messaggio:%s \n Stato SQL:%s \n Codice errore:%s", e.getMessage(), e.getSQLState(), e.getErrorCode());
throw new EccezioneDaMostrare(msg);
}
chiudiConnessione();
return false;
}
}
else
return false;
}
(the first line just opens a connection with the server as creautenti, and conn is the Connection object). So I logged in as root on the server, and called
GRANT SELECT ON `mysql`.`proc` TO 'creautenti'@'%' WITH GRANT OPTION;
which updated creautenti's privileges accordingly, but when I call
GRANT SELECT ON `mysql`.`proc` TO '<username>'@'%';
logged in as creautenti on any other user, the privileges aren't modified. All the routines are in salvataggi, on which I give EXECUTE privileges to every user, so I don't think that's the problem either.
The Informix JDBC driver provides the Statement , PreparedStatement , and CallableStatement methods, which can be used to execute stored procedures.
There are two different ways to execute a stored procedure. The first and most common approach is for an application or user to call the procedure. The second approach is to set the procedure to run automatically when an instance of SQL Server starts.
CallableStatement: It is used to execute SQL stored procedures.
You might be better off actually granting access on the mysql.proc
table to your application user. So connect to your MySQL database as root and run the following:
GRANT SELECT ON `mysql`.`proc` TO '<username>'@'%';
Your Java app should then be able to see the correct metadata without having to specify noAccessToProcedureBodies=true
Also ensure that the user under which you are connecting to the database has execute privileges on the procedure in question. Again, as root user or a user with grant privileges:
GRANT EXECUTE ON PROCEDURE db.storedproc TO '<username>'@'%';
Good luck!
TLDR; Change your Callable Statement to reference parameters by index instead of name.
After having a similar problem I updated my JDBC driver mysql-connector-java-5.1.26-bin.jar. The error then changed from
User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
to
No access to parameters by name when connection has been configured not to access procedure bodies
I changed my Callable Statement to reference parameters by index instead of name, and hey presto it works.
Updating the driver may not be necessary, just knowing to use indexes instead of names when you don't have metadata access or routine body access.
Good Luck
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With