2

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?

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.