Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 734
Orgrim Avatar asked Oct 24 '11 13:10

Orgrim


People also ask

Which is used to execute stored procedure from a JDBC program?

The Informix JDBC driver provides the Statement , PreparedStatement , and CallableStatement methods, which can be used to execute stored procedures.

How are stored procedures executed?

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.

Which of the following is used to execute stored procedures from a JDBC program Mcq?

CallableStatement: It is used to execute SQL stored procedures.


2 Answers

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!

like image 79
Tom Mac Avatar answered Sep 24 '22 21:09

Tom Mac


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

like image 20
Kickaha Avatar answered Sep 25 '22 21:09

Kickaha