What are definer and invoker rights in database, Oracle and MySQL? I just don't get it.
2 Answers
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.
-
How to invoke the sql invoker mode? I believe it is the definer mode which gets invoked by default... Jun 28, 2016 at 9:11
-
You must declare it in the procedure
CREATE PROCEDURE DEL_EMP AUTHID CURRENT_USER AS ...
Jun 28, 2016 at 9:33
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 ProceduresApr 08, 2013
: Why does this procedure raise a privilege error?Mar 08, 2013
: EMS SQL manager permission problemMar 23, 2012
: MySQL Views Authorization