# Sending the Output of the Honeypot to a MySQL Database

- [Sending the Output of the Honeypot to a MySQL Database](#sending-the-output-of-the-honeypot-to-a-mysql-database)
  - [Prerequisites](#prerequisites)
  - [Installation](#installation)
  - [MySQL Configuration](#mysql-configuration)
  - [Honeypot Configuration](#honeypot-configuration)
  - [Restart the honeypot](#restart-the-honeypot)

## Prerequisites

- Working honeypot installation
- MySQL Server installation

## Installation

When writing to a MySQL database, the honeypot uses the free databases provided
by MaxMind for the purposes of geoloacting the IP addresses. Start by
downloading the database update program for your particular kind of Windows from
[GitHub](https://github.com/maxmind/geoipupdate/releases) and put it in a
directory listed in the `PATH` variable of the environment.

Create an account at the [MaxMind web
site](https://support.maxmind.com/knowledge-base/articles/create-a-maxmind-account),
log in, go to "My Account" and then to "Manage license keys". Write down the
account ID, generate a license key, and copy it.

Go to the directory `data`, where the gelolocation databases will reside:

```powershell
PS C:\> cd \adbhoneypot-workdir\data
```

Create in this directory a file named `geoip.cfg` with the following contents:

```geoip.cfg
AccountID <ACCOUNT>
LicenseKey <KEY>
EditionIDs GeoLite2-City GeoLite2-ASN
DatabaseDirectory C:\adbhoneypot-workdir\data
LockFile C:\adbhoneypot-workdir\data\.geoipupdate.lock
```

Change the paths in the options `DatabaseDirectory` and `LockFile` if you
have opted to use paths different from the ones suggested by the
honeypot installation documentation. Make sure you replace `<ACCOUNT>`
and `<KEY>` with the account and license key obtained from MaxMind.

Download the latest version of the Maxmind geolocation databases:

```powershell
PS C:\adbhoneypot-workdir\data> geoipupdate -f geoip.cfg
```

To have the database updated automatically (it is updated on MaxMind's site
every second Tuesday of each month, so download it every second Wednesday), log
in as Administrator (or launch an elevated PowerShell window, if the user
`HoneyPotter` is allowed to do it) and run the script `geoipupdtask.ps1` in the
working directory:

```powershell
PS C:\adbhoneypot-workdir\data> ..\geoipupdtask.ps1 
```

It expects that the program `geoipupdate.exe` resides in one of the directories
listed in the `PATH` variable of the environment, that the configuration file
for it is named `geoip.cfg` and resides in the current directory, and that the
updating task is to be run at 00:00. Also, it creates an updating task named
`GeoIPUpdate`, which resides in the task folder `\` and has the description
`GeoIP database updater`. You can change any of these parameters via
command-line options to the script:

```powershell
PS C:\adbhoneypot-workdir\data> ..\geoipupdtask.ps1 -TaskName "My GeoIP Database Updater" -TaskPath "\MyTasks" -TaskDescription "Updates the GeoIP database" -RunTime "03:00:00" -geoipupdate "C:\Program File\geoipupdate\geoipupdate.exe" -geoipconfig "C:\adbhoneypot-workdir\data\geoip.cfg"
```

If you already have the MaxMind geolocation databases installed and updated on
your machine in some other place, use their respective paths in the
`[output_mysql]` section of the file `honeypot.cfg`, as mentioned below.

## MySQL Configuration

First create a database named `adbhoneypot` and grant access to it to a user
named `adbhoneypot`:

```powershell
PS C:\> mysql -p -u root
MySQL> CREATE DATABASE IF NOT EXISTS adbhoneypot;
MySQL> CREATE USER IF NOT EXISTS 'adbhoneypot'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD HERE' PASSWORD EXPIRE NEVER;
MySQL> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON adbhoneypot.* TO 'adbhoneypot'@'localhost';
```

(Make sure you specify a proper password that you want to use for the user
`adbhoneypot` instead of 'PASSWORD HERE'.)

If you're going to use a third-party tool for accessing the data from the
database (e.g., [Grafana](https://www.grafana.com) for visualizing the data),
it is advisable also to create a separate user that has read-only privileges
to the database and have the third-party tool access the database as that
user, so that in case the third-party tool contains some kind of vulnerability
and is breached (and the attacker obtains the database user password from it),
the attacker cannot modify the database:

```mysql
MySQL> CREATE USER IF NOT EXISTS 'adbhoneypotReadOnly'@'localhost' IDENTIFIED WITH mysql_native_password BY 'OTHER PASSWORD HERE' PASSWORD EXPIRE NEVER;
MySQL> GRANT SELECT ON adbhoneypot.* TO 'adbhoneypotReadOnly'@'localhost';
```

(Make sure you specify a proper password that you want to use for the user
`adbhoneypotReadOnly` instead of 'OTHER PASSWORD HERE'.)

Finally, make sure that the user-related changes are committed to the database:

```mysql
MySQL> FLUSH PRIVILEGES;
MySQL> exit
```

Next, load the database schema:

```powershell
PS C:\> cd \adbhoneypot-workdir
PS C:\adbhoneypot-workdir> mysql -p -u adbhoneypot adbhoneypot
MySQL> source ./docs/mysql/mysql.sql;
MySQL> exit
```

## Honeypot Configuration

Add the following entries to the file `C:\adbhoneypot\etc\honeypot.cfg`

```honeypot.cfg
[output_mysql]
enabled = true
host = localhost
database = adbhoneypot
username = adbhoneypot
password = PASSWORD HERE
port = 3306
# Whether to store geolocation data in the database
geoip = true
# Location of the databases used for geolocation
geoip_citydb = data/GeoLite2-City.mmdb
geoip_asndb = data/GeoLite2-ASN.mmdb
```

Make sure you use the password you specified for the MySQL user `adbhoneypot`
instead of 'PASSWORD HERE'. Make sure the options `geoip_citydb` and
`geoip_asndb` point to the correct paths of the two MaxMind geolocation
databases.

## Restart the honeypot

```powershell
PS C:\adbhoneypot-workdir> C:\adbhoneypot-env\scripts\activate.ps1
(adbhoneypot-env) PS C:\adbhoneypot-workdir> adbhoneypot restart
```
