Database Security

Database security protects against unauthorized access, data breaches, and malicious attacks through multiple layers of defense.

Security Objectives (CIA Triad):

1. Confidentiality: Only authorized users access data
   Prevent: Unauthorized disclosure, eavesdropping

2. Integrity: Data accuracy and consistency
   Prevent: Unauthorized modification, corruption

3. Availability: Authorized users can access when needed
   Prevent: DoS attacks, system failures

Security Layers:

1. Network Security: Firewalls, VPNs, encryption
2. OS Security: User accounts, file permissions
3. Database Security: Authentication, authorization, auditing
4. Application Security: Input validation, secure coding

Key Threats:

• SQL Injection: Malicious SQL in user input
• Unauthorized Access: Weak passwords, stolen credentials
• Privilege Escalation: Gaining higher access than authorized
• Insider Threats: Malicious employees
• Data Leakage: Unencrypted data transmission
• DoS/DDoS: Overwhelming system with requests

Example 1: SQL Injection Attack & Prevention

Vulnerable Login (PHP):
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
$result = mysqli_query($conn, $query);

Attack:
Username: admin' OR '1'='1
Password: anything

Query becomes:
SELECT * FROM users WHERE username='admin' OR '1'='1' AND password='anything'
(Always true, logs in as admin!)

Secure Version (Prepared Statement):
$stmt = $conn->prepare("SELECT * FROM users WHERE username=? AND password=?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$result = $stmt->get_result();

Example 2: RBAC Implementation

-- Create roles
CREATE ROLE sales_rep;
CREATE ROLE sales_manager;
CREATE ROLE admin;

-- Grant permissions to roles
GRANT SELECT ON Customers TO sales_rep;
GRANT SELECT, INSERT ON Orders TO sales_rep;

GRANT SELECT ON Customers TO sales_manager;
GRANT SELECT, INSERT, UPDATE ON Orders TO sales_manager;
GRANT SELECT ON SalesReport TO sales_manager;

GRANT ALL PRIVILEGES ON DATABASE company TO admin;

-- Assign users to roles
GRANT sales_rep TO john, mary;
GRANT sales_manager TO alice;
GRANT admin TO bob;

-- Role hierarchy (manager inherits rep permissions)
GRANT sales_rep TO sales_manager;

Example 3: Encryption

-- Column-level encryption (SQL Server)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

CREATE CERTIFICATE SSNCert
WITH SUBJECT = 'SSN Encryption Certificate';

CREATE SYMMETRIC KEY SSNKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE SSNCert;

-- Encrypt data
OPEN SYMMETRIC KEY SSNKey
DECRYPTION BY CERTIFICATE SSNCert;

INSERT INTO Employees (Name, SSN_Encrypted)
VALUES ('John Doe', EncryptByKey(Key_GUID('SSNKey'), '123-45-6789'));

CLOSE SYMMETRIC KEY SSNKey;

-- Decrypt data
OPEN SYMMETRIC KEY SSNKey
DECRYPTION BY CERTIFICATE SSNCert;

SELECT Name, CONVERT(VARCHAR, DecryptByKey(SSN_Encrypted)) AS SSN
FROM Employees;

CLOSE SYMMETRIC KEY SSNKey;

Example 4: Auditing

-- Enable auditing for sensitive table (PostgreSQL)
CREATE TABLE audit_log (
audit_id SERIAL PRIMARY KEY,
table_name VARCHAR(50),
action VARCHAR(10),
user_name VARCHAR(50),
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
old_data JSON,
new_data JSON
);

-- Trigger to log changes
CREATE OR REPLACE FUNCTION log_salary_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, action, user_name, old_data, new_data)
VALUES ('Salaries', 'UPDATE', current_user,
row_to_json(OLD), row_to_json(NEW));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_audit
AFTER UPDATE ON Salaries
FOR EACH ROW EXECUTE FUNCTION log_salary_changes();

Best Practices:

1. Defense in Depth:

   • Multiple security layers
   • Firewall + Authentication + Authorization + Encryption + Auditing
   • If one fails, others still protect

2. Principle of Least Privilege:

   • Grant minimum necessary permissions
   • Application account: Only needed tables
   • Read-only users: No write permissions

3. Input Validation:

   • Validate all user input
   • Use parameterized queries
   • Whitelist, not blacklist

4. Regular Security Audits:

   • Review user permissions
   • Check audit logs for anomalies
   • Penetration testing

5. Encryption:

   • Encrypt sensitive data at rest
   • Use SSL/TLS for connections
   • Secure key management

6. Patch Management:

   • Keep database software updated
   • Apply security patches promptly

7. Monitoring & Alerting:

   • Monitor failed login attempts
   • Alert on unusual access patterns
   • Real-time intrusion detection

Compliance Standards:

• GDPR: EU data protection
• HIPAA: Healthcare data (US)
• PCI DSS: Credit card data
• SOX: Financial reporting

Real-World Scenarios:

• Healthcare: Encrypt patient records, audit all access
• Banking: MFA, transaction auditing, encryption
• E-commerce: PCI DSS compliance, secure payment data
• Government: MAC for classified data