SQL auditing system and method for database release

Abstract

The invention relates to an SQL auditing system and method for database release. The system comprises an authority management module, an equipment management module and a work order management module, and the authority management module is used for obtaining authority information of different users in the system; the equipment management module is used for acquiring information of a to-be-managed database and managing the to-be-managed database based on the information of the to-be-managed database; and the work order management module is used for acquiring SQL work orders of users in the system, and checking and publishing the SQL work orders according to the authority information of different users and the information of the SQL work orders. Compared with the prior art, the method has the advantages of being high in efficiency and accuracy, capable of saving labor cost, good in expansibility and the like.

Description

technical field [0001] The present invention relates to the field of database operation and maintenance, in particular to a database publishing-oriented SQL auditing system and method. Background technique [0002] In the existing database management practice, the operation of the database is performed by logging into the target machine that can be connected to the database, and using the client to execute the database statement to realize the operation of DML and DDL statements; [0003] At present, the traditional database statement publishing process of major companies is as follows: 1. Develop and write corresponding SQL statements according to the requirements, and submit them to the corresponding DBA by email after the test environment is executed; 2. The DBA manually reviews the syntax specification of the statement and whether the statement conforms to the company's After the specification, log in to the machine to execute the sql statement provided by the developmen...

AI Technical Summary

Problems solved by technology

At present, the common practice is that people with different roles, such as developers and DBAs, communicate with each other by email. When various abnormalities occur during the release process, the release process can only be re-operated after direct communication with people with different roles. However, this process does not It is conducive to the standardization and normalization of the release process, and it is difficult to strictly control the database statement release process

[0005] As microservices and containerization become mainstream technical solutions, database applications are also growing. However, traditional database management and operation and maintenance rely heavily on manpower, and the process is complex, time-consuming and laborious, and has poor timeliness. The main disadvantages are as follows:

[0006] 1. For the database query process, the existing scheme does not have a strict approval process, and there are process loopholes, which cannot effectively control the query of online data, which affects the statistical accuracy of other operation and maintenance data such as database access. increase, the frequency of database queries increases, and the changes of database statements are more difficult to trace, and the history of changes cannot be queried and counted; in addition, due to the multiple interactions between the SQL bill of lading person and the DBA in the form of emails, data security is involved. Confidentiality and other issues

[0007] 2. For the division of SQL bill of lading person, DBA, operation and maintenance roles and SQL execution plan, the current role division is not clear, there is no platform for centralized authorization and authentication, and the SQL bill of lading person does not specify personnel roles, so it is difficult to manage and control. When actually executing SQL statements, there are many manual interventions. When execution exceptions occur, for example, when the execution time is long, manual intervention must be performed, which takes up a lot of DBA working time. The steps involved in the release process such as execution process and result feedback are all In this way, it can be seen that the coupling between the bill of lading person, DBA, and operation and maintenance roles in the current plan is relatively high, and the same as the execution of SQL, there are problems of imprecise processes and irregular operations

[0008] 3. Regarding the current database query statement writing, it is required that the SIT, UAT, and PAT environment data are consistent. At present, based on data security considerations, major companies often have different data in various environments, which leads to poor execution results. The expected situation, and the types of databases include relational and non-relational types, and the types of databases also include Oracle, MySQL, SQLServer, etc., so corresponding database statements are required. The current technical solution does not have a unified SQL statement to carry (possibly txt, word, excel, etc. form mail) and encoding format, which can easily cause garbled characters and affect query efficiency; in addition, due to the lack of a complete SQL statement verification strategy, data modification and addition operations may be overwritten and deleted by mistake, and there are many uncertainties. It is difficult to assess, and the data security risk in the online environment is relatively high

Images