Devart MySQL X Stored Procedure X Previlege of MySQL user

[RNOVAK]

Is this a BUG ?
Driver Devart and Stored Procedure X previleges on *.* from MySQL

Version Devart: 4.70.27 (windows)
Test on Delphi 2010
MySQL Version: 5.1.42-community
//--------------------------------------------------------------------
Stored Procedure to test:

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_PAC_PRONT`(
IN `INPUT_PARAM1` INT,
IN `INPUT_PARAM2` INT,
IN `INPUT_PARAM3` INT)
LANGUAGE SQL NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE MyVar1, MyVar2 integer;
SET MyVar1 =1;
SET MyVar2 =2;
SELECT MyVar1, MyVar2;
END;
//--------------------------------------------------------------------
I used TSQLConnection and TSQLStoredProc

With DBExpress driver MySQL from Delphi, params:
hostname=127.0.0.1
port=3308 (I installed MySQL 5.1 on port 3308)
database=mysqltest
user_name=userteste
password=abc123
(works fine)

With DBExpress Devart MySQL, params:
hostname=127.0.0.1:3308
database=mysqltest
user_name=userteste
password=abc123
(happens Assertion failure, but depend permissions of user),

With user root, the TSQLStoredProc open with success
but with user usertest happens : "Assertion failure...."

On MySQL I did:
CREATE USER 'usertest'@'localhost' IDENTIFIED BY 'abc123';
GRANT ALL ON mysqlteste.* TO 'usertest'@'localhost';
flush privileges;

All works fine with root
All works fine with driver from Delphi
All works fine with Devart but only if user = root, or user with previlege SELECT on *.*
Happens Assertion Failure with driver Devart when user = usertest
but if to do on MySQL: "GRANT SELECT ON *.* TO 'usertest'@'localhost'; " so Devart works fine too. (but see SELECT ON *.* to usertest i dont want)

Seems a problem with permission on MySQL X driver
Without "GRANT SELECT ON *.* TO 'usertest'@'localhost'" is possible to execute the Stored Procedure on MySQL, but occurs error using Devart MySQL DBExpress.

With "GRANT SELECT ON *.* TO 'usertest'@'localhost'" all works fine (on MySQL, On DBExpress driver from Bolrdand, on DBExpress driver from Devart.)

Roberto Novakosky

[AndreyZ]

Hello,

The point is that our dbExpress driver executes SELECT query to the information_schema table. It allows us to create parameters automatically. You can avoid this problem without granting SELECT privileges to the usertest user by creating the SP_PAC_PRONT stored procedure with definer 'usertest'@'localhost' in the following way:

Code: Select all

CREATE DEFINER=`usertest`@`localhost` PROCEDURE `SP_PAC_PRONT`( 
IN `INPUT_PARAM1` INT, 
IN `INPUT_PARAM2` INT, 
IN `INPUT_PARAM3` INT) 
LANGUAGE SQL NOT DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
COMMENT '' 
BEGIN 
DECLARE MyVar1, MyVar2 integer; 
SET MyVar1 =1; 
SET MyVar2 =2; 
SELECT MyVar1, MyVar2; 
END;

[RNOVAK]

I dont know if is usefull.. but sugestion to study on future versions:
when run time, instead SELECT on information_schema to do "SHOW CREATE PROCEDURE `database`.`StoredProcedureName`;
when events of Design time as to populate the drop down of TSQLStoredproc.StoredProcName continue the use of information_schema

I'm not sure if this sintax 'SHOW' is equal to all MySQL versions.

Very thanks

Roberto Novakosky

[AndreyZ]

DbxMda uses the SHOW CREATE PROCEDURE statement. The problem is that MySQL server doesn't return information to the "Create Procedure" column if the connection was established with the usertest user. You can check it with any other tool.