Database Security: Encryption, Auditing & Access Control

Imagine you're a developer at a fintech startup. Your database holds millions of customer records, including credit card numbers and personal details. One day, a junior developer accidentally exposes the database credentials in a public GitHub repo. Panic ensues. How do you prevent a breach? This is where database security comes in.

Database security isn't just about passwords. It's a multi-layered approach that includes encryption (so even if data is stolen, it's unreadable), auditing (so you can trace any suspicious activity), and access control (so only the right people have the right permissions). In this tutorial, you'll learn how to implement these three pillars using practical SQL and configuration examples. We'll cover encryption at rest and in transit, setting up fine-grained access control with roles, and enabling auditing to log all queries. By the end, you'll be able to harden your database against common threats and comply with regulations like GDPR and PCI-DSS.

1. Encryption at Rest: Protecting Stored Data

Encryption at rest ensures that data stored on disk is unreadable without the proper decryption keys. This protects against physical theft of drives, unauthorized access to backup files, and cloud provider breaches. Most modern databases offer Transparent Data Encryption (TDE), which automatically encrypts data as it is written to disk and decrypts it when read by authorized users. For example, in MySQL 8.0, you can enable TDE using the InnoDB tablespace encryption feature. Here's how to encrypt an existing table:

First, ensure the encryption plugin is active. Then, set the default encryption algorithm. Finally, alter the table to enable encryption. The key is managed by the database or an external key management system. Always back up your encryption keys separately; losing them means losing your data.

-- Enable InnoDB tablespace encryption (MySQL 8.0)
INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
SET GLOBAL keyring_file_data = '/var/lib/mysql-keyring/keyring';

-- Encrypt an existing table
ALTER TABLE customers ENCRYPTION='Y';

2. Encryption in Transit: Securing Network Traffic

Data in transit is vulnerable to interception via man-in-the-middle attacks. Encryption in transit, typically using TLS/SSL, ensures that all communication between the database client and server is encrypted. Enable TLS on your database server and require clients to use SSL connections. For PostgreSQL, you can configure ssl = on in postgresql.conf and place the certificate files. For MySQL, set require_secure_transport = ON. Here's how to create a user that must connect via SSL:

This forces the user to use an encrypted connection. You can also enforce SSL for all connections by setting the system variable. Always use strong cipher suites and regularly update certificates.

-- MySQL: Create user that requires SSL
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password' REQUIRE SSL;
GRANT SELECT, INSERT ON mydb.* TO 'app_user'@'%';

-- Verify SSL is enabled
SHOW VARIABLES LIKE '%ssl%';

3. Role-Based Access Control (RBAC): Least Privilege

Access control ensures that users and applications have only the permissions they need. RBAC simplifies management by grouping permissions into roles. Create roles for different job functions (e.g., read_only, data_entry, admin) and assign users to those roles. Avoid using the root or superuser account for routine operations. Here's an example in PostgreSQL:

This creates a read-only role and grants it to a user. The user can only select from tables in the schema. In MySQL, roles work similarly. Always revoke unnecessary privileges and regularly audit role memberships.

-- PostgreSQL: Create read-only role and assign to user
CREATE ROLE read_only;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

CREATE USER jane WITH PASSWORD 'securepass';
GRANT read_only TO jane;

4. Auditing: Tracking Database Activity

Auditing records all database events for security analysis and compliance. It helps detect unauthorized access, SQL injection attempts, and policy violations. Most databases provide built-in auditing or plugins. For MySQL, use the audit log plugin. For PostgreSQL, use the pgAudit extension. Here's how to enable basic auditing in MySQL:

This logs all queries to a file. You can filter by user, database, or event type. In PostgreSQL, pgAudit allows fine-grained logging. Regularly review audit logs and set up alerts for suspicious patterns like failed logins or unusual query volumes.

-- Install audit log plugin (MySQL)
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'JSON';

-- Check audit log status
SHOW VARIABLES LIKE 'audit_log%';

5. Column-Level Encryption: Protecting Sensitive Fields

Sometimes you need to encrypt specific columns, like credit card numbers or social security numbers, even from database administrators. Column-level encryption uses application-side encryption or database functions. For example, PostgreSQL's pgcrypto extension provides encryption functions. Here's how to encrypt a column using AES-256:

This encrypts the credit_card column with a key. The key must be stored securely, ideally in a vault. Decryption requires the same key. This approach protects data even if the database is compromised, but it adds complexity to queries (you cannot search directly on encrypted data without decryption).

-- PostgreSQL: Encrypt a column using pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Insert encrypted data
INSERT INTO payments (user_id, credit_card_encrypted)
VALUES (1, pgp_sym_encrypt('4111111111111111', 'encryption_key'));

-- Decrypt when needed
SELECT pgp_sym_decrypt(credit_card_encrypted, 'encryption_key') AS credit_card
FROM payments WHERE user_id = 1;

6. Security Best Practices: Putting It All Together

A secure database requires a holistic approach. Combine encryption, access control, and auditing with other best practices: - Regularly update database software to patch vulnerabilities. - Use strong, unique passwords and enforce password policies. - Implement network segmentation: place databases in private subnets. - Use database firewalls or proxy servers to filter queries. - Conduct regular security audits and penetration testing. - Backup encryption keys and test recovery procedures. - Monitor for anomalies using automated tools.

Remember, security is a process, not a one-time setup. Stay informed about new threats and update your defenses accordingly.

-- Quick security health check (MySQL)
-- Check for users with weak passwords
SELECT user, host, authentication_string FROM mysql.user WHERE authentication_string = '';

-- Check for anonymous accounts
SELECT user, host FROM mysql.user WHERE user = '';

-- Check for users with global privileges
SELECT user, host, Super_priv FROM mysql.user WHERE Super_priv = 'Y';