Can MyDAC or UniDAC run stored procedures if granted EXECUTE privilege only?

[nmadani]

The SQL script below creates a test database, with a table, a stored procedure with input and output parameters and a user with EXECUTE only privileges on the test database.

Would MyDAC or UniDAC be able to use the restricted account (session123_user) that is created to properly run the stored procedure (login_proc) using TMyConnection and TMyStoredProc instances?

Thanks in advance,

NM

Code: Select all

DROP DATABASE IF EXISTS testdb123;
CREATE DATABASE testdb123;

CREATE TABLE testdb123.users_table(
  id INT(11) UNSIGNED NOT NULL,
  login VARCHAR(40) NOT NULL,
  `password` VARCHAR(40) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX login (login)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;

INSERT INTO testdb123.users_table VALUES (1, 'john', 'password'), (2, 'jim', 'password');

DELIMITER $$
CREATE PROCEDURE testdb123.login_proc(IN usr VARCHAR(40), IN pswd VARCHAR(40), OUT sess_id VARCHAR(40))
SQL SECURITY DEFINER
BEGIN
  DECLARE lID   INT;
  DECLARE lUser VARCHAR(40);
  DECLARE lPswd VARCHAR(40);
  SELECT * INTO lID, lUser, lPswd FROM users_table WHERE login = usr AND `password` = pswd;
  SET sess_id = '0123456789012345678901234567890123456789';
END$$
DELIMITER ;

DELETE FROM mysql.user WHERE USER = "session123_user" AND HOST = "%";
FLUSH PRIVILEGES;
GRANT EXECUTE ON testdb123.* TO session123_user@'%' IDENTIFIED BY 'password123';
SHOW GRANTS FOR session123_user@'%';

[AndreyZ]

Hello,

Yes, you will be able to execute it, but you will have to create parameters for the login_proc stored procedure manually. This is due to the fact that the session123_user user does not have the SELECT right. In this case neither MyDAC, nor UniDAC (and no other components as well) will not be able to obtain the information about parameters and the correct SQL code from the server.
Here is a code example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  MyStoredProc1.SQL.Text := 'CALL login_proc(:usr, :pswd, @sess_id); SELECT @sess_id';
  MyStoredProc1.Params.CreateParam(ftString, 'usr', ptInput);
  MyStoredProc1.Params.CreateParam(ftString, 'pswd', ptInput);
  MyStoredProc1.Params.CreateParam(ftString, 'sess_id', ptOutput);
  MyStoredProc1.ParamByName('usr').AsString := 'jim';
  MyStoredProc1.ParamByName('pswd').AsString := 'password';
  MyStoredProc1.Execute;
  ShowMessage(MyStoredProc1.FieldByName('@sess_id').AsString);
end;

[nmadani]

Thank you for the response. So in other words, the session account does not need to be granted SELECT privileges to the mysql.proc data for obtaining metadata in order to call the stored procedure, as long as all parameters created manually are correct? This is a issue we are having with our current access components that must be able to make a SHOW CREATE PROCEDURE call to the server.

[AndreyZ]

So in other words, the session account does not need to be granted SELECT privileges to the mysql.proc data for obtaining metadata in order to call the stored procedure, as long as all parameters created manually are correct?

Yes, it is correct.