3523 <XXXX>:The user does not have XXXXX access to XXX.XXXX (Procedure fail with 3523 Error when SQL SECURITY INVOKER use in the procedure)

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.

Comments