Why Java application can't execute procedures if I added grant all privileges?

user3637971 0
Viewed 45

I have a Java web application that uses MySQL. In the webapp log it shows the following message:

Error querying database.  Cause: java.sql.SQLException: 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.

Checking in this forum I saw a solution where I had to add GRANT SELECT ON mysql.proc TO webapp@'%'; in order to solve the problem. The thing is I see this solution as intrusive because I giving access to every procedures from databases to webapp user. Why shouldn't be enough adding grant all privileges on bdwebapp.* to webapp@'%'; in order to enable stored procedure executions from web application?

I see the problem appears when web application invokes the procedure. Even though, if I execute by mysql shell: call sp_getClientByCode(1); It gives the result without GRANT SELECT ON mysql.proc TO webapp@'%'.

This the Java instruction using mybatis when calling stored procedure.

@Select("{call sp_getClientByCode(#{clientId, mode=IN, jdbcType=INTEGER})}")
@Options(statementType = StatementType.CALLABLE)
List<App> getClientByCode(@Param("clientId") Integer userId);

This is the procedure implementation:

CREATE DEFINER=`webapp`@`%` PROCEDURE `sp_getClientByCode`(clientId INT)
BEGIN
    SELECT id, name FROM clients where id = clientId;
END
mybatis mariadb java mysql
1 Answers

The java connector is trying to validate the type of the arguments to your stored procedure independently of the mybatis declaration.

You could apply noAccessToProcedureBodies=true like the error message suggests.

A better solution however is to use MariaDB Connector/J that uses a MariaDB unprivileged operation (using information_schema.parameters) to examine the arguments of stored procedures.

danblack0
Related