Identify the MySQL user that executes the query and changes the database

Question

Our database has been breached. Though we changed the MySQL database user credentials, even moved to a new database, changed the server user credentials, CPanel, and FTP user credentials, and hardened the security, still the data gets breached time and again.

It seems like a trojan / malware hidden inside the system files. We tried almost everything (complete malware / virus scan, applied the security patches to our application, enabling MySQL logs) from our end as well as from the hosting provider, but still the malware was not found.

What we've found so far is,

• The malicious program doesn't use the application to breach our database. Because if it uses the application it'll be logged as well, as it doesn't use the MySQL user which the application uses to interact and manage the data.

• It changes only the specific database entities and adds redundant information to the tables.

We have enabled the MySQL General Query log to log all the queries (though we need to log only data manipulation queries), and through this we can find only the queries and their timestamps, not having the other crucial details like MySQL user which executes the query and how the user got connected to the database.

1. Can the MySQL / MariaDB security experts help us to find which MySQL user ran which query, through some log or even database insert before/after or update before/after triggers will help?

2. Is there any way to monitor MySQL processes to check which system process triggered the MySQL Daemon to execute the query, or in other words how the MySQL user connects to the database?

Answer 1

Out of the box, MySQL/MariaDB do not have the auditing that you need. There is an "Audit" plugin that you should add.

As preventative measures:

• If it was a hacker, then you need to tighten access to the PHP sources, Apache directories, etc. I often see a flood of Apache log entries with bots trying various login names/passwords.

• Each programmer/DBA needs to have their own login. See

• Each app needs its own login, and its passwords need to be in a secure place (that's a separate discussion).

• Most users and apps should be GRANTed access to only dbname.*, not *.*. Furthermore, the PRIVILEGES should be limited to what is needed, not ALL PRIVILEGES.

• Hide each user's password from others:

    # setup:
    mysql_config_editor set -G main_mysql -u Joe -p -h host.machine
    # (and enter password)
    # use (by Joe):
    mysql --login-path=main_mysql
  

• A limited number of users should have SUPER privilege.

• A replica needs only REPLICATION SLAVE Privilege. Those who need to see the state of a replica can be granted REPLICATION CLIENT. (This is probably quite safe.)

• Use Unix, not Windows; the latter is much more popular with hackers.

• "Bind" or "escape" all fill-ins that come from <form>s. Read about the evils and horrors of "SQL injection" -- this lets a hacker not only get to the database, but the entire filesystem.

Answer 2

The simplest way would be to use the enable the slow log rather than the general log.

• Mar 02, 2018 : mysql 5.7 general_log table has user_host but the file does not (Slow Log File)
• Dec 27, 2011 : How to do MySQL User Accounting (Slow Log Table)

Both the slow log file and table can record the user and host of a query that was executed.

You will have to set long_query_time to 0.0001 to catch virtually every query.

The only drawback would be the fact that the slow log only records completed queries. Long running queries that change things will not be recorded until they are done.