SQL SECURITY option enhanced the way access rights are defined and checked for stored procedures. “SQL SECURITY INVOKER” means that the access rights are checked during runtime based on the user that invokes the procedure. The database access rights are not used in this case. So, all users that call the procedures must have appropriate access rights on the database.
Example:
-- As DBC user
CREATE DATABASE db1 AS PERM = 1e6;
GRANT DELETE ON db1 to db1;
CREATE TABLE db1.tab1(a INT, b INT);
CREATE USER User1 AS PERM = 1e6 PASSWORD = User1;
Grant execute procedure on db1 to User1;
Grant create procedure on db1 to dbc;
REPLACE PROCEDURE db1.sp1()
SQL SECURITY INVOKER
BEGIN
delete from db1.tab1;
END;
-- As User1
Call db1.sp1();
*** Failure 3523 SP1:The user does not have DELETE access to db1.tab1.
-- As DBC user
If we remove the “SQL SECURITY INVOKER” from the procedure then the User1 will be able to delete from db1.tab1 by calling the procedure while the User1 does not have delete grant.
REPLACE PROCEDURE db1.sp1()
BEGIN
delete from db1.tab1;
END;
-- As User1
Call db1.sp1();
*** Procedure has been executed.