4

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

6

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.

  • How to invoke the sql invoker mode? I believe it is the definer mode which gets invoked by default... – deostroll Jun 28 '16 at 9:11
  • You must declare it in the procedure CREATE PROCEDURE DEL_EMP AUTHID CURRENT_USER AS ... – Wernfried Domscheit Jun 28 '16 at 9:33
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 :

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

Your Answer

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