Non-invasive MySQL user login auditing method

Abstract

The invention discloses a non-invasive MySQL user login auditing method, relates to the technical field of database auditing, and solves the technical problem of security risk and performance risk caused by log record triggering by intercepting login operation in the prior art. The method comprises the following steps: capturing accounts account information of MySQL in real time, wherein the accounts account information comprises a user name, a client I P, a current connection number and a historical total connection number; calculating a newly added connection, an interrupted connection and a continuous connection according to the account number connection number acquired continuously twice; repeating the previous steps to form a connection log of the user in a certain time period so as to realize user access auditing; according to the method, the MySQL account information is regularly collected, so that the login user information can be quickly obtained, which is obviously different from the log recording triggered by intercepting the login operation in the prior art, and the method is a non-invasive log recording method, so that the safety and performance problems are solved.

Description

Technical Field

[0001] The present invention relates to the technical field of database auditing, and particularly relates to a non-intrusive MySQL user login auditing method. Background Art

[0002] The existing MySQL user login auditing methods can be roughly divided into three categories:

[0003] (1) Installation of audit plugins: such as MariaDB Audit Plugin or MySQL Enterprise Audit.

[0004] (2) Enabling the general log method: General_log provided by MySQL itself.

[0005] (3) Login-triggered logging method: implemented by writing triggers or configuring init_connect parameters, etc.

[0006] The principles of these three solutions are as follows:

[0007] (1) The audit plugin intercepts database operation events (such as user logins) through the API or hook mechanism provided by the MySQL server. When these events occur, they trigger the corresponding processing of the audit plugin, achieving comprehensive monitoring and recording of user login activities.

[0008] (2) MySQL General_log records all operations performed by the client on the MySQL server. By collecting all the execution logs in the General Log and performing data cleaning and processing, user login and operation logs are formed.

[0009] (3) Log information such as the logged-in username and client IP is recorded when the user connects to the MySQL server.

[0010] The difference between the proposed solution of this application and the existing technical solutions is that it does not change the original configuration and table structure of the MySQL server and does not occupy additional memory and CPU resources of the MySQL server.

[0011] Specific existing patented technologies that can be referred to include: the security auditing method for user sports training data based on MySQL General Log disclosed in publication number CN117744152A, the method, system, storage medium, and terminal for MySQL access auditing disclosed in publication number CN110674160A, and an Oracle database user secure login method and system disclosed in publication number CN106372534A.

[0012] III. Defects of the Existing Technical Solutions:

[0013] (1) Security risks: There are security risks in both Method 1 and Method 3. For example, it is necessary to change the original configuration or table structure of the target MySQL, which increases the risk of human error; the log file may contain sensitive information, increasing the risk of sensitive data leakage; the failure of the user login trigger log record operation may lead to connection failure and thus affect the business.

[0014] (2) Huge log files: The storage of the logs in Method 2 requires a very large disk space. For some industries such as the financial industry, the log retention time required by regulatory requirements may be up to several months, which is difficult to meet.

[0015] (3) Performance impact: There are performance problems in both Method 1, Method 2, and Method 3, which will increase the memory occupancy and CPU load of the MySQL server, especially affecting the database performance in high-concurrency scenarios. Summary of the Invention

[0016] To solve the problems existing in the above-mentioned prior art, the present invention provides a non-intrusive MySQL user login auditing method. By regularly collecting MySQL account information, it realizes the rapid acquisition of login user information, which is significantly different from the existing technology of intercepting login operations to trigger log records. It is a non-intrusive log recording method, thus solving the security and performance problems. According to the data collected at the T-th and (T-1)-th times, the connection situation of the same user within a time period is obtained through correlation calculation. In addition, the recording form of the user login log is further optimized, having the advantage of lightweight log files.

[0017] A non-intrusive MySQL user login auditing method includes the following steps:

[0018] Step 1: Create a user login log table mysql_user_connect, a calculation table current_connect, and a base_connect in the database for storing logs.

[0019] Step 2: Migrate the data in current_connect to the base_connect table to obtain historical user connection information.

[0020] Step 3: Collect the latest data in the performance_schema.accounts table of the MySQL server and store it in the current_connect table to obtain the current user connection information.

[0021] Step 4: Compare the number of connections of base_connect and current_connect, calculate the newly added connections, persistent connections, and interrupted connections, and store them in the log table mysql_user_connect.

[0022] Step 5: Repeat steps 2, 3, and 4 to form the summary information of MySQL user login logs within a specified time period.

[0023] Furthermore, the said step 1 includes:

[0024] Step 1.1: Create the user login log table mysql_user_connect;

[0025] Step 1.2: Create the current connection temporary table current_connect for calculation;

[0026] Step 1.3: Create the historical connection temporary table base_connect for calculation.

[0027] Furthermore, the said step 2 includes:

[0028] Step 2.1: Truncate the base_current table to obtain an empty base_connect table;

[0029] Step 2.2: Migrate the content in the current_connect table to the empty base_connect table to obtain the historical user connection information.

[0030] Furthermore, the said step 3 includes:

[0031] Step 3.1: Truncate the current_connect table to obtain an empty current_connct table;

[0032] Step 3.2: Collect the data of the accounts table from all MySQL instances to obtain the accounts temporary data file;

[0033] Step 3.3: Restore the temporary data file to the current_connect table to obtain the currently collected user connection information.

[0034] Furthermore, the said step 4 includes:

[0035] Step 4.1: Update the disconnected connection information, including updating the connection end time;

[0036] Step 4.2: Update the persistent connections;

[0037] Step 4.3: Record the newly added connection information, including database IP, port, username, client IP, and connection start time.

[0038] The beneficial effects of the present invention include:

[0039] No security risks: By regularly capturing the connection count information of the MySQL accounts account, the execution statement is SELECT, without the need to intercept the user login operation for additional DML operations, avoiding the risk of introducing audit operation risks and causing user access to the database to fail. In addition, by using the present invention, only the account name, client IP, and connection count information of the logged-in user are recorded, and there is no sensitive data in the log, so there is no risk of sensitive data leakage.

[0040] No performance impact: Although there is no clear limit on the maximum number of users that can be created in MySQL, the actual number is affected by factors such as hardware resources, operating system limitations, and MySQL configuration. Therefore, the data volume of the accounts table is usually very small. The present invention collects accounts information, executes a simple query SQL, has high collection efficiency, does not increase the server load, and avoids affecting the database performance.

[0041] Lightweight logs: Avoid enabling the General Log in the traditional method to record all operation logs of the MySQL instance. Simplify the storage of logs through calculation, record the connection status of the same account according to time intervals, and the log file occupies little resources. Brief Description of the Drawings

[0042] Figure 1 It is a flowchart of a non-intrusive MySQL user login auditing method according to an embodiment of the present application. Detailed Embodiments

[0043] To make the objectives, technical solutions, and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present application. Obviously, the described embodiments are only some of the embodiments of the present application, rather than all of them. Therefore, the detailed description of the embodiments of the present application provided in the accompanying drawings is not intended to limit the scope of the present application to be protected, but only represents the selected embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without creative efforts belong to the scope of protection of the present application.

[0044] Embodiment 1

[0045] A non-intrusive MySQL user login auditing method, as Figure 1 shown, includes the following steps:

[0046] Step 1: Create a user login log table mysql_user_connect and calculation tables current_connect and base_connect in the database for storing logs (non-production MySQL instance);

[0047] Step 2: Migrate the data in current_connect to the base_connect table to obtain historical user connection information;

[0048] Step 3: Collect the latest data from the performance_schema.accounts table of the MySQL server and store it in the current_connect table to obtain the current user connection information;

[0049] Step 4: Compare the connection counts in base_connect and current_connect, calculate the newly added connections, persistent connections, and interrupted connections, and store them in the log table mysql_user_connect;

[0050] Step 5: Repeat steps 2, 3, and 4 to form the summary information of MySQL user login logs within a specified time period.

[0051] The specific steps of the above step 1 include the following steps:

[0052] Step 1.1: Create the mysql_user_connect table, and the creation statement is as follows;

[0053] create table mysql_user_connect {

[0054] id bigint unsigned not null auto_increment comment 'logical auto-increment primary key',

[0055] db_ip varchar(15) default ”not null comment 'database IP',

[0056] db_port int default 3306 not null comment 'database port',

[0057] ... / / Other database information can be added according to actual needs,

[0058] user varchar(50) not null default ”comment 'connected user name',

[0059] host varchar(20) not null default "comment'Connection user client IP',

[0060] begintime datetime not null default '9999-12-31 00:00:00' comment 'Earliest login time',

[0061] endtime datetime not null default '9999-12-31 00:00:00' comment 'Latest logout time',

[0062] period_connections int not null default 0 comment 'Accumulated connection count',

[0063] createtime timestamp not null default current_timestamp comment 'Collection time',

[0064] basetime timestamp not null default current_timestamp on update current_timestamp comment 'Last calculation time',

[0065] primary key(id),

[0066] index idx_ip_port(ip,port),

[0067] index idx_user_host(user,host),

[0068] }engine = innodb comment 'MySQL user login log table';

[0069] Step 1.2 Create a temporary table current_connect for calculation, and the creation statement is as follows;

[0070] create table current_connect({

[0071] id bigint unsigned not null auto_increment,

[0072] db_ip varchar(15) default "not null comment 'Database IP',

[0073] db_port int default 3306 not null comment 'Database port',

[0074] user varchar(50) default "" comment 'Connected user',

[0075] host varchar(20) default "" comment 'Client IP',

[0076] current_connections int defalut 0 comment 'Current connection count of a certain account',

[0077] total_connections int default 0 comment 'Total connection count of a certain account',

[0078] createtime timestamp not null default current_timestamp,

[0079] primary key(id),

[0080] key idx_user(user,host)

[0081] ) engine = innodb comment 'Currently collected user connection count of MySQL instance';

[0082] Step 1.3 Create a temporary table base_connect for calculation, and the creation statement is as follows;

[0083] create table base_connect({

[0084] id bigint unsigned not null auto_increment,

[0085] db_ip varchar(15) default "" not null comment 'Database IP',

[0086] db_port int default 3306 not null comment 'Database port',

[0087] user varchar(50) default "comment'Connected user',

[0088] host varchar(20) default "comment'Client IP',

[0089] current_connections int defalut 0 comment 'Current connection count of a certain account',

[0090] total_connections int default 0 comment 'Total connection count of a certain account',

[0091] createtime timestamp not null default current_timestamp,

[0092] primary key(id),

[0093] key idx_user(user,host)

[0094] ) engine = innodb comment 'Historical collection of user connection counts for MySQL instances';

[0095] The specific steps of Step 2 are as follows:

[0096] Step 2.1: Truncate the base_current table to obtain an empty base_connect table;

[0097] Execute the statement: truncate table base_connect;

[0098] Step 2.2: Migrate current_connect to obtain historical user connection information;

[0099] Execute the statement: insert into base_connect select * from current_connect;

[0100] The specific steps of Step 3 are as follows:

[0101] Step 3.1: Truncate the current_connect table to obtain an empty current_connct table;

[0102] Execution statement: truncate table current_connect;

[0103] Step 3.1: Collect data from the accounts table of all MySQL instances to obtain the accounts temporary data file;

[0104] Execution statement (the example is the SQL statement for collecting accounts data of a certain MySQL instance, and the parameters are all examples, subject to actual requirements):

[0105] mysql - ucode_read - p123456 - h10.71.239.10 - P3306 - N - e "select '10.71.239.10',3306,ifnull(user,'0'),ifnull(host,'0'),current_connections,total_connections,now() from performance_schema.accounts" >> accounts_tmp.sql

[0106] Step 3.3: Restore the temporary data file to the current_connect table to obtain the currently collected user connection information;

[0107] Execution statement (the parameters are all examples, subject to actual requirements):

[0108] mysql - u code_dba - p123456 - h10.71.239.10 - P3306 - e "load data infile 'accounts_tmp.sql' into table current_connect (db_ip,db_port,user,host,current_connections,total_connections,createtime)"

[0109] The above Step 4 calculates the user connection situation within the specified time range based on the user connection count information collected twice from current_connect and base_connect. Specifically, it includes the following steps:

[0110] Step 4.1: Update the disconnected connection information (update the connection end time), and execute the SQL statement as follows:

[0111] update mysql_user_connect a inner join base_connect b on a.db_ip = b.db_ip and a.db_port = b.db_port and a.user = b.user and a.host = b.host and a.basetime = b.createtime inner join current_connect c on b.db_ip = c.db_ip and b.db_port = c.db_port and b.user = c.user and b.host = c.host set a.endtime = c.createtime, a.basetime = c.createtime, a.period_connections = (a.period_connections + (c.total_connections - b.total_connections)) where c.total_connections >= b.total_connections and c.current_connections = 0;

[0112] Step 4.2: Update persistent connections. Execute the following SQL statement:

[0113] update mysql_user_connect a inner join base_connect b on a.db_ip = b.db_ip and a.db_port = b.db_port and a.user = b.user and a.host = b.host and a.basetime = b.createtime inner join current_connect c on b.db_ip = c.db_ip and b.db_port = c.db_port and b.user = c.user and b.host = c.host set a.basetime = c.createtime, a.period_connections = (a.period_connections + (c.total_connections - b.total_connections)) where c.total_connections >= b.total_connections and c.current_connections != 0;

[0114] Step 4.3: Record the newly added connection information (database IP, port, username, client IP, connection start time), and execute the SQL statement as follows:

[0115] insert into mysql_user_connect(db_ip, db_port, user, host, begintime, connections, basetime) select a.db_ip, a.db_port, a.user, a.host, a.createtime, a.current_connections, a.createtime from current_connect a left join base_connect b on a.db_ip = b.db_ip and a.db_port = b.db_port and a.user = b.user and a.host = b.host where a.current_connections != 0 and ifnull(b.current_connections, 0) = 0.

[0116] Specifically, the comparison between the solution involved in this embodiment and the existing MySQL user login audit solution is shown in Table 1:

[0117] Table 1 Comparison of This Solution with Other Solutions

[0118]

[0119] It can be seen that the solution involved in this embodiment has significant advantages over the prior art in terms of log volume, performance, and security. These advantages make this solution have better application effects in some industries such as the financial industry. Specifically, the log storage is related to the storage cost. For industries that need to retain login logs for a long time, the log volume is too large and the storage cost is too high to meet the regulatory requirements. Performance issues and security risks directly affect the business and may cause business losses in some cases.

[0120] The above-described embodiments merely represent the specific implementation manners of the present application, and the description thereof is relatively specific and detailed. However, it should not be construed as a limitation on the protection scope of the present application. It should be noted that for those of ordinary skill in the art, without departing from the concept of the technical solution of the present application, several modifications and improvements can still be made, and these all belong to the protection scope of the present application.

Claims

1. A non-invasive MySQL user login audit method, characterized in that: The following steps are involved: Step 1: Create the user login log table mysql_user_connect and the calculation tables current_connect and base_connect in the database used to store logs; Step 2: Migrate the current_connect data to the base_connect table to obtain historical user connection information; Step 3: Collect the latest performance_schema.accounts table data of the MySQL server and store it in the current_connect table to obtain the current user connection information; Step 4: Compare the base_connect and current_connect connection numbers, calculate the newly added connections, continuous connections, and disconnected connections, and store them in the log table mysql_user_connect; Step 5: Repeat steps 2, 3, and 4 to generate the summary information of MySQL user login logs within the specified time period to implement user access auditing.

2. A non-invasive MySQL user login audit method according to claim 1, characterized in that: The step 1 comprises: Step 1.1: Create a user login log table mysql_user_connect; Step 1.2: Create a temporary table current_connect for calculation; Step 1.3: Create a temporary table base_connect for historical connections used for calculation.

3. A non-invasive MySQL user login audit method according to claim 1, characterized in that: The step 2 comprises: Step 2.1: Truncate the base_current table to obtain the empty base_connect table; Step 2.2: Migrate the contents of the current_connect table to the empty base_connect table to obtain historical user connection information.

4. A non-invasive MySQL user login audit method according to claim 1, characterized in that: The step 3 comprises: Step 3.1: Truncate the current_connect table to get an empty current_connct table; Step 3.2: Collect the accounts table data from all MySQL instances and obtain the accounts temporary data file; Step 3.3: Restore the temporary data file to the current_connect table to obtain the current collection user connection information.

5. A non-invasive MySQL user login audit method according to claim 1, characterized in that: The step 4 comprises: Step 4.1: Update the disconnected information, including updating the connection end time; Step 4.2: Update persistent connection; Step 4.3: Record the newly added connection information including database IP, port, user name, client IP, and connection start time.

Images