Skip to content

Database Security Deep Dive

Jump to bottom
Alex Stojcic edited this page Apr 3, 2025 · 1 revision

Database Security Deep Dive

Database security is a critical component of overall application security, as databases often store an organization's most valuable and sensitive information. This guide covers comprehensive strategies for securing databases in modern web applications.

Database Security Core Principles

Defense in Depth

Security should be implemented at multiple layers:

  1. Network level - Firewalls, VPNs, network segregation
  2. Database instance level - Authentication, access controls, encryption
  3. Application level - Input validation, parameterized queries, ORM use
  4. Data level - Encryption, masking, tokenization

Principle of Least Privilege

Each component and user should have the minimum access necessary:

  • Database users with limited permissions
  • Connection pools with restricted service accounts
  • Separation between application and administrative access
  • Time-limited elevated privileges

Secure Database Configuration

Instance Hardening

MySQL/MariaDB Hardening

-- Disable remote root login
UPDATE mysql.user SET Host='localhost' WHERE User='root';
FLUSH PRIVILEGES;

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

-- Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

-- Enable binary logging for audit
SET GLOBAL log_bin = ON;

PostgreSQL Hardening

-- Set connection restrictions in pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
host    all             app_user        127.0.0.1/32            scram-sha-256
host    all             app_user        ::1/128                 scram-sha-256

-- Configure password encryption
ALTER SYSTEM SET password_encryption = 'scram-sha-256';

-- Set connection limits
ALTER ROLE app_user CONNECTION LIMIT 100;

Network Security

  • Host database on private subnet - Not accessible from public internet
  • Use VPC/VPN for secure connections
  • IP whitelisting for administrative access
  • Enable TLS/SSL for all connections

Example of enabling SSL in PostgreSQL:

-- In postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

-- Force SSL connections
ALTER USER app_user WITH PASSWORD 'strong_password' VALID UNTIL '2025-12-31';
ALTER ROLE app_user SET ssl = true;

Authentication and Authorization

Create Application-Specific Database Users

-- PostgreSQL example
-- Create application user with limited permissions
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password';

-- Grant specific schema permissions
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant table-level permissions
GRANT SELECT, INSERT, UPDATE ON users TO app_user;
GRANT SELECT, UPDATE ON products TO app_user;

-- Grant sequence usage for auto-increment columns
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Explicitly revoke destructive permissions
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM app_user;

Role-Based Access Control in MySQL

-- Create roles
CREATE ROLE 'app_read_role', 'app_write_role', 'app_admin_role';

-- Set permissions for roles
GRANT SELECT ON myapp.* TO 'app_read_role';
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_write_role';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_admin_role';

-- Create users
CREATE USER 'read_user'@'localhost' IDENTIFIED BY 'password1';
CREATE USER 'write_user'@'localhost' IDENTIFIED BY 'password2';
CREATE USER 'admin_user'@'localhost' IDENTIFIED BY 'password3';

-- Assign roles to users
GRANT 'app_read_role' TO 'read_user'@'localhost';
GRANT 'app_write_role' TO 'write_user'@'localhost';
GRANT 'app_admin_role' TO 'admin_user'@'localhost';

-- Activate default roles
SET DEFAULT ROLE 'app_read_role' TO 'read_user'@'localhost';
SET DEFAULT ROLE 'app_write_role' TO 'write_user'@'localhost';
SET DEFAULT ROLE 'app_admin_role' TO 'admin_user'@'localhost';

Data Protection Strategies

Encryption at Rest

Transparent Data Encryption (TDE)

Many enterprise databases support TDE:

  • SQL Server: Enable through SQL Server Configuration Manager
  • Oracle: Configure using ADMINISTER KEY MANAGEMENT
  • PostgreSQL: Use encryption extensions or filesystem encryption
  • MySQL: Use keyring plugin for data-at-rest encryption

Column-Level Encryption

For targeted protection of sensitive columns:

-- Create table with encrypted columns (PostgreSQL with pgcrypto)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  -- Encrypted columns
  ssn TEXT NOT NULL,
  credit_card TEXT NOT NULL
);

-- Insert with encryption
INSERT INTO customers (name, ssn, credit_card)
VALUES (
  'John Doe',
  pgp_sym_encrypt('123-45-6789', 'encryption_key'),
  pgp_sym_encrypt('4111-1111-1111-1111', 'encryption_key')
);

-- Query with decryption
SELECT 
  name,
  pgp_sym_decrypt(ssn::bytea, 'encryption_key') as ssn,
  pgp_sym_decrypt(credit_card::bytea, 'encryption_key') as credit_card
FROM customers;

Row-Level Security (RLS)

PostgreSQL example:

-- Enable RLS on table
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

-- Create policy for multi-tenant application
CREATE POLICY tenant_isolation_policy ON customer_data
  USING (tenant_id = current_setting('app.current_tenant_id')::integer);

-- Application sets the tenant context for each connection
-- In application code before query execution:
await client.query("SET app.current_tenant_id = $1", [tenantId]);

-- Create policy for user-specific data
CREATE POLICY user_data_policy ON user_documents
  USING (user_id = current_setting('app.current_user_id')::integer);

Data Masking and Tokenization

For development/testing environments or reporting:

-- PostgreSQL dynamic data masking
CREATE VIEW masked_customers AS
SELECT
  id,
  name,
  -- Mask SSN except last 4 digits
  CONCAT('XXX-XX-', RIGHT(ssn, 4)) AS ssn,
  -- Mask credit card
  CONCAT('XXXX-XXXX-XXXX-', RIGHT(credit_card, 4)) AS credit_card,
  email,
  created_at
FROM customers;

-- Grant access to the masked view instead of the table
REVOKE ALL ON customers FROM reporting_role;
GRANT SELECT ON masked_customers TO reporting_role;

Secure Database Access from Applications

Connection Pooling Security

// Node.js example with pg pool
const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT,
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('/path/to/server-ca.pem').toString(),
    key: fs.readFileSync('/path/to/client-key.pem').toString(),
    cert: fs.readFileSync('/path/to/client-cert.pem').toString(),
  },
  // Security settings
  max: 20, // Maximum connections
  idleTimeoutMillis: 30000, // Close idle connections
  connectionTimeoutMillis: 2000, // Connection timeout
  allowExitOnIdle: true // Clean shutdown
});

Preventing SQL Injection

Parameterized Queries

// Node.js with PostgreSQL
async function getUserData(userId) {
  // SECURE: Parameterized query
  const query = 'SELECT id, name, email FROM users WHERE id = $1';
  const result = await pool.query(query, [userId]);
  return result.rows[0];
}

// INSECURE - DO NOT DO THIS:
// const query = `SELECT id, name, email FROM users WHERE id = ${userId}`;

ORM Usage

Using Prisma ORM (TypeScript):

// Define schema in schema.prisma
// model User {
//   id    Int     @id @default(autoincrement())
//   email String  @unique
//   name  String?
// }

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

// Secure: ORM handles SQL parameterization
async function getUserData(userId: number) {
  return await prisma.user.findUnique({
    where: {
      id: userId
    },
    select: {
      id: true,
      name: true,
      email: true
    }
  });
}

Connection String Security

Secure connection string handling:

// .env file (not in version control)
DATABASE_URL=postgresql://username:password@localhost:5432/mydb?sslmode=require

// Load with dotenv
require('dotenv').config();

// Access securely
const connectionString = process.env.DATABASE_URL;

Monitoring and Auditing

Audit Logging

Enable database audit logging to track sensitive operations:

-- PostgreSQL auditing with pgaudit extension
CREATE EXTENSION pgaudit;

-- Configure in postgresql.conf
-- pgaudit.log = 'write,ddl'
-- pgaudit.log_catalog = on
-- pgaudit.log_parameter = on
-- pgaudit.log_relation = on

-- Create audit role for specific tables
CREATE ROLE audit_admin;
GRANT audit_admin TO postgres;

-- Audit specific tables
SELECT audit.audit_table('users');
SELECT audit.audit_table('financial_transactions');

Query Monitoring

Implement real-time query monitoring for:

  • Slow queries that could indicate issues
  • Unusual access patterns
  • Failed login attempts
  • Privileged operations
-- Enable slow query logging in MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries taking more than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

Backup and Recovery Security

Secure Backup Practices

  1. Encrypt backups:

    # PostgreSQL backup with encryption
pg_dump dbname | gpg -c > backup.sql.gpg

# Restore
gpg -d backup.sql.gpg | psql dbname

  2. Secure transport to offsite storage:

    # Secure copy to backup server
scp -i ~/.ssh/id_rsa backup.sql.gpg backup_user@backup-server:/backups/

  3. Implement least privilege for backup processes:

    -- Create backup-specific user with minimal permissions
CREATE USER backup_user WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE myapp TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

Database Security in the Cloud

AWS RDS Security Best Practices

  1. Enable encryption:

    • Select "Enable encryption" during instance creation
    • Use AWS KMS keys for management

  2. Network security:

    # Security Group Configuration
Type: PostgreSQL
Protocol: TCP
Port: 5432
Source: 10.0.0.0/16 (VPC CIDR only)

  3. IAM authentication:

    // AWS SDK v3 example
import { RDSDataClient, ExecuteStatementCommand } from "@aws-sdk/client-rds-data";

const client = new RDSDataClient({ region: "us-west-2" });

const params = {
  secretArn: "arn:aws:secretsmanager:us-west-2:123456789012:secret:my-rds-secret",
  resourceArn: "arn:aws:rds:us-west-2:123456789012:cluster:my-cluster",
  sql: "SELECT * FROM users WHERE id = :id",
  parameters: [{ name: "id", value: { longValue: 42 } }],
  database: "mydb"
};

const command = new ExecuteStatementCommand(params);
const response = await client.send(command);

Google Cloud SQL Security

  1. Configure private IP and authorized networks
  2. Enable Cloud SQL Auth Proxy for secure connections
  3. Use service account with minimal permissions

Detecting and Preventing Database Attacks

SQL Injection Prevention Checklist

  • Use parameterized queries / prepared statements
  • Implement input validation with whitelist approach
  • Use ORM with security features
  • Apply principle of least privilege to database users
  • Enable WAF to block common SQL injection patterns
  • Regularly patch database software

Detecting Attacks

Implement monitoring for:

  1. Unusual query patterns:

    • Multiple failed queries in succession
    • Queries with SQL comments or typical injection patterns
    • Queries attempting to access system tables

  2. Unusual access patterns:

    • Access from new locations/IP addresses
    • Access outside normal business hours
    • Sudden increase in query volume

  3. Privilege escalation attempts:

    • Failed attempts to execute administrative commands
    • Attempts to change user permissions

ORM Security Best Practices

Prisma ORM Security (Node.js/TypeScript)

// Secure Prisma implementation
import { PrismaClient } from '@prisma/client'

// Avoid exposing error details
const prisma = new PrismaClient({
  errorFormat: 'minimal',
  log: [
    { level: 'query', emit: 'event' },
    { level: 'error', emit: 'stdout' },
  ],
})

// Log queries to backend only (not client)
prisma.$on('query', (e) => {
  console.log('Query executed:', {
    query: e.query,
    duration: e.duration,
    timestamp: e.timestamp,
  })
})

// Use transactions for data integrity
async function transferFunds(fromId, toId, amount) {
  return await prisma.$transaction(async (tx) => {
    // Deduct from source account
    const fromAccount = await tx.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } },
    })
    
    if (fromAccount.balance < 0) {
      throw new Error('Insufficient funds')
    }
    
    // Add to target account
    await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    })
    
    // Record transaction
    await tx.transaction.create({
      data: {
        fromAccountId: fromId,
        toAccountId: toId,
        amount,
        status: 'COMPLETED',
      },
    })
  })
}

Secure Entity Framework Usage (C#/.NET)

// Configure with secure options
services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(
        Configuration.GetConnectionString("DefaultConnection"),
        sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null);
            sqlOptions.CommandTimeout(30);
        }
    ));

// Use parameterized LINQ queries
public async Task<UserDTO> GetUserDataAsync(int userId)
{
    // Safe: Parameters handled securely
    var user = await _context.Users
        .Where(u => u.Id == userId)
        .Select(u => new UserDTO
        {
            Id = u.Id,
            Name = u.Name,
            Email = u.Email
        })
        .FirstOrDefaultAsync();
        
    return user;
}

// Use explicit loading for sensitive relationships
public async Task<IActionResult> GetUserWithRoles(int userId)
{
    var user = await _context.Users.FindAsync(userId);
    
    if (user == null)
        return NotFound();
    
    // Only load specific data if authorized
    if (User.IsInRole("Admin"))
    {
        await _context.Entry(user)
            .Collection(u => u.Roles)
            .LoadAsync();
    }
    
    return Ok(user);
}

Additional Resources

Clone this wiki locally