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 rolesmysql -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 AssessmentSHOW 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 Assessmentselect 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
2018Hacker 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