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)

Popular posts from this blog

Remote Desktop Protocol (RDP) Security

Image
Common Remote Desktop Protocol (RDP) Vulnerabilities Terminal Services Encryption Level is Medium or Low Microsoft Windows Remote Desktop Protocol Server Man-in-the-Middle Weakness Terminal Services Doesn't Use Network Level Authentication (NLA) Only Terminal Services Encryption Level is Medium or Low Vulnerability Assessment: Host Assessment: Remediation: Local Computer Policy/Computer Configuration/Administrative Templates/Windows Components/Remote Desktop Services/Remote Desktop Session Host/Security/Set client connection encryption level Set client connection encryption level to High Note: High: The High setting encrypts data sent from the client to the server and from the server to the client by using strong 128-bit encryption. Use this encryption level in environments that contain only 128-bit clients (for example, clients that run Remote Desktop Connection). Clients that do not support this encryption level cannot connect to RD Se
Read more

Penetration Testing - Network

Image
Manual Vulnerability Assessment TCP/21: FTP Anonymous FTP Enabled anonymous guest TCP/22: SSH nmap -p 22 --script ssh2-enum-algos <ip_address> SSH Weak Algorithms Supported SSH Server CBC Mode Ciphers Enabled ssh -oCiphers=<ciphers> <ip_address> SSH Weak MAC Algorithms Enabled ssh -oMACs=<algorithm> <ip_address> SSH Protocol v1 Supported ssh -1 <ip_address> -v Hardening on SSH Ciphers aes256-ctr,aes192-ctr,aes128-ctr MACs hmac-sha2-512-etm@openssh.com,hmac-sha2-256-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-512,hmac-sha2-256,umac-128@openssh.com TCP/23: Telnet Unencrypted Telnet Server telnet <ip_address> 23 TCP/25: SMTP SMTP Service Cleartext Login Permitted telnet <ip_address> 25 EHLO <ip_address> AUTH LOGIN Mailserver answer to VRFY and EXPN requests * nc <ip_address> 25 EXPN root VRFY root TCP/53: DNS DNS Server Cache Snooping Remote Information Disclosure
Read more

Damn Vulnerable Web Services (DVWS) - Walkthrough

Image
Installation Damn Vulnerable Web Services  (DVWS) is an insecure web application with multiple vulnerable web service components that can be used to learn real world web service vulnerabilities. https://github.com/snoopysecurity/dvws WSDL Enumeration Spider DVWS using Burp Suite and look for service.php Requests processed by SOAP service include  check_user_information ,  owasp_apitop10 ,  population  and  return_price XPATH Injection User Login: 1' or '1'='1 User Password: 1' or '1'='1 Command Injection Original Request parameter value of name is " find "   by default  Edited Request change the parameter value of  name  from "find" to " dir "   Cross Site Tracing (XST) Hint of " The NuSOAP Library service is vulnerable to a Cross-site scripting flaw " is given by DVWS. Exploit is published at exploit DB ( https://www.exploit-db.com/e
Read more

Offensive Security Testing Guide

Image
This cheat sheet is the compilation of commands we learnt to exploit the vulnerable machines. The commands below may not be enough for you to obtain your Offensive Security Certified Professional (OSCP). So... Try Harder! Information Gathering Operating System Windows Interesting Path "Documents and Settings"/Administrator/Desktop file:///C:/xampp/readme_en.txt file:///C:/xampp/passwords.txt file:///C:/xampp/webdav/webdav.txt file:///C:/xampp/apache/conf/extra/httpd-dav.conf file:///C:/xampp/apache/conf/extra/httpd-xampp.conf file:///C:/xampp/apache/logs/access.log file:///C:/xampp/apache/logs/error.log file:///C:/xampp/security/webdav.htpasswd file:///C:/xampp/htdocs/dashboard/phpinfo.php file:///C:/xampp/phpmyadmin/config.inc.php file:///C:/xampp/php/logs/php_error_log file:///C:/xampp/mysql/bin/my.ini C:\Users\<User>\AppData\Local\Temp #Email Address C:\Users\<User>\AppData\Local\Microsoft\Outlook Active Connection netstat -anob #R
Read more

Server Message Block (SMB) Security

Image
Common SMB related vulnerabilities Microsoft Windows SMBv1 Multiple Vulnerabilities SMB Signing Disabled Microsoft Windows SMB NULL Session Authentication Microsoft Windows SMB Shares Unprivileged Access Network Discovery: TCP port 5357 - Web Services on Devices API (WSDAPI) File and Printer Sharing: TCP port 135 - Remote Procedure Call (RPC) TCP port 139 - NETBIOS Session Service TCP port 445 - Server Message Block (SMB) By disable NetBIOS over TCP/IP (TCP Port 139), NETBIOS name discovery will be prevented Microsoft Windows SMBv1 Multiple Vulnerabilities Vulnerability Assessment: NSE script smb-protocols can be used to check if the server supported NT LM 0.12 (SMBv1) . Host Assessment: Get-ItemProperty HKLM:\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters | ForEach-Object {Get-ItemProperty $_.pspath} Remediation: Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters
Read more

Host Configuration Assessment - Windows

Image
OS Information Gathering systeminfo wmic computersystem get domainrole 0 - Standalone workstation 1 - Member workstation 2 - Standalone server 3 - Member server 4 - Domain controller secedit /export /cfg cfg.ini > nul net user administrator > netuseradmin.txt auditpol.exe /get /category:* > auditpol.txt netsh advfirewall show allprofiles > firewall.txt net accounts > netaccount.txt gpresult /f /h evid/gporesult.html > nul accesschk /accepteula -q -a * > accesschk.txt *Simplify the process with Scgary ! User Right Assignment type cfg.ini | grep "^SeAuditPrivilege\|^SeCreatePagefilePrivilege\|^SeRemoteShutdownPrivilege\|^SeRemoteInteractiveLogonRight\|^SeEnableDelegationPrivilege\|^SeLockMemoryPrivilege\|^SeDenyNetworkLogonRight\|^SeChangeNotifyPrivilege\|^SeDebugPrivilege\|^SeDenyBatchLogonRight\|^SeCreateGlobalPrivilege\|^SeShutdownPrivilege\|^SeIncreaseQuotaPrivilege\|^SeTrustedCredManAccessPrivilege\|^SeDenyInteractiveLogonRight
Read more