Web Server Hardening - MySQL

MySQL Security

In order to understand the possible attack can be done on MySQL, please read our write up for SQL Injection at http://www.axcelsec.com/2018/02/penetration-testing-with-owasp-top-10.html.

SHOW DATABASES;
USE MYSQL;
SELECT * FROM user;
SELECT Host,User,Password FROM user;

CIS Benchmark

3. File System Permissions

SHOW VARIABLES WHERE
variable_name = 'datadir' OR
variable_name = 'plugin_dir' OR  #Plugin Directory
variable_name LIKE 'log_bin_basename' OR
variable_name LIKE 'log_error' OR
variable_name LIKE 'slow_query_log_file' OR
variable_name LIKE 'relay_log_basename' OR 
variable_name LIKE 'general_log_file' OR
variable_name = 'ssl_key'; #SSL Key Files

---

4. General

SHOW VARIABLES WHERE variable_name LIKE "version";
SHOW VARIABLES LIKE 'have_symlink'; #Ensure the Value returned is DISABLED.
SHOW DATABASES LIKE 'test'; #Ensure that no rows are returned (Ensure the 'test' Database Is Not Installed)
SELECT * FROM information_schema.plugins WHERE PLUGIN_NAME='daemon_memcached';  #Ensure that no rows are returned
SHOW VARIABLES WHERE variable_name = 'secure_file_priv' AND Value<>''; #The Value should contain a valid path
SHOW VARIABLES WHERE variable_name = 'local_infile'; #Ensure the Value field is set to OFF
SHOW VARIABLES LIKE 'sql_mode'; #Ensure that STRICT_ALL_TABLES is in the list returned.

Ensure the 'test' Database Is Not Installed

SHOW DATABASES LIKE 'test';
DROP DATABASE "test"; #If the above SQL statement is not return zero rows

Improve MySQL Installation Security

mysql_secure_installation
#/usr/bin/mysql_secure_installation
#C:\xampp\mysql\bin\mysql_secure_installation.pl

• set a password for root accounts
• remove root accounts that are accessible from outside the local host
• remove anonymous-user accounts
• remove the test database

---

5. MySQL Permissions

To display the privileges and roles

mysql -e "SHOW GRANTS"
mysql -u root -e "SHOW GRANTS"

To check which accounts have access to what

SHOW GRANTS;
SHOW PRIVILEGES;

Ensure Only Administrative Users Have Full Database Access

SELECT user, host
FROM mysql.user
WHERE (Select_priv = 'Y')
OR (Insert_priv = 'Y')
OR (Update_priv = 'Y')
OR (Delete_priv = 'Y')
OR (Create_priv = 'Y')
OR (Drop_priv = 'Y');

SELECT user, host
FROM mysql.db
WHERE db = 'mysql'
AND ((Select_priv = 'Y')
OR (Insert_priv = 'Y')
OR (Update_priv = 'Y')
OR (Delete_priv = 'Y') 
OR (Create_priv = 'Y')
OR (Drop_priv = 'Y'));

Ensure Privileges Is Not Give To Non-Administrative Users

SELECT user, host, File_priv, Process_priv, Super_priv, Shutdown_priv, Create_user_priv, Grant_priv
from mysql.user 
where File_priv = 'Y'
OR Process_priv = 'Y'
OR Super_priv = 'Y' 
OR Shutdown_priv = 'Y'
OR Create_user_priv = 'Y' 
OR Grant_priv = 'Y';

SELECT user, host FROM mysql.db WHERE Grant_priv = 'Y';

Ensure Privileges Is Not Give To Non-Slave Users

SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';

Ensure DML/DDL Grants Are Limited to Specific Databases and Users

SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Alter_priv
FROM mysql.db
WHERE Select_priv='Y'
OR Insert_priv='Y'
OR Update_priv='Y'
OR Delete_priv='Y'
OR Create_priv='Y'
OR Drop_priv='Y'
OR Alter_priv='Y';

MySQL Permissions Hardening

REVOKE FILE ON *.* FROM '<user>';  #Disallow from reading and writing files on the server host
REVOKE PROCESS ON *.* FROM '<user>'; #Disable the ability view currently executing MySQL statements
REVOKE SUPER ON *.* FROM '<user>'; #Disable the ability to perform many actions, including view and terminate currently executing MySQL statements
REVOKE SHUTDOWN ON *.* FROM '<user>'; #Disable the ability to shut down the MySQL server
REVOKE CREATE USER ON *.* FROM '<user>'; #Disable the ability to add/drop users, alter existing users' names, and manipulate existing users' privileges
REVOKE GRANT OPTION ON *.* FROM '<user>'; #Disable the ability to grant other principals additional privileges

#Deny request updates that have been made on the master server
REVOKE REPLICATION SLAVE ON *.* FROM <user>; 

#Limiting the users with the rights to modify or create data structures
REVOKE SELECT ON <host>.<database> FROM <user>;
REVOKE INSERT ON <host>.<database> FROM <user>;
REVOKE UPDATE ON <host>.<database> FROM <user>;
REVOKE DELETE ON <host>.<database> FROM <user>;
REVOKE CREATE ON <host>.<database> FROM <user>;
REVOKE DROP ON <host>.<database> FROM <user>;
REVOKE ALTER ON <host>.<database> FROM <user>;

Reference: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

---

6. Auditing and Logging

SHOW VARIABLES WHERE
variable_name LIKE 'log_bin_basename' OR   #Ensure the value returned does not indicate root ('/'), /var, or /usr
variable_name LIKE 'log_error' OR   #Ensure the Value returned is not empty
variable_name LIKE 'log_error_verbosity';   #A value of 2 enables logging of error and warning messages

Auditing and Logging Hardening using my.ini (C:\xampp\mysql\bin\my.ini)

log_error = "mysql_error.log"  #Enabling error logging
log-raw = OFF  #To prevent password written to log files in plain text

User Statistics

set global userstat=ON;
SELECT user,total_connections,denied_connections,total_ssl_connections FROM information_schema.user_statistics;
SHOW USER_STATISTICS;

User Connection Errors

---

7. Authentication

SELECT @@global.sql_mode;  #Ensure result contains NO_AUTO_CREATE_USER
SELECT @@session.sql_mode; #Ensure result contains NO_AUTO_CREATE_USER

SELECT User,host FROM mysql.user WHERE authentication_string=''; #No rows will be returned if all accounts have a password set
SELECT user, host FROM mysql.user WHERE host = '%';  #Ensure no rows are returned (No Users Have Wildcard Hostnames)
SELECT user,host FROM mysql.user WHERE user = ''; #Ensure no rows are returned (No Anonymous Accounts Exist)

#SHOW VARIABLES LIKE 'default_password_lifetime'; #default_password_lifetime should be less than or equal to 90
#SHOW VARIABLES LIKE 'validate_password%';
## validate_password_length should be 14 or more
## validate_password_mixed_case_count should be 1 or more
## validate_password_number_count should be 1 or more
## validate_password_special_char_count should be 1 or more
## validate_password_policy should be MEDIUM or STRONG

MySQL Native Password Hashing

SELECT PASSWORD('test');
SELECT CONCAT('*', UPPER(SHA1(UNHEX(SHA1('test')))));

echo -n "test" | sha1sum | cut -c1-40 | xxd -p -r | sha1sum | cut -c1-40 | tr '[a-z]' '[A-Z]'

Anonymous Account

mysql -e "SELECT version(),user(),current_user()"

#Exploitation by Anonymous Account
SHOW SCHEMAS;
SELECT table_schema, table_name FROM information_schema.tables;

USE test;
CREATE TABLE t1(i1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(100) NOT NULL);
INSERT INTO t1(i1, v1) VALUES (1, REPEAT('abcde',20)); #abcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcde
INSERT INTO t1(i1, v1) SELECT NULL, a.v1 FROM t1 a, t1 b, t1 c;

Query OK, 8 rows affected (0.13 sec)

Brute-Force Login

msf > use auxiliary/scanner/mysql/mysql_login
msf > use auxiliary/admin/mysql/mysql_enum
mysqldump --single-transaction --host=192.168.24.2 -u test -p dvwa > dvwa.sql

Authentication Hardening

#To assign a password to a MySQL user account
SET PASSWORD FOR '<user>'@'<host>' = '<clear password>' #'
GRANT USAGE ON *.* TO '<user>'@'<host>' IDENTIFIED BY '<clear password>';#If above statement return "ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number"

DELETE FROM mysql.user WHERE user=''; #Removing anonymous accounts
DROP USER '<user>'@'<host>' #removes one or more MySQL accounts and their privileges.

SET GLOBAL default_password_lifetime=90

Pluggable Authentication

SHOW PLUGINS;
SELECT PLUGIN_NAME FROM PLUGINS WHERE PLUGIN_TYPE='AUTHENTICATION';

# Maria DB
# https://mariadb.com/kb/en/library/password-authentication-and-encryption-plugins/

To specify how the server should listen for TCP/IP connections

bind-address="127.0.0.1" #Uncomment the statement in my.ini

---

8. Network

CIS Assessment

SHOW variables WHERE variable_name = 'have_ssl';  #Ensure the Value returned is YES
SELECT user, host, ssl_type FROM mysql.user WHERE NOT HOST IN ('::1', '127.0.0.1', 'localhost'); #Ensure the ssl_type for each user returned is equal to ANY, X509, or SPECIFIED

SSL System Variables

SHOW VARIABLES LIKE '%ssl%';
SHOW SESSION STATUS LIKE 'Ssl_version';
SHOW SESSION STATUS LIKE 'Ssl_cipher';

Network Hardening

GRANT USAGE ON *.* TO 'my_user'@'app1.example.com' REQUIRE SSL;

---

9. Replication

CIS Assessment

select ssl_verify_server_cert from mysql.slave_master_info; #Verify the value of ssl_verify_server_cert is 1.
SHOW GLOBAL VARIABLES LIKE 'master_info_repository'; #The result should be TABLE instead of FILE.
select user, host from mysql.user where user='repl' and Super_priv = 'Y'; #No rows should be returned (Limiting replication Users to have the SUPER privilege)
SELECT user, host FROM mysql.user WHERE user='repl' AND host = '%'; # Ensure no rows are returned (Ensure No Replication Users Have Wildcard Hostnames)

---

Security News

2018
Hacker Fail: IoT botnet command and control server accessible via default credentials
2018 National Exposure Index Research Report (Rapid 7)

2017
Ransomware attacks targeted hundreds of MySQL databases

---

Reference

Implementing MySQL Security Features (Ronald Bradford, Colin Charles)