Database security: securing SQL Server, MySQL and PostgreSQL

Databases are the most valuable target in any organization: customer data, financial data, password hashes, trade secrets. At the same time, databases are often poorly secured—default configurations, overly broad permissions, and a lack of encryption are the norm. This guide outlines the most important security measures for the three most common database systems.

Common Database Security Issues

Typical findings in DB security assessments:

Authentication:
  □ Root/SA account active and accessible
  □ Weak default passwords (root/root, sa/password)
  □ Passwords in plain text in config files
  □ Shared credentials: all applications use the same DB user
  □ No MFA for administrative database access

Authorization:
  □ Application users with GRANT ALL or DBA privileges
  □ No schema-level locking: Application reads tables it doesn’t need
  □ Publicly accessible DB port (3306, 5432, 1433 directly from the Internet!)

Encryption:
  □ No TLS for connections (plaintext transmission!)
  □ No encryption at rest (hard drive theft = data loss)
  □ Passwords stored as MD5 or SHA1 (unsalted!)

Logging:
  □ No audit log: who queried which data?
  □ No detection of database dumps (SELECT * FROM customers WHERE 1=1)
  □ Logs are not stored centrally (SIEM integration missing)

Patch management:
  □ Outdated database versions with known CVEs
  □ No patching process for database servers

PostgreSQL Hardening

File: postgresql.conf

# Limit connections:
listen_addresses = '127.0.0.1'    # Only localhost! No 0.0.0.0!
port = 5432
max_connections = 100

# Enforce SSL:
ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file = '/etc/postgresql/server.key'
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'

# Logging (for auditing):
log_connections = on
log_disconnections = on
log_duration = on
log_statement = 'ddl'          # Log DDL statements (CREATE/DROP/ALTER)
log_min_duration_statement = 1000  # Log queries > 1s (slow queries)
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_destination = 'csvlog'
logging_collector = on

---

File: pg_hba.conf (Client Authentication):

# Only specific hosts are allowed to connect:
# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  peer      # Only OS user "postgres"
host    myapp     myappuser 10.0.1.0/24    scram-sha-256   # App server IP
host    all       all       0.0.0.0/0      reject    # All others: BLOCKED

---

Users and Permissions:

-- Revoke root access (postgres user only locally):
REVOKE CONNECT ON DATABASE myapp FROM PUBLIC;

-- Application user with minimal privileges:
CREATE USER myapp_user WITH PASSWORD 'StrongPassword123!';
GRANT CONNECT ON DATABASE myapp TO myapp_user;
GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myapp_user;

-- DO NOT: GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user;
-- DO NOT: GRANT SUPERUSER TO myapp_user;

-- Read-Only User for Reporting:
CREATE USER reporting_user WITH PASSWORD 'ReadOnly456!';
GRANT CONNECT ON DATABASE myapp TO reporting_user;
GRANT USAGE ON SCHEMA public TO reporting_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;

-- Row-Level Security (RLS) for multi-tenant:
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY kunden_tenant_policy ON kunden
  USING (tenant_id = current_setting('app.tenant_id')::INT);

---

Password Hashing in PostgreSQL:
-- WRONG: MD5 (default in older versions):
-- CREATE USER user WITH PASSWORD 'pw' ENCRYPTED;  -- generates MD5!

-- CORRECT: scram-sha-256 (default since PostgreSQL 10):
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
-- Then reset all passwords!

MySQL/MariaDB Hardening

Initial hardening with mysql_secure_installation:
  mysql_secure_installation
  → Set root password: YES
  → Remove anonymous users: YES
  → Disable remote root login: YES
  → Remove test database: YES
  → Reload privileges: YES

/etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
# Network:
bind-address = 127.0.0.1          # No remote access!
skip-networking = 0               # Local socket ok
port = 3306

# Security:
local-infile = 0                  # Disable LOAD DATA LOCAL INFILE
skip-symbolic-links               # Prevent symlink attacks
secure-file-priv = /var/lib/mysql-files  # Restrict file access
sql_mode = "STRICT_ALL_TABLES,NO_AUTO_CREATE_USER"

# SSL:
ssl-ca = /etc/mysql/ca.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
require_secure_transport = ON    # SSL REQUIRED for all connections!

# Logging:
general_log = OFF                 # Log all queries (high I/O!)
general_log_file = /var/log/mysql/general.log
log_error = /var/log/mysql/error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

---

User hardening:

-- Remove unnecessary users:
SELECT user, host FROM mysql.user;
DROP USER IF EXISTS ''@'localhost';    -- Anonymous user
DROP USER IF EXISTS ''@'hostname';    -- Anonymous user
DROP USER IF EXISTS 'root'@'%';       -- PROHIBIT remote root!

-- Root allowed only locally:
UPDATE mysql.user SET host='localhost' WHERE user='root';
FLUSH PRIVILEGES;

-- Application user:
CREATE USER 'webapp'@'10.0.1.%' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'webapp'@'10.0.1.%';
-- DO NOT: GRANT ALL ON *.* TO 'webapp';

-- Password policy plugin:
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
SET GLOBAL validate_password_policy=STRONG;
SET GLOBAL validate_password_length=12;
SET GLOBAL validate_password_mixed_case_count=1;
SET GLOBAL validate_password_special_char_count=1;

-- Audit logging (MariaDB Audit Plugin):
INSTALL PLUGIN server_audit SONAME 'server_audit';
SET GLOBAL server_audit_logging=ON;
SET GLOBAL server_audit_events='CONNECT,QUERY_DDL';

Microsoft SQL Server Hardening

Basic hardening measures:

-- Disable the SA account (if Windows Authentication is used):
ALTER LOGIN sa DISABLE;
-- Or: set a strong password + rename:
ALTER LOGIN sa WITH NAME = [sqlbackup_only];
ALTER LOGIN sqlbackup_only WITH PASSWORD = 'StrongLongPassword!2024';

-- Windows Authentication only (no SQL Authentication):
-- SSMS: Server Properties → Security → Windows Authentication Mode

-- Disable xp_cmdshell (OS commands via SQL!):
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;

-- Restrict remote connections (specific IPs only):
-- SQL Server Configuration Manager → SQL Server Network Configuration
-- → TCP/IP Properties → IP Addresses → Configure Specific IPs

-- Enable Transparent Data Encryption (TDE):
-- Encrypt database files on disk:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EncryptionPassword!';
CREATE CERTIFICATE ServerCert WITH SUBJECT = 'TDE Certificate';
CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE ServerCert;
ALTER DATABASE MyDatabase SET ENCRYPTION ON;

---

Least Privilege for Application Users:
-- Create a new login:
CREATE LOGIN AppUser WITH PASSWORD = 'SecurePassword123!';

-- Access only to a specific database:
USE MyDatabase;
CREATE USER AppUser FOR LOGIN AppUser;

-- Only necessary schema permissions:
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO AppUser;
DENY EXECUTE ON xp_cmdshell TO AppUser;  -- Extra security!
-- DO NOT: GRANT CONTROL ON DATABASE TO AppUser;
-- DO NOT: Membership in db_owner!

---

SQL Server Audit:
CREATE SERVER AUDIT MyAudit
  TO FILE (FILEPATH = 'C:\SQLAudit\');

CREATE DATABASE AUDIT SPECIFICATION MyDBSpec
FOR SERVER AUDIT MyAudit
  ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public);

ALTER SERVER AUDIT MyAudit WITH (STATE = ON);
ALTER DATABASE AUDIT SPECIFICATION MyDBSpec WITH (STATE = ON);

SQL Injection Prevention - Code Level

SQL injection is the #1 database threat (OWASP A03:2021)

WRONG - String concatenation:
  # Python (insecure!):
  query = "SELECT * FROM users WHERE name = '" + user_input + "'"
  cursor.execute(query)
  # Attacker: user_input = "' OR '1'='1"
  # → Query: SELECT * FROM users WHERE name = '' OR '1'='1'
  # → All users are returned!

CORRECT - Prepared Statements:
  # Python (secure!):
  query = "SELECT * FROM users WHERE name = %s"
  cursor.execute(query, (user_input,))

  # Java JDBC:
  PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE name = ?"
  );
  stmt.setString(1, userName);

  # Node.js with pg (PostgreSQL):
  const result = await client.query(
    'SELECT * FROM users WHERE name = $1',
    [userName]
  );

  # PHP PDO:
  $stmt = $pdo->prepare('SELECT * FROM users WHERE name = :name');
  $stmt->execute(['name' => $userName]);

ORM as additional protection:
  → Prisma, Sequelize, Hibernate, Entity Framework
  → ORMs automatically generate prepared statements
  → BUT: Raw queries in ORM can still be vulnerable!
  → Hibernate: session.createQuery("... WHERE id = :id").setParameter("id", id)

Stored Procedures (if parameterized):
  CREATE PROCEDURE GetUser @UserName NVARCHAR(50)
  AS
  BEGIN
    SELECT * FROM Users WHERE Name = @UserName;
  END;
  -- NO dynamic SQL in SP: EXEC('SELECT... ' + @param) → insecure!

Input validation as a second line of defense:
  → Type checking: is ID really a number? int(user_id)
  → Whitelist: accept only allowed characters
  → Limit length: max 255 characters for strings
  → NO blacklist! Attackers always find ways to bypass them

Database Monitoring and Audit

What needs to be monitored?

  □ All logins (successful + failed)
  □ Privileged actions (GRANT, DROP, ALTER)
  □ Data access to sensitive tables (customers, finances, passwords)
  □ Mass queries: "SELECT * WHERE 1=1" → potential data dump!
  □ Connections from unusual IP addresses
  □ Connections active outside business hours
  □ Schema changes (CREATE TABLE, ALTER TABLE)

SIEM integration:

  Filebeat → Elasticsearch (for MySQL logs):
  filebeat.inputs:
  - type: log
    paths:
      - /var/log/mysql/general.log
      - /var/log/mysql/error.log
    fields:
      source: mysql
      env: production

  Alert for suspicious queries (Elasticsearch KQL):
  message: *SELECT * FROM* AND message: *WHERE 1=1*
  → Possible database dump!

Database Activity Monitoring (DAM) Tools:
  → IBM Guardium: Enterprise DAM
  → Imperva Database Security: comprehensive monitoring
  → pgAudit: Open Source for PostgreSQL
  → MySQL Enterprise Audit: commercial

  DAM Features:
  → Real-time detection of SQL injection patterns
  → Behavior baseline: normal query traffic vs. anomalies
  → Automatic blocking upon detected attack (optional)
  → GDPR-compliant logging (who, what, when)

Backup security:
  □ Are backups encrypted? (mysqldump | gpg -e > backup.sql.gpg)
  □ Backup server: separate from the production network
  □ Backup access: dedicated user with minimal privileges
  □ Restore test: monthly (can we actually restore data?)
  □ Backup retention: how long? (GDPR: no longer than necessary!)