PL/Mysql - can't run procedure with sql security invoker
-
Good evening from Brazil. I granted select and update to the user but I still can't run the procedure below. What are the other privileges I need to grant to him?
delimiter //
drop procedure if exists sp_optimistic_locking //
create procedure sp_optimistic_locking
(
p_from_account int,
p_to_account int,
p_transfer_amount numeric(10,2),
p_status int,
p_message varchar(100)
)
sql security invoker
modifies sql data
begin
declare l_account_balance1 numeric(10,2);
declare l_account_balance2 numeric(10,2);
declare l_account_timestamp1 timestamp;
declare l_account_timestamp2 timestamp;select balance, account_timestamp into l_account_balance1, l_account_timestamp1
from account_balance where account_id = p_from_account;if l_account_balance1 >= p_transfer_amount then
start transaction;
-- call long_running_validation(p_from_account);
select balance, account_timestamp into l_account_balance2, l_account_timestamp2
from account_balance where account_id = p_from_account for update;if (l\_account\_balance1 <> l\_account\_balance2 or l\_account\_timestamp1 <> l\_account\_timestamp2) then rollback; set p\_status = -1; set p\_message = concat('Transaction canceled due to previous update of account ', p\_from\_account); else update account\_balance set balance = balance - p\_transfer\_amount where account\_id = p\_from\_account; update account\_balance set balance = balance + p\_transfer\_amount where account\_id = p\_to\_account; commit; set p\_status = 0; set p\_message = 'transaction ok'; end if; else set p\_status = -1; set p\_message = 'Transaction canceled due to account balance less than desired withdrawal';
end if;
select 'Transaction code', p_status;
select 'Transaction message', p_message;
end;
//