Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of MYSQL stored procedure permissions, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

Summary of MYSQL database permissions:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

There are three types of permissions related to the stored procedure itself, namely, CREATE ROUTINE, ALTER ROUTINE, and EXECUTE. Generally speaking, if you need permission to create and delete stored procedures, you need to grant CREATE ROUTINE permission; if you have permission to modify stored procedures, you need to give ALTER ROUTINE permission; if you need permission to call stored procedures, you need to give EXECUTE permission.

However, MYSQL itself has some restrictions on the syntax structure of the stored procedure definition, and it also strictly filters the user's permission to call, which is mainly related to the stored procedure definition parameters: Definer and Security_type, the former is the user who created the stored procedure, generally expressed in the form of root@localhost and so on. Security_type is mainly divided into DEFINER | INVOKER, which is mainly used to audit the security audit of calling stored procedures. If set to DEFINER, the user creating the stored procedure needs to exist, have the permission to call the stored procedure and have access to the objects in the stored procedure. Each call will audit the definer=root@localhost to see whether it exists and is authorized by the corresponding permission. If set to INVOKER, Each call will not check whether the account corresponding to definer exists. It only requires that the user who calls the stored procedure has the permission to execute the stored procedure and access the permission to include objects in the stored procedure.

The test case verification is as follows:

This example uses the dbtest database and the table T1 below, respectively, using three root,dbuser01,dbuser02 users.

1. Create a test account dbuser01

Create an account dbuser01, and only grant usage,create routine permission

[root@node1 ~] # mysql

(root:localhost:Wed Dec 14 14:19:05 2016) [(none)] > grant USAGE on *. * to dbuser01@'10.127.%' identified by 'dbuser01'

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Dec 14 14:19:25 2016) [(none)] > grant create routine on dbtest.* to dbuser01@'10.127.%'

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Dec 14 14:19:52 2016) [(none)] > flush privileges

Query OK, 0 rows affected (0.01 sec)

(root:localhost:Wed Dec 14 14:20:33 2016) [(none)] > show grants for dbuser01@'10.127.%'

+-+

| | Grants for dbuser01@10.127.% |

+-+

| | GRANT USAGE ON *. * TO 'dbuser01'@'10.127.%' IDENTIFIED BY PASSWORD' * 0B9488E6078162E584CCE461DE11578474EBBC84' |

| | GRANT CREATE ROUTINE ON `dbtest`. * TO 'dbuser01'@'10.127.%' |

+-+

2 rows in set (0.00 sec)

two。 Create a stored procedure pro_test

Log in to dbtest database with dbuser01 and create a stored procedure pro_test

[root@node4] # mysql-udbuser01-pdbuser01-h20.127.32.121-D dbtest

Mysql > delimiter / /

Mysql > create procedure pro_test () begin select * from T1; end;//

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > delimiter

Stored procedure pro_test call scenario 1

Scenario 1: who created the stored procedure: dbuser01

Dbuser01 permission: usage on *. *, create routine ON `dbtest`. *

Definer: dbuser01@10.127.%

Security_type: DEFINER

Dbuser01 calls the stored procedure pro_test:

Mysql > call pro_test

ERROR 1370 (42000): execute command denied to user 'dbuser01'@'10.127.%' for routine' dbtest.pro_test'

Dbuser01 calls the stored procedure pro_test:

(root:localhost:Wed Dec 14 14:34:28 2016) [dbtest] > call pro_test ()

ERROR 1370 (42000): execute command denied to user 'dbuser01'@'10.127.%' for routine' dbtest.pro_test'

Root calls the stored procedure:

(root:localhost:Wed Dec 14 14:34:28 2016) [dbtest] > call pro_test ()

ERROR 1370 (42000): execute command denied to user 'dbuser01'@'10.127.%' for routine' dbtest.pro_test'

Scenario 01 conclusion: dbuser01 user exists, and after Security_type: DEFINER,dbuser01 creates a stored procedure, you need to grant the account execute stored procedure pro_test permission, otherwise you will report that you have no permission to execute. That is, when the stored procedure is called, the

Definer: dbuser01@10.127.% # # to see if this user has permission to execute stored procedures

Security_type: DEFINER

Stored procedure pro_test call scenario 2

Scenario 02: who created the stored procedure: dbuser01

Dbuser01 permissions: usage on *. *, create routine ON `dbtest`. *, execute on procedure dbtest.pro_test

Definer: dbuser01@10.127.%

Security_type: DEFINER

(root:localhost:Wed Dec 14 14:34:32 2016) [dbtest] > grant execute on procedure dbtest.pro_test to 'dbuser01'@'10.127.%'

Query OK, 0 rows affected (0.00 sec)

Dbuser01 calls the stored procedure pro_test:

Mysql > call pro_test ()

ERROR 1142 (42000): SELECT command denied to user 'dbuser01'@'10.127.32.122' for table 't1'

Root calls the stored procedure:

(root:localhost:Wed Dec 14 14:47:03 2016) [dbtest] > call pro_test ()

ERROR 1142 (42000): SELECT command denied to user 'dbuser01'@'10.127.%' for table 't1'

Scenario 02 conclusion: dbuser01 user exists, and after Security_type: DEFINER, dbuser01 creates a stored procedure, you need to give the account execute stored procedure pro_test permission and access permission to the corresponding objects in the stored procedure, such as select on dbtest.t1 permission, otherwise no permission will be reported for execution. That is, when calling the stored procedure, it will check Definer: dbuser01@10.127.% # # to see if this user has the permission to execute the stored procedure and access the object.

Security_type: DEFINER

Stored procedure pro_test call scenario 3

Scenario 03: who created the stored procedure: dbuser01

Dbuser01 permissions: usage on *. *, create routine ON `dbtest`. *, execute on procedure dbtest.pro_test,select on dbtest.t1

Definer: dbuser01@10.127.%

Security_type: DEFINER

(root:localhost:Wed Dec 14 15:43:32 2016) [dbtest] > grant select on dbtest.t1 to 'dbuser01'@'10.127.%'

Query OK, 0 rows affected (0.01 sec)

Dbuser01 calls the stored procedure pro_test:

Mysql > call pro_test ()

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Root calls the stored procedure:

(root:localhost:Wed Dec 14 15:43:45 2016) [dbtest] > call pro_test ()

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Scenario 03 conclusion: dbuser01 user exists, and after Security_type: DEFINER, dbuser01 creates a stored procedure, you need to give the account execute stored procedure pro_test permission and access permission to the corresponding objects in the stored procedure, such as select on dbtest.t1 permission, otherwise no permission will be reported for execution. That is, when calling the stored procedure, it will check Definer: dbuser01@10.127.% # # to see if this user has the permission to execute the stored procedure and access the object.

Security_type: DEFINER

Stored procedure pro_test call scenario 4

Scenario 04: who created the stored procedure: dbuser02

Dbuser01 permissions: usage on *. *, create routine ON `dbtest`. *, execute on procedure dbtest.pro_test,select on dbtest.t1

Dbuser02 permission: execute on procedure dbtest.pro_test

Definer: dbuser01@10.127.%

Security_type: DEFINER

(root:localhost:Wed Dec 14 15:44:44 2016) [dbtest] > grant execute on procedure dbtest.pro_test to 'dbuser02'@'10.127.%' identified by' dbuser02'

Query OK, 0 rows affected (0.00 sec)

Dbuser02 calls the stored procedure pro_test:

[root@node4] # mysql-udbuser02-pdbuser02-h20.127.32.121-D dbtest

Mysql > call pro_test ()

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from T1

ERROR 1142 (42000): SELECT command denied to user 'dbuser02'@'10.127.32.122' for table 't1'

Scenario 04 conclusion: dbuser01 user exists, and after Security_type: DEFINER, dbuser01 creates a stored procedure, you need to give the account execute stored procedure pro_test permission and access permission to the corresponding objects in the stored procedure, such as select on dbtest.t1 permission, otherwise you will report no permission to execute. That is, when calling the stored procedure, it will check Definer: dbuser01@10.127.% # # to see if this user has the permission to execute the stored procedure and access the object.

Security_type: DEFINER

Other users, such as dbuser02, need only be given execute permission to call the pro_test stored procedure, and the object permission in it is not required, as long as the user who created the process has the permission to execute and access the object.

Stored procedure pro_test call scenario 5

Scenario 05: delete user dbuser01

Dbuser02 permission: execute on procedure dbtest.pro_test,select on dbtest.t1

Definer: dbuser01@10.127.%

Security_type: DEFINER

(root:localhost:Wed Dec 14 16:11:13 2016) [dbtest] > delete from mysql.user where user='dbuser01'

Query OK, 1 row affected (0.00 sec)

(root:localhost:Wed Dec 14 16:11:24 2016) [dbtest] > flush privileges

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Dec 14 16:31:29 2016) [dbtest] > grant SELECT ON `dbtest`.`t1`to 'dbuser02'@'10.127.%'

Query OK, 0 rows affected (0.00 sec)

Dbuser02 calls the stored procedure pro_test:

Mysql > call pro_test

ERROR 1449 (HY000): The user specified as a definer ('dbuser01'@'10.127.%') does not exist

Root calls the stored procedure:

(root:localhost:Wed Dec 14 16:11:27 2016) [dbtest] > call pro_test

ERROR 1449 (HY000): The user specified as a definer ('dbuser01'@'10.127.%') does not exist

(root:localhost:Wed Dec 14 16:12:08 2016) [dbtest] > show procedure status\ G

* * 1. Row *

Db: dbtest

Name: pro_test

Type: PROCEDURE

Definer: dbuser01@10.127.%

Modified: 2016-12-14 14:24:46

Created: 2016-12-14 14:24:46

Security_type: DEFINER

Comment:

Character_set_client: utf8

Collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

Scenario 05 conclusion: the dbuser01 user is deleted and the Security_type: DEFINER, dbuser01 creation stored procedure pro_test cannot be accessed by other accounts. That is, when the stored procedure is called, the

Definer: dbuser01@10.127.% # # to see if this user has permission to execute stored procedures and access objects

Security_type: DEFINER

Stored procedure pro_test call scenario 6

Scenario 06: delete user dbuser01

Dbuser02 permission: execute on procedure dbtest.pro_test

Definer: dbuser01@10.127.%

Security_type: INVOKER

(root:localhost:Wed Dec 14 16:31:50 2016) [dbtest] > alter procedure pro_test SQL SECURITY INVOKER

Query OK, 0 rows affected (0.00 sec)

Dbuser02 calls the stored procedure pro_test:

Mysql > call pro_test

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

If dbuser02 has only the permission of execute but not the permission of select on dbtest.t1, the call will also report an error.

Mysql > call pro_test

ERROR 1142 (42000): SELECT command denied to user 'dbuser02'@'10.127.32.122' for table 't1'

Root calls the stored procedure:

(root:localhost:Wed Dec 14 16:42:56 2016) [dbtest] > call pro_test

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.01sec)

Query OK, 0 rows affected (0.01 sec)

Scenario 06 conclusion: dbuser01 user is deleted, and Security_type: INVOKER, dbuser01 create stored procedure pro_test can be granted execute permission, access to the corresponding object in the stored procedure, account execution. That is, whether the stored procedure will be checked when it is called

Definer: dbuser01@10.127.% # # will not check whether this user has permission to execute stored procedures or access objects

Security_type: INVOKER # # only check whether the calling stored procedure account has execute permission and access object permission

Stored procedure pro_test call scenario 7

When the scenario 06:dbuser01 exists and the account permissions are reclaimed

Dbuser02 permission: execute on procedure dbtest.pro_test

Definer: dbuser01@10.127.%

Security_type: INVOKER

Dbuse01 execute on procedure dbtest.pro_test from dbuser01 only

(root:localhost:Wed Dec 14 16:43:35 2016) [dbtest] > grant USAGE on *. * to dbuser01@'10.127.%' identified by 'dbuser01'

Query OK, 0 rows affected (0.00 sec)

(root:localhost:Wed Dec 14 16:58:10 2016) [dbtest] > revoke EXECUTE ON PROCEDURE `dbtest`.`pro _ test`from 'dbuser01'@'10.127.%'

Query OK, 0 rows affected (0.00 sec)

Dbuser01 calls the stored procedure pro_test:

Mysql > call pro_test

ERROR 1370 (42000): execute command denied to user 'dbuser01'@'10.127.%' for routine' dbtest.pro_test'

Dbuser02 calls the stored procedure pro_test:

Mysql > call pro_test

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Root calls the stored procedure:

(root:localhost:Wed Dec 14 16:58:37 2016) [dbtest] > call pro_test

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Dbuser01 reclaims execute on procedure dbtest.pro_test from dbuser01 and select on dbtest.t1

(root:localhost:Wed Dec 14 16:59:45 2016) [dbtest] > revoke select on `dbtest`.`t1`from 'dbuser01'@'10.127.%'

Query OK, 0 rows affected (0.00 sec)

Dbuser01 calls the stored procedure pro_test:

Mysql > call pro_test

ERROR 1370 (42000): execute command denied to user 'dbuser01'@'10.127.%' for routine' dbtest.pro_test'

Dbuser02 calls the stored procedure pro_test:

Mysql > call pro_test

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Root calls the stored procedure:

(root:localhost:Wed Dec 14 17:01:17 2016) [dbtest] > call pro_test

+-+

| | id |

+-+

| | 3 |

| | 4 |

| | 1 |

+-+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Scenario 07 conclusion: the dbuser01 user exists, and the Security_type: INVOKER, dbuser01 creation stored procedure pro_test can be executed by an account with execute permission and access to the corresponding objects in the stored procedure. That is, whether the stored procedure will be checked when it is called, even if dbuser01 is the creator of the stored procedure, but it is not given the permissions of execute and select on dbtest.t1, and it cannot execute pro_test.

Definer: dbuser01@10.127.% # # will not check whether this user has permission to execute stored procedures or access objects

Security_type: INVOKER # # only check whether the calling stored procedure account has execute permission and access object permission

Thank you for reading this article carefully. I hope the article "sample Analysis of the permissions of MYSQL stored procedures" shared by the editor will be helpful to you. At the same time, I also hope that you will support and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.