Shulou(Shulou.com)06/01 Report--

Foreword:

We all specify host when we create a database user, that is, a complete user can be described as' username'@'host'. When creating a user without explicitly specifying host, the default is%, which means that all ip segments can use this user, or we can specify host as an ip or ip segment, which will only allow the database user to be used on the specified ip host. However, you should also understand that 'username'@'%' and' username'@'192.168.6.%' are two unrelated users who can have different passwords and permissions. It is not recommended to create multiple users with different host with the same name, and do not easily change the user's host. The author has encountered failures caused by changing the user's host. I will share them below to tell you the causes and consequences.

1. Fault simulation

At that time, in order to standardize security, the host of a user of a program was changed from% to the ip segment of the application server. after a period of time, some functions reported an error. After troubleshooting, it was found that the stored procedure could not be called (you can think about the reason first). The failure operation is simulated below.

# original user, table, and stored procedure simulation to create mysql > create user' testuser'@'%' identified by '123456 query OK, 0 rows affected (0.04 sec) mysql > grant select,insert,update,delete,execute on `testdb`. * to' testuser'@'%';Query OK, 0 rows affected (0.01 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > show grants for 'testuser'@'%' +-- + | Grants for testuser@% | +- -+ | GRANT USAGE ON *. * TO 'testuser'@'%' | | GRANT SELECT INSERT, UPDATE, DELETE, EXECUTE ON `testdb`. * TO 'testuser'@'%' | +-+ CREATE TABLE `students` (`id`int (11) NOT NULL, `name` varchar (20), `age`int (11), PRIMARY KEY (`id`) ENGINE=InnoDB INSERT INTO `students`VALUES ('1001years,' lodd', '23'); INSERT INTO `students`VALUES (' 1002years, 'sdfs',' 21'); INSERT INTO `students`VALUES ('1003years,' sdfsa', '24'); DROP PROCEDURE IF EXISTS select_students_count;DELIMITER $CREATE DEFINER= `testuser` @ `% `PROCEDURE `select_students_ count` () BEGIN SELECT count (id) from students;END$$DELIMITER; # call normal mysql > call select_students_count () using testuser user call stored procedure +-+ | count (id) | +-+ | 3 | +-+ # change user host rename user mysql > RENAME USER 'testuser'@'%' to' testuser'@'192.168.6.%';Query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.01 sec) mysql > show grants for 'testuser'@'192.168.6.%' +-+ | Grants for testuser@localhost | | +-+ | GRANT USAGE ON *. * TO 'testuser'@'localhost' | | GRANT SELECT | INSERT, UPDATE, DELETE EXECUTE ON `testdb`. * TO 'testuser'@'localhost' | +-# the stored procedure cannot be called by the testuser user again Use failure mysql > call select_students_count () ERROR 1449 (HY000): The user specified as a definer ('testuser'@'%') does not exist2. Troubleshooting and resolution

In fact, after we manually call the stored procedure, we can clearly see from the error content that it is because of the problem that 'testuser@'%' users do not exist. Because the definer of the stored procedure is' testuser@'%', and we change the user's host to 192.168.6%, then when we call the stored procedure later, the system determines that the master user of the stored procedure does not exist, so the system rejects the request and throws an exception.

When we know the above reasons, the solution will be much clearer, we just need to change the owner of the stored procedure to a new user. In fact, after changing the user, the views, stored procedures, functions, triggers and events under that user will be affected. When we define views, stored procedures and functions using DEFINER attributes, if we call these objects, the system will first determine whether the master user of this object exists, and an error will be thrown directly if it does not exist.

There are two solutions to this problem. One is to change the security property of this stored procedure from DEFINER to INVOKER, which is not recommended by individuals. The difference between DEFINER and INVOKER will be explained in the next chapter. The second is to change the owner of this stored procedure. Here's how to change it and verify it:

# change the owner mysql of the stored procedure through the system table > update mysql.proc set definer='testuser@192.168.6.%' where db='testdb' and name='select_students_count' and type='PROCEDURE'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: verify the success of the call using testuser user call mysql > call select_students_count () +-+ | count (id) | +-+ | 3 | +-+ 1 row in set (0.00 sec) 3.DEFINER and INVOKER expand knowledge

In MySQL, when creating views (view), functions (function), stored procedures (procedure), triggers (trigger), and events (event), you can specify the security authentication method (that is, SQL SECURITY) attribute, whose value can be DEFINER or INVOKER, indicating whose permission is used to execute during execution.

DEFINER: INVOKER is executed by the permissions of the user specified by definer (the definer): executed by the permissions of the user who calls this view (stored procedure)

By default, the system is designated as DEFINER. When the SQL SECURITY attribute is DEFINER, the user specified by DEFINER must exist in the database, and the user must have the corresponding operation permissions and the permissions of the referenced related objects in order to execute successfully. Regardless of whether the current user has permissions. When the SQL SECURITY property is INVOKER, as long as the executor has the execute permission and the permission of the referenced related object, it can be executed successfully.

After knowing the above knowledge, you may already understand the causes and consequences of the above failures. In daily production, it is not recommended to use the INVOKER attribute, because after defining SQL SECURITY as INVOKER, other users need to have not only the execution permission of the object but also the permissions of other referenced related objects when calling this object, which greatly increases the complexity of operation and maintenance. The following reviews the whole article and sorts out some personal suggestions for your reference:

Do not create multiple users with different host with the same name. Do not easily change the user's host. To change the user host, use the RENAME USER statement to directly update the host property in the mysql.user system table, which will result in the loss of permissions. After you change the user host, you should pay attention to the DEFINER property of each object under this user. It is recommended that you define SQL SECURITY as DEFINER to create objects such as views, stored procedures, and so on. When migrating the database, be aware that there are DEFINER users with relevant object definitions in the new environment.

Summary:

In this paper, starting from a fault, a detailed record of the cause of the fault and the knowledge involved, in fact, such as the DEFINER attribute of these details are easy to be ignored, only encounter problems we will explore. I hope this article can make you learn something new, especially the above suggestions are summarized by the author's daily operation and maintenance. Originality is not easy, please support us!

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.