Definer vs Invoker vs Owner Vs Creator --- SQL Privileges
We are relatively new to using stored procedures in our data load processes (just in the past couple of years).
Today we've learned the disadvantage of the default 'definer' priviledge by removing privledges from a user who created a procedure and no longer works at our organization.
First question --- Is there a method to determine what privilege was used when the procedure was compiled besides using a 'show procedure' statement and then reviewing the output? We've been looking at the DBC columns and haven't found anything yet.
Second question --- what were your factors in deciding to use 'invoker' vs 'owner' priviledge? The 'invoker' option will require more rights granted to the user executing (call) the stored procedure -- which could be defined in a role. The 'owner' option requires an extract right to create an SP with that option and seems more like a macro. Anybody executing the stored procedure only needs the 'execute procedure' right.
Most of our stored procedures are compliled on the database where the target object is. So for example if the SP does update/delete/inserting into a table, the SP is compiled on the database where table is located.
3rd QUestion - Have you used the 'Creator' option and why?
I don't know of any way to determine the SQL SECURITY value other than to inspect the code.
Oversimplified (and colored by my own opinions):
Is their a system level setting for changing the default from definer to invoker?
So far -- I thinking ==
For development work - leaving it at invoker
-- a developer shouldn't be creating SP's for objects they don't have access to.
For production migration - change it to owner.
-- would make granting rights for service accounts easier and less direct rights needed.