Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.8
-
Fix Version/s: N/A
-
Component/s: Data Definition - Procedure
-
Labels:None
Description
In the following example error 1370 is ok, but I'm trying to handle it, and this seems to be impossible.
MariaDB [test]> CREATE USER u2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> DELIMITER ||
MariaDB [test]> CREATE DEFINER = u2 PROCEDURE p()
-> SQL SECURITY DEFINER
-> BEGIN
-> DECLARE EXIT HANDLER
-> FOR 1370
-> SELECT 1370 AS `ERROR`;
-> DECLARE EXIT HANDLER
-> FOR SQLSTATE '42000'
-> SELECT '42000' AS `ERROR`;
-> SHOW PROCESSLIST;
-> SELECT 'OK';
-> END;
-> ||
Query OK, 0 rows affected (0.03 sec)
MariaDB [test]> DELIMITER ;
MariaDB [test]> CALL p();
ERROR 1370 (42000): execute command denied to user 'u2'@'%' for routine 'test.p'
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
In other words, MariaDB checks the permissions before executing the procedure? And in no case 1370 error can occur during the procedure execution?
The test case was a simplification of the real-life case. I don't exactly remember what I was trying to do, but in the real-life case there was no definer. However, if the users had not the rights to access the table, I didn't want the whole procedure to fail.
There is always a definer, if it's not given explicitly in the CREATE PROCEDURE .. statement, then it's the current user.
It's possible that in the real-life case, your user had a permission to create the procedure (CREATE ROUTINE grant), but not to execute it (EXECUTE grant).
And naturally, EXECUTE grant is checked before executing the procedure, otherwise this type of permission wouldn't make any sense.
If your user had the EXECUTE grant, but lets say didn't have permissions to access a table used in that routine, and you had a handler for that error, it would have worked.
It doesn't look like a problem with the error.
The procedure has the default SQL SECURITY value (DEFINER), so the definer must have the EXECUTE privilege, which it doesn't. It's the execution of the whole procedure that is denied, so how can the handler work?
If we modify the test case this way, it works:
MariaDB [test]> CREATE USER u2@localhost; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> GRANT EXECUTE ON PROCEDURE test.p TO u2@localhost; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> delimiter || MariaDB [test]> CREATE DEFINER = u2@localhost PROCEDURE p() -> SQL SECURITY DEFINER -> BEGIN -> DECLARE EXIT HANDLER -> FOR 1370 -> SELECT 1370 AS `ERROR`; -> DECLARE EXIT HANDLER -> FOR SQLSTATE '42000' -> SELECT '42000' AS `ERROR`; -> SELECT * FROM mysql.user; -> SELECT 'OK'; -> END || Query OK, 0 rows affected (0.15 sec) MariaDB [test]> delimiter ;