MySQL Bugs: #61203: noAccessToProcedureBodies does not work anymore

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:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.5.16	OS:	Linux
Assigned to:	John Russell	CPU Architecture:	Any
Tags:	jdbc, noAccessToProcedureBodies

Description:
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.

connector test program

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

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());

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

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()) {
			fakeParameterTypes(true);
			
			return;
		}

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+).

ok, we'll try it. thanks

Underprivileged user definition

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

Test case

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

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()) {
			paramTypesRs.previous();
			hasResults = true;
		}
	} catch (Exception e) {
		// paramTypesRs is empty, proceed with fake params. swallow, was expected 
	}
	if (hasResults){
		convertGetProcedureColumnsToInternalDescriptors(paramTypesRs);
	} else {
		fakeParameterTypes(true);
	}

In short, the change I made is not documented and your case works for me no matter what I do.

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

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);
			cStmt.execute();
			cStmt.close();
			con.close();
			System.out.println("success");
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		finally {
			if (con != null) {
				con.close();
			}
		}

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.

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.

Cyril, Pankaj, I am still unable to repeat the problem...

Env:
  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@192.168.1.4 on test
--cut--
		   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
BEGIN
 SET @a = b + c;
END
>>>> 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'@'192.168.1.4' for tabl
e 'proc'
mysql> SHOW PROCEDURE STATUS LIKE 'testbug61203again' ... and so on

So, still can't repeat.

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

tonci,

No it fails when I use the the same name in create and call on my server ... (I didn't try to set useinformationschema)

Cyril, please use useInformationSchema=true as a workaround for now while I check against 5.5 server.

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?

Bug#63839 was marked as duplicate of this report.

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.

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.

Fixed in r1118.

Added to changelog for 5.1.19: 

Underprivileged execution of stored procedures fixed.