1

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?

2 Answers 2

3

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.

2
  • Thanks a lot for your expert advice. In our case only MySQL root user has access to all the databases with all the previleges. I think they might be using the root user to change the data, but still not confirmed.
    – Haijerome
    Commented Feb 2, 2023 at 16:03
  • Change the password on the grounds that it may have been discovered by someone else. And/or give each person who has "root" his own SYSTEM USER login (root1, root2 or whatever) That will make it easier to track and/or shut down when the villain is found.
    – Rick James
    Commented Feb 15, 2023 at 16:00
1

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

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.

1
  • Thanks a lot for your expert opinion. Yes we'll enable the slow log as you have mentioned in the other threads and get more information of the query executioner (user) and host. BTW if we enable Binary logs (which logs only the data manipulation queries) , can we get the required information.
    – Haijerome
    Commented Feb 2, 2023 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.