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

The access scope of the front-end recovery production account, that is, the account naming method that used to be xxx@ "%", has been modified to xxx@ "IP address" based on the IP of the machines deployed by the front-end application. Try to reduce the occurrence of untrusted clients connecting to the database and strengthen data security.

However, after recovering the xxx@ "%" account, it is found that the production subsystem has been reporting an error, and there is no exception in the xxx@ "%" account. At first, by checking the configuration of the jdbc database connection string of the production code, it is found that the address is configured as the hostname of the server. It is suspected that the domain name was mistakenly resolved to the IP address of the public network. The same error still exists after the domain name is modified to the IP of the private network and reissued.

Later, after investigation, it is finally found that because the subsystem also uses triggers, but there is no definer defined when writing triggers, the definer of the trigger defaults to the current user who wrote the trigger, that is, xxx@ "%".

It turns out that when mysql deletes a user, it will only affect the user table, db table and tables_priv table of the mysql system library, and the trigger created by this user will not be deleted, because the information of the trigger is stored in the triggers table of the information.schema library. Therefore, when other ordinary users (without administrator privileges) want to call other users' triggers, they will report an error.

When the problem is located, it will be easy to solve:

1. Delete the trigger of the original xxx@ "%" and redefine the trigger whose definer is xxx@ "IP address"

2. Ordinary accounts can call triggers, and you need to configure triggers permissions, otherwise an error will be reported with trigger permissions.

To sum up:

The reason why a database is a database is its strong ability to store and retrieve data. Although the database also has triggers, custom functions and stored procedures such as functions, but the implementation of functions is at the expense of part of the performance of the database. Moreover, it will also lead to the tight coupling between the front-end application and the back-end service, the front-end will be changed, and the follow-up service will also change. Therefore, unless it is a special scenario such as BI, data analysis, and so on, the general production environment disables trigger, functions, and stored procedure. Its function is realized at the code level to reduce the load of the database.

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.