Database Security & User Management

Database systems are the bedrock of modern information technology, storing vast quantities of critical data ranging from financial records and personal health information to intellectual property and operational metrics. Database security refers to the processes, tools, and controls used to safeguard database systems from threats and vulnerabilities.

The consequences of a data breach can be catastrophic: financial losses, legal penalties, reputational damage, and regulatory non-compliance with frameworks such as GDPR, HIPAA, and SOX. A robust database security posture uses a multi-layered defense-in-depth strategy, acknowledging that no single measure is foolproof.

The CIA Triad

Essential terms for understanding database security at the university level.

Access control models dictate how permissions are managed and enforced within a database system. Four primary models are used across the industry.

Comparison of Models

SQL provides the GRANT and REVOKE statements to manage access control, forming the basis of DAC in relational databases. These statements are fundamental to implementing the Principle of Least Privilege.

Object Privileges

-- Grant specific object privileges
GRANT SELECT ON Employees TO john_doe;
GRANT INSERT ON Orders TO sales_app;
GRANT UPDATE ON Employees (salary, department_id) TO hr_manager;
GRANT DELETE ON Temp_Records TO cleanup_job;
GRANT REFERENCES ON Departments TO hr_app;
GRANT EXECUTE ON CalculateBonus TO payroll_user;
GRANT ALL PRIVILEGES ON Products TO admin_user;

System Privileges

-- System-level privileges
GRANT CREATE TABLE TO dev_user;
GRANT CREATE USER TO security_admin;
GRANT BACKUP DATABASE TO backup_operator;

Role Creation and Management

-- Create a role and assign privileges
CREATE ROLE Sales_Manager;
GRANT SELECT, INSERT, UPDATE ON Orders TO Sales_Manager;
GRANT EXECUTE ON GenerateSalesReport TO Sales_Manager;

-- Assign role to users
GRANT Sales_Manager TO alice;
GRANT Sales_Manager TO bob;

-- Revoke role from a user
REVOKE Sales_Manager FROM alice;

WITH GRANT OPTION & Cascading Revoke

-- Carol can now re-grant this privilege to others
GRANT SELECT ON Customers TO carol WITH GRANT OPTION;

-- Carol grants to David
-- GRANT SELECT ON Customers TO david;  (executed by Carol)

-- Revoking from Carol cascades to David
REVOKE SELECT ON Customers FROM carol CASCADE;

-- Final state:
-- Alice: no privileges
-- Bob: INSERT on Orders
-- Carol: no privileges

SQL Injection (SQLi) is one of the most prevalent and dangerous web application vulnerabilities, often leading to full data compromise. It exploits input validation flaws to inject malicious SQL code.

Classic SQL Injection

Occurs when user-supplied input is directly concatenated into a SQL query without sanitization.

-- Vulnerable query construction
SELECT * FROM Users
WHERE username = 'admin' OR '1'='1'
AND password = 'any';
-- '1'='1' is always true, bypassing authentication

Union-Based Injection

-- Attacker appends UNION SELECT to extract data from other tables
SELECT name, description FROM Products WHERE id = 1
UNION SELECT credit_card_number, cvv FROM CreditCards;

Vulnerable vs. Secure Code

# VULNERABLE: Direct concatenation
query = f"SELECT * FROM Users "
        f"WHERE username = "
        f"'{username}' "
        f"AND password = "
        f"'{password}'"
cursor.execute(query)

# SECURE: Parameterized query
query = "SELECT * FROM Users "
        "WHERE username = %s "
        "AND password = %s"
cursor.execute(query,
  (username, password))

Defense Techniques

Encryption is a critical control for protecting data confidentiality, both when it is stored and when it is being transmitted. The security of encrypted data depends entirely on the security of its encryption keys.

Transparent Data Encryption (TDE)

TDE encrypts entire database files at rest without requiring application modifications. It uses a key hierarchy: a TDE Protector (master key) encrypts a database encryption key (DEK), which encrypts the data pages. TDE typically adds 2-10% CPU overhead depending on workload and hardware.

Key Management

Hardware Security Modules (HSMs) are dedicated physical devices for securely storing and managing cryptographic keys with tamper resistance. Key Management Systems (KMS) provide centralized software-based key management. Best practices include separating keys from encrypted data, regular key rotation, and secure backup procedures.

Auditing and monitoring are essential for accountability, compliance, and detecting security incidents. An audit trail is a chronological record of database activities, capturing who did what, when, where, and how.

What to Audit

Compliance Frameworks

What is the difference between authentication and authorization?

Authentication verifies who you are (e.g., username/password). Authorization determines what you are allowed to do after you have been authenticated (e.g., SELECT data, UPDATE records). Authentication always comes first; authorization builds on a verified identity.

Why are parameterized queries considered the best defense against SQL injection?

Parameterized queries separate the SQL code structure from the user-supplied data. The database treats parameters purely as data values, preventing them from being interpreted as executable SQL commands. This neutralizes injection attempts regardless of input content.

What is Transparent Data Encryption (TDE) and when should it be used?

TDE encrypts entire database files at rest (data files, log files, backups) transparently to applications. It should be used to protect data on disk from unauthorized access if the physical storage medium is compromised, especially for compliance requirements. It does not protect data in memory or in transit.

How does Role-Based Access Control (RBAC) simplify database security management?

RBAC simplifies management by creating an intermediary layer of roles. Instead of granting individual privileges to many users, privileges are assigned to roles, and users are assigned to roles. This streamlines provisioning, de-provisioning, and privilege changes, especially in large organizations.

What is the Principle of Least Privilege (PoLP) and why is it important?

PoLP dictates that users, applications, or processes should be granted only the minimum necessary permissions to perform their specific tasks, and no more. It limits the potential damage from a compromised account or application, reducing the attack surface and impact of breaches.

What are the key considerations for managing encryption keys?

Key management involves generating, storing, distributing, rotating, and revoking keys securely. Key considerations include using Hardware Security Modules (HSMs) or dedicated Key Management Systems (KMS), ensuring keys are stored separately from encrypted data, implementing regular key rotation, and having robust backup and recovery procedures for keys.