MYSQL-Issue in accessing stored procedure globally by all users

Murugappan Murugappan

Hi Team,

Whenever i am creating stored procedure SP1 in mysql workbench, Definer gets created automattically with my user name(user1) by default with the syntax 'username'@'hostname'. so stored procedure can accessed only by my user.

If i am logging from another user(user2)  and trying to access the stored procedure SP1. This user can't be able to access the stored procedure SP1.

My Assumption - The reason behind the issue is not able to access SP1 from user2 is due to that definer created automattically with the user name as user1.

Need solution - So whether there will be any possibility of restricting the definer creating automattically or Is there any way to give permissions to access SP1 by all users irrespective of definer restriction.

second question - for one user, i have not given drop and delete privilage. Eventhough that user able to drop and delete the stored procedure. how is ti possible and not able to use revoke command?

Thanks in Advance,

Murugappan Murugappan.

Gaz in Oz

As for question 1, you specify

SQL SECURITY INVOKER

in your procedure stored procedure.

Without it, the default is DEFINER.

https://dev.mysql.com/doc/refman/5.7/en/stored-objects-security.html#stored-objects-security-sql-security

Definitions for stored routines (procedures and functions) and views can include an  SQL SECURITY characteristic with a value of DEFINER or INVOKER to specify whether the object executes in definer or invoker context. If a definition omits the SQL SECURITYcharacteristic, the default is definer context.

Dave Stokes-MySQL Community Team-Oracle

For your second question, check the ALTER_ROUTINE privilege for that user

Gaz in Oz

As for question 1, you specify

SQL SECURITY INVOKER

in your procedure stored procedure.

Without it, the default is DEFINER.

https://dev.mysql.com/doc/refman/5.7/en/stored-objects-security.html#stored-objects-security-sql-security

Definitions for stored routines (procedures and functions) and views can include an  SQL SECURITY characteristic with a value of DEFINER or INVOKER to specify whether the object executes in definer or invoker context. If a definition omits the SQL SECURITYcharacteristic, the default is definer context.

Murugappan Murugappan

Hi. I have tried this option but it is not working. Can you give me some other options.

Murugappan Murugappan

Hi Dave, by removing alter routine option also it is allowing the user to drop the stored procedure. Can you give some other option.