Database Security
Database security encompasses the policies, mechanisms, and controls that protect a DBMS and its data from unauthorized access, modification, or destruction. It addresses three fundamental goals: confidentiality (data is accessible only to authorized users), integrity (data remains accurate and consistent), and availability (the system is accessible when needed).
Access Control Models
Discretionary Access Control (DAC)
The data owner grants and revokes privileges to other users. Standard SQL GRANT/REVOKE mechanism.
GRANT SELECT, INSERT ON employees TO hr_role;
REVOKE DELETE ON employees FROM intern_role;Mandatory Access Control (MAC)
System-enforced classification levels (e.g., Top Secret, Secret, Confidential, Unclassified). Users have clearance levels (subjects); data has classification labels (objects). Based on the Bell-LaPadula Model — a formal state machine with “secure states”:
- Simple Security Property (ss-property) — “No Read Up”: A subject cannot read data at a higher classification
- Star Property (*-property) — “No Write Down”: A subject cannot write data to a lower classification (prevents leaking classified information)
- Tranquility Principle — Security levels cannot change during an active access session
Limitations: Bell-LaPadula focuses only on confidentiality (not integrity or availability), uses static policies, and can create usability challenges in practice.
Role-Based Access Control (RBAC)
Permissions are assigned to roles, and users are assigned to roles. Simplifies administration for large organizations.
MySQL full RBAC lifecycle:
-- Create user
CREATE USER 'analyst'@'%' IDENTIFIED BY 'secure_password';
-- Grant privileges
GRANT SELECT, INSERT ON sales_db.* TO 'analyst'@'%';
-- Inspect grants
SHOW GRANTS FOR 'analyst'@'%';
-- Revoke privileges
REVOKE INSERT ON sales_db.* FROM 'analyst'@'%';
-- Drop user
DROP USER 'analyst'@'%';Attribute-Based Access Control (ABAC)
Access decisions based on attributes of the user, resource, action, and environment (e.g., “department = Finance AND time = business_hours”). More flexible than RBAC for complex policies.
Data Protection Techniques
Encryption
| Scope | Algorithm / Protocol | Description |
|---|---|---|
| At rest | AES-256 | Data encrypted on disk. Protects against physical theft. |
| In transit | TLS/SSL | Client-DBMS and inter-node communication. Uses hybrid encryption (asymmetric key exchange → symmetric data encryption). |
| Column-level | AES, application-managed | Encrypt specific sensitive columns (SSN, credit card). Minimizes performance overhead. |
| Transparent Data Encryption (TDE) | DBMS-managed | Encrypts at storage level without application changes; automates encrypt/decrypt within the DBMS. |
| Elliptic Curve (ECC) | ECC | Stronger security with shorter key lengths; efficient for mobile/wireless/IoT. |
Python encryption example (encrypt then store in SQLite):
from cryptography.fernet import Fernet
key = Fernet.generate_key() # Store this securely!
cipher = Fernet(key)
encrypted = cipher.encrypt(b"sensitive data")
# Store `encrypted` in database
# Retrieve and decrypt:
decrypted = cipher.decrypt(encrypted)DBMS-Specific Security
| DBMS | Built-in Security |
|---|---|
| PostgreSQL | Full RBAC, row-level security, SSL/TLS, pg_hba.conf for host-based auth |
| MySQL | RBAC, TDE (Enterprise), SSL, audit plugin |
| SQLite | No built-in user management — security managed at file system level. Encryption via extensions: SQLCipher (open-source) or SQLite Encryption Extension (SEE) (commercial). Access control must be handled by the application. |
Data Masking and Redaction
- Static masking — Replace sensitive data in non-production copies (test databases)
- Dynamic masking — Real-time transformation of query results based on user role (e.g., show
***-**-1234for SSN) - Redaction — Remove or obscure sensitive fields entirely
SQL Injection Prevention
SQL injection occurs when untrusted input is concatenated into SQL queries:
-- VULNERABLE
query = "SELECT * FROM users WHERE name = '" + user_input + "'"
-- If user_input = "'; DROP TABLE users; --" → catastrophic
-- SAFE: parameterized queries
query = "SELECT * FROM users WHERE name = ?"
execute(query, [user_input])Prevention:
- Parameterized queries / prepared statements (always)
- Input validation — Whitelist expected patterns
- Least privilege — Application DB user has minimal permissions
- Views — Expose only necessary data
- ORMs — Abstract away raw SQL construction
Auditing and Monitoring
- Audit logs — Record who accessed what data, when, and what changes were made
- Real-time monitoring — Alert on suspicious patterns (unusual queries, bulk data access, off-hours access)
- Database activity monitoring (DAM) — Network-level or agent-based monitoring independent of DBMS logs
Threat Categories
| Type | Examples |
|---|---|
| Accidental | Misconfiguration, application bugs, human error |
| Malicious | SQL injection, privilege escalation, data exfiltration, ransomware |
| Infrastructure | Network attacks, physical access, side-channel attacks |
DBA Security Responsibilities
- Manage user accounts and roles (principle of least privilege)
- Configure authentication mechanisms (LDAP, Kerberos, certificates)
- Maintain encryption and key management
- Schedule and verify backups
- Apply security patches promptly
- Conduct regular access audits
- Implement and test disaster recovery plans
Data Governance
- Data stewardship — Assign ownership and accountability for data quality
- Metadata management — Catalog what data exists, where, and who owns it
- Compliance — GDPR, HIPAA, SOX, PCI-DSS impose specific requirements on data handling, retention, and access
- Data quality — Validation rules, deduplication, consistency checks