Logging user login information using init_connect parameter

1. Scene:

Suppose you are a company mysql-DBA and suddenly all the data in the company's database is deleted.
Despite data backups, tens of millions of dollars have been lost due to service stoppage, and now the company needs to identify the person who did the deletion.
However, there are many people who have database operation privileges, how to investigate?Where is the evidence?Do you feel powerless?
mysql itself does not have the ability to operate audits, does that mean you can only think you're down in this situation?
This article will discuss an easy way of thinking for mysql access auditing.

2. Overview:

mysql itself already provides a detailed sql execution record, general log, but opening it has several drawbacks:
1) Regardless of whether the sql has grammatical errors or not, it will be recorded as soon as it is executed, resulting in a large amount of useless information being recorded. Later filtering is difficult.
2) When there is a large amount of SQL concurrency, log records will give io a certain impression that the efficiency of the database is reduced.
3) Log files can easily expand rapidly, and improper handling can have an impact on disk space.

3. Views of this article:

Use init-connect + binlog method for mysql operation audit.
Because mysql binlog records all sql statements that actually modify the database for a long time, their execution time, and connection_id, but does not record detailed user information corresponding to connection_id.
So this article will use init-connect to record the user and connection_id information for each connection during its initialization phase.
When conducting behavior tracking in subsequent audits, the final conclusions are drawn based on the binlog record's behavior and the corresponding connection-id combined with previous connection log records.

4. Text:

1. Create a database and tables to hold connection logs

Be careful:
This table must be used by every client (INSERT privilege), otherwise it cannot be recorded, and more importantly, if you do not have privileges on this table, the account will not be able to log on to MySQL!!!
Therefore, it is recommended that version 5.6 and earlier use the default database, test, which can be used by all users by default. After 5.7, only create public libraries or assign rights to each account.

Here's an example of a self-built public library:

mysql> create database mylog; Query OK, 1 row affected (0.00 sec) mysql> use mylog; Database changed mysql> create table accesslog ( `connection_id` int(11) not null comment 'link ID', `account` varchar(100) comment 'Account used', `login_user` varchar(100) comment 'log on user', `login_time` timestamp comment 'login time', primary key (connection_id) )engine=innodb comment='Account Logon Information'; Query OK, 0 rows affected (0.01 sec)

2. Set custom libraries as public libraries

Note: The feature of a public library is that the User field of the library in the mysql.db table is empty, that is, no owner is specified for the library.

1) First assign the custom library to a user to have records in mysql.db;

mysql> grant select,insert on mylog.* to abc@'%'; -- Here abc@'%'Account already exists in the system Query OK, 0 rows affected (0.00 sec)

2) Modify the User field recorded by the custom library in mysql.db to be empty

mysql> select Select_priv,Insert_priv,User,Host from mysql.db where Db='mylog' and User='abc' ; +-------------+-------------+------+------+ | Select_priv | Insert_priv | User | Host | +-------------+-------------+------+------+ | Y | Y | abc | % | +-------------+-------------+------+------+ 1 row in set (0.00 sec)

Before modifying, note that the filter condition is correct and that careful operation affects other records

mysql> update mysql.db set User='' where Db='mylog' and User='abc' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select Select_priv,Insert_priv,User,Host,Db from mysql.db ; mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

3. Set init_connect

mysql> show global variables like 'init_connect'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | init_connect | | +---------------+-------+ 1 row in set (0.00 sec)

Sets the insert action that is triggered after logon.

mysql> set global init_connect='insert into mylog.accesslog(connection_id,account,login_user,login_time) values(connection_id(),current_user(),user(),now());'; Query OK, 0 rows affected (0.00 sec)

Note: This configuration can be written to the configuration file if it is used for a long time.

View the results after setup:

mysql> show global variables like 'init_connect'; +---------------+-------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------+ | init_connect | insert into mylog.accesslog(connection_id,account,login_user,login_time) values(connection_id(),current_user(),user(),now()); | +---------------+-------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

4. Validation

Before performing login:

mysql> select * from mylog.accesslog;

Perform login operations:

[root@localhost ~]# mysql -ubook -p123456 -h192.168.32.2

After login:

mysql> select * from mylog.accesslog; +---------------+---------------+----------------+---------------------+ | connection_id | account | login_user | login_time | +---------------+---------------+----------------+---------------------+ | 26 | book@% | [email protected] | 2020-05-07 11:45:11 | +---------------+---------------+----------------+---------------------+ 1 row in set (0.00 sec)

You can see that the login information of the book user is recorded after login.

5. Cancel setup afterwards

1) Cancel parameter configuration
Parameter values before query modification

mysql> show global variables like 'init_connect'; +---------------+-------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------+ | init_connect | insert into mylog.accesslog(connection_id,account,login_user,login_time) values(connection_id(),current_user(),user(),now()); | +---------------+-------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

Restore parameter values

mysql> set global init_connect='';

View modified parameter content

mysql> show global variables like 'init_connect'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | init_connect | | +---------------+-------+ 1 row in set (0.00 sec)

2) Delete database privilege record information

mysql> select Select_priv,Insert_priv,User,Host from mysql.db where Db='mylog' and User='' ; +-------------+-------------+------+------+ | Select_priv | Insert_priv | User | Host | +-------------+-------------+------+------+ | Y | Y | | % | +-------------+-------------+------+------+ 1 row in set (0.00 sec)

Before modifying, note that the filter condition is correct and that careful operation affects other records

mysql> delete from mysql.db where Db='mylog' and User='' ; Query OK, 1 row affected (0.00 sec) mysql> select Insert_priv,User,Host,Db from mysql.db ; mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

3) Delete custom database
Note: Make sure there are only log tables in the library that we want to delete before doing so

mysql> show tables from mylog; +-----------------+ | Tables_in_mylog | +-----------------+ | accesslog | +-----------------+ 1 row in set (0.00 sec) mysql> drop database mylog; Query OK, 1 row affected (0.00 sec)

5. Used for record tracking

1.thread_id confirmation

Suppose you want to know who deleted the test.dummy table on November 25, 2009, at more than 9 a.m.You can use the following statements to locate (-B line matching records and their first five rows of information)

mysqlbinlog –start-datetime='2020-05-07 11:50:00' –stop-datetime='2020-05-07 11:55:00' binlog.xxxx | grep 'dummy' -B 5

You get the following results (visible thread_id is 5):

# at 300777 #200507 11:50:11 server id 10 end_log_pos 301396 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1259052840; drop table test.dummy;

2. User Confirmation

Once thread_id is confirmed, finding the culprit is simply a question of the sql statement.

mysql> select * from mylog.accesslog where connection_id=5 ;

You can see that [email protected] did it.

+---------------+---------------+---------------------+--------------------------+ | connection_id | account | login_user | login_time | +---------------+---------------+---------------------+--------------------------+ | 5 | testuser2@% | [email protected] | 2020-05-07 11:50:11 | +---------------+---------------+---------------------+--------------------------+ 1 row in set (0.00 sec)

VI. Q&A

Q: Does using init_connect affect server performance?
A: In theory, only one record will be inserted into the database each time a user connects, which will not have a significant impact on the database.Unless the connection frequency is very high (of course, at this point it is important to note how the connection is multiplexed and controlled, not whether it is to be used in this way).

How is the Q:access-log table maintained?
A: Because it is a log system, archive storage engine is recommended to facilitate data compression.If you have a large number of database connections, it is recommended that you export the data at a certain time and then clean up the tables.

Q: Is there any other use for tables?
A: Yes!Of course, the access-log table is not only used for auditing, but also for data analysis on database connections, such as daily connection number distribution maps, etc., which can't be expected.

Q: Will there be any missing records?
A: Yes, init-connect will not be executed when the super user logs in.So there is no record of database superusers in the access-log, which is why we don't advocate multiple superusers and many people use them.

Add new comment

0 comments