What are definer and invoker rights in database, Oracle and MySQL?

Question

What are definer and invoker rights in database, Oracle and MySQL? I just don't get it.

Answer 1

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 is, 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.

Answer 2

MySQL Perspective

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 :

• Query
• View
• 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

• Jun 12, 2013 : MySQL: Securing Access using Stored Procedures
• Apr 08, 2013 : Why does this procedure raise a privilege error?
• Mar 08, 2013 : EMS SQL manager permission problem
• Mar 23, 2012 : MySQL Views Authorization