The story background

Not long ago, a friend asked me on wechat whether I could restore the deleted data. I asked the reason, but it was caused by a configuration item.


Story details

In Spring Boot to use JPA to operate the database, JPA will not do a detailed introduction, I believe that we all know or have used.

There is a configuration item in JPA that automatically initializes the table structure or updates the functionality of the table structure at startup. That sounds great. Very practical.

In fact, this is a very dangerous function, I think it should not provide this function, as long as there is a gap, there may be problems.

This configuration is: spring.jpa.hibernate.dcl-auto

  • Create (Danger factor 2 stars)

When the application starts, if there is no corresponding table in the database, it automatically creates a table structure based on the entity class structure. If the table already exists, the data in the table is cleared.

  • Create-drop (Danger factor 3 stars)

When the application starts, if there is no corresponding table in the database, it automatically creates a table structure based on the entity class structure. If the table already exists, the data in the table is cleared.

When the program stops, all tables in the database are deleted.

  • Update (Danger factor 1 star)

When the application starts, if there is no corresponding table in the database, it automatically creates a table structure based on the entity class structure. If the table already exists, it will check whether new fields are added or the length of the table is changed. If so, it will update the table structure without affecting the data.

  • Validate (Hazard factor 0 stars)

Validate does not update or delete tables or data, only validation logic.

  • None (Danger factor 0 stars)

Disable DDL operations.

How to prevent

The root of this problem is that the technical director does not pay attention to online security issues, mainly including the following points:

  • Data is not backed up

Online data must be backed up and stored on different machines to reduce risk.

  • Database account permissions are subdivided, and services are logically deleted without deletion permission

It may be better if your company has a DBA, who has more control over database-related security. If you don’t have a DBA, you’re basically managing development yourself, and the technical lead should do that.

The average developer can just give a read-only account and look up data online occasionally.

Also need to have a read and write account, used in the program.

Such as delete, DDL operations such permission to the administrator account on the line, otherwise it is easy to accident.

  • The framework in the application has the function of DDL operation, which is prohibited

This time, in addition to the developer’s carelessness, the main problem is the built-in DDL function in the framework. Features like this should be banned.

All table structures should be confirmed before going live, preferably manually, as DDL operations inherently lock tables and should be done during off-peak business periods. Don’t trust the program to do it automatically.

I remember we also had the logic to automatically create tables in the code before, which was pointed out in the code review and removed. Although said to be easier to use, but increased the risk of online databases. Some open source frameworks have similar logic that automatically creates tables for you when they don’t exist.

About the author: Yin Jihuan, a simple technology lover, the author of Spring Cloud Micro-service – Full stack Technology and Case Analysis, Spring Cloud Micro-service Introduction combat and Progress, the founder of the public account Monkey World.

If you are interested, you can pay attention to my wechat public number, Simtiandi, and read more technical articles for the first time. I also have some open source code on GitHub github.com/yinjihuan