MySQL Bugs: #46190: when procedure is run with connnect net, dont' need select routines schema

Bug #46190	when procedure is run with connnect net, dont' need select routines schema
Submitted:	15 Jul 2009 6:26	Modified:	16 Jul 2009 9:23
Reporter:	ws lee	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	5.2.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In net connector, use procedure bodies = true is dangerous option.
In order to use procedure bodies = true option, need grant mysql.proc.
but, mysql.proc have all db's procedure.
this very dangerous in secrity.
In example,
testdb1 use  company A and testdb2 user company B.
but, company A user can see Company B's procedure.

so, we must use procedure bodies = false.
but, if use "use procedure bodies = false" option, bad performance in many procedure number.

How to repeat:
1) connector net 5.2.3 and use procedure bodies = false
680865 Connect  test@test on 
680865 Query    SHOW VARIABLES
680865 Query    SHOW COLLATION
680865 Query    SET NAMES utf8;SET character_set_results=NULL
680865 Query    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'test' AND ROUTINE_NAME LIKE 'usp1001'   
680865 Query    call test.usp1001 ('123213', @1825110124param2)
680865 Query    SELECT @1825110124param2
680865 Quit  

this query is very slow. why select INFORMATION_SCHEMA.ROUTINES?
it is no problem only run call test.usp1001 ('123213', @1825110124param2).
 

P.S)
I have tested in connector net 1.0.10 and use procedure bodies = false

683703 Connect  test@test on 
683703 Query    SHOW VARIABLES
683703 Query    SHOW COLLATION
683703 Query    SET NAMES utf8;SET character_set_results=NULL
683703 Query    call test.usp9999('123213', @1522345314param2)
683703 Query    SELECT @1522345314param2
683703 Quit  

in this case not problem.(connector net 1.0.10 and and use procedure bodies = false and mysql.proc select not given)

Suggested fix:
When procedure run with connect net, don't select information_schema in case "use  procedure bodies = false" like connect net 1.0.10 version.

Hi Lee and thanks for your report.

Unfortunately, there are two things reported which both are not a bug and can't be done otherwise, at least not now.

  o Slow I__S performance is logged against MySQL server and in process of fixing. Please check Bug#19588.
  o Revert to c/NET 1.x behavior is also not an option as that kind of behavior has many many drawbacks and presents workaround and not functioning feature.

Your best chance is to wait on changed (faster) I__S implementation in server.

To. Tonci Grgin
Thanks you reply.

I want replya agins about two question.

question 1>
In Mysql Connector/Net 1.0.10.1, I have tested.
- case 1
strCON1 = "server=xxx;user id=xxx;password=xxx;pooling=false"

in this case, if procedure run, select INFORMATION_SCHEMA.ROUTINES tables.

696875 Connect   test@test.internal on 
696875 Query    SHOW VARIABLES
696875 Query    SHOW COLLATION
696875 Query    SET NAMES utf8;SET character_set_results=NULL
696875 Query    SELECT ROUTINE_SCHEMA, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='usp1'
696875 Query    SHOW CREATE PROCEDURE test.usp1
696875 Query    call test.usp1('123213', @2785751796param2)
696875 Query    SELECT @2785751796param2
696875 Quit     

but, in case 2 not use INFORMATION_SCHEMA.ROUTINES 
- case 2
strCON2 = "server=xxx;user id=xxx;password=xxx;pooling=false; use procedure bodies=false"

in this case, not used INFORMATION_SCHEMA.ROUTINES at all.
just is runed call test.usp1

696902 Connect  test@test.internal on 
696902 Query    SHOW VARIABLES
696902 Query    SHOW COLLATION
696902 Query    SET NAMES utf8;SET character_set_results=NULL
696902 Query    call test.usp1('123213', @3535034616param2)
696902 Query    SELECT @3535034616param2
696902 Quit

In Mysql Connector/Net 1.0.10.1, if use "use procedure bodies=false" option, no problem.
this is ideal action. 

※In Mysql Connector/Net 1.0.10.1, "use procedure bodies " option is supported???
Test you.

questin 2>
In Mysql Connector/Net 5.2.3,
when "use prodecure bodies = false" given, why select information_routines table?
Without selecting information_routines table, no problem.

※When "use prodecure bodies = false" given, I want just runned procedure(call procedure) without selecting information_routines, In Mysql Connector/Net 5.2.3,

I want reply the below two question.

Sure Lee.

> ※In Mysql Connector/Net 1.0.10.1, "use procedure bodies " option is supported???

The problem is that you don't read server/connector changelogs. c/NET 1.x was out before I__S implementation in server so above option was implemented differently.

> In Mysql Connector/Net 5.2.3, when "use prodecure bodies = false" given, why select information_routines table? Without selecting information_routines table, no problem.

And how do you think c/NET should know what to send and how to read it back? In c/NET 5.x "use prodecure bodies = false" means only that you do not have privilege to select from mysql.procs table thus I__S should be used.

My conclusion is that this is not a bug in connectors (any). Connectors *should* use I__S to collect metadata about stored procedures (and other stuff) as *all* of frameworks/API's request that. The fact that I__S implementation is sometimes slow/inadequate is not a connectors problem. How would you, for example, put SP's in Server Explorer of VS, or modify it there, if you don't have metadata retrieval mechanism in place?
Further more, with "use prodecure bodies = true" c/NET just made a try to run faster, this is not a proper way of collecting metadata.

There are numerous bugs and changelog entries covering this so please read them. Just for reference:
5.2.2:
- The procedure parameters schema collection has been altered to match what is coming
with MySQL 6.0. Some fields have been removed and others combined. Please review
your application for incompatibilities.

5.0.9:
- fixed problem where old code was preventing creating parameter objects with non-input direction using
just a constructor (Bug #32093)
...

Also, please upgrade to, at least, c/NET 5.2.7. 5.2.3 is rather old and we already have c/NET 6.0 and 6.1.

Thanks Tonci Grgin.

I am not developer and database admin.
so, i dont't know net conector detaily.

In PERL DBI, do not use Information_schema. too.
this is no problem performance and security.

anyway, I want add any option NET also,
without using informatino_schema.

Ok, will pass suggestion to others.