How MySQL modifies all the Definer

What is definer in MySQL and what is the role?

We will define a definer= ' xxx ' when MySQL creates view, trigger, function, procedure, event, similar to the following:

CREATEalgorithm=UNDEFINED Definer=' Root ' @ '%`SQL SECURITY DefinerVIEW' V_ questions ' as    SELECT' Q '. ' ID ' as' id ', ' q '. ' title ' as' title ' fromTest q; or something like this:CREATEDefiner=' Root ' @ '%`PROCEDURE' User_count ' () LANGUAGE SQL notDeterministicCONTAINSSQL SQL SECURITY definer COMMENT"'BEGIN    Select Count(*) fromMysql.User;END

Some of accents's SQL SECURITY is actually followed by two options, one for Definerand one for INVOKER

SQL SECURITY {definer | INVOKER}: Indicates who has permission to execute. Definer represents the permissions that are performed by the defined by

The INVOKER represents execution with the caller's permission. By default, the system is specified as Definer

Take the stored procedure as an example:

(1) The MySQL stored procedure specifies the actual user who executes the stored procedure by specifying the SQL security clause;

(2) If the SQL Security clause is specified as definer, the stored procedure executes the stored procedure using the definer of the stored procedure, Verify that the user calling the stored procedure has EXECUTE permission on the stored procedure and that the Definer user has permission to the related object referenced by the stored procedure;

(3) If the SQL Security clause is specified as Invoker, then MySQL performs this procedure with the user who is currently calling the stored procedure and verifies that the user has execute permissions on the stored procedure and permissions on the related object referenced by the stored procedure;

(4) If the specified SQL Security clause is not displayed, MySQL defaults to executing the stored procedure in Definer.

Let's look at a few small examples.

first Authorize one:Grant  All  onTestDB.*  to 'User1'@'%'Identified by '000000'  with Grant option; Then we create a stored procedure as follows: Use' TestDB ';DROP procedure IF EXISTS' User_count ';D elimiter $$ Use' TestDB ' $$CREATEDefiner=' Root ' @ '%`PROCEDURE' User_count ' () LANGUAGE SQL notDeterministicCONTAINSSQL SQL SECURITY INVOKER COMMENT"'BEGIN    Select Count(*) fromMysql.User;END$ $DELIMITER;

Log in with the root account:

Mysql>  UseTestDB;DatabaseChangedmysql>Call User_count ();+----------+| Count(*)|+----------+|        3 |+----------+1Rowinch Set(0.00sec) Query OK,0Rows Affected (0.00sec) can be queried normally. We'll use User1 to log in: MySQL>  UseTestDB;DatabaseChangedmysql>Call User_count (); ERROR1142(42000):SELECTCommand denied to User 'User1'@'localhost'  for Table 'User'

Find the system error query is not, this is because we defined in the above SQL security value is invoker, stored procedure execution process will be executed with User1 with the permissions, which call to the MySQL library, and our User1 account only testdb the use of the library permissions, Therefore, the failure is returned.

Let's change the invoker above to Definer and try it again:

UpdateMysql.proc SetSecurity_type='Definer' whereDb='TestDB'  andName='User_count'to log in again with User1: MySQL>  UseTestDB;DatabaseChangedmysql>Call User_count ();+----------+| Count(*)|+----------+|        3 |+----------+1Rowinch Set(0.00sec) Query OK,0Rows Affected (0.00Sec

Discovery can be queried, because user1 to the stored procedure User_count have execute permission, although it still does not have the right to operate the MySQL library directly, because we define the SQL security is definer, so at execution time is executed as root, So it can be queried normally.

What if it is convenient to modify all the definer that have been defined in MySQL?

Due to the early development of the test library, we often define the definer as ' root ' @ '% ', and then moved to the production library to be changed back, there are a lot of updates, hundreds of views, functions, such as a change is too troublesome and may also be omitted. The following is a summary of the convenient way to modify all definer, until the leak check the role of the vacancy.

The definer now involved in MySQL are view, trigger, function, procedure, event. Let's make a presentation.

1. Modify the definer of function and procedure

Select Definer from Mysql.proc; --Functions, stored procedures

Update Mysql.proc set definer= ' [email protected] '; --if there is a restricted library or other where conditions can be added

2. Modify the Definer of the event

Select Definer from MySQL. EVENT; --timed events

Update MySQL. EVENT set definer= ' [email protected] ';

3. Modify the view's Definer

More trouble than the change of function:

Select Definer from INFORMATION_SCHEMA. views;

Select Concat ("Alter definer= ' user ' @ ' localhost ' SQL SECURITY definer VIEW", Table_schema, ".", TABLE_NAME, "as", View_ DEFINITION, ";") From INFORMATION_SCHEMA. views where definer<> ' [email protected] ';

The query out of the statement to execute it again.

4. Modify Trigger's Definer

At present there is no specific convenient method, you can use tools such as Heidisql, SQLYOG, etc. to modify each. Note that it is necessary to lock the table before changing, because if there are other table changes in the process of triggering, it will cause inconsistent data.

Flush Tables with Readlock

Unlock tables

If there is a way to find ways, remember to leave a message, learn from each other.

How MySQL modifies all the Definer