MySQL Stored Procedure permission check

1. Permission related:

1. ModifySQL security

SQL code

Alter procedure www SQL SECURITY INVOKER;

Alter procedure www SQL SECURITY DEFINER;

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 specifiedDEFINERThe stored procedure uses the DEFINER of the stored procedure to execute the stored procedure, and verifies whether the user who calls the stored procedure has the execute Permission for the stored procedure and whether the DEFINER user has the permission for the objects referenced by the stored procedure;

3) if the SQL SECURITY clause is specifiedINVOKERMySQL will use the user who calls the stored procedure to execute this process, and verify whether the user has the execute Permission for the stored procedure and the permission of the objects referenced by the stored procedure;

4) if the specified SQL SECURITY clause is not displayed, MySQL executes the stored procedure with DEFINER by default.

3. Execute Stored Procedure authorization

SQL code

Grant execute on procedure test. * TO 'wtc '@' % ';

Grant create routine, alter routine, SELECT, CREATE, INSERT, UPDATE, DELETE, execute on test. * TO 'wtc '@' % 'identified BY '123'

Create routine: Permission to CREATE a stored procedure

Alter routine: modify stored procedure Permissions

Ii. Experiment:

Session 1:

Mysql> show grants for ly @ '% ';

| Grants for ly @ %

| Grant usage on *. * TO 'ly '@' % 'identified by password' * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

Mysql> show create procedure hhl. pr_param_in \ G

* *************************** 1. row ***************************

Procedure: pr_param_in

SQL _mode:

Create Procedure: CREATEDEFINER = 'root' @ 'localhost'PROCEDURE 'pr _ param_in '(

In id int

)

SQL SECURITYDEFINER

Begin

INSERTHhl. T VALUES (1, 'hhl ');

End

Character_set_client: latin1

Collation_connection: latin1_swedish_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

Grant execute on procedure hhl. pr_param_in to 'ly '@' % ';

Session 2:

Mysql-uly-p123-h 1.1.1.5

Mysql> call hhl. pr_param_in (1 );

Query OK, 1 row affected (0.00 sec)

AboveSQL SECURITY =DEFINER,Ly user calls the stored procedureDEFINER ='Root' @ 'localhost', That is, check whether the caller ly has the execute Permission for the stored procedure and the DEFINER user ('Root' @ 'localhost') Whether the permission is granted to the objects referenced by the stored procedure.

* If SQL SECURITY = INVOKER

Session 1:

Mysql> alter procedure hhl. pr_param_in SQL SECURITY INVOKER;

Query OK, 0 rows affected (0.00 sec)

Mysql> show create procedure hhl. pr_param_in \ G

* *************************** 1. row ***************************

Procedure: pr_param_in

SQL _mode:

Create Procedure: create definer = 'root' @ 'localhost' PROCEDURE 'pr _ param_in '(

In id int

)

SQL SECURITYINVOKER

Begin

Insert into hhl. t VALUES (1, 'hhl ');

End

Character_set_client: latin1

Collation_connection: latin1_swedish_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

Session 2:

Mysql> call hhl. pr_param_in (1 );

ERROR 1142 (42000): INSERT command denied to user 'ly '@ 'node5' for table 'T'

AboveSQL SECURITY =INVOKER,Ly user calls Stored ProcedureThat is, check whether ly has the execute Permission for the stored procedure and whether it has the permission for the objects referenced by the stored procedure. ly does not have the t table insert permission for the hhl database.

This article is from My DBA life blog, please be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1216786