Bug #61203 noAccessToProcedureBodies does not work anymore
Submitted: 17 May 2011 15:10 Modified: 22 Mar 2012 19:16
Reporter: Cyril SCETBON Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S1 (Critical)
Version:5.5.16 OS:Linux
Assigned to: John Russell CPU Architecture:Any
Tags: jdbc, noAccessToProcedureBodies

[17 May 2011 15:10] Cyril SCETBON
a java program (calling a mysql procedure) that worked with version 5.1.13 does not work anymore. It complains about privileges access on the procedure body although we use noAccessToProcedureBodies=true in the connection string.

How to repeat:
create a procedure in a test database that accepts one argument of type varchar, and use the code attached with both versions 5.1.13 and 5.1.16.
it'll print 'It works :)' with version 5.1.13 but will crash with the following exception with jdbc connector 5.1.16 :

Exception: 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.
[17 May 2011 15:13] Cyril SCETBON
connector test program

Attachment: JSandBox.java (text/x-java), 1021 bytes.

[17 May 2011 16:21] Cyril SCETBON
It comes from the function determineParameterTypes in the file src/com/mysql/jdbc/CallableStatement.java
In version 5.1.13, if the parameter NoAccessToProcedureBodies is set, you called fakeParameterTypes while now (5.1.16) you throw an exception !!

if (this.connection.getNoAccessToProcedureBodies()) {
                        throw SQLError.createSQLException("No access to parameters by name when connection has been configured not to access procedure bodies", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
[17 May 2011 16:43] Cyril SCETBON
sorry I've copy/pasted the wrong code concerning the code causing the exception, but it really seems to come from the function determineParameterTypes. I think it may come from the call you make to this.connection.getMetaData without testing this.connection.getNoAccessToProcedureBodies, but I didn't check deeper in your code.

One more information is that you stopped using fakeParameterTypes when NoAccessToProcedureBodies is ON in version 5.1.14. I've tested my sample code and it confirms that it starts failing in version 5.1.14
[20 May 2011 13:42] Tonci Grgin
Cyril, I will have to dig deeper into this but, for now, I know why I removed this:
	private void determineParameterTypes() throws SQLException {
		if (this.connection.getNoAccessToProcedureBodies()) {

It was preventing "underprivileged" user to run stored procedures create by him! This was a side-effect of many bug-fixes related to Bug#56305.

Even though this code is an "old" one (ie. for pre-I__S servers) I'll still have to find a good place for
		if (this.connection.getNoAccessToProcedureBodies()) {
which will satisfy both conditions.

In the meantime, please try useInformationSchema=true for underprivileged users if your MySQL server supports it (5.0.7+).
[20 May 2011 17:15] Cyril SCETBON
ok, we'll try it. thanks
[24 May 2011 7:53] Tonci Grgin
Underprivileged user definition

Attachment: UserPrivs-bug61203.txt (text/plain), 3.91 KiB.

[24 May 2011 7:53] Tonci Grgin
Test case

Attachment: TestBug61203.java (text/java), 5.47 KiB.

[24 May 2011 7:59] Tonci Grgin
Cyril, I was unable to repeat the problem reported. Please see attached files and check if I missed something (and, of course, if they work for you).

Note that I changed the definition from FUNCTION to PROCEDURE during the test. This is to conform to your test case, works for me either way.

fakeParameterTypes gets called from determineParameterTypes only not based on noAccessToProcedureBodies parameter value but rather on actual failure to determine parameters:
	boolean hasResults = false;
	try {
		if (paramTypesRs.next()) {
			hasResults = true;
	} catch (Exception e) {
		// paramTypesRs is empty, proceed with fake params. swallow, was expected 
	if (hasResults){
	} else {

In short, the change I made is not documented and your case works for me no matter what I do.
[26 May 2011 16:02] Cyril SCETBON
It was hard to find, but I got the same error with your test case. However, I had to use the same letter case in all CREATE/CALL procedure cause it's important on Linux.
The matter is that you create a procedure called testbug10310again, but you call the procedure TestBug10310Again !!

I think the reason you didn't see the error is that show create procedure is case insensitive while show procedure status like is case sensitive. your code should see that this last statement does not return information and throw the same exception but that's not the case.

the test fails on cStmt2
[15 Dec 2011 1:24] Pankaj Karn
I am experiencing this bug on my project. Here is code that I am running:
Connection con = null;

		try {
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useInformationSchema=true&noAccessToProcedureBodies=true", "basic", "basic11");
			CallableStatement cStmt = null;
			cStmt = con.prepareCall("{call test_sproc(?)}");
			cStmt.setInt(1, 1);
		} catch (Exception e) {
		finally {
			if (con != null) {

This code works with v5.1.13, but when run with v5.1.18 throws exception "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." 

However, if I go to mysql workbench and call stored procedure ( call test_sproc(1) ) with same user credential, it works fine.
[16 Dec 2011 7:49] Tonci Grgin
Cyril, the behavior you described would mean there is no bug in c/J actually. If you use same procedure name then all should work, right? Why I changed casing in my test case, I can not remember any more but I do remember I did it on purpose.

Your findings are described in verified Bug#48445.

Now, I'll recheck this patch.
[16 Dec 2011 8:31] Tonci Grgin
Cyril, Pankaj, I am still unable to repeat the problem...

  o MySQL server 5.1.31-log on OpenSolaris host.
  o "ci" collations used, so AnYCasE works (you are free to change all to lower-case, for example)
mysql> show variables like "%coll%";
| Variable_name        | Value             |
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | utf8_general_ci   |
  o My general query log shows correct exec path is chosen:

		   13 Connect	basic@ on test
		   13 Query	/* mysql-connector-java-5.1.19-SNAPSHOT ( Revision: mark.matthews@oracle.com-20111202162606-9z3xgaug3r11kv4i ) */SELECT @@session.auto_increment_increment
		   13 Query	SHOW COLLATION
		   13 Query	SET character_set_results = NULL
		   13 Query	SET autocommit=1
		   13 Query	SET sql_mode='STRICT_TRANS_TABLES'
		   13 Query	DROP PROCEDURE IF EXISTS testbug61203again
		   13 Query	CREATE PROCEDURE testbug61203again(a float, b bigint, c int) NO SQL
 SET @a = b + c;
>>>> FAILS	   13 Query	SELECT name, type, comment FROM mysql.proc WHERE name like 'testbug61203again' and db <=> 'test' ORDER BY name
>>>> So we move on 13 Query	SHOW PROCEDURE STATUS LIKE 'testbug61203again'
		   13 Query	SHOW FUNCTION STATUS LIKE 'testbug61203again'
		   13 Query	SHOW CREATE PROCEDURE `test`.`testbug61203again`
		   13 Query	CALL testbug61203again(2.0,1,1)
		   13 Quit	

mysql -ubasic -p -h192.168.1.2 test
Enter password: ********
Server version: 5.1.31-log MySQL Community Server (GPL)

mysql> SELECT name, type, comment FROM mysql.proc WHERE name like 'testbug61203a
gain' and db <=> 'test' ORDER BY name;
ERROR 1142 (42000): SELECT command denied to user 'basic'@'' for tabl
e 'proc'
mysql> SHOW PROCEDURE STATUS LIKE 'testbug61203again' ... and so on

So, still can't repeat.
[16 Dec 2011 10:46] Cyril SCETBON
Sorry but the tests were on done on 5.5 and not 5.1 so just replace each occurence of 5.1.x in my comments by 5.5.x


No it fails when I use the the same name in create and call on my server ... (I didn't try to set useinformationschema)
[16 Dec 2011 10:48] Tonci Grgin
Cyril, please use useInformationSchema=true as a workaround for now while I check against 5.5 server.
[16 Dec 2011 21:29] Pankaj Karn
I am adding few more details here:
- Env: Mysql Server version: 5.1.38-log MySQL Community Server (GPL)
- I am runnig my query against environment which is owned by DBAs. they did the deployment, so when I ran query 'SHOW PROCEDURE STATUS ....", definer field is admin user and not the user I am logging in.
Is that going to make any difference?
[22 Dec 2011 13:13] Tonci Grgin
Bug#63839 was marked as duplicate of this report.
[22 Dec 2011 14:09] Tonci Grgin
Pankaj, no. Test case attached covers just that.
A colleague of mine was able to repeat the problem with 5,5,18 running on Fedora host using JDK 1.5.0_22-b03. We are working on it now.
[3 Jan 2012 18:49] David Cross
I am seeing this bug also, and the workaround does not work (mysql server version 5.1.39); but its possible I don't have access to the information_schema table for it to work

I think the status should be updated from "Can't Repeat".  Is there an ETA on a fix; being open since May 2011 is quite the lag.
[27 Jan 2012 11:53] Alexander Soklakov
Fixed in r1118.
[22 Mar 2012 19:16] John Russell
Added to changelog for 5.1.19: 

Underprivileged execution of stored procedures fixed.