CODEWITHSUNDEEP
mysqlstored-proceduresjdbc
Orgrim
Orgrim

Reputation: 357

stored procedures executed through jdbc

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.

Upvotes: 7

Views: 11227

Answers (3)

Vijay Bhatt
Vijay Bhatt

Reputation: 1391

Following steps worked for me in mysql

Step 1 : add/edit following

Connection con = DriverManager.getConnection("jdbc:mysql://ipaddress:3306/logparser?noAccessToProcedureBodies = true ", "root", "");

Step 2 : Execute following query GRANT ALL ON logparser.proc TO root@'%';

where logparser is DB name and proc is procedure name.

Upvotes: 1

Kickaha
Kickaha

Reputation: 3857

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

Upvotes: 3

Tom Mac
Tom Mac

Reputation: 9853

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!

Upvotes: 4

Related Questions