Database Security

-- MySQL/MariaDB access control

-- Create database user with limited privileges
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd123!';

-- Grant only necessary privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';

-- Read-only user for reporting
CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'AnotherStr0ngP@ss!';
GRANT SELECT ON myapp.* TO 'report_user'@'localhost';

-- Application user - no DDL privileges
CREATE USER 'web_app'@'192.168.1.%' IDENTIFIED BY 'C0mpl3xP@ssword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.users TO 'web_app'@'192.168.1.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.posts TO 'web_app'@'192.168.1.%';

-- Apply changes
FLUSH PRIVILEGES;

-- View user privileges
SHOW GRANTS FOR 'app_user'@'localhost';

-- Revoke privileges
REVOKE DELETE ON myapp.* FROM 'app_user'@'localhost';

-- Remove user
DROP USER 'old_user'@'localhost';

-- Set strong password policies (MySQL 8.0+)
INSTALL COMPONENT 'file://component_validate_password';

-- Configure password validation
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- Password expiration
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Force password change on next login
ALTER USER 'temp_user'@'localhost' PASSWORD EXPIRE;

-- Prevent password reuse
SET GLOBAL password_history = 6;
SET GLOBAL password_reuse_interval = 365;

-- Lock account after failed login attempts
ALTER USER 'app_user'@'localhost' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

# MySQL transparent data encryption (TDE)

# 1. Generate master key
sudo mkdir -p /var/lib/mysql-keyring
sudo chown mysql:mysql /var/lib/mysql-keyring

# 2. Configure my.cnf
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

# 3. Restart MySQL
sudo systemctl restart mysql

# 4. Encrypt existing tables
ALTER TABLE users ENCRYPTION='Y';
ALTER TABLE sensitive_data ENCRYPTION='Y';

# 5. Set default encryption for new tables
SET GLOBAL default_table_encryption=ON;

# 6. Verify encryption status
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

# PostgreSQL encryption at rest

# 1. Initialize cluster with encryption
initdb -D /var/lib/postgresql/data --data-checksums

# 2. Configure postgresql.conf
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/root.crt'

# 3. Encrypt specific columns (pgcrypto)
CREATE EXTENSION pgcrypto;

-- Encrypt data
INSERT INTO users (email, password)
VALUES ('user@example.com', crypt('password', gen_salt('bf')));

-- Verify password
SELECT * FROM users
WHERE email = 'user@example.com'
AND password = crypt('password', password);

# MySQL SSL/TLS configuration

# 1. Generate SSL certificates
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

# 2. Configure my.cnf
[mysqld]
require_secure_transport=ON
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

# 3. Require SSL for specific user
ALTER USER 'app_user'@'%' REQUIRE SSL;

# 4. Verify SSL connection
SHOW STATUS LIKE 'Ssl_cipher';
SHOW VARIABLES LIKE '%ssl%';

# 5. Connect with SSL (from application)
mysql --ssl-mode=REQUIRED \
--ssl-ca=/path/to/ca.pem \
-h database.example.com \
-u app_user -p

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

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

# Secure MySQL backup script
#!/bin/bash

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp"
ENCRYPTION_KEY="your-strong-encryption-key"

# Create backup directory with restricted permissions
mkdir -p $BACKUP_DIR
chmod 700 $BACKUP_DIR

# Dump database
mysqldump --single-transaction \
--routines --triggers \
--user=backup_user \
--password=$DB_PASSWORD \
$DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Encrypt backup
openssl enc -aes-256-cbc -salt \
-in $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz \
-out $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz.enc \
-k $ENCRYPTION_KEY

# Remove unencrypted backup
rm $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Set restrictive permissions
chmod 600 $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz.enc

# Remove backups older than 30 days
find $BACKUP_DIR -name "*.enc" -mtime +30 -delete

# Upload to secure offsite storage
aws s3 cp $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz.enc \
s3://secure-backup-bucket/ \
--server-side-encryption AES256

-- MySQL Enterprise Audit (commercial) or MariaDB Audit Plugin

-- Install audit plugin (MariaDB)
INSTALL SONAME 'server_audit';

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

-- Log file location
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';
SET GLOBAL server_audit_file_rotate_size = 1000000;

-- Query audit log
SELECT * FROM mysql.audit_log
WHERE command_class = 'drop_table'
AND timestamp > NOW() - INTERVAL 1 DAY;

-- PostgreSQL audit with pgAudit extension
CREATE EXTENSION pgaudit;

-- Configure postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all' -- or 'ddl', 'write', 'read'
pgaudit.log_catalog = off
pgaudit.log_parameter = on

-- Audit specific database
ALTER DATABASE myapp SET pgaudit.log = 'write, ddl';

-- View audit logs
SELECT * FROM pg_stat_statements
WHERE query LIKE '%DELETE%' OR query LIKE '%DROP%';

-- MySQL data masking (MySQL 8.0+)

-- Create masked view for developers
CREATE VIEW users_masked AS
SELECT
id,
CONCAT(LEFT(email, 3), '***@***.com') AS email,
'*******' AS password,
CONCAT(LEFT(name, 1), '***') AS name,
created_at
FROM users;

-- Grant access to masked view only
GRANT SELECT ON myapp.users_masked TO 'developer'@'localhost';

-- Function-based masking
DELIMITER $$
CREATE FUNCTION mask_email(email VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE masked VARCHAR(255);
SET masked = CONCAT(
LEFT(email, 2),
'***@',
SUBSTRING_INDEX(email, '@', -1)
);
RETURN masked;
END$$
DELIMITER ;