How to Avoid Accidental Database Deletion?

How to avoid accidental database deletion is almost a cliché topic and is also a concern for most large-scale enterprises. The database deletion incidents that occurred in major companies such as JD Daojia, Weimob, Lianjia, and Cisco are still fresh in our minds. Regardless of whether the parties involved did it intentionally or due to a system bug, the consequences and losses are profound and irreparable. Only by intercepting the database deletion operation at the physical level can we truly eliminate such incidents, as no one can guarantee that they won't be the next victim.

Therefore, the topic of this article, although not groundbreaking, is of great importance and is related to the core asset of an enterprise: data.

Traditional Change Process

The importance of data goes without saying, but understanding is one thing, and the reality is that many companies' databases still have the following issues:

• Direct Database Connection: This is the biggest security risk. The database accounts of many companies are usually divided according to departments, meaning that all members of a department use the same account, making it difficult to distinguish which personnel executed the SQL, and the audit is challenging. At the same time, it is impossible to customize permissions based on each person's responsibilities, and there is a risk of unrelated personnel having change permissions. In addition, changes in database permissions due to personnel additions, transfers, and resignations are also difficult to manage effectively.
• Unregulated Changes: Developers have different experiences and habits, and they may use different database design patterns, naming rules, data types, etc. This can lead to a chaotic database structure, data redundancy, and a vicious cycle, making the database more difficult to maintain over time. Although companies may promote production database standards through training and other means, without platform standards and mandatory approval processes, it is impossible to ensure that all developers follow the rules.
• Incomplete Change Process: For changes submitted by the data source, in most cases, they are submitted to the DBA by developers through email, JIRA, and other means. Of course, there are also companies with incomplete processes that directly use chat tools for communication and submission of data changes. What's worse, developers directly make changes to the production environment, which is very unsafe.

Solving the above problems can greatly avoid the risk of accidental database deletion. Below, I will provide a solution that uses the NineData tool to fully demonstrate the entire process from entering the data source, configuring standards and approval processes, to developers submitting production releases, showing how NineData can solve the problem of accidental database deletion.

NineData integrates multiple modules such as database DevOps, data replication, data backup, and data comparison. We need to use its database DevOps module to manage our database. Database DevOps has functions such as data asset management, data query, SQL execution, data editing, data import and export, SQL approval process, SQL standard pre-inspection, approval process, and sensitive data protection, which can fully cover the issues we mentioned above.

Step One: Enter the Data Source into NineData

By entering the data source into NineData, achieve a unified platform login within the company, no longer needing to use a variety of clients and tools. Most importantly, it completely eliminates the security issues brought by direct database connections.

NineData provides a Data Owner feature, which will play an important role in the later configuration of the approval process. I will explain in detail in a specific section, so let's skip it for now. After creating the data source (i.e., entering the data source into NineData), the creator of the data source is the default Owner.

Step Two: Configure Development Standards and Approval Processes

By default, the NineData platform provides general standards and processes for development and production environments. Each data source needs to select an environment when created, and after creation, it will be bound to the standards and processes corresponding to the selected environment by default.

If the default standards and processes do not meet your requirements, you can manually configure them according to the actual situation. This process takes disabling the SQL Console change capability of the production database and configuring a two-level approval process as examples to introduce the configuration method.

1. Open the details page of the SQL development standard that needs to be configured, find the two rules responsible for managing the SQL Console change capability, and delete all SQL types that allow operations.

   

   Rule Name	Switch	Edit Details
   	On	Click on the column on the right side of , find , delete all operation types in the text selection box, and click .
   	On	Click on the column on the right side of , find , delete all operation types in the text selection box, and click .

2. Open the details page of the approval process that needs to be configured, find the approval process of the target task, add a new approval process, and select the approver.

   

   According to the configuration in the picture, if developers submit an SQL task and it passes the standard pre-inspection, they need to go through a first-level and second-level approval before it can be executed in the production database.

   It is necessary to mention the Data Source Owner configured in the picture. We mentioned in step one that this is a solution to simplify the configuration of the approval process. Generally, companies will have two methods to configure the approval process:

   • Put all approvers into one approval process: Put multiple business leaders into a single approval process, and the submitter selects according to the actual situation. The advantage of this method is that it is easy to configure, and only one adjustment is needed in the later stage if there is a personnel change; the disadvantage is that when there are many business leaders, it takes a long time to find them, and if the submitter is not familiar with the business situation, they may choose the wrong business leader.
   • Create different approval processes for all businesses: To avoid the above problem, each business has an independent approval process. The advantage of this method is precision; the disadvantage is that if there are 1000 businesses, then 1000 approval processes need to be configured. Regardless of the initial configuration cost, in case of personnel changes, each process needs to be adjusted, and the maintenance difficulty is huge.

   By using the Data Source Owner plan, administrators can configure different leaders for each business (data source, database), that is, Data Source Owners, and select Data Source Owner as the approver in the approval process instead of configuring specific personnel. When the submitter submits an operation application for a specific data source or database, the system will automatically pull the Data Source Owner of that data source or database, effectively simplifying the approval process configuration and reducing operational costs and maintenance difficulty.

Usage Effect

After the above configuration, the basic work is done, and the remaining is to require all employees in the company who need to access the database to log in through the NineData platform to perform related operations.

1. SQL Console changes have been disabled, and ordinary employees cannot directly perform DDL or DML operations on the production database through the SQL Console. The page will guide users to create an SQL task and go through the approval process for release.

   

2. After the user submits the SQL task, the system will first review the SQL according to the SQL development standards pre-configured by the administrator. After passing the review, manual review is required. Since we configured a two-level approval process for the rule level as compliant with standards in the above steps, when the system determines that the current SQL hits compliant with standards, the user needs to select two approvers.

   

   Moreover, since we selected Data Source Owner (i.e., the user named NineData in the example) as the first-level approver when configuring the approval process, the system automatically pulled the user named NineData when the user selected the first-level approver.

3. After the approver approves, the SQL task executor selected by the user can execute the SQL in the production database. Since this example chooses automatic execution, the system will immediately execute the SQL after the approver passes.

   

4. After execution, by querying through the SQL Console, it is found that the data has been successfully written.

   

Summary

So far, the company has obtained a more reliable data change solution and no longer needs to worry about the problem of accidental database deletion.

The reason for recommending NineData is not only because it can perfectly solve the company's problems, but more importantly, if the number of the company's data sources is less than 10, it is completely free. For small and medium-sized enterprises, being able to obtain such comprehensive and powerful data management and protection tools at zero cost is undoubtedly an excellent plan to reduce costs and increase efficiency.

Moreover, what the author introduced is actually just a few basic functions of NineData. As can be seen from the demonstration effect in this article, there are still data tracking, data archiving, SQL code review, slow query analysis, DSQL, data import and export, and other functions in the navigation bar on the left side of the page. These are all powerful tools that can help companies manage databases. Due to space reasons, they cannot be introduced in detail in this article. It is recommended to try it directly and experience the power of NineData yourself.