Definer and invoker parsing of MySQL stored procedure

"Permissions for Stored Procedures"

ALTER routine edit or delete a stored procedure

Create routine creating a stored procedure

Execute run Stored procedure

"Creation syntax for stored procedures"

Delimiter//--Declaration Separator (Command Terminator)

Create

Definer = User@hostname | Current_User

Procedure stored procedure name (parameters)

Comment ' Notes '

SQL Security Definer | Invoker-SQL Security settings

Begin

The body of the stored procedure

End

//

delimiter; --Declaration Separator (Command Terminator)

"Create statement for function"

Delimiter//--Declaration Separator (Command Terminator)

Create

Definer = User@hostname | Current_User

function functions name (parameters)

return value type

Comment ' Notes '

SQL Security Definer | Invoker-SQL Security settings

Begin

The body of a function

End

//

delimiter; --Declaration Separator (Command Terminator)

"Definer and Invoker's explanation."

When you create a stored procedure, you can specify the SQL security attribute, set to Definer or Invoker, to tell MySQL whether to execute the stored procedure with the privileges of the Definer user or as the caller's permissions.

By default, the user who invokes the stored procedure must have EXECUTE permission on the stored procedure, and the user specified by Definer must be a user that exists in the Mysql.user table, using the Definer method.

Definer mode, the default Definer=current_user, when the stored procedure executes, MySQL checks the definer defined user ' user_name ' @ ' host_name ' permissions;

In Invoker mode, the permissions of the stored procedure caller are checked when the stored procedure executes.

Case one: Definer

CREATE definer = ' admin ' @ ' localhost ' PROCEDURE account_count ()

BEGIN

SELECT ' Number of accounts: ', COUNT (*) from Mysql.user;

End;

In this case, no matter which user a invokes the stored procedure, the stored procedure executes with the permissions of the ' admin ' @ ' localhost ', even if this user A does not have permission to query the Mysql.user table.

This column more highlights: http://www.bianceng.cn/database/MySQL/

Case two: Invoker

CREATE definer = ' admin ' @ ' localhost ' PROCEDURE account_count ()

SQL Security Invoker

BEGIN

SELECT ' Number of accounts: ', COUNT (*) from Mysql.user;

End;

In this case, although the Definer parameter is still in the stored procedure statement, because the SQL security specifies the invoker, it executes as the caller's amount when the stored procedure executes. Whether the stored procedure can execute successfully at this time depends on whether the caller has query permissions for the Mysql.user table.

Case

Case one: calling a stored procedure

The caller of the stored procedure is: admin@192.168.1.1

The definer of the stored procedure is: admin@%

MySQL exists in the user is: admin@192.168.%.%

At this point admin@192.168.1.1 is able to access the database because it conforms to the admin@192.168.%.% authorization rules, but when it invokes the definer= ' admin@% ' stored procedure, MySQL will check the Mysql.user user table exists admin@% This user, MySQL check result is admin@% This user does not exist, this time will return an error, prompt "Ther user specified as a definer (' admin@% ') does not exist.

Case two: Create a stored procedure

Using user admin@192.168.1.1 to connect to MySQL, the user has the all privileges of the test library, performing the actions to create the stored procedure:

The definer defined in the stored procedure are: admin@%

MySQL exists in the user is: admin@192.168.%.%

At this point, you will encounter an error, prompt "error 1227 (42000): Access denied; Need (at least one of) the SUPER privilege (s) for this operation

Repair definer= ' admin@192.168.%.% ', or remove definer parameters, can return to normal.

Description

Case one is problematic, the caller and owner of the stored procedure are admin@192.168.1.1, but Definer is admin@%, because the command to create the stored procedure is executed by the root user, so no error is encountered in case two.

"Stored Procedure common commands"

To view the creation statement for a stored procedure:

Show create procedure stored procedure name;

To view information about stored procedures:

Show procedure status like ' stored procedure name ' \g

To view the Definer information for a stored procedure:

Select Db,name,type,sql_security,definer from Mysql.proc where type= ' PROCEDURE ' and db= ' database name ';

To modify the definer of a stored procedure:

Update Mysql.proc set ' definer ' = ' admin@192.168.%.% ' where db like ' db_% ';