Overview
It's not the problems that scare me, but the fear of not being able to find them. In operation and maintenance teams, it's common to require logs of operations or development activities on servers—such as regular audits or when a Server has been hacked and logs have been deleted. In such situations, it's essential to know what happened. Today, I'm sharing our DIY solution for recording shell and MySQL operations on our current servers.
We tested some hardware bastion solutions before, but they lacked user-friendliness, flexibility, and were expensive. Plus, they could become single points of failure. We also explored open-source alternatives like ttyrec, which can record terminal sessions and support text matching. However, we found serious bugs during actual use, and other modified versions of OpenSSH didn't meet our expectations either.
In light of these challenges, we made simple modifications to Bash and MySQL code to create a low-cost, high-performance log auditing solution. This system allows us to track every action taken by users, whether through the shell or MySQL, ensuring full traceability and accountability.
Server logs are crucial for auditing purposes. Here are some examples of the logs generated:
This is just a small part of our open-source-inspired approach. I hope it helps others who are looking for similar solutions.
1. Linux Bash Audit
Many people have heard of Bash modification tools for audit purposes. We've been using one since 2011, and over the years, we've fixed many bugs and optimized the system. The features we now support include:
- Accurate tracking of shell operations per user
- Support for remote logging via SSH IP "command" and SCP
- Logging continues even when switching users
- New support for MySQL operation logs
These features are based on real-world needs and have been tested extensively. Although not perfect, they can meet most requirements as long as there's no deliberate attempt to avoid logging. They work well in multi-user scenarios, such as when multiple people share the same account, like root.
1.1 Basic Functions
The basic function is to record each user's command history. There's a prototype online, where we modified the bashhist.c file and the bash_syslog_history function around line 701. After the changes, we had a more reliable way to log all shell activities.
1.2 Fingerprint Variable Processing
Next, we dealt with the NAME_OF_KEY variable. The idea was simple: when a user logs in, we automatically run a script that sets this variable to a specific person’s identifier. We don’t recommend using passwords anymore—too unprofessional.
In the ~/.ssh/authorized_keys file, we set the first three columns to identify the user. We used a format like work_number@name_pinyin and processed it through a script located at /etc/bash_ywjt. Here's an example of the script content:
2. MySQL Operation Logs
We've long searched for a MySQL plugin that would allow us to log all operations. We tried several options, including the audit plugin, but none met our needs. Unlike MySQL binlogs or .mysql_history, we needed detailed records of actions taken by different users on the same system.
For this, we used Percona-Server-5.5. Initially, we enabled syslog logging manually by adding the syslog option to the client section in /etc/my.cnf. This setup was straightforward:
1) When using mysqldump, errors occurred.
2) It was easy to bypass the log by adding --no-defaults to the mysql command line.
3) Only root operations were logged, not those from other users.
To solve these issues, we turned to open-source code modification.
2.1 Source Code Modification
We opened the syslog switch in the source code. It was easier than expected. We found the client/mysql.cc file, and the logic was simple. We located the following code:
2.2 Interactive Records
Modifying the code revealed a small issue: even if syslog was enabled, certain commands like -e in the shell couldn't be logged. While we already had shell logs, we wanted to capture everything. So we adjusted the code to remove the 'and' condition related to CLIENT_INTERACTIVE:
2.3 Massive Log Problems
In practice, large SQL imports created massive logs, which wasn't ideal. We already had a solution for shell commands, but for MySQL, we had to modify the code again. We changed the syslog writing logic to only log up to MAX_SYSLOG_MESSAGE length, ignoring excess data.
Additionally, we introduced the NAME_OF_KEY variable from our Bash audit into MySQL.cc to ensure precise user identification.
2.4 Custom Parameters
Even with the log enabled, we wanted to provide an option to disable it. We modified the parameter definition and replaced the previous syslog call with something like this:
3. Remote Log Center
All logs were previously sent to /var/log/messages via syslog. A better approach is to forward them to a centralized log server. This is simple: add a configuration to /etc/rsyslog.conf:
4. Intranet Log Interception
As an additional measure, we implemented log interception. Our custom system writes logs to the public network, but intranet servers may face issues where the original internal IP isn't visible. To solve this, we used a Juniper firewall to intercept logs and forward them to an internal log center. Here's a sample configuration:
PC Motherboard,Mini Motherboard,Motherboard PC
Shenzhen Innovative Cloud Computer Co., Ltd. , https://www.xcypc.com