Database security is not optional - it's essential. Data breaches cost companies millions in damages and destroy customer trust. Whether you're building a startup or managing enterprise systems, following database security best practices protects your data, your users, and your reputation.

The CIA Triad of Database Security

Every security strategy should address:

Confidentiality: Only authorized users access data
Integrity: Data remains accurate and unmodified
Availability: Data is accessible when needed

Authentication and Access Control

Use Strong Authentication

Never use default credentials or weak passwords.

-- Bad: Weak password
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password123';

-- Good: Strong password with minimum requirements
CREATE USER 'app_user'@'localhost' 
IDENTIFIED BY 'Kj9#mP2$vN4@qL8!xR5';

Implement Principle of Least Privilege

Grant only the permissions users actually need.

-- Bad: Too many privileges
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'localhost';

-- Good: Specific permissions for specific databases
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'app_user'@'localhost';
GRANT SELECT, INSERT ON myapp.orders TO 'app_user'@'localhost';

-- Even better: Separate users for different operations
CREATE USER 'app_read'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT ON myapp.* TO 'app_read'@'localhost';

CREATE USER 'app_write'@'localhost' IDENTIFIED BY 'different_password';
GRANT INSERT, UPDATE ON myapp.* TO 'app_write'@'localhost';

Use Role-Based Access Control

-- Create roles
CREATE ROLE 'app_reader';
CREATE ROLE 'app_writer';
CREATE ROLE 'app_admin';

-- Grant permissions to roles
GRANT SELECT ON myapp.* TO 'app_reader';
GRANT INSERT, UPDATE ON myapp.* TO 'app_writer';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_admin';

-- Assign roles to users
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password';
GRANT 'app_reader' TO 'john'@'localhost';

CREATE USER 'jane'@'localhost' IDENTIFIED BY 'password';
GRANT 'app_writer', 'app_reader' TO 'jane'@'localhost';

SQL Injection Prevention

SQL injection remains one of the most dangerous vulnerabilities.

Always Use Parameterized Queries

// BAD: Vulnerable to SQL injection
const email = req.body.email;
const query = `SELECT * FROM users WHERE email = '${email}'`;
db.query(query);

// GOOD: Parameterized query
const email = req.body.email;
const query = 'SELECT * FROM users WHERE email = ?';
db.query(query, [email]);

# BAD: String concatenation
user_id = request.form['user_id']
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

# GOOD: Parameterized query
user_id = request.form['user_id']
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

// BAD: Direct string interpolation
$email = $_POST['email'];
$query = "SELECT * FROM users WHERE email = '$email'";

// GOOD: Prepared statements (PDO)
$email = $_POST['email'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);

Input Validation

// Validate and sanitize input
function validateEmail(email) {
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  return emailRegex.test(email) && email.length <= 255;
}

function validateUserId(id) {
  const userId = parseInt(id, 10);
  return Number.isInteger(userId) && userId > 0;
}

// Use before database query
if (!validateEmail(email)) {
  return res.status(400).json({ error: 'Invalid email' });
}

Encryption

Encrypt Data in Transit (TLS/SSL)

// Node.js MySQL connection with SSL
const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'database.example.com',
  user: 'app_user',
  password: 'strong_password',
  database: 'myapp',
  ssl: {
    ca: fs.readFileSync('/path/to/ca-cert.pem'),
    cert: fs.readFileSync('/path/to/client-cert.pem'),
    key: fs.readFileSync('/path/to/client-key.pem')
  }
});

# Python PostgreSQL connection with SSL
import psycopg2

conn = psycopg2.connect(
    host="database.example.com",
    database="myapp",
    user="app_user",
    password="strong_password",
    sslmode="require",
    sslcert="/path/to/client-cert.pem",
    sslkey="/path/to/client-key.pem",
    sslrootcert="/path/to/ca-cert.pem"
)

Encrypt Sensitive Data at Rest

-- PostgreSQL with pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt sensitive columns
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255),
  ssn BYTEA  -- Store encrypted
);

-- Insert encrypted data
INSERT INTO users (email, ssn) VALUES (
  '[email protected]',
  pgp_sym_encrypt('123-45-6789', 'encryption_key')
);

-- Query encrypted data
SELECT 
  email,
  pgp_sym_decrypt(ssn, 'encryption_key') as ssn
FROM users;

Hash Passwords Properly

Never store passwords in plain text. Always use proper hashing.

// Node.js with bcrypt
const bcrypt = require('bcrypt');

// Hash password before storing
async function createUser(email, password) {
  const saltRounds = 12;
  const hashedPassword = await bcrypt.hash(password, saltRounds);
  
  await db.query(
    'INSERT INTO users (email, password) VALUES (?, ?)',
    [email, hashedPassword]
  );
}

// Verify password during login
async function verifyLogin(email, password) {
  const user = await db.query(
    'SELECT * FROM users WHERE email = ?',
    [email]
  );
  
  if (!user) return false;
  
  const isValid = await bcrypt.compare(password, user.password);
  return isValid;
}

Network Security

Firewall Configuration

# Allow only specific IPs to access database port
sudo ufw allow from 192.168.1.0/24 to any port 3306
sudo ufw deny 3306

# Or use cloud provider security groups
# Example: AWS Security Group rules
# Type: MySQL/Aurora
# Protocol: TCP
# Port: 3306
# Source: 10.0.1.0/24 (application subnet)

Disable Remote Root Access

-- Remove remote root access
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1');
FLUSH PRIVILEGES;

-- Check which users can connect remotely
SELECT User, Host FROM mysql.user;

Use Private Networks

# Bind MySQL to private IP only (my.cnf)
[mysqld]
bind-address = 10.0.1.5

# PostgreSQL (postgresql.conf)
listen_addresses = '10.0.1.5'

Audit Logging

Enable comprehensive logging to detect and investigate security incidents.

MySQL Audit Logging

-- Install audit plugin
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

-- Configure audit logging
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_incl_users = 'admin,app_user';

-- View audit logs
-- Logs typically in /var/log/mysql/audit.log

PostgreSQL Audit Logging

# postgresql.conf
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'
log_connections = on
log_disconnections = on
log_duration = on

Monitor Suspicious Activity

-- Create alert for failed login attempts
-- Example monitoring query
SELECT 
  user,
  host,
  COUNT(*) as failed_attempts,
  MAX(event_time) as last_attempt
FROM mysql.general_log
WHERE command_type = 'Connect' 
  AND argument LIKE '%Access denied%'
  AND event_time > NOW() - INTERVAL 1 HOUR
GROUP BY user, host
HAVING COUNT(*) > 5;

Backup Security

Encrypt Backups

# MySQL backup with encryption
mysqldump -u root -p myapp | \
  openssl enc -aes-256-cbc -salt -out backup.sql.enc

# Restore encrypted backup
openssl enc -d -aes-256-cbc -in backup.sql.enc | \
  mysql -u root -p myapp

# PostgreSQL backup with encryption
pg_dump myapp | \
  gpg --encrypt --recipient [email protected] > backup.sql.gpg

# Restore encrypted backup
gpg --decrypt backup.sql.gpg | psql myapp

Secure Backup Storage

# Set proper permissions on backup files
chmod 600 /backups/database-*.sql
chown postgres:postgres /backups/database-*.sql

# Store backups in encrypted storage
# AWS S3 with encryption
aws s3 cp backup.sql.enc s3://my-backups/ \
  --server-side-encryption AES256

Test Backup Recovery

#!/bin/bash
# Automated backup test script

# Create test restore
mysql -u root -p -e "CREATE DATABASE backup_test"
mysql -u root -p backup_test < latest_backup.sql

# Verify data integrity
ORIGINAL_COUNT=$(mysql -u root -p -N -e "SELECT COUNT(*) FROM myapp.users")
RESTORED_COUNT=$(mysql -u root -p -N -e "SELECT COUNT(*) FROM backup_test.users")

if [ "$ORIGINAL_COUNT" == "$RESTORED_COUNT" ]; then
  echo "Backup verification successful"
else
  echo "Backup verification FAILED"
  exit 1
fi

# Clean up
mysql -u root -p -e "DROP DATABASE backup_test"

Configuration Hardening

Disable Unnecessary Features

# MySQL configuration (my.cnf)
[mysqld]
# Disable local file access
local-infile = 0

# Disable symbolic links
symbolic-links = 0

# Limit max connections
max_connections = 200

# Set connection timeout
wait_timeout = 600
interactive_timeout = 600

# Disable LOAD DATA LOCAL INFILE
local-infile = 0

Set Proper File Permissions

# MySQL data directory
chmod 750 /var/lib/mysql
chown mysql:mysql /var/lib/mysql

# Configuration files
chmod 644 /etc/mysql/my.cnf
chown root:root /etc/mysql/my.cnf

# PostgreSQL
chmod 700 /var/lib/postgresql/data
chown postgres:postgres /var/lib/postgresql/data

Data Masking and Anonymization

Protect sensitive data in non-production environments.

-- Create anonymized copy for development
CREATE TABLE users_dev AS
SELECT 
  id,
  CONCAT('user', id, '@example.com') as email,
  'Test User ' || id as name,
  MD5(RANDOM()::text) as password_hash,
  created_at
FROM users;

-- Mask credit card numbers
UPDATE orders_dev 
SET card_number = CONCAT(
  '****-****-****-',
  RIGHT(card_number, 4)
);

Regular Security Maintenance

Update and Patch

# Check database version
mysql --version
psql --version

# Update regularly
sudo apt update
sudo apt upgrade mysql-server

# Subscribe to security mailing lists
# MySQL: https://lists.mysql.com/announce
# PostgreSQL: https://www.postgresql.org/list/pgsql-announce/

Password Rotation Policy

-- Set password expiration (MySQL)
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Force immediate password change
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;

Regular Security Audits

-- Review user privileges
SELECT user, host, 
  Select_priv, Insert_priv, Update_priv, Delete_priv,
  Create_priv, Drop_priv, Grant_priv
FROM mysql.user;

-- Check for users without passwords
SELECT user, host FROM mysql.user WHERE authentication_string = '';

-- Review database access
SHOW GRANTS FOR 'app_user'@'localhost';

Compliance and Standards

GDPR Compliance

-- Implement right to deletion
DELETE FROM users WHERE id = ?;
DELETE FROM user_activity WHERE user_id = ?;
DELETE FROM orders WHERE user_id = ?;

-- Data export for portability
SELECT 
  json_object(
    'email', email,
    'name', name,
    'created_at', created_at,
    'orders', (SELECT json_arrayagg(json_object('id', id, 'total', total)) 
               FROM orders WHERE user_id = users.id)
  ) as user_data
FROM users
WHERE id = ?;

PCI DSS for Payment Data

Never store CVV/CVC codes
Encrypt cardholder data
Restrict access to payment data
Maintain audit logs
Use tokenization services

Security Checklist

Before deploying to production:

✅ All default passwords changed
✅ Least privilege access implemented
✅ Parameterized queries used everywhere
✅ TLS/SSL enabled for connections
✅ Sensitive data encrypted at rest
✅ Firewall rules configured
✅ Remote root access disabled
✅ Audit logging enabled
✅ Backups encrypted and tested
✅ Unnecessary features disabled
✅ File permissions secured
✅ Security updates scheduled
✅ Monitoring and alerting configured
✅ Incident response plan documented

Conclusion

Database security is an ongoing process, not a one-time task. By implementing these best practices, you'll significantly reduce the risk of data breaches and ensure compliance with security standards.

Remember:

Prevention is cheaper than recovery
Layer your security (defense in depth)
Monitor continuously
Update regularly
Test everything

A secure database protects your users, your business, and your reputation.

Secure Database Management

Download LunoDB to manage your databases securely with SSL/TLS support, connection encryption, and tools to help you maintain security best practices across all your database systems.

Database Security Best Practices: Protecting Your Data in 2025