Skip to content
English
Customer portal
Contact us
  • There are no suggestions because the search field is empty.

09.04 Securing the Database

SimpleRisk's MySQL database holds the entire risk register, audit trail, and configuration. Protect it via least-privileged DB user, network isolation, TLS for the application-to-database connection, MySQL-level secure-defaults configuration, automated encrypted backups, and OS-level access control on the database files.

Why this matters

The database is where everything lives. The risk register, the audit trail, every user account and permission grant, every framework and control mapping, every uploaded document's metadata, every configuration setting. A compromised database is a compromised SimpleRisk install: an attacker reading the database can see every risk, every audit finding, every internal compliance gap; an attacker writing to the database can grant themselves admin, hide actions from the audit trail, or lock out legitimate users.

The Encryption Extra (The Encryption Extra Overview) protects sensitive content fields against exfiltration. Database hardening is the broader set of controls that protects the database itself: who can connect to it, what credentials they need, whether the connection is encrypted, where backups live, who can read the database files on the OS. The two layers complement each other; neither is sufficient alone.

The honest scope to know up front: most of these controls are MySQL and OS configuration, not SimpleRisk configuration. SimpleRisk's role is "be a good database client": use a least-privileged user, enable TLS to the database, fail closed on connection errors. The hardening of the database itself is the database administrator's domain. This article covers both — what SimpleRisk needs and what the DB admin needs to configure on their side.

Before you start

Have these in hand:

  • Admin access to the SimpleRisk installation for the SimpleRisk-side configuration changes.
  • Root or DBA access to the MySQL server for the database-side changes.
  • Network access to verify connectivity between the application server and database server.
  • A documented current state: which user account does SimpleRisk use to connect, what permissions does it have, what's the network path between application and database.
  • A maintenance window if you're changing connection credentials or the network path; the application can't function during the change.

Step-by-step

1. Use a dedicated, least-privileged database user

SimpleRisk should connect as a user with permissions only on the SimpleRisk database — not as root, not as a user with *.* privileges.

Check current state:

# Connect to MySQL as root or a privileged user
mysql -u root -p

# Show current users
SELECT user, host FROM mysql.user;

# Show what the SimpleRisk user has access to
SHOW GRANTS FOR 'simplerisk'@'%';

If the SimpleRisk user has ALL PRIVILEGES *.* or similar, restrict it:

-- Drop the over-permissive user (carefully — note current grants first)
DROP USER 'simplerisk'@'%';

-- Recreate with limited privileges
CREATE USER 'simplerisk'@'%' IDENTIFIED BY '
  
   '; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, REFERENCES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER ON simplerisk.* TO 'simplerisk'@'%'; FLUSH PRIVILEGES;

Why these specific grants: SimpleRisk's installer and upgrade flow create tables, alter columns, and create stored routines, so it needs DDL on its own database. It doesn't need access to other databases.

For installs that don't run upgrades through the application (operators run upgrades manually with broader privileges), the runtime user can be even more restricted:

-- Runtime-only user without DDL
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON simplerisk.* TO 'simplerisk_runtime'@'%';

Switch SimpleRisk to use the runtime user for normal operation; switch to a privileged user only during upgrades.

2. Constrain the database user's source host

The 'simplerisk'@'%' form lets the user connect from any host. Restrict to the application server(s):

-- Drop the wildcard-host user
DROP USER 'simplerisk'@'%';

-- Recreate constrained to the application server's IP or hostname
CREATE USER 'simplerisk'@'10.0.1.50' IDENTIFIED BY '
  
   '; -- Or by hostname CREATE USER 'simplerisk'@'app-server.internal.example.com' IDENTIFIED BY '
   
    '; -- Grant the same privileges as before GRANT ... ON simplerisk.* TO 'simplerisk'@'10.0.1.50'; FLUSH PRIVILEGES;

For multi-server SimpleRisk deployments, create one user per app server (or use a wildcard that covers your subnet, e.g., 'simplerisk'@'10.0.1.%'). The principle: connections from anywhere else are rejected at the auth layer.

3. Enable TLS for the application-to-database connection

By default MySQL accepts unencrypted connections. The application's database password and every query and result transit in plaintext. For installs where the database server is on a different host than the application server (typical), enable TLS:

On the MySQL server (/etc/mysql/my.cnf or the equivalent path):

[mysqld]
require_secure_transport = ON
ssl_ca = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem

Restart MySQL.

On the SimpleRisk side (simplerisk/includes/config.php):

The configuration accepts SSL parameters; the exact options depend on the install version. Typical options:

$DB_SSL = true;
$DB_SSL_CA = '/etc/ssl/mysql-ca.pem';   // Path to the CA certificate
$DB_SSL_VERIFY = true;                    // Verify the server certificate

Test the connection: open the SimpleRisk login page; if it loads and you can authenticate, the TLS connection is working. The MySQL server log should show TLS-encrypted connections from the application server.

For installs where the database is on the same server as the application (loopback connection), TLS is less critical but still defensible. Most production deployments separate the two.

4. Network-isolate the database

The database server should not be reachable from the public internet. Put it on an internal subnet; allow connections only from the application server(s); block everything else.

For cloud deployments:

  • AWS: place RDS / EC2 MySQL in a private subnet; security group allows port 3306 only from the application security group.
  • Azure: similar — private subnet, NSG rules.
  • GCP: private VPC, firewall rules.

For on-prem deployments:

  • Database server in a backend VLAN not routable from end-user networks.
  • Application server in a DMZ-style middle tier.
  • Firewall between them allows only the database port.

The principle: even if SimpleRisk's application has a vulnerability that exposes the database connection details, the network layer prevents external access.

5. Configure MySQL securely

A few my.cnf settings worth verifying:

[mysqld]
# Disable old, weak password authentication
default_authentication_plugin = caching_sha2_password

# Disable LOAD DATA LOCAL INFILE — used in some attack scenarios
local_infile = OFF

# Disable symbolic links to avoid TOCTOU attacks
symbolic_links = OFF

# Bind to specific IP, not all interfaces (if not using TLS)
bind-address = 10.0.1.10  # Internal IP, not 0.0.0.0

Run mysql_secure_installation on a fresh install:

  • Sets a root password.
  • Removes anonymous users.
  • Disables remote root login.
  • Removes the test database.
  • Reloads the privilege tables.

These are baseline hardening steps; do them once at install time.

6. Set strong credentials

The SimpleRisk database user's password should be strong and managed:

  • Length: 32+ characters; the password is configured once in simplerisk/includes/config.php and rarely typed by humans, so length doesn't friction operators.
  • Generation: random, from a password generator. Don't pick something memorable.
  • Storage: in simplerisk/includes/config.php only; the file's filesystem permissions should be 0640 or 0600 owned by the web server user.
  • Rotation: on a schedule that matches your program's policies (annually for most programs); on personnel changes (an admin who left should not have credentials they could use).

To rotate:

  1. Set a new password for the SimpleRisk user via MySQL: ALTER USER 'simplerisk'@' ' IDENTIFIED BY ' ';
  2. Update simplerisk/includes/config.php with the new password.
  3. Restart the SimpleRisk PHP process / clear PHP opcache so the new config takes effect.
  4. Verify the application connects successfully.

7. Configure automated, encrypted backups

Backups are essential; unprotected backups defeat the purpose. See Database Backup and Restore for the full backup workflow. Specifically for security:

  • Encrypt backups at rest: use mysqldump piped through GPG or AWS S3 server-side encryption or your backup software's encryption feature.
  • Store backups off-server: same considerations as for the master encryption key.
  • Restrict backup access: backups contain everything in the database. Treat backup file permissions and access control as carefully as you treat the database itself.
  • Test restores periodically: an untested backup is a wish.

If the Encryption Extra is active, the encrypted fields in the backup remain encrypted (the backup is just a SQL dump of the database state). The master key is what makes the backup useful for restore — without the key, the encrypted fields in the restored database are inaccessible.

8. OS-level access control on database files

The MySQL data directory (typically /var/lib/mysql/) contains the database files. OS-level access control:

  • Ownership: mysql:mysql (the MySQL daemon user).
  • Permissions: 0700 on the data directory; 0660 on individual files. Default in standard MySQL installs.
  • Verify: ls -la /var/lib/mysql/.

A user with read access to these files (via OS-level compromise or misconfiguration) reads the entire database without going through MySQL's authentication. The OS-level controls are part of the defense.

9. Audit and monitoring

Configure database-level audit logging if your program needs it:

  • MySQL Enterprise Audit Plugin (commercial; for MySQL Enterprise Edition).
  • Percona Audit Log Plugin (free; for Percona Server).

These log connection attempts, query execution, and privilege changes. Forward the logs to a SIEM or log aggregator for monitoring and alerting.

For programs without dedicated database auditing, the SimpleRisk-side audit trail (the audit_log table) covers application-level events; combined with web server access logs, this provides reasonable visibility without database-side audit.

10. Plan for incident response

If the database is compromised:

  • Rotate credentials: change the SimpleRisk database user's password; update config.php.
  • Rotate the encryption master key (see Key Management and Rotation).
  • Audit access: who connected, when, what queries did they run.
  • Restore from backup if data integrity is compromised.
  • Notify stakeholders and (if regulated content was potentially exposed) the relevant regulators per your incident response plan.

Document the procedure before you need it.

Common pitfalls

A handful of patterns recur with database security.

  • Using root as the SimpleRisk database user. It works but the blast radius of a SimpleRisk vulnerability is much larger. Use a least-privileged user.

  • Allowing the database to listen on all interfaces with no network controls. The default MySQL bind-address = 0.0.0.0 plus open firewalls produces an internet-exposed database. Network isolate.

  • Leaving the database password short or reused. If the password is in config.php, length is free. Use a long random password.

  • Not enabling TLS for the application-to-database connection. Plaintext network transit. Enable TLS unless the connection is loopback.

  • Storing backup files unencrypted on accessible storage. A backup file is the entire database. Treat it like the database itself.

  • Granting the SimpleRisk user access to multiple databases. A SimpleRisk vulnerability shouldn't be able to read other applications' data. Grants per-database, not *.*.

  • Forgetting to restart MySQL after config changes. my.cnf changes don't take effect until restart. Verify after restart.

  • Not testing backup restores. "We have backups" without "we've tested restoring them" is a wish.

  • Treating the Encryption Extra as a substitute for database hardening. Encryption protects exfiltrated data; database hardening prevents the exfiltration in the first place. Both layers.

  • Not rotating database credentials when admins leave. A departed admin who knew the database password retains the ability to access the database (if they can reach it). Rotate as part of off-boarding.

  • Forgetting the OS-level data directory permissions. Filesystem read access bypasses MySQL authentication entirely. Verify directory permissions.

  • Configuring database audit logging and never reviewing the logs. Logging without review produces compliance theater. Either review or don't bother logging.

Related

Reference

  • Permission required: Database administrator access for the MySQL-side configuration; SimpleRisk admin for the application-side config.php changes.
  • Implementing files (SimpleRisk): simplerisk/includes/config.php (database connection settings — host, port, username, password, optional SSL parameters).
  • Implementing files (MySQL): /etc/mysql/my.cnf (or distribution-specific path) for server configuration; /var/lib/mysql/ (or distribution-specific) for the data directory.
  • Database user grants: Recommended: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, REFERENCES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER on simplerisk.*. Constrained-host (not '%'). Strong random password.
  • TLS configuration: Server-side: require_secure_transport = ON in my.cnf plus ssl_ca, ssl_cert, ssl_key. Client-side: $DB_SSL = true plus CA path in config.php.
  • Network isolation: Database on a private network/subnet; firewall allows port 3306 only from the application server(s); not reachable from public internet or end-user networks.
  • External dependencies: A configured TLS certificate for the database server; an off-server backup destination; a SIEM or log aggregator for audit log forwarding (optional).