The security policy of the MySQL stored procedure

1. CREATE ROUTINE

1 GRANT CREATE ROUTINE   2  on Database - *  3  to User (s)    4 [withGRANT OPTION]

Create routine creates stored procedures and functions, and you can create views and tables. Now the built-in root has this permission, while emptying the alter routine permission.

2. EXECUTE

1 GRANT EXECUTE  2  on Database - *  3  to User (s)    4 [withGRANT OPTION]

　　Execute determines whether a stored procedure can be used or executed, and the creator of the stored procedure has this permission by default.

3. SHOW ROUTINE

1 GRANT SHOW ROUTINE 2  on Database - *  3  to User (s)    4 [withGRANT OPTION]

Because we already have grant show view privileges to control views by default, the grant show routine privilege may be added in the future to ensure compatibility

4. Invokers and Definers

1 CREATE PROCEDUREP26 ()2 SQL SECURITY INVOKER3     SELECT COUNT(*) fromT//4 CREATE PROCEDUREp27 ()5 SQL SECURITY Definer6     SELECT COUNT(*) fromT//7 GRANT INSERT  onDB5.*  toPeter//

As mentioned earlier, you are the root user who created two stored procedures and assigned the INSERT permission to Peter. Note that Peter has no SELECT permission on table T, only the root user has. Because of this, the above code Peter calls P26 will fail, and calling P27 will succeed, The reasons are described in the previous section.

The security policy of the MySQL stored procedure