-- MySQL: Enable SSL/TLS for database connections
# 1. Generate SSL certificates
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# 2. Configure MySQL to use SSL
[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

# 3. Connect using SSL
mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

-- PostgreSQL: Enable SSL/TLS for database connections
# 1. Generate SSL certificates
openssl req -new -text -out server.req
openssl rsa -in privkey.pem -modulus -noout -out modulus
openssl req -x509 -in server.req -text -key privkey.pem -out server.crt
chmod og-rwx privkey.pem

# 2. Configure PostgreSQL to use SSL
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'privkey.pem'

# 3. Connect using SSL
psql "host=localhost user=postgres sslmode=require"

-- MySQL: Configure certificate-based authentication
# 1. Generate client certificates
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# 2. Configure MySQL to use certificate-based authentication
[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

[client]
ssl-ca=ca.pem
ssl-cert=client-cert.pem
ssl-key=client-key.pem

-- PostgreSQL: Configure certificate-based authentication
# 1. Generate client certificates
openssl req -new -text -out client.req
openssl rsa -in privkey.pem -modulus -noout -out modulus
openssl req -x509 -in client.req -text -key privkey.pem -out client.crt

# 2. Configure PostgreSQL to use certificate-based authentication
hostssl all all 0.0.0.0/0 cert clientcert=1

-- MySQL: Restrict access to specific IP addresses/networks
CREATE USER 'game_server'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON fivem.* TO 'game_server'@'192.168.1.100';

-- MySQL: Create roles and assign permissions
CREATE ROLE fivem_read_only, fivem_data_entry, fivem_manager;

GRANT SELECT ON fivem.* TO fivem_read_only;
GRANT INSERT ON fivem.player_data TO fivem_data_entry;
GRANT INSERT, UPDATE, DELETE ON fivem.* TO fivem_manager;

CREATE USER 'game_viewer'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';
GRANT fivem_read_only TO 'game_viewer'@'192.168.1.100';

-- PostgreSQL: Create roles and assign permissions
CREATE ROLE fivem_read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA fivem TO fivem_read_only;

CREATE ROLE fivem_data_entry;
GRANT INSERT ON fivem.player_data TO fivem_data_entry;

CREATE ROLE fivem_manager;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA fivem TO fivem_manager;

CREATE USER game_viewer WITH PASSWORD 'StrongPassword123!';
GRANT fivem_read_only TO game_viewer;

-- MySQL: Disable unnecessary components during installation
# For MySQL 8.0, add the following options during installation:
mysqld=--skip-profiling,--skip-perfschema

# For existing installations, you can disable components in the my.cnf file:
skip-perfschema
skip-profiling

# PostgreSQL: Disable unnecessary components during installation
# Add the following options to the postgresql.conf file:
shared_preload_libraries = '' # Disables all preloaded libraries

-- MySQL: Enable and configure the built-in firewall
# Enable the firewall
INSTALL SONAME 'MYSQLX_FIREWALL';

# Create a whitelist for allowed IP addresses
MYSQLX_FIREWALL_INSTALL(
    'WHITELIST_INET',
    'WHITELIST_USERS',
    'client_ip=192.168.1.0/24,127.0.0.1, user=fivem_viewer,fivem_entry,fivem_admin'
);

# Start the firewall
MYSQLX_FIREWALL_ACTIVATE();

-- PostgreSQL: Enable and configure the built-in firewall (pg_hba.conf)
# Allow connections from specific IP addresses
host    all             all             192.168.1.0/24            md5
host    all             all             127.0.0.1/32               md5

# Deny all other connections
host    all             all             0.0.0.0/0                 reject

-- Create users with strong passwords and assign roles
CREATE USER 'fivem_viewer'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT fivem_read_only TO 'fivem_viewer'@'localhost';

CREATE USER 'fivem_entry'@'localhost' IDENTIFIED BY 'AnotherStrongPass!';
GRANT fivem_data_entry TO 'fivem_entry'@'localhost';

CREATE USER 'fivem_admin'@'localhost' IDENTIFIED BY 'SuperSecurePass123!';
GRANT fivem_manager TO 'fivem_admin'@'localhost';

-- MySQL: Enable and configure audit logging
INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging=ON;
SET GLOBAL server_audit_file_rotate_size=1000000; # Rotate log files at 1MB
SET GLOBAL server_audit_file_rotate_max_retained_files=10; # Keep 10 log files

-- Configure log events to capture
SET GLOBAL server_audit_events='CONNECT,QUERY';

-- PostgreSQL: Enable and configure logging
# Edit the postgresql.conf file
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 100000 # Rotate log files at 100MB

-- MySQL: Enable and configure audit logging
INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging=ON;
SET GLOBAL server_audit_file_rotate_size=1000000; # Rotate log files at 1MB
SET GLOBAL server_audit_file_rotate_max_retained_files=10; # Keep 10 log files

-- Configure log events to capture
SET GLOBAL server_audit_events='CONNECT,QUERY';

-- PostgreSQL: Enable and configure logging
# Edit the postgresql.conf file
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 100000 # Rotate log files at 100MB

# Install and configure an IDS/IPS solution like Snort or Suricata
# Example for Snort on Ubuntu
sudo apt-get install snort

# Configure Snort to monitor database traffic
# Edit the snort.conf file
ipvar HOME_NET 192.168.1.0/24
ipvar EXTERNAL_NET !$HOME_NET

# Add rules to detect potential threats
include $RULE_PATH/mysql.rules
include $RULE_PATH/postgresql.rules
include $RULE_PATH/sql-injection.rules

# Start Snort in IDS mode
sudo snort -A console -q -u snort -g snort -c /etc/snort/snort.conf -i eth0

-- MySQL: Set up alerts for failed login attempts and SQL injection
DELIMITER $$
CREATE TRIGGER failed_login_attempts_trigger
AFTER INSERT ON mysql.general_log
FOR EACH ROW
BEGIN
    IF NEW.argument LIKE 'ACCESS DENIED%' THEN
        INSERT INTO failed_login_attempts (user, host, timestamp)
        VALUES (SUBSTRING_INDEX(NEW.argument, '@', 1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.argument, '@', -1), ']', 1),
                NEW.event_time);
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER sql_injection_attempts_trigger
AFTER INSERT ON mysql.general_log
FOR EACH ROW
BEGIN
    IF NEW.argument RLIKE '(^(\\\\\\\'|\\\'|\\%27|\')|\\b(union|select|insert|update|delete)\\b.*(\\b(from|into)\\b.*(\\b(information_schema|mysql|sys|data)\\b|\\bconcaten\\(|\\bchar\\())|\\b(outfile|load_file|into|dumpfile))' THEN
        INSERT INTO sql_injection_attempts (user, host, query, timestamp)
        VALUES (SUBSTRING_INDEX(NEW.argument, '@', 1),
                SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.argument, '@', -1), ']', 1),
                NEW.argument,
                NEW.event_time);
    END IF;
END$$
DELIMITER ;

-- PostgreSQL: Set up alerts for failed login attempts and SQL injection
CREATE EXTENSION IF NOT EXISTS log_fdw;

CREATE SERVER log_server
FOREIGN DATA WRAPPER log_fdw
OPTIONS (filename '/var/log/postgresql/postgresql-%Y-%m-%d_%H%M%S.log');

CREATE TABLE failed_login_attempts (
    username TEXT,
    client_addr TEXT,
    timestamp TIMESTAMP
);

CREATE RULE failed_login_alert AS
ON INSERT TO failed_login_attempts
WHERE NEW.username IS NOT NULL
DO NOTIFY failed_login_attempt,
           E'Username: ' || NEW.username || E'\nClient Address: ' || NEW.client_addr || E'\nTimestamp: ' || NEW.timestamp;

CREATE VIEW failed_logins WITH (security_barrier) AS
SELECT
    split_part(message, ' ', 10) AS username,
    split_part(message, ' ', 11) AS client_addr,
    timestamp
FROM pg_log.postgresql_log
WHERE message LIKE 'FATAL%password authentication failed for user%';

# Analyze MySQL audit logs
sudo mysqlauditgrep --query-log=/var/log/mysql/audit.log --query-pam-ksok --query-has-comment-augment --query-has-sleep-augment --query-has-delay-augment

# Analyze PostgreSQL logs
sudo grep 'FATAL' /var/log/postgresql/*.log | awk '{print $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11}'

-- Create roles for FiveM server operations
CREATE ROLE fivem_read_only, fivem_data_entry, fivem_manager;

-- Grant minimum required permissions to each role
GRANT SELECT ON fivem.* TO fivem_read_only;
GRANT INSERT ON fivem.player_data TO fivem_data_entry;
GRANT INSERT, UPDATE, DELETE ON fivem.* TO fivem_manager;

-- Create users and assign roles
CREATE USER 'fivem_viewer'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT fivem_read_only TO 'fivem_viewer'@'localhost';

CREATE USER 'fivem_entry'@'localhost' IDENTIFIED BY 'AnotherStrongPass!';
GRANT fivem_data_entry TO 'fivem_entry'@'localhost';

CREATE USER 'fivem_admin'@'localhost' IDENTIFIED BY 'SuperSecurePass123!';
GRANT fivem_manager TO 'fivem_admin'@'localhost';

-- Enforce a strong password policy
SET GLOBAL validate_password.length=14;
SET GLOBAL validate_password.number_count=2;
SET GLOBAL validate_password.mixed_case_count=1;
SET GLOBAL validate_password.special_char_count=1;
SET GLOBAL validate_password.dictionary_file='/usr/share/mysql/english_dictionary.txt';

-- Update user password to comply with the new policy
ALTER USER 'fivem_viewer'@'localhost' IDENTIFIED BY 'N3wStr0ngPa$$word';

-- Install the MFA plugin
INSTALL PLUGIN authentication_mfa SONAME 'authentication_mfa.so';

-- Create an MFA-enabled user account
CREATE USER 'fivem_superadmin'@'localhost' IDENTIFIED BY 'UltraSecurePass123!';

-- Configure MFA for the user, using a hardware security key
ALTER USER 'fivem_superadmin'@'localhost'
IDENTIFIED WITH authentication_mfa
BY 'initial_secret_key'
REQUIRE SECURE_REMOTE_USER;

-- Create a role for FiveM server developers
CREATE ROLE fivem_developer;
GRANT SELECT, INSERT, UPDATE ON fivem.resources TO fivem_developer;

-- Assign the role to a user
GRANT fivem_developer TO 'dev_user'@'%';

-- Grant SELECT permission on specific tables
GRANT SELECT ON fivem.player_data, fivem.server_logs TO 'fivem_viewer'@'%';

-- Grant INSERT permission on specific columns
GRANT INSERT(name, score) ON fivem.player_scores TO 'fivem_entry'@'%';

-- Create a stored procedure for inserting player scores
DELIMITER $$
CREATE PROCEDURE InsertPlayerScore(
    IN p_name VARCHAR(50),
    IN p_score INT
)
BEGIN
    -- Input validation
    IF p_score < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Score cannot be negative';
    END IF;

    -- Check if player exists
    IF NOT EXISTS (SELECT 1 FROM player_data WHERE name = p_name) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Player does not exist';
    END IF;

    -- Insert score
    INSERT INTO player_scores (name, score)
    VALUES (p_name, p_score);
END$$
DELIMITER ;

-- Grant execute permission on the stored procedure
GRANT EXECUTE ON PROCEDURE InsertPlayerScore TO 'fivem_entry'@'%';

-- Create a view that masks sensitive player data
CREATE VIEW player_data_public AS
SELECT name, game_id, join_date
FROM player_data;

-- Grant SELECT permission on the view
GRANT SELECT ON player_data_public TO 'fivem_viewer'@'%';