MySQL stored procedure user privilege

Question

I have a query regarding user permission on a stored procedure.

If a user that does not have permissions to create tables in a database, but it does have permissions to create and execute stored procedures

If those stored procedures create tables (as part of the SQL code that is executed) then, will the user get an error when executing the stored procedure when it tries to create a table?

Answer 1

It depends on the SQL SECURITY characteristic you defined when you created the procedure. SQL SECURITY for a stored procedure can be either DEFINER or INVOKER.

If your user can not create table but the definer of the stored procedure can, then your user will be able to create a table through the stored procedure.

By default, the SQL SECURITY characteristic in MySQL is DEFINER.

To put it in a nutshell :

• Stored procedure with SQL SECURITY DEFINER : runs with stored procedure creator rights
• Stored procedure with SQL SECURITY INVOKER : runs with current user rights

• Big_Al_Tx: Thanks for this succinct answer. I had a very similar question as the OP, and now: problem solved.