How to Protect Applications using MySQL 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 & handle your data.

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 & handle your data.

Deploy Sample PHP/MySQL Web application

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 used the GKE environment for the application deployment.

[Step #1] Clone GitHub Repo:

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

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

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 requests a service that checks where it should forward the request. You will access the IP and port defined in the service configuration file to access the webserver.

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

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

Check the status of the service:

kubectl get svc

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

kubectl apply -f https://raw.githubusercontent.com/accuknox/microservices-demo/main/php-mysql-webapp/mysql-pv-claim.yaml

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

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

Check the status of the pod and service.

kubectl get po,svc

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.

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 mysql command prompt.

mysql -u root -p.sweetpwd.

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

SHOW DATABASES;
USE my_db;
CREATE TABLE users
(
name varchar(20)
);
INSERT INTO users (name)
VALUES ('John');

Now check the external IP of the web service. If everything goes fine, then this screen should be seen.

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.

my.cnf

The default configuration file is called my.cnf and can be located in several 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

my-new.cnf

This file is created when there is an existing my.cnf file and the mysql_install_db script is run. 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.

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

Backups

The main two options are to copy the database files or use mysqldump.

File copy

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

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:

$ mysqldump -u root -p demodb > dbbackup.sql

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, You can audit/restrict all these sensitive configuration files and processes that use these files. If you know the locations of files and processes you can create policy at ease.

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 run-time.
# 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: ["STIGS", "MYSQL"]
  message: "Alert! mysql configuration files access has been accessed"
  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):

kubectl apply -f https://raw.githubusercontent.com/accuknox/microservices-demo/main/php-mysql-webapp/ksp-audit-mysql-config-files.yaml

Try to use mysqldump command:

kubectl exec -it mysql-7d9977c67d-7rcmb -- bash
[email protected]:/# mysqldump -u db_user -p .mypwd my_db users > dumpfilename.sql

Kubearmor logs:

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

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

Kubearmor detects this event, and we will receive logs like this:

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.

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 network 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 labelled 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.

Conclusion

Auto-discovered policies are generated based on the network flow of the sample application. This will restrict all unwanted connections and reduce the application's attack surface. It allows only minimum traffic that the application needs to operate.

AccuKnox provides out-of-the-box recommended policies to secure the cloud environment. The policy-templates open-source repository provides policy templates based on KubeArmor and Cilium for known CVEs, attack vectors, compliance frameworks (such as MITRE, PCI-DSS, STIG, etc.) and popular workloads. You can also handcraft your security policies to secure your MySQL cluster.

Subscribe to Accuknox Docs

Don’t miss out on the latest issues. Sign up now to get access to the library of members-only issues.
[email protected]
Subscribe