What are definer and invoker rights in database, Oracle and MySQL? I just don't get it.
Let's take a simple example:
You have this procedure using "definer rights" - which is the default in Oracle.
CREATE PROCEDURE DEL_EMP AS BEGIN DELETE FROM EMP; END;
Another user who calls this procedure only needs
EXECUTE privilege for this procedure, it is not required that such user has
DELETE privilege on table EMP.
Procedure runs under permission of the procedure owner (or user who defined it, thus it is called "definer" rights).
"Inovker Rights" is the opposite. A user who likes to runs this procedure successfully must have
EXECUTE privilege for this procedure and
DELETE privilege for table EMP.
There are some more points regarding definer and invoker rights but for a general understanding this should be enough.
The DEFINER and INVOKER rights (a.k.a. the
SQL SECURITY) signify how mysqld looks at requests for and anticipates what the mysql user is calling for :
- Stored Procedure (See MySQL Documentation on this)
as well as if the mysql user has all necessary rights to the following:
- The query's underlying tables
- The view's underlying tables
- The stored procedure's underlying tables
- EXECUTE privilege for the stored procedure
I have addressed this question in the past