Skip to content

How to protect MySQL application with AccuKnox

Database Management is an important part when you have a large amount of data around you. MySQL is one of the most famous open-source Relational Databases to store and handle your data. So securing the data is the main concern for any organization.

AccuKnox provides runtime cloud security for your applications. In this cookbook, we will demonstrate how MySQL applications can be protected.

Prerequisites

Install open-source AccuKnox tools to your cloud environment.

Run the following script to install Daemon sets and Services

curl -s https://raw.githubusercontent.com/accuknox/tools/main/install.sh | bash 

For more details, check this help page
or
Use the enterprise tier of the AccuKnox product

Deploy Sample PHP/MySQL Web application in Kubernetes

We will create two deployments; one for the webserver and the other for MySQL DB. The web server will read data from MySQL DB and show it in the browser.

Here we have used the GKE environment.

[Step 1] Clone GitHub Repo

git clone https://github.com/accuknox/samples.git
cd samples/php-mysql-webapp

[Step 2] Deploy Web server

kubectl apply -f https://raw.githubusercontent.com/accuknox/samples/main/php-mysql-webapp/webserver.yaml

Run kubectl get pods in the terminal to get the response:

You should be able to see the output like this:

NAME                         READY   STATUS    RESTARTS   AGE
webserver-55f99f9ffb-f4rvk   1/1     Running   0          2d3h

Alright, the pod is created but we can’t access it despite having its IP, the reason is that the Pod IP is not public. So we use service. When a user tries to access an app, for instance, a web server here, it actually makes a request to a service which itself then checks where it should forward the request. Now to access the webserver you will just access the IP and port as defined in the service configuration file.

[Step 3] Now let's deploy web-service

kubectl apply -f https://raw.githubusercontent.com/accuknox/samples/main/php-mysql-webapp/webserver-svc.yaml

Check the status of the service

kubectl get svc

You should be able to see the output like this

NAME             TYPE           CLUSTER-IP     EXTERNAL-IP      PORT(S)        AGE
kubernetes       ClusterIP      10.16.0.1      <none>           443/TCP        27d
web-service      LoadBalancer   10.16.9.151    35.193.121.214   80:31533/TCP   2d3h

[Step 4] Create the persistent volume claim to keep your data intact.

[Step 5] Create deployment and service for MySQL DB.

kubectl apply -f https://raw.githubusercontent.com/accuknox/samples/main/php-mysql-webapp/mysql.yaml    

Check the status of the pod and service

kubectl get po,svc  

You should be able to see the output like this

NAME                             READY   STATUS    RESTARTS   AGE
pod/mysql-796674bfb-dl495        1/1     Running   0          115s
pod/webserver-5f7dbd89d6-5ng7r   1/1     Running   0          19m
pod/webserver-5f7dbd89d6-hnrz9   1/1     Running   0          19m
pod/webserver-5f7dbd89d6-pmw4s   1/1     Running   0          19m

NAME                     TYPE           CLUSTER-IP   EXTERNAL-IP    PORT(S)        AGE
service/kubernetes       ClusterIP      10.8.0.1     <none>         443/TCP        32m
service/mysql8-service   ClusterIP      10.8.1.249   <none>         3306/TCP       27s
service/web-service      LoadBalancer   10.8.0.92    34.70.234.72   80:32209/TCP   14m

Now the application is deployed. You can insert data into the database in two ways. You can use a MySQL Client or directly execute to the MySQL server pod.

Connect using a MySQL client:

kubectl run -it --rm --image=mysql:5.6 --restart=Never mysql-client -- mysql -h mysql8-service -p.sweetpwd.

You should be able to see the output like this

$ kubectl run -it --rm --image=mysql:5.6 --restart=Never mysql-client -- mysql -h mysql8-service -p.sweetpwd.
If you don't see a command prompt, try pressing enter.

mysql>

Directly executing into MySQL pod:

kubectl exec -it mysql-796674bfb-dl495 -- bash

Note: Replace mysql-796674bfb-dl495 with your mysql pod name.

Now you are inside MySQL pod. Use the below command to enter the MySQL command prompt.

mysql -u root -p.sweetpwd.

You should be able to see the output like this

$ kubectl exec -it mysql-69559dfd5d-nzmcd -- bash
[email protected]:/# mysql -u root -p.sweetpwd.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Now you are inside the MySQL terminal. First, you need to create a users table and add values to the table.

Use the below commands to do that.

SHOW DATABASES;

You should be able to see the output like this

USE my_db;

You should be able to see the output like this

CREATE TABLE users
(
name varchar(20)
);

You should be able to see the output like this

INSERT INTO users (name)
VALUES ('John');

Now check the external IP of the web service. If everything works well, you'll see this screen with the name John.

Working with Open-Source AccuKnox tools

The policy-templates open-source repository provides policy templates based on KubeArmor and Cilium policies for known CVEs and attacks vectors, compliance frameworks such as PCI-DSS, MITRE, STIG, NIST, CIS, etc., popular workloads such as GoLang, Python, PostgreSQL, Cassandra, MySQL, WordPress, etc.

We hope that you also contribute by sending policy templates via pull requests or Github issues to grow the list.

Go to GitHub repository: https://github.com/kubearmor/policy-templates

AccuKnox provides a number of policy templates for your MySQL workloads.

Let's see a policy from the policy templates repo.

Audit your MySQL Server Sensitive Configuration files with KubeArmor

MySQL Server, also known as mysqld, is a single multithreaded program that does most of the work in a MySQL installation. It does not spawn additional processes. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.

(i) my.cnf:

The default configuration file is called my.cnf and can be located in a number of directories. On Linux and other Unix related platforms, the locations are using /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf or in the default installation directory. This file contains configuration settings that will be loaded when the server starts, including settings for the clients, server, mysqld_safe wrapper and various other MySQL client programs.

However, if they're not there, you can use mysqld to find the configuration. Run the following command inside the MySQL server pod.

mysqld --help --verbose

The first part of the lengthy response describes the options you can send to the server when you launch it. The second part displays the configuration settings during the server compilation.

Near the start of the output, find a couple of lines that look similar to the following example:

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
The following groups are read: mysqld server mysqld-8.0

(ii) my-new.cnf

This file is created when there is an existing my.cnf file and the mysql_install_db script is running. The mysql_install_db script is designed to develop the my.cnf file if it does not exist. If the file does exist, then the file is created using the name my-new.cnf to avoid overwriting an existing configuration file. It is then up to the user to compare the two, determine files and determine which options are still valid, for the new install and change the files as required to get the new my.cnf configuration file.

(iii) Log files

By default, MySQL stores its log files in the following directory:

/var/log/mysql

Check the MySQL configuration if you don't find the MySQL logs in the default directory. View the my.cnf file and look for a log_error line, as in:

log_error = /var/log/mysql/error.log

(iv) Backups

The two main options are to copy the database files or use mysqldump as follows:

File copy

By default, MySQL creates a directory for each database in its data directory, /var/lib/mysql.

Note: Ensure you set the permissions on that file to restrict read access for password-security reasons.

mysqldump

Another approach to backing up your database is to use the mysqldump tool. Rather than copying the database files directly, mysqldump generates a text file that represents the database. By default, the text file contains a list of SQL statements to recreate the database, but you can also export the database in another format like .CSV or .XML. You can read the man page for mysqldump to see all its options.

The statements generated by mysqldump go straight to standard output. You can specify a to redirect the output by running the following command in the command line:

This command tells mysqldump to recreate the demodb database in SQL statements and to write them to the file dbbackup.sql. Note that the username and password options function the same as the MySQL client to include the password directly after -p in a script.

With the help of KubeArmor and Policy-templates, You can audit/restrict all these sensitive configuration files and processes that use these files easily.

Following KubeArmor policy will Audit configuration files and block mysqldump command.

# KubeArmor is an open source software that enables you to protect your cloud workload at runtime.
# To learn more about KubeArmor visit: 
# https://www.accuknox.com/kubearmor/ 

apiVersion: security.kubearmor.com/v1
kind: KubeArmorPolicy
metadata:
  name: ksp-restrict-access-mysql-server-config
  namespace: default # Change your namespace
spec:
  tags: ["MYSQL", "config-files", "mysqldump"]
  message: "Alert! mysql configuration files has been accessed and/or mysqldump command is has been used."
  selector:
    matchLabels:
      app: mysql8 # Change your labels
  file:
    severity: 5
    matchPaths:
    - path: /etc/mysql/my.cnf
      ownerOnly: true
    matchDirectories:
    - dir: /etc/mysql/
      recursive: true
      ownerOnly: true
    - dir: /var/lib/mysql/
      readOnly: true
      recursive: true
    - dir: /var/log/mysql/
      recursive: true
    action: Audit
  process:
    severity: 10
    matchPaths:
    - path: /usr/bin/mysqldump
    action: Block 

Apply KubeArmor Security Policy (KSP) from the Policy templates and perform following steps:

  1. Go to Policy templates GitHub Repository.
  2. Check MySQL folder then copy the URL of the policy raw file.
  3. Apply it using kubectl apply command.
kubectl apply -f https://raw.githubusercontent.com/kubearmor/policy-templates/main/MySQL/system/ksp-restrict-access-mysql-server-config-files.yaml
  1. Use mysqldump command:
kubectl exec -it mysql-69559dfd5d-nzmcd -- bash
[email protected]:/# mysqldump -u db_user -p .mypwd my_db users > dumpfilename.sql

Note: Replace mysql-69559dfd5d-nzmcd with your mysql pod name.

  1. You should be able to see the output like this
[email protected]:/# mysqldump -u db_user -p .mypwd my_db users > dumpfilename.sql
bash: /usr/bin/mysqldump: Permission denied
[email protected]:/# 

View KubeArmor logs:

a. Enable port-forwarding for KubeArmor relay

kubectl port-forward -n kube-system svc/kubearmor 32767:32767

b. Observing logs using karmor CLI

karmor log

You should be able to see the output like this

gRPC server: localhost:32767
Created a gRPC client (localhost:32767)
Checked the liveness of the gRPC server
Started to watch alerts
== Alert / 2022-02-08 03:10:05.867619 ==
Cluster Name: default
Host Name: gke-cys-feb8-default-pool-4852bc33-rmcr
Namespace Name: default
Pod Name: mysql-69559dfd5d-nzmcd
Container ID: 4dd61ec15b1f8075b8ac9ebe2aeed413c01e57af7d4e9bec7cff82b65f761677
Container Name: mysql
Severity: ksp-restrict-access-mysql-server-config
Tags: 10
Message: MYSQL,config-files,mysqldump
Type: Alert! mysql configuration files has been accessed and/or mysqldump command is has been used.
Source: MatchedPolicy
Operation: /bin/bash
Resource: Process
Data: /usr/bin/mysqldump -u db_user -p .mypwd my_db users
Action: syscall=SYS_EXECVE
Result: Block

Accessing /etc/mysql/my.cnf config. file;

[email protected]:/# cat /etc/mysql/my.cnf 

KubeArmor detects this event and you will receive logs like this: Check karmor log

== Alert / 2022-02-08 03:12:40.413064 ==
Cluster Name: default
Host Name: gke-cys-feb8-default-pool-4852bc33-rmcr
Namespace Name: default
Pod Name: mysql-69559dfd5d-nzmcd
Container ID: 4dd61ec15b1f8075b8ac9ebe2aeed413c01e57af7d4e9bec7cff82b65f761677
Container Name: mysql
Severity: ksp-restrict-access-mysql-server-config
Tags: 5
Message: MYSQL,config-files,mysqldump
Type: Alert! mysql configuration files has been accessed and/or mysqldump command is has been used.
Source: MatchedPolicy
Operation: /bin/cat /etc/mysql/my.cnf
Resource: File
Data: /etc/mysql/my.cnf
Action: syscall=SYS_OPENAT fd=-100 flags=/etc/mysql/my.cnf
Result: Audit

Securing configuration files are a necessity for any application. With KubeArmor you can effectively do that and with the options like readOnly , ownerOnly , recursive, matchDirectoriesetc you can fine-tune the policy enforcement. See more KubeArmor Policy Specification

Protect Using Auto Discovered Policies

AccuKnox policy auto-discovery engine leverages the pod visibility provided by KubeArmor and Cilium to auto-generate network and system policies.

Select Policy Manager -> Auto Discovered Policies

We deployed our sample application on the default namespace. Check default namespace for policies

Following are the auto-discovered policies generated by AccuKnox. Let's briefly explain the policies.

apiVersion: cilium.io/v2
kind: CiliumNetworkPolicy
metadata:
  name: autopol-ingress-anvyjxzgmiqcyws
spec:
  description: Auto Discovered Policy
  endpointSelector:
    matchLabels:
      app: mysql8
  ingress:
  - fromEndpoints:
    - matchLabels:
        app: apache
        k8s:io.kubernetes.pod.namespace: default
    toPorts:
    - ports:
      - port: "3306"
        protocol: TCP

This policy will be enforced at the ingress (against the inbound network flows) of the MySQL pod (pods labeled with app: mysql8 will be picked).

This enables endpoints with the label app: apache and k8s:io.kubernetes.pod.namespace: default to communicate with all endpoints with the label app: mysql8, but they must share using TCP on port 3306.

Endpoints with other labels will not communicate with the MySQL pod.

apiVersion: cilium.io/v2
kind: CiliumNetworkPolicy
metadata:
  name: autopol-egress-nqfbfchvptougbs
spec:
  description: Auto Discovered Policy
  endpointSelector:
    matchLabels:
      app: apache
  egress:
  - toEndpoints:
    - matchLabels:
        app: mysql8
        k8s:io.kubernetes.pod.namespace: default
    toPorts:
    - ports:
      - port: "3306"
        protocol: TCP

This policy is very similar to the first policy. This will be enforced at the webserver pod's egress (against the outbound network flows) (pods labelled with the app: apache will be picked).

apiVersion: cilium.io/v2
kind: CiliumNetworkPolicy
metadata:
  name: autopol-egress-xkvuxpmcxgtpacv
spec:
  description: Auto Discovered Policy
  endpointSelector:
    matchLabels:
      app: apache
  egress:
  - toEndpoints:
    - matchLabels:
        k8s-app: kube-dns
        k8s:io.kubernetes.pod.namespace: kube-system
    toPorts:
    - ports:
      - port: "53"
        protocol: UDP

This one is the DNS policy for the webserver pod.

All these policies are generated based on the network flow of the sample application.

It is allowing only minimum traffic that the application needed to operate. This will restrict all unwanted connections and reduce the application's attack surface.

Conclusion

Auto-discovered policies are generated based on the network flow of the application.

It is allowing only minimum traffic that the application needed to operate. This will restrict all unwanted connections and provide runtime security. You can also handcraft your own security policies to secure your MySQL cluster.

KubeArmor Slack: Join the KubeArmor community on Slack!

Now you can protect your workloads in minutes using AccuKnox, it is available to protect your Kubernetes and other cloud workloads using Kernel Native Primitives such as AppArmor, SELinux, and eBPF.

Let us know if you are seeking additional guidance in planning your cloud security program.

Back to top